MySQL Forums :: Performance :: InnoDB CPU Spikes


Advanced Search

InnoDB CPU Spikes
Date: March 18, 2009 02:43AM

We are facing CPU utilization to increase when a select query is fired against a table having approximately 100million records (on average). This table has InnoDB as its engine. The select query is executed to generate MIS reports.

The cpu utilization of mysqld increases from 1.5% to about 50% till the completion of the query. Being a audit log table the transactions are written to continuously to this table at a rate of 200TPS.

The load average on this machine is 0.3 - 0.6 but when the reports are generated it peaks to 2 - 3.

The reports are generated everyday and have the date in the where clause. Even with an index on the table the cpu utilization does not seem to have major changes.


Hardware
# uname -a
SunOS GATEWAY-SUN2 5.10 Generic_127127-11 sun4u sparc SUNW,Sun-Fire-V490

8GB RAM



My.cnf
# cat my.cnf
[mysqld]
innodb_flush_log_at_trx_commit=2
innodb_doublewrite=0
innodb_buffer_pool_size=1G
innodb_max_dirty_pages_pct=90
datadir=/gateway/mysql/data_dir


Table Defn
mysql> desc tblAuditLog;
+--------------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+-------------+------+-----+---------+----------------+
| pkAuditLogId | bigint(20) | NO | PRI | NULL | auto_increment |
| fldInvokeId | varchar(6) | YES | | NULL | |
| fldCreatedDate | datetime | YES | | NULL | |
| fldDialogId | varchar(6) | YES | | NULL | |
| fldExternalSystemInfo | varchar(80) | YES | | NULL | |
| fldShortCode | varchar(10) | YES | | NULL | |
| fldRequestTimestamp | datetime | YES | | NULL | |
| fldResponseTimestamp | datetime | YES | | NULL | |
| fldMsisdnnumber | varchar(14) | YES | | NULL | |
| fldESRequesttimestamp | datetime | YES | | NULL | |
| fldESResponsetimestamp | datetime | YES | | NULL | |
| fldEngineName | varchar(10) | YES | | NULL | |
| fldExternalSystem | varchar(50) | YES | | NULL | |
| fldRegionName | varchar(10) | YES | | NULL | |
| fldResponsetype | tinyint(1) | YES | | NULL | |
+--------------------------+-------------+------+-----+---------+----------------+
15 rows in set (0.01 sec)


Any guidance / pointers will be very helpful for me to get out of this problem.

Thanks in advance!

Options: ReplyQuote


Subject Views Written By Posted
InnoDB CPU Spikes 6546 Sabari girish Viswanathan 03/18/2009 02:43AM
Re: InnoDB CPU Spikes 2471 Aftab Khan 03/18/2009 03:08AM
Re: InnoDB CPU Spikes 2387 Rick James 03/18/2009 08:29PM
Re: InnoDB CPU Spikes 2259 Sabari girish Viswanathan 03/19/2009 04:48AM
Re: InnoDB CPU Spikes 2422 Rick James 03/19/2009 10:46AM
Re: InnoDB CPU Spikes 2238 Sabari girish Viswanathan 03/19/2009 03:47AM
Re: InnoDB CPU Spikes 2347 Sabari girish Viswanathan 03/19/2009 03:55AM
Re: InnoDB CPU Spikes 2183 Aftab Khan 03/19/2009 04:49AM
Re: InnoDB CPU Spikes 2225 Sabari girish Viswanathan 03/19/2009 10:20AM
Re: InnoDB CPU Spikes 1942 Sabari girish Viswanathan 03/30/2009 01:25AM
Re: InnoDB CPU Spikes 2037 Rick James 03/30/2009 07:13PM
Re: InnoDB CPU Spikes 2063 Sabari girish Viswanathan 03/31/2009 02:31AM
Re: InnoDB CPU Spikes 2165 Rick James 03/31/2009 08:12AM
Re: InnoDB CPU Spikes 1973 Sabari girish Viswanathan 04/01/2009 03:27AM
Re: InnoDB CPU Spikes 1971 Aftab Khan 04/01/2009 10:01AM
Re: InnoDB CPU Spikes 2032 Sabari girish Viswanathan 04/01/2009 10:22AM
Re: InnoDB CPU Spikes 1989 Sabari girish Viswanathan 04/01/2009 03:45AM
Re: InnoDB CPU Spikes 2114 Rick James 04/01/2009 11:57PM
Re: InnoDB CPU Spikes 2081 Sabari girish Viswanathan 04/06/2009 01:23AM
Re: InnoDB CPU Spikes 2066 Rick James 04/06/2009 08:01PM
Re: InnoDB CPU Spikes 1916 Sabari girish Viswanathan 04/07/2009 12:14AM
Re: InnoDB CPU Spikes 2012 Rick James 04/08/2009 04:36PM
Re: InnoDB CPU Spikes 2057 Sabari girish Viswanathan 04/16/2009 07:02AM


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.