Re: Load avg is 65 and cpu is 100% on mysqld

[Mannoj Kumar]

| subscription | CREATE TABLE `subscription` (
`idsubscription` bigint(120) NOT NULL AUTO_INCREMENT,
`idkeyword` bigint(120) NOT NULL,
`msisdn` varchar(24) NOT NULL,
`subscribedon` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`unsubscribedon` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`subscribemode` varchar(12) NOT NULL DEFAULT 'web',
`unsubscribemode` varchar(12) DEFAULT NULL,
`billed` varchar(12) NOT NULL DEFAULT 'no',
`status` varchar(12) NOT NULL DEFAULT 'active',
`billingstatus` char(12) NOT NULL DEFAULT 'true',
`circle` char(24) DEFAULT 'unknown',
`chargedbalance` float DEFAULT NULL,
PRIMARY KEY (`idsubscription`),
UNIQUE KEY `BilledIndex` (`billed`,`status`,`idsubscription`),
KEY `GroupMSISDN` (`msisdn`),
KEY `SubInfo` (`idkeyword`,`msisdn`,`status`),
KEY `CircleRandomIndex` (`status`,`billingstatus`,`circle`),
KEY `BillingStatusIndex` (`billingstatus`),
KEY `BillingStatusCombinedIndex` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=6776159 DEFAULT CHARSET=latin1 |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Total size of the table is 6755971.

Terrible ratio; you are better turning the QC off. >> You mean I should be turning off -> have_query_cache | YES ?


These are some selects and updates happening frequently.
Info: SELECT idsubscription,idkeyword,msisdn,subscribemode,billed,status,circle FROM subscription WHERE status = 'active' AND billingstatus = 'false' limit 6
*************************** 7. row ***************************

Info: SELECT idsubscription,idkeyword,msisdn,subscribemode,billed,status,circle FROM subscription WHERE status = 'active' AND billingstatus = 'false' limit 8
*************************** 8. row ***************************
Info: SELECT idsubscription,idkeyword,msisdn,subscribemode,billed,status,circle FROM subscription WHERE status = 'active' AND billingstatus = 'false' limit 7
*************************** 9. row ***************************
State: Updating
Info: UPDATE subscription SET billingstatus = 'true' WHERE idsubscription = 6133858 AND billingstatus = 'false' LIMIT 1
*************************** 10. row ***************************

I increased the max_tmp_table_size to 3G. Now the disk tables are not increasing more. So load is a kinda gradual 0.4 as of now.. Still we might expect it to rise again. As mem is fully occupied on 64bit.
| Created_tmp_disk_tables | 1466 |
| Created_tmp_files | 18 |
| Created_tmp_tables | 4323 |

Looks good _if_ you have at least 16GB of RAM _and_ you are using InnoDB. --> yes its 16G.