Improve performance on write-only table?

Question

I am using MySQL 5.5 with innoDB. The basis of my server is Netty, JDBC and BoneCP.

I have a log table that contains user inputs(HTTP header, request body etc). This table will only be read very rarely for reasons like security and data recovery. Therefore the read performance is not something we care about.

There are five columns in this table.

Name       | Type
--------------------------------------------------
userNumber | medium integer
logTime    | timestamp
header     | varchar(100)
body       | varchar(200)

What are some tips that will improve the insert performance?

Answer 1

The thing that slows down INSERTS more than anything is indexes. When you insert data, the indexes must be maintained so they are correct. If you dont have indexes, there will be no overhead in maintaining them.

Some other things you can do if you are still not getting the performance you want is to:

• Put the database on a SSD (solid state drive), this will give about a 100-1 performance boost.
• If there are multiple tables in your database, get this table onto another drive by itself (Another Database or partition the data if mysql can do that). This allows the engine work on the data for this table while it also works on other requests on the other drive. (This is a trick that I know works in SQL Server, not sure about mysql)

The biggest choke point for databases is the HD. Do what you can to it, and the database will follow.

Answer 2

I just read about blackhole engine in mysql today. This could fit your requirement I believe, if you have a slave set-up. You can dump all the data into your master but will get saved only in slave. The load on your master server should be minimal.