MySQL Forums
Forum List  »  Performance

Re: Load avg is 65 and cpu is 100% on mysqld
Posted by: Rick James
Date: June 25, 2012 08:06AM

> Total size of the table is 6755971.
Is that Data + Index? Both are cached by the innodb_buffer_pool_size. In any case, this should be plenty:
> innodb_buffer_pool_size | 12884901888

> have_query_cache | YES ?
says that the QC is compiled and available; it does not say whether it is turned on or off. See Aftab's comments.

This is a curious mixture...
> UPDATE subscription SET billingstatus = 'true' WHERE idsubscription = 6133858 AND billingstatus = 'false' LIMIT 1
Since the PRIMARY KEY is (idsubscription), idsubscription is unique. Hence there can never be more than one row with idsubscription = 6133858. (There could be zero.) So, the LIMIT 1 adds nothing useful.
The PK is perfect for this query.

What is the goal here?
> SELECT idsubscription,idkeyword,msisdn,subscribemode,billed,status,circle FROM subscription WHERE status = 'active' AND billingstatus = 'false' limit 6
There is no ORDER BY, so you are getting 6 rows at the whim of the Engine and the optimizer. Assuming that status and billing status each have 2 value and are not too biased toward one value or the other, no single-key index would be useful, and the query will perform a time-consuming "table scan". This _might_ be useful:
INDEX(active, billingstatus) -- or the reverse.

This does not need to be UNIQUE:
> UNIQUE KEY `BilledIndex` (`billed`,`status`,`idsubscription`),
Since it contains a unique value (the PK), it is guaranteed unique even if you make it a simple INDEX. Doing so would speed up INSERTs slightly.

> bigint(120)
There is only one size of a BIGINT -- 8 bytes. The "(120)" adds nothing.

> `billed` varchar(12) NOT NULL DEFAULT 'no',
Learn about ENUMs; such would be much more efficient.

> `chargedbalance` float DEFAULT NULL,
For Money, it is usually much better to use DECIMAL, thereby controlling rounding errors.

> KEY `BillingStatusCombinedIndex` (`status`)
An index on a 2-value field is almost never used. Drop it.

> | Created_tmp_tables | 4323 |
That is a count since last restart. How long has mysqld been running? The per-second quotient is useful; the absolute value is not.

> | Created_tmp_disk_tables | 1466 |
There are many reasons for spilling to a "disk" table; max_tmp_table_size is only one.

> Total size of the table is 6755971.
How much do you have in other tables? If it is only a few megaytes, then buffer_pool = 12G is overkill.

The first of these "covers" for the second:
> KEY `CircleRandomIndex` (`status`,`billingstatus`,`circle`),
> KEY `BillingStatusCombinedIndex` (`status`)
DROP the second.

Suggest you
1. implement our suggestions
2. turn on the slowlog and collect some info
3. use pt-query-digest to scan that log
4. present the SHOW CREATE TABLE, EXPLAINs, and digest output for further critique.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Load avg is 65 and cpu is 100% on mysqld
1021
June 25, 2012 08:06AM


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.