Where is my database bottleneck?

Forum Moderators: open

Message Too Old, No Replies

Where is my database bottleneck?

mixart

5:45 am on Aug 20, 2010 (gmt 0)

I have one MySQL database that is about 2 gig, 70 tables, 17million rows total.

I've checked my queries and slow query logs and there seems to be no big issues there. I've done explain on many queries and nothing real slow going on.

However, my database seems to take a while to respond at times. The execution times are fine, just takes time to respond. Some times pages on my website will just cause the browser to say "connecting..." for about 4-5 seconds before the page starts loading.

One other things I noticed:
When I just load the database in phpmyadmin, it takes about 3-5 seconds to just list all the tables and the database structure

Any ideas what might be causing this bottleneck? or ways I can try to improve this "listing of tables" in phpmyadmin to see if that's the cause?

It seems like perhaps it is taking a while to make a mysql connection or something?

whoisgregg

1:49 pm on Aug 20, 2010 (gmt 0)

I have a MySQL database that is the same number of rows, 42 tables, ~4 gig. The best performance boost I got was by tweaking settings in my.cnf to give more RAM to MySQL.

These happen to be my settings, you may find you need different settings to get the best performance out of your db:

max_heap_table_size=1G
tmp_table_size=1G
innodb_buffer_pool_size=2G
table_cache=1024
max_tmp_tables=128
key_buffer_size=256M
query_cache_size=32MB

Make sure you have enough RAM in the machine to use these settings. The machine with the settings above has 16 GB RAM.

mixart

4:41 pm on Aug 20, 2010 (gmt 0)

I appreciate you sharing these settings with me. I know that many of my values are not that high (I do however only have a total of 4Gig on my server).

I'll tweak some settings and see if it helps.