MySQL: How many UPDATES per second can an average box support?

Question

We've got a constant stream of simple updates to a single MySQL table (storing user activity information). Let's say we group these into batch updates each second.

I want a ballpark idea of when mysql on a typical 4-core 8GB box will start having an issue keeping up with the updates coming in each second. E.g. how many rows of updates can I make @ 1 per second?

This is a thought exercise to decide if I should get going with MySQL in the early days of our applications release (simplify development), or if MySQL's likely to bomb so soon as to make it not worth even venturing down that path.

Solution 1

The only way you can get a decent figure is through benchmarking your specific use case. There are just too many variables and there is no way around that.

It shouldn't take too long either if you just knock a bash script or a small demo app and hammer it with jmeter, then that can give you a good idea.

I used jmeter when trying to benchmark a similar use case. The difference was I was looking for write throughput for number of INSERTS. The most useful thing that came out when I was playing was the 'innodb_flush_log_at_trx_commit' param. If you are using INNODB and don't need ACID compliance for your use case, then changing it to 0. This makes a huge difference to INSERT throughput and will likely do the same in your UPDATE use case. Although note that with this setting, changes only get flushed to disk once per second, so if your server gets a power cut or something, you could lose a seconds worth of data.

On my Quad Core 8GB Machine for my use case:

• innodb_flush_log_at_trx_commit=1 resulted in 80 INSERTS per second
• innodb_flush_log_at_trx_commit=0 resulted in 2000 INSERTS per second

These figures will probably bear no relevance to your use case - which is why you need to benchmark it yourself.

Solution 2

A lot of it depends on the quality of the code which you use to push to the DB.

If you write your batch to insert a single value per INSERT request (i.e.,

INSERT INTO table (field) VALUES (value_1);
INSERT INTO table (field) VALUES (value_2);
...
INSERT INTO table (field) VALUES (value_n);

, your performance will crash and burn.

If you insert multiple values using a single INSERT (i.e.

INSERT INTO table (field) values (value_1),(value_2)...(value_n);

, you'll find that you could easily insert many records per second

As an example, I wrote a quick app which needed to add the details of a request for an LDAP account to a holding DB. Inserting one field at a time (i.e., LDAP_field, LDAP_value), execution of the whole script took 10's of seconds. When I concatenated the values into a single INSERT request, execution time of the script went down to about 2 seconds from start to finish. This included the overhead of starting and committing a transaction

Hope this helps

Solution 3

Its not easy to give a general answer to this question. The numbers you ask for rely heavily not only on the hardware of your database server, MySQL itself, but also on server/client configuration, network and - equally important - on your database/table design too.

Generally speaking, with a naked MySQL setup on a state-of-the-art server and update statements using unique keys, I don't have issues below 200 update-statementsp er second if I fire them from localhost, at least that's what I get on my six year old winxp test enviroment. A naked installation on a new system will scale this way higher. If you think way bigger, one server isn't the way to go. MySQL can be tweaked and scaled out in some ways, therefore many companies rely heavily on it.

Just some basics:

• If the fields you want to update have huge index files, the update statements are alot slower since each statement has to write not only data, but also index informations.
• If your update statement cannot use an index, it might take longer for the server to allocate the required fields it has to update.
• Slow memory and/or slow harddisks might also slow down overall server performance.
• Slow network connection slows down communication between client and server.

There are whole books written about it, so I'll stop here and advise some further reading, if you're interested!

Comments

[Roman Starkov]

The only problem is that this setting is global, so if you have one DB that needs it and one that doesn't, you have to install two instances of MySQL...

[NiCk Newman]

Wouldn't setting the innodb_flush_log_at_trx_commit to 0 result in a faster performance? Ho ware you getting 2000 inserts per second at level 2 when level 2 is the most ACID compliant?

[theon]

Yes, you're right. 0 is the setting that only flushes to disk every second. Updated. Thanks.