1

I have RedHat 5.3 (Tikanga) with Mysql 5.0.86 configued with RAID 10 HW,

I run an application that queries Mysql/InnoDB and MyIsam tables,

the queries are super fast,but some queires on Innodb tables sometimes slow down and take more than 1-3 seconds to run and these queries are simple and optimized,

this problem occurred just on innodb tables in different time with random queries.

Why is this happening only to Innodb tables?

the below is the Innodb status and some Mysql variables:

show innodb status\G
*************************** 1. row ***************************
Status: 
=====================================
120325 10:54:08 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 19 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 22943, signal count 22947
Mutex spin waits 0, rounds 561745, OS waits 7664
RW-shared spins 24427, OS waits 12201; RW-excl spins 1461, OS waits 1277
------------
TRANSACTIONS
------------
Trx id counter 0 119069326
Purge done for trx's n:o < 0 119069326 undo n:o < 0 0
History list length 41
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 29093, OS thread id 1166043456
MySQL thread id 703985, query id 5807220 localhost root
show innodb status
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
132777 OS file reads, 689086 OS file writes, 252010 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 366, seg size 368,
62237 inserts, 62237 merged recs, 52881 merges
Hash table size 8850487, used cells 3698960, node heap has 7061 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 15 3415398745
Log flushed up to   15 3415398745
Last checkpoint at  15 3415398745
0 pending log writes, 0 pending chkp writes
218214 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 4798817080; in additional pool allocated 12342784
Buffer pool size   262144
Free buffers       101603
Database pages     153480
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 151954, created 1526, written 494505
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 29093, id 1162049856, state: waiting for server activity
Number of rows inserted 77675, updated 85439, deleted 0, read 14377072495
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set, 1 warning (0.02 sec)
=============================


read_buffer_size        = 128M
sort_buffer_size        = 256M
tmp_table_size          = 1024M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size=10M
innodb_lock_wait_timeout=100
innodb_buffer_pool_size=4G
join_buffer_size = 128M
key_buffer_size = 1G

can any one help me ?

different queries on different Databases But one thing they have in common that the problem occurs on the Innodb tables

the following structure for one table:

mysql> show create table C_A\G
*************************** 1. row ***************************
       Table: C_A
Create Table: CREATE TABLE `C_A` (
  `C_no` varchar(32) NOT NULL default '',
  `C_t` double(11,2) default NULL,
  `act` enum('0','1') default '0',
  `us` enum('0','1') default '0',
  `event_time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `pro` enum('0','1') default '0',
  `C_type` enum('pre','ep','pon') NOT NULL default 'pre',
  `C_number` bigint(20) NOT NULL default '0',
  `cur` varchar(3) default 'JOR',
  PRIMARY KEY  (`C_no`),
  KEY `act_ndx` (`act`),
  KEY `us_ndx` (`us`),
  KEY `index1` (`C_no`(20),`act`),
  KEY `C_number_index` (`C_number`),
  KEY `type_index` (`C_type`),
  KEY `cur_index` (`cur`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

the first slow query for update statement i change it to select:

Count         : 1  (100.00%)
Time          : 3 s total, 3 s avg, 3 s to 3 s max  (100.00%)
Lock Time (s) : 0 total, 0 avg, 0 to 0 max  (0.00%)
Rows sent     : 0 avg, 0 to 0 max  (0.00%)
Rows examined : 0 avg, 0 to 0 max  (0.00%)
Database      : 

explain select * from C_A where C_no='05de';
+----+-------------+---------------------+-------+----------------+---------+---------+-------+------+-------+
| id | select_type | table               | type  | possible_keys  | key     | key_len | ref   | rows | Extra |
+----+-------------+---------------------+-------+----------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | C_A                 | const | PRIMARY,index1 | PRIMARY | 34      | const |    1 |       | 
+----+-------------+---------------------+-------+----------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

the second slow query for select statement:

Count         : 1  (33.33%)
Time          : 22 s total, 22 s avg, 22 s to 22 s max  (62.86%)
Lock Time (s) : 0 total, 0 avg, 0 to 0 max  (0.00%)
Rows sent     : 1 avg, 1 to 1 max  (33.33%)
Rows examined : 119.75k avg, 119.75k to 119.75k max  (88.60%)
Database      : 
mysql> explain SELECT sum(amount) FROM C_A 
    ->  WHERE C_t > 0 AND act='1' AND  us='0' AND pro='0' AND C_Type = 'pre';
+----+-------------+---------------------+------+--------------------------------+----------+---------+-------+--------+-------------+
| id | select_type | table               | type | possible_keys                  | key      | key_len | ref   | rows   | Extra       |
+----+-------------+---------------------+------+--------------------------------+----------+---------+-------+--------+-------------+
|  1 | SIMPLE      | C_A                 | ref  | act_ndx,us_ndx,type_index      | us_ndx   | 2       | const | 318902 | Using where | 
+----+-------------+---------------------+------+--------------------------------+----------+---------+-------+--------+-------------+
1 row in set (0.00 sec)
4
  • Are you saying that the same query on the same table sometimes runs fast and sometimes runs slow or that different queries on different tables with different storage engines run at different speeds?
    – Ladadadada
    Mar 25, 2012 at 11:36
  • I think it might be helpful to describe the database structure and queries. I think the most likely problem is in index usage or query plan, but neither of those is evaluable with what you have posted.
    – seandavi
    Mar 25, 2012 at 13:42
  • different queries on different Databases But one thing they have in common that the problem occurs on the Innodb tables.
    – user105196
    Mar 25, 2012 at 14:31
  • What does MySQLTuner tell you? It's a good starting place for MySQL performance issues.
    – devicenull
    Mar 26, 2012 at 3:36

1 Answer 1

1

You can use mysqltuner, tuning-primer or MySQLReport to find the bottlenecks.

Also, check the table and ibdata1 size. To large ibdata1 can lead to slow operations with InnoDB.

http://www.serveradminblog.com/2011/03/tuning-mysql-performance-howto-part-1/ http://www.serveradminblog.com/2010/12/reducing-ibdata1-howto/

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .