MySQL Forums
Forum List  »  Performance

Re: limitation on request per second
Posted by: Rick James
Date: June 15, 2008 07:37PM

Ouch: these 3 lines fight each other:
I am using InnoDB
key_buffer_size 1073741824
innodb_buffer_pool_size 8388608
You 1GB key_buffer is virtually unused -- it is used only for MyISAM index caching.
You 8MB innodb cache is very small (considering you 1GB of RAM); it is needed for caching InnoDB data and indexes. Recommend changing that to, say, 700MB if nothing but MySQL is on the machine; else 70% of available RAM. [This is the most likely fix.]

This one puzzles me -- are you using 2 masters?
| auto_increment_increment | 2 |

innodb_flush_log_at_trx_commit = 1, esp with IDE drive may be an issue:

When innodb_flush_log_at_trx_commit is set to 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit. When this value is 1 (the default), the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. When set to 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues.

The default value of this variable is 1, which is the value that is required for ACID compliance. You can achieve better performance by setting the value different from 1, but then you can lose at most one second worth of transactions in a crash. If you set the value to 0, then any mysqld process crash can erase the last second of transactions. If you set the value to 2, then only an operating system crash or a power outage can erase the last second of transactions. However, InnoDB's crash recovery is not affected and thus crash recovery does work regardless of the value. Note that many operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld that the flush has taken place, even though it has not. Then the durability of transactions is not guaranteed even with the setting 1, and in the worst case a power outage can even corrupt the InnoDB database. Using a battery-backed disk cache in the SCSI disk controller or in the disk itself speeds up file flushes, and makes the operation safer. You can also try using the Unix command hdparm to disable the caching of disk writes in hardware caches, or use some other command specific to the hardware vendor. The default value of this variable is 1 (prior to MySQL 4.0.13, the default is 0).

Note: For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, you should use innodb_flush_log_at_trx_commit=1, sync_binlog=1, and innodb_safe_binlog in your master server my.cnf file.
-- http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.html

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: limitation on request per second
2805
June 15, 2008 07:37PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.