slushpuppie Posted May 27, 2009 Share Posted May 27, 2009 working a site that has quite a large database: 125 tables ~93,935,032 records 46.7G it's indexed quite well i believe, but sometimes it'll hit a query that's causing other queries to be queued up and brings the whole site down. they are queries that'll usually run just fine, but under some circumstances, which i believe are based on the serverload - it can't handle it. one of the common situations is when a table with 4.0M records needs to join with a table with 15.1M and another with 6.2M at the same time. the database is on it's own server, which is a pretty solid server. 4 processors: Intel® Xeon® CPU E5430 @ 2.66GHz 16G of ram nothing else served from it besides this database i know this isn't the most descriptive post, but is anyone out there running stuff on the same size/scope or larger? any tips/ideas/suggestions? Quote Link to comment Share on other sites More sharing options...
gassaz Posted May 27, 2009 Share Posted May 27, 2009 You shlould check some variable values of the server, variables like key buffer, sort buffer size. If you database type is InnoDb check variables like buffer pool size. Some times you can improve mysql server increasing this variables. Quote Link to comment Share on other sites More sharing options...
slushpuppie Posted May 27, 2009 Author Share Posted May 27, 2009 thanks! i will check out these values. the database is a mixture of innoDB and MyISAM. Quote Link to comment Share on other sites More sharing options...
fenway Posted June 2, 2009 Share Posted June 2, 2009 Those are some heavy queries... serious optimization would likely be needed here. Quote Link to comment Share on other sites More sharing options...
kickstart Posted June 2, 2009 Share Posted June 2, 2009 one of the common situations is when a table with 4.0M records needs to join with a table with 15.1M and another with 6.2M at the same time. What kind of joins? All the best Keith Quote Link to comment Share on other sites More sharing options...
michael624 Posted June 3, 2009 Share Posted June 3, 2009 that many tables is a lot. Maybe your table is not normalized. 125 tables is too much for even a bank. But you should check your settings limits. you should increase your limits of open connections and cache size limit. Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted June 3, 2009 Share Posted June 3, 2009 one of the common situations is when a table with 4.0M records needs to join with a table with 15.1M and another with 6.2M at the same time. Check your server settings as suggested. If it doesn't help then I'd start thinking of ways to reduce the load caused by those types of queries in particular. If you're managing a database of such magnitude, I'd imagine you already know this, but I'll toss it out there anyways. On those large table JOINs, if the number of join columns is not large, consider making indexes on just the columns involved in the joins if you don't have those indexes already. MySQL will plow through the JOIN conditions much faster if all the data it needs for the join is contained in an index. For example if 3 columns are joined and only two of them are in a usable index, then MySQL will still have to refer to the actual table to pull the third value to determine if a join is necessary. Are you having any problems with insert or update performance? If so then you may have too many indexes. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.