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

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?

share|improve this question

2 Answers 2

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)

share|improve this answer
    
Thanks Erik, I had run top before posting and the load seems pretty low but some queries can shoot up to 90% or more, which I'm guessing are the resource intensive Cron jobs I have running which may have been causing congestion but as you point out, MySQL should not be dropping INSERTs. The code is quite old so I'm not really catching exceptions as such so looks like I'll have to implement this. Thanks. –  Swanny Aug 8 '11 at 14:30

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.
share|improve this answer

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.