Mysql/php Connection, Config, Index, Query, buffers, nfi im out of options

first of all, my main question:

What are some good troubleshooting techniques when you run into a variable problem regarding slow database access.

Background: I have a system that is handling multiple db connections so that I may use unbuffered queries on the main table select. I have an issue where I see a pause on a very simple query (select id,name,title,etc from working where non-unique-indexed-id limit 1) the system then either inserts if no record found or overwrites as an update.

I am seeing multi second pauses on these queries with no pauses on processing the same query in between.

Indexes, could be getting corrupt or taking too long to update (char(13)), unbuffered or buffered makes little difference on this point query.

This is happening on multiple tables (they are built and thrown away after a run has processed them, there is 开发者_Go百科around 30 different copies that have slightly different processes involved but the data stored is the same) at different times on different consecutive runs, time intervals on the wait is differing too.

Also, I am looking at the mysql processlist and not seeing this query waiting, this is making me believe its a transfer issue between php cli and mysql (same box)

Sorry if this makes little sense - my brain is absolutely fried and exhausted right now.

Has anyone come across anything like this before, if so, how did you track down the root cause?

I only recently noticed that mytop will poll at sub-second intervals, which can be rather revealing.

To me this sounds merely like table locking on MyISAM engine tables. You don't mention what engine your tables are running, though. Also what is your insert/update/select ratio? If you have a high rate of updates, using MySQLs query cache might be little little improvement.

What does an EXPLAIN tell you about the more expensive queries? You might need to adjust the way you've designed your indexes, or re-order your where clause in your query. If EXPLAIN gives you a full table scan or very high row count, you're probably not using your indexes as best you can.

Can you explain more about your non-unique-indexed-id? A SHOW CREATE TABLE might be helpful.

EDIT

If you've reduced your buffer size and seen improvement, that tells me that the time it takes to allocate memory in your case could be significant. If query performance is under 1 second, I would start to examine system performance, especially memory. I suggest starting with top, sorting by memory, and displaying swap space and number of page faults. Or running vmstat, and keep an eye out for swap activity. If you see high wait times, you might be waiting on disk throughput. (A switch to innodb without a performance change is interesting. If you were low on ram, I would think that a low memory situation would expose itself more rapidly.)

Is this a web application? If so, is there a correlation between the number of connections/threads, httpd connections and query latency? I've seen php baloon apache processes from 35MB to 512MB and larger, and subsequently the machine would grind into swap-land.

There is a possibility that you have a hardware issue, too. It's always worth the time to check dmesg to see if there are any kernel reports of memory or disk problems. Is there something else running on the system? Would you have any cron jobs that might be competing for disk or memory?