Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

database status

share|improve this question
    
Can you supply the table's definition? (all columns, datatypes and indexes) –  ypercube May 23 '12 at 8:35
    
Can you give a brief snippet of your SHOW FULL PROCESSLIST when it's taking 100% cpu? How many connections are you allowing vs how many are taken during this time? –  Derek Downey May 23 '12 at 14:16
    
Please run these two queries : SHOW GLOBAL VARIABLES LIKE 'innodb%'; and SELECT VERSION(); and display their output. –  RolandoMySQLDBA May 23 '12 at 15:46
    
Please provide the number of inserts per second you are executing. –  dabest1 May 23 '12 at 17:28
    
Your code is very susceptible to SQL injection. Use prepared statements and parameterized values. –  Aaron Brown May 24 '12 at 1:19
add comment

3 Answers 3

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.

share|improve this answer
    
my server memory is 2GB, so according to memory i set innodb buffer pool to 500M, and log files 25% to pool, also set log buffer to 64M. But still server is heavy busy. Should i upgrade memory? Also my server is on 32 bit ubuntu, so max i can set memory to 4 GB. –  shihon Dec 1 '12 at 13:00
    
If the server is just for MySQL (no apache, no PHP), then innodb_buffer_pool_size can be up 75% of 2GB, which is 1536M. If you upgrade to 4GB, innodb_buffer_pool_size can be 3G. Log files should be 25% of buffer pool as you stated. –  RolandoMySQLDBA Dec 1 '12 at 18:24
    
Server is running apache2, mysql and php, should i go for upgrade memory in this situation or is there any optimal solution except to innodb buffer pool? –  shihon Dec 2 '12 at 6:58
add comment

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?

share|improve this answer
    
how do i get insert in batch if data is insert via external source like different clients with different values....is it reliable if i used: codeinsert into t_name(col1, col2, col3) values (val1, val2, val3), (val1, val2, val3), (val1, val2, val3), (val1, val2, val3), (val1, val2, val3);code –  shihon May 24 '12 at 5:58
add comment

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.

share|improve this answer
    
I agree to this approach- I'm batching ~1500 inserts every 5 seconds on one app and it is sub second. mysql seems to have some mechanism implemented internally that makes batch inserts happen really really quickly. –  Don Wool Jul 24 '12 at 4:05
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.