CODEWITHSUNDEEP
mysql
mckeegan375
mckeegan375

Reputation: 255

MySQL Connection Limit Advice

I've hit a problem with my MySQL queries and was hoping someone could offer some help/advice.

I'm developing a PHP-based system which combines quite a lot of data in different tabs on one page (tab1 = profile, tab2 = address, tab3 = payments etc.) and as a result, one page can have up to 34/40 MySQL queries pulling from different tables or with different criteria.

The page load became really slow and I asked my web host if they knew what was wrong and they advised it's because of slow MySQL queries (some over 2 seconds). They also said that my MySQL user is only allowed 15 connections at a time.

If my page has 40 queries and only 15 connections are allowed at a time, does this mean they effectively queue and wait for one to complete? If this was the case then I can understand why the page is taking a while to load but i'm not sure of the solution. Is 15 MySQL queries considered a lot or is this quite a tight restriction by my host (HostMonster)?

Also, if there were 15 users accessing the system at the same time, would this 15 connections be split between each of them or is it 15 connections per user logged into the site? I assume they mean per database user but all people who access the system will be using the same database user so it seems impossible to create a system in which several users can access at one?

The whole connections thing has confused me a little.

Thanks in advance for any help!

Upvotes: 0

Views: 230

Answers (2)

Masood Alam
Masood Alam

Reputation: 415

If having max connect error problem then use this command from command line

mysqladmin flush-hosts  -uuser -p'password'

this will flush hosts that MySQL has recorded and will build the list again. In the newer version of MYSQL 5.6 you get more information on this but not on previous version.

You can set the following

max_connect_errors 10000

to avoid the message to appear again.

15 queries or connection is not a problem at all, in busy databases we have seen thousand connections and tens of thousands of queries per second.

Upvotes: 0

Ed Heal
Ed Heal

Reputation: 60007

  1. Have one connection per page. Put the queries into sequence
  2. Optimize those queries - see explain and use indexes
  3. Perhaps combine queries to reduce the through put.

BTW 10+ queries per page is excessive IMHO.

Upvotes: 2

Related Questions