Re: MyISAM web site logs 99% write

[Rick James]

You are pushing the limits of MySQL. Here are some tips...

Any Engine...

Batch the INSERTs. A single INSERT with 100 rows will run considerably faster than one row per INSERT statement. (Like 10 times as fast.)

LOAD FILE... also runs very fast.

Every extra index on the table costs insert time.

It is very important to have a disk write cache turned on. I hope the RAID is hardware, not software.

MyISAM...

IF the table has NEVER had a DELETE or UPDATE, and IF the inserts are going onto the "end" of the table, this is more efficient than otherwise.

key_buffer_size -- needs to be appropriately sized.

Can you get away without any indexes?

InnoDB...

InnoDB will have a PRIMARY KEY, even if you don't specify one. An AUTO_INCREMENT is good and efficient.

Many benchmarks show InnoDB being faster than MyISAM. But benchmarks tend to test only one very limited case.

You can probably live with just a PRIMARY KEY, explicitly specified. We can get into tips about this later.

innodb_buffer_pool_size should be about 12GB if you use InnoDB.

There are about 3 other settings (for performance) that will need tuning to maximize performance (at the expense of crash protection). (Note that MyISAM has much less crash protection.)


I bring up both engines because it may be that you should INSERT into one type, then massage the data into the other.

SHOW CREATE TABLE -- that may trigger some other ideas.

What will you do with the data? Will you ever SELECT from the table? You will have more problems then.

Or do you summarize it? In which case, you could do
Plan A: Summarize in code _before_ doing inserts; then insert only the summary rows (which might be only a few hundred instead of 5K-7K/sec).
Plan B: Have two threads alternating -- one is busy inserting rows for a minute, then processing them for the next minute. The other thread is alternating with it.

Note: TRUNCATE TABLE is very efficient for deleting all the rows from a MyISAM table.

Once you describe your situation further, we can consider whether PARTITION might be useful.