MySQL Forums
Forum List  »  Performance

Re: Load avg is 65 and cpu is 100% on mysqld
Posted by: Rick James
Date: June 23, 2012 02:06PM

> innodb_flush_log_at_trx_commit = 2, but ideally 1 should be fine right to commit whenever any inserts or updates issued on auto-commit mode?
With "2", there will be a disk hit after every COMMIT and after any statement run with autocommit turned on. Rule of Thumb: You can do only 100 disk hits per second.

> Qcache_hits: 199188
> Qcache_inserts: 5963443
Terrible ratio; you are better turning the QC off.

> Indexes looks good.
No offense, but I don't buy that statement. Show me the SELECTs and the CREATE TABLE.

> select query I get the result in 1 sec
1 second is a loooong time for a SELECT. 1ms is good if the disk does not need to be hit. 10ms is ok if you need a disk hit.

Lots of 1 second SELECTs across lots of connections --> large "load average".

> Created_tmp_tables | 1820
If the server has been up for an hour or more. this is not a bad number.

One of several reasons I ask for CREATE TABLE, is so that you will divulge the Engine you are using.

> innodb_buffer_pool_size | 12884901888
Looks good _if_ you have at least 16GB of RAM _and_ you are using InnoDB.

> key_buffer_size | 8388608d
is terrible _if_ you are using MyISAM.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Load avg is 65 and cpu is 100% on mysqld
1085
June 23, 2012 02:06PM


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.