Mysql – Fastest engine for Mass Updates (only updates done on table)

innodbmyisamMySQLstorage-engine

I have a table of 2 columns and 60 million rows. I want to update the table by incrementing one field by 1 on various different rows (This is the only type of operation that will ever be done on the table). The updates that occur happen all at once everytime, so I am usually updating 30-40 million out of the 60 million rows once a day. This table is only used by one user so I am not worried about multiple users trying to connect to the table at the same time or anything like that. Which engine (and parameters) would be fastest to perform just updates and nothing else on (well there will be one select statement once in a while, but I am not concerned about the performance of select statements)? I am currently using MyISAM as I am not worried about ACID, but it currently is quite slow for mass updates. Essentially I am trying to find the fastest way to do MASS Updates.

Best Answer

Your question is somewhat like a question I answered back on Sep 06, 2013 : mySQL for analytics and updating same row

In that answer, I recommended the following

Adding a third column to the table to represent a slot
Select an arbitrary value for the number of slots
Making a composite PRIMARY KEY
- hour boundary
- slot number
Doing an INSERT with ON DUPLICATE KEY UPDATE to increment the counter

Since you are doing 30-40 million updates, you may need to choose a very high number of slots. In your case, I would go with 250,000 slots. The high the number of slots, the lower the incidence of collision (trying to update the same slot).

As far as the choice of storage engine, you cannot stay with MyISAM. Why ? MyISAM will do a full table lock for every update. Doing 40 million updates require 40 million table locks. My posted answer works well with InnoDB's row level locking. Even if you implement the slot paradigm, MyISAM will still do 40 million table locks with 40 million updates.

Definitely, you must go with InnoDB. You also need to tune InnoDB.

Larger Buffer Pool (increasing innodb_buffer_pool_size)
Larger Log Buffer (increasing innodb_log_buffer_size to 128M)
More Write I/O Threads (setting innodb_write_io_threads to 8 or more)

If you are using MySQL 5.1, upgrade to MySQL 5.6.

Give it a Try !!!

UPDATE 2014-09-15 00:21 EDT

I learned the concept of slots for counters from page 140 of the Book

the Book

My post (dated Sep 06, 2013) is the first post in the DBA StackExchange to mention this book for your specific issue. Someone else mentioned this technique one month later in this same forum (counter table in mysql dates Oct 18, 2013)

I just tried googling for this concept search for mysql counter slot

I found some posts:

The main point I am making is that this concept is not new. You can implement it.

Give it a Try !!!

MyISAM ROW FORMAT

If the table is a lot of VARCHAR fields, I would love to suggest the following

For every MyISAM table you load, do this beforehand

ALTER TABLE mytable ROW_FORMAT=Fixed;

This will increase query performance up to 15-30% without changing anything else. The side effect of doing this is having bigger tables and bigger indexes.

I have earlier posts on this:

May 10, 2011 : What is the performance impact of using CHAR vs VARCHAR on a fixed-size field? (TRADEOFF #2)
Aug 12, 2011 : Which DBMS is good for super-fast reads and a simple data structure? (Par 3)
Sep 20, 2011 : Best of MyISAM and InnoDB
Jan 03, 2012 : Optimized my.cnf for high-end and busy server (Under the heading Replication)

INCREASED RAM

If you have many indexes, you may want to increase RAM on the DB Server and then attempt to preload all MyISAM index pages into the key buffer.

First compute how big a key buffer you would like (in GigaBytes)

SELECT CEILIING(SUM(index_length)/POWER(1024,3)) SuggestedKeyBufferSize
FROM information_schema.tables
WHERE engine='MyISAM' AND table_schema NOT
IN ('information_schema','performance_schema','mysql');

If SuggestedKeyBufferSize can be set up for the DB Server, then do the following (example 4G)

STEP 01) Set this in /etc/my.cnf

[mysqld]
key_buffer_size=4G

STEP 02) preload all MyISAM indexes

See my post on how to do this : MySQL warm procedure

Mysql – Optimizing table and choosing storage engine

Your second version is certainly better. To further improve...

TINYINT --> TINYINT UNSIGNED (yeah, this does not really make any difference; just cleaner)

DATE + TIME --> DATETIME or TIMESTAMP ...

It is almost always a bad idea to split DATE and TIME; it is so much easier to split a DATETIME than it is to put it back together.
Sizes: DATE:3, time as TIMYINT:1, DATETIME:8, TIMESTAMP:4
You can play games to turn a date&time into a DATETIME or TIMESTAMP representing the beginning of the hour (which you appear to need).

MyISAM vs InnoDB --

InnoDB recovers better from crashes
Unlike the 'wisdom' of a decade ago, InnoDB is likely to be faster.
InnoDB will take 2x-3x the disk footprint. 16M rows --> under a GB? Yawn.

Long queries, long lock times -- Well, let's see the naughty queries. Sounds like they would benefit from "summary tables". Offhand, I would suggest subtotals for each day. Such a table would be upwards of 24x smaller, hence faster if you need a 'table scan'. (TINYINT --> SMALLINT)

Best Answer

Give it a Try !!!

UPDATE 2014-09-15 00:21 EDT

Give it a Try !!!

Related Solutions

Mysql – Optimizing MySQL for Read-Only

MyISAM ROW FORMAT

INCREASED RAM

Mysql – Optimizing table and choosing storage engine

Related Question