How to use insert delay with the InnoDB engine and use less connection for insert statements?

Question

I'm working on an application which involves a lot of database writes, approximately ~70% inserts and 30% reads. This ratio would also include updates which I consider to be one read and one write. Through insert statements multiple clients insert data in database via insert statement below:

$mysqli->prepare("INSERT INTO `track` (user, uniq_name, ad_name, ad_delay_time ) values (?, ?, ?, ?)");

The question is should I use either insert_delay or use mysqli_multi_query mechanism because insert statement utilizes ~100% cpu on server. I am using the InnoDB engine on my database so insert delayed is not possible. Insertion on server is ~36k/hr and 99.89% read, also i am using select statement there retrieve data seven times in single query, this query takes 150 seconds on server to execute. What kind of technique or mechanism can I use for this task? My server memory is 2 gb, should i expand memory?. Have a look on this problem, any suggestion will be grateful to me.

Table's structure:

+-----------------+--------------+------+-----+-------------------+----------------+
| Field           | Type         | Null | Key | Default           | Extra          |
+-----------------+--------------+------+-----+-------------------+----------------+
| id              | int(11)      | NO   | PRI | NULL              | auto_increment |
| user            | varchar(100) | NO   |     | NULL              |                |
| uniq_name       | varchar(200) | NO   |     | NULL              |                |
| ad_name         | varchar(200) | NO   |     | NULL              |                |
| ad_delay_time   | int(11)      | NO   |     | NULL              |                |
| track_time      | timestamp    | NO   | MUL | CURRENT_TIMESTAMP |                |
+-----------------+--------------+------+-----+-------------------+----------------+

My database present status, it shows 41k insertions (writes), which is very slow for my database.

Answer 1

Since you have more writes then reads, I would like to recommend the following

Decent Tuning of InnoDB would be the key

Buffer Pool (Sized by innodb_buffer_pool_size)

Since InnoDB does not support INSERT DELAYED, using a large InnoDB Buffer Pool is closest thing you can get to INSERT DELAYED. All DML (INSERTs, UPDATEs, and DELETEs) would be cached in the InnoDB Buffer Pool. Transactional information for the Writes are written immediately to the Redo Logs (ib_logfile0, ib_logfile1). The writes that are posted in the Buffer Pool are periodically flushed from memory to disk via ibdata1 (InsertBuffer for Secondary Indexes, Double Write Buffer). The larger the Buffer Pool, the large the amount of INSERTs can be cached. In a system with 8GB or more of RAM, use 75-80% of the RAM as the innodb_buffer_pool_size. In a system with very little RAM, 25% (to accommodate the OS).

CAVEAT : You can set innodb_doublewrite to 0 to speed up writes even more, but at the risk of data integrity. You can also speed things up with setting innodb_flush_method to O_DIRECT to prevent caching InnoDB to the OS.

Redo Logs (Sized by innodb_log_file_size)

By default, the redo logs are named ib_logfile0 and ib_logfile1 and would be 5MB each. The size should be 25% of the innodb_buffer_pool_size. If the redo logs already exist, add the new setting in my.cnf, shutdown mysql, delete them, and restart mysql.

Log Buffer (Sized by innodb_log_buffer_size)

The log buffer holds changes in RAM before flushing them into the redo logs. The default is 8M. The larger the log buffer, the less the Disk I/O. Be careful with very large transactions, as this may slow down COMMITs by milliseconds.

Accessing Multiple CPUs

MySQL 5.5 and the MySQL 5.1 InnoDB Plugin have settings to have InnoDB Storage Engine access multiple CPUs. Here are the options you need to set:

• innodb_thread_concurrency sets the upper bound on number of concurrent threads that InnoDB can hold open. It is usually recommended to set for this is (2 X Number of CPUs) + Number of Disks. Last year, I learned firsthand from the Percona NYC Conference that you should set this to 0 in order to alert the InnoDB Storage Engine to find the best number of threads for the environment it is running in.
• innodb_concurrency_tickets sets the number of threads that can bypass concurrency checking with impunity. After that limit is reached, thread concurrency checking becomes the norm again.
• innodb_commit_concurrency sets the number of concurrent transactions that can be committed. Since the default is 0, not setting this allows any number of transactions to commit simultaneously.
• innodb_thread_sleep_delay sets the number of milliseconds an InnoDB thread can be dormant before reentering the InnoDB queue. Default is 10000 (10 sec).
• innodb_read_io_threads (set this to 3000) and innodb_write_io_threads (set this to 7000) (both since MySQL 5.1.38) allocate the specified number of threads for reads and writes. Default is 4 and maximum is 64. Set these to 64. Also, set the innodb_io_capacity to 10000.

Upgrade to MySQL 5.5

If you have MySQL 5.0, upgrade to MySQL 5.5. If you have MySQL 5.1.37 or prior, upgrade to MySQL 5.5. If you have MySQL 5.1.38 or above and want to remain in MySQL 5.1, install the InnoDB Plugin. That way, you can take advantage of all the CPUs for InnoDB.

Answer 2

INT(2) still uses 4 bytes -- perhaps you meant TINYINT UNSIGNED?

How many different values in setno? If it is small, the KEY(setno) will never be used. INSERTing has to update that index; removing the KEY will speed up INSERT some.

CHAR(10) -- Is flag always 10 characters long? And in utf8? Perhaps you could use flag VARCHAR(10) CHARACTER SET ascii

Batch your inserts -- 100 at a time will run 10 times as fast. (Beyond 100 is getting into 'diminishing returns'.)

What is the value of autocommit? Are you wrapping each INSERT in BEGIN...COMMIT? What is the value of innodb_flush_log_at_trx_commit?

Answer 3

Set up a queue. Application would write into a queue 1 row at a time and then take rows out and insert into a database in batch based on number of rows of on amount of time passed since last insert.

I have seen where batching the inserts 10,000 at a time is the fastest, so you would need to test to find a sweet spot.

You could create your own simple queue system or use an existing one. Here are some examples: HornetQ and File::Queue. Here is a post on SE listing some other good options: Message queues in perl,php,python.