Mysql – How to improve MySQL INSERT and UPDATE performance

innodbMySQLperformance

This question can probably be asked on StackOverflow as well, but I'll try here first…

Performance of INSERT and UPDATE statements in our database seems to be degrading and causing poor performance in our web app.

Tables are InnoDB and the application uses transactions. Are there any easy tweaks that I can make to speed things up?

I think we might be seeing some locking issues, how can I find out?

Best Answer

  1. Check if your hardware and OS is properly configured and tuned:

    • Source of problem (CPU/IO/Memory/Swap Usage). Do you have a lot of IOP's? Are CPU loaded? If you have a lot of read IOP's probably you don't have enough big InnoDB buffer_pool. If CPU is loaded probably your queries do full table scans instead of using proper indexes.
    • Disk/RAID/LVM setup. In some specific setups LVM striping could give you benefit by eqalizing disk load (no hardware RAID, multiple LUNS connected)
    • IO scheduler: when you have good hardware RAID controller, probably noop is the best. RedHat made some tests and they said, that for Oracle (and other DB) CFQ is best choice. You need to run some benchmarks (like tpc-c or tpc-e) and choose, what is best for your hardware.
    • Good filesystem - ext3 does not perform well in database specific workloads. Better is XFS or OCFS2. You need some benchmarks again.
    • Watch, if your system uses swap. Using swap degrades mysql performance.

  2. Check, if your MySQL/InnoDB instance is properly tuned:

    • buffer pool size - cache data pages in memory
    • innodb_flush_method=O_DIRECT - avoid double IO buffering
    • increase InnoDB log file size - for write intensive workload this could improve performance. But remember: bigger log file size means longer crash recovery. Sometimes in hours!!!
    • innodb_flush_log_at_trx_commit=0 or 2 - If you're not concern about ACID and can loose transactions for last second or two.
    • key_buffer_size - very important to MyISAM, but it is used for disk temporary tables.
    • Watch your INNODB STATUS
  3. Analyze your workload - catch all your queries to slowquery log and run mk-query-digest on it. You can catch all queries using tcpdump and maatkit
    • What queries takes most of your server time?
    • Are any temporary tables created, especially big temporary tables?
    • Learn, how to use explain
    • Is your application uses transactions? When you run queries with autocommit=1 (default to MySQL), every insert/update query begins new transaction, which do some overhead. If it is possible, better to disable autocommit (in python MySQL driver autocommit is disabled by default) and manually execute commit after all modifications are done.
    • Is your application makes series of inserts to the same table in a loop? Load data infile command is much faster for series of inserts.
    • Remember: select count(*) from table; is much slower for innodb than for myisam.
    • What types of INSERT/UPDATE queries take most of server time? How can they be optimized?
    • Check, if your DB has proper indexes and add them, if needed.

In our environment we had situation, that one type of update queries was slow. Estimated time to finish batch job was 2 days!!! After analyzing slowquery log we find, that this type of update query needs 4 seconds to complete. Query looked like this: update table1 set field1=value1 where table1.field2=xx table2.field3=yy and table2.field4=zz. After converting update query to select query and running explain on that select query we find, that this type of query doesn't use index. After creating proper index we had reduced update query execution time to miliseconds and whole job finished in less than two hours.

Some useful links:

Related Topic