Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

My database (XAMPP) runs at almost 350 questions a second is 99% in the SELECT category. The website I'm running is heave on timed ajax calls that refresh data every 10 seconds. During this, there are numerous table joins involved all with proper indexing for each column. Normally, I load data (for a certain page) in less than 1.5 seconds with a refresh rate of 10 seconds for the next query.

Things get interesting during the end of the day: the once 1.5 second data load, has now reached almost 4.7 seconds. I've been recording the time it takes to load this certain page for almost a week and the number always seems to go up after a couple of hours of work. There are 6 years users in the company that sit in these automatic refresh pages and about 10 other users doing simple searches.

My CPU during this process goes through some interesting cycles. It will start a 20% and reach 100% within second and drops back to 20% percent after 3 seconds being at 100%. This strange cycle on begins when things start slowing down at the end of the day.

I've tried flushing tables, optimizing tables and tweak my mysql.ini files but with no luck. My lack of experience is what's keeping me back.

Any ideas on how to fix this?

Here are some stats thrown my way (phpMyAdmin) that are always in red and require attention:

Created tmp disk tables = 9

Handler read rnd 765

Handler read rnd next 43.6 k

Opened tables 88

Qcache free blocks 1.5 k

share|improve this question
    
From what it looks like, you don't have enough memory on the server. What's the memory usage like? –  Nathan C May 21 '13 at 19:55
    
I have 8GB of ram and constantly running at 2.9GB.. –  Dimitri May 21 '13 at 19:57
    
Take a look at this: dev.mysql.com/doc/refman/5.0/en/query-cache-configuration.html - it may have to do with the caching issue. From what it sounds like, your CPU drops once the results are cached in the server, then spikes again when the purge happens and you're hitting the server. –  Nathan C May 21 '13 at 20:00
    
Correction.....your database might not be configured to use a sufficient amount of memory. –  mdpc May 21 '13 at 20:03
    
Maybe the Database SE might be more appropriate for this question? –  mdpc May 21 '13 at 20:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.