Você pode usar mysqltuner, tuning-primer ou MySQLReport para encontrar os gargalos.
Além disso, verifique a tabela e o tamanho ibdata1. Para grandes ibdata1 pode levar a operações lentas com InnoDB.
Eu tenho o RedHat 5.3 (Tikanga) com o Mysql 5.0.86 configurado com o RAID 10 HW,
Eu rodei uma aplicação que consulta as tabelas Mysql / InnoDB e MyIsam,
as consultas são super rápidas, mas algumas queiram nas tabelas Innodb às vezes ficam lentas e demoram mais de 1-3 segundos para serem executadas e essas consultas são simples e otimizadas,
este problema ocorreu apenas nas tabelas innodb em diferentes momentos com consultas aleatórias.
Por que isso está acontecendo apenas nas tabelas Innodb?
abaixo está o status Innodb e algumas variáveis do Mysql:
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
alguém pode me ajudar?
diferentes consultas em diferentes Bancos de Dados Mas uma coisa eles têm em comum que o problema ocorre nas tabelas Innodb
a seguinte estrutura para uma tabela:
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)
a primeira consulta lenta para a instrução de atualização eu altero para selecionar:
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)
a segunda consulta lenta para a instrução select:
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)
Tags mysql5