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 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?




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 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?



