Re: Load avg is 65 and cpu is 100% on mysqld
> 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.