homepage Welcome to WebmasterWorld Guest from 54.237.98.229
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Where is my database bottleneck?
mixart

5+ Year Member



 
Msg#: 4189600 posted 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

WebmasterWorld Senior Member whoisgregg us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4189600 posted 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

5+ Year Member



 
Msg#: 4189600 posted 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