

How do you optimize a MySQL database for writes? - jongs

I have a write intensive application running on EC2. Any thoughts on how to optimize it to be able to make several thousands concurrent writes on the MySQL DB?
======
srini1234
Firstly, write scaling is a hard problem. Secondly, secret to write scaling is
in read scaling. That is, cache reads as much as possible, so that the writes
get all the throughput.

Having said that, there are a bunch of things one can do:

1) Start with the data model. Design a data model so that you do not ever
delete or update a table. Only operation is an insert. Use Effective Date,
Effective Sequence and Effective Status to implement Insert, Update and Delete
operations using just the Insert Command. This concept is called Append Only
model. Checkout RethinkDB..

2) Set the Concurrent Insert flag to 1. This makes sure that the tables keep
inserting while reads are in progress.

3) When you have only Inserts at the tail, you may not need row-level locks.
So, use MyISAM (this is not to take anything away from InnoDB, which I will
come to later).

4) If all this does not do much, create a replica table in Memory Engine. If
you have a table called MY_DATA, create a table called MY_DATA_MEM in memory
table.

5) Redirect all Inserts to the MEM table. Create a View that UNIONS both
tables and use that view as your Read Source.

6) Write a daemon that periodically moves MEM contents to the Main table and
deletes from the Mem table. It may be ideal to implement the MOVE operation as
a Delete trigger on the Mem table (I am hoping triggers are possible on Memory
Engine, not entirely sure).

7) Do not do any deletes or Updates on the MEM table (they are slow) also pay
attention to the cardinality of the keys in your table (HASH vs B-Tree : Low
Card -> Hash, High Card-> B-Tree)

8) Even if all the above does not work, ditch jdbc/odbc. Move to InnoDB and
use Handler Socket interface to do the direct inserts (Google for Yoshinori-
San MySQL)

I have not used the HS myself, but the benchmarks are impressive. There is a
even Java HS Project on Google Code.

Hope that helps..

------
carbocation
You could start by turning off indexing. Not sure what your use case is here,
so that may or may not be reasonable.

~~~
jongs
Indexing is on but my understanding is that it only help reading, not writing
on the DB

~~~
carbocation
What you say is true; indexing does not make writes faster. In fact, it makes
writing to the DB _slower_ , which is why I suggested to turn it off. Now,
there may be extenuating circumstances (e.g., you also read from this DB)
which make this decision more difficult.

~~~
jongs
Why would it make it slower?

~~~
carbocation
Instead of just inserting a record, it now has to do something else, too
(update the index).

Try taking a look at
[http://kevin.vanzonneveld.net/techblog/article/improve_mysql...](http://kevin.vanzonneveld.net/techblog/article/improve_mysql_insert_performance/)
, which suggests some things you can try. This might be a question better
suited for StackOverflow, by the way.

~~~
jongs
thanks

------
imp
On EC2, I increased write throughput on one table by 3X by mounting 2
additional EBS volumes to the server and partitioning the table across the
three volumes. That may not be as beneficial to you though if you have many
different tables that are being written to.

