How to improve MySQL INSERT and UPDATE performance?

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?

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:

• SHOW INNODB STATUS walk through
• What to tune in MySQL Server after installation
• Innodb Performance Optimization Basics
• How to calculate a good InnoDB log file size
• Choosing innodb_buffer_pool_size
• Choosing proper innodb_log_file_size

With the default innoDB config you'll be limited to how fast you can write and flush transactions to disk. If you can deal with losing a little ACID, experiment with innodb_flush_log_at_trx_commit. Set to 0 to write and flush the log to disk about every second. Set to 1 (default) to write and flush on every commit. Set to 2 to write to the log file after every commit but flush only once per second.

If you can deal with losing 1s of transactions, this can be a great way to vastly improve write performance.

Also, pay attention to what your disks are doing. RAID 10 > RAID 5 for writes at the cost of an extra disk.

Locking issues will be exampled by the connection statuses in show full processlist;

Read through the my.cnf and MySQL documentation. Configuration options are very well documented.

Generally speaking, you want as much to be processed in memory as possible. For query optimization, that means avoiding temporary tables. Proper application of indexes.

Tuning is going to be specific to your preferred database engine and application architecture. There's substantial resources pre-existing an Internet search away.

• MySQL Documentation
• What to tune in MySQL Server after installation

Switching on the Innodb monitors can help identify the causes of locks and deadlocks:

SHOW ENGINE INNODB STATUS and the InnoDB Monitors

InnoDB is a pretty good engine. However, it highly relies on being 'tuned'. One thing is that if your inserts are not in the order of increasing primary keys, innoDB can take a bit longer than MyISAM. This can easily be overcome by setting a higher innodb_buffer_pool_size. My suggestion is to set it at 60-70% of your total RAM. I am running 4 such servers in production now, inserting about 3.5 million rows a minute. They already have close to 3 Terabytes. InnoDB it had to be , because of the highly concurrent inserts. There are further ways to speed up inserts. And I've benchmarked some.