homepage Welcome to WebmasterWorld Guest from 54.167.173.250
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Where is my database bottleneck?
mixart




msg:4189602
 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




msg:4189766
 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




msg:4189887
 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved