MySQL INSERT occasionally not inserting into DB

Question

I have an INSERT query which adds an order to the DB, it has been running on a production server successfully for a few years, today around 10% of these INSERTs have not appeared in the DB table, there have been no changes to the code that runs this query so I'm assuming this is performance based as the server is certainly under a heavier load recently.

The table is MyISAM with around 90,000 records running on a CentOS server.

Are there any troubleshooting methods I can use to see why the query is not inserting the data as I feel the server itself is the bottleneck?

Answer 1

High load would not cause MySQL to blindly drop INSERT queries. My guess is that your application is perhaps getting an error when connecting to the DB and as such, MySQL is never really receiving the INSERT query. I would recommend examining your code to make sure you're catching and logging DB errors correctly.

As an aside, have you actually checked to see if the server is experiencing high load. You say that this table has 90k records (which is actually quite small), but you don't give any figures as to how many queries/second the server is handling. Run top to watch the load average during peak usage, paying attention to not only the load average itself, but also what percentage of time processes are sitting in iowait (the %wa value in top)

Answer 2

I also supect that the code ignores an error during execution. Possible reasons:

• MySQL connection limit reached (see ErikA's answer). Basicly the server does not get the INSERT queries. Check the number of connections (threads_connected in "SHOW GLOBAL STATUS;" compared to max_connections).
• A parameter is wrong and the INSERT fails, but the error is ignored. Enable the MySQL Query Log to see if the server gets a query with wrong parameters
• The code fails, before it sends the query (e.g. due to magic parameter conversion). Check the php error log / web server error log.