MySQL Process List – improve performance – lock issue-Collection of common programming errors

2 years ago
admin
3 minutes

In the SHOW PROCESSLIST – it is showing about 30+ rows has been locked for a few seconds and then update.

Is there a way to speed it up faster to update?

Example:

+------+-----------------+-----------+----------------+---------+------+----------+-------------------------------------------------------------------------------
| Id   | User            | Host      | db             | Command | Time | State    | Info                                                                          
+------+-----------------+-----------+----------------+---------+------+----------+--------------------------------------------------------------------------------
|  265 | user            | localhost | xxxxxxxxxxxxxx | Query   |   15 | Updating | UPDATE data SET status = '1', error = 'Unknown error'  WHERE f= 0xxxxx    
|  269 | user            | localhost | xxxxxxxxxxxxxx | Query   |   17 | Updating | UPDATE data SET status = '1', error = 'Invalid ....'  WHERE f= 0xxx 
|  280 | user            | localhost | xxxxxxxxxxxxxx | Query   |    7 | Updating | UPDATE data SET status = 1  WHERE f = 0xxxx                                           
|  300 | user            | localhost | xxxxxxxxxxxxxx | Query   |    1 | Updating | UPDATE data SET status = '1', error = 'Unknown ....'  WHERE f= 0xx             
|  314 | user            | localhost | xxxxxxxxxxxxxx | Query   |   13 | Updating | UPDATE data SET status = '1', error = 'Invalid....'  WHERE f= 0xxxx
|  327 | user            | localhost | xxxxxxxxxxxxxx | Query   |   11 | Updating | UPDATE data SET status = '1', error = 'Unknown ....'  WHERE f= 0xxxx               
|  341 | user            | localhost | xxxxxxxxxxxxxx | Sleep   |    2 |          | NULL                                                                                        
|  350 | user            | localhost | xxxxxxxxxxxxxx | Query   |    7 | Updating | UPDATE data SET status = '1', error = 'Unknown ....'  WHERE f= 0xxx                
|  360 | user            | localhost | xxxxxxxxxxxxxx | Query   |    5 | Updating | UPDATE data SET status = 1  WHERE f = 0xxxx

There are a lot of UPDATE – I am using InnoDB, some fields are index.

In the data table, there are over 500,000 rows need to be updated while it looping in PHP CLI script (running in background/process in Linux).

    • Make sure the f column is indexed so the WHERE clause is using it. Otherwise you’ll scan the whole table for every UPDATE.

    • Try to group updates so you can set many rows with one UPDATE instead of one row at a time.

    • Make sure you’re not using autocommit. Try to execute multiple changes per transaction.

    • Make sure you have tuned InnoDB. Most people use the default values for innodb_buffer_pool_size, innodb_log_file_size, and innodb_io_capacity. The default values are not tuned for high performance.

    • Set innodb_flush_log_at_trx_commit=2 to reduce fsyncs, as @Paulo H. suggests.

  2. InnoDB uses IO in every INSERT/UPDATE to ensure de ACID property, you can change this behavior defining innodb_flush_log_at_trx_commit variable to 2, so MySQL uses IO every second instead of every commit (pretty good in many cases).

    More info here

  3. A quick fix is to strategically add a sleep() call, so as you step through several thousand rows you don’t overwhelm the database. A good practice is to sleep for 1/100th of a second for every row, then sleep for a full 5 seconds for every 100 rows. This is a general rule with the systems I am involved with, so you may need to tinker with these settings a bit to get them to work for you.

    One concern I have is your user table storing text value for the error column. Text columns are not as performant as int columns, not to mention if you ever need to update an error value, you may affect several thousand records.

  4. InnoDB is the correct engine for what you’re doing as it locks the row, not the whole table. I’d recommend ensuring that you’ve properly indexed your table, you’re using correct column types and that they’re as small as they need to be (so something that’s either true or false should be stored as an unsigned int 1).

Originally posted 2013-11-10 00:14:46.