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