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.
Best Answer
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:
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.