# Another InnoDB performance question :)

**URL:** <https://forums.percona.com/t/another-innodb-performance-question/437>\
**Category:** Other MySQL® Questions\
**Created:** [September 4, 2007, 11:34pm UTC](https://forums.percona.com/t/another-innodb-performance-question/437 "2007-09-04T23:34:07Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![dmz006](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/dmz006/32/764_2.png) [@dmz006](https://forums.percona.com/u/dmz006)\
**Post date:** [September 4, 2007, 11:34pm UTC](https://forums.percona.com/t/another-innodb-performance-question/437/1 "2007-09-04T23:34:07Z")

</div>

Ok here goes…

I have an innodb with multiple database (MyIASM & InnoDB) but one InnoDB one is causing me major issues.

I have 71 tables using about 4.9G in data.

The machine is an Intel P4 single processor with 2G ram (this use to run fine before I converted to InnoDB…)

I have a few tables that tend to be my problems, one of which has 19 million records, the others 11 & 6.

I believe I’ve tuned the system as best I can but still see select(\*) from history (19MM table) take 15+ minutes! Now if you consider that there are going to be lots of selects / updates on that table every minute then you can see part of my problem )

Any and all suggestions welcome!

Also, on some of the other tables with MM records select count runs in \< 1 minute. What should I expect for read/update/inserts in databases / tables of this size? Why would this be good before i switched to innodb?

David

[mysqld]  
old\_passwords = false # inserted by debconf  
user = mysql  
pid-file = /var/run/mysqld/mysqld.pid  
socket = /var/run/mysqld/mysqld.sock  
port = 3306  
basedir = /usr  
datadir = /data/mysql  
tmpdir = /tmp  
language = /usr/share/mysql/english  
#skip-external-locking

innodb\_buffer\_pool\_size=1GB  
innodb\_log\_file\_size=256M  
innodb\_flush\_log\_at\_trx\_commit=2  
max\_connections = 200  
skip-locking  
key\_buffer = 384M  
max\_allowed\_packet = 16M  
table\_cache = 2000  
sort\_buffer\_size = 10M  
read\_buffer\_size = 10M  
myisam\_sort\_buffer\_size = 128M  
thread\_stack = 1024K  
thread\_cache\_size = 80  
thread\_cache = 900  
query\_cache\_limit= 1M  
query\_cache\_size= 32M  
query\_cache\_type = 1

# Try number of CPU’s\*2 for thread\_concurrency

#thread\_concurrency = 4

log-slow-queries = /var/log/mysql/mysql-slow.log  
skip-bdb

---

<div class="post-metadata">

**Author:** ![james](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@james](https://forums.percona.com/u/james)\
**Post date:** [September 5, 2007, 7:51am UTC](https://forums.percona.com/t/another-innodb-performance-question/437/2 "2007-09-05T07:51:58Z")

</div>

Well, for one thing, InnoDB doesn’t keep an exact count of the exact number of records in a table, so I believe a COUNT(\*) results in a full table scan (it’s going to have to count the number of records in the primary key, but since that index is clustered, that’s probably going to be as intensive as a full table scan).

MyISAM does keep an exact count of the number of records in a table, so COUNT(\*) should be instantaneous (or near instantaneous, at least).

One thing you might try is a COUNT(x) where x is a column that is a non-primary key index and that always has a value.

I have an InnoDB table with over 300 million records on a server with an Athlon and one gig of RAM, and this works fine on it.

---

<div class="post-metadata">

**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\
**Post date:** [September 5, 2007, 8:43am UTC](https://forums.percona.com/t/another-innodb-performance-question/437/3 "2007-09-05T08:43:45Z")

</div>

Does it use PRIMARY KEY to execute count(\*) ? (Check explain)  
If your primary key is fragmented it may be much faster to use other index to compute count which can be forced with force index clause.

---

<div class="post-metadata">

**Author:** ![dmz006](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/dmz006/32/764_2.png) [@dmz006](https://forums.percona.com/u/dmz006)\
**Post date:** [September 5, 2007, 9:35am UTC](https://forums.percona.com/t/another-innodb-performance-question/437/4 "2007-09-05T09:35:05Z")

</div>

—±-----------±----------±-------------±------------±- ---------±------------±---------±-------±-----±-------- —±--------+  
| Table | Non\_unique | Key\_name | Seq\_in\_index | Column\_name | Collation | Cardinality | Sub\_part | Packed | Null | Index\_type | Comment |  
±--------±-----------±----------±-------------±-------- ----±----------±------------±---------±-------±-----±- ----------±--------+  
| history | 1 | history\_1 | 1 | itemid | A | 18 | NULL | NULL | | BTREE | |  
| history | 1 | history\_1 | 2 | clock | A | 19762479 | NULL | NULL | | BTREE | |  
±--------±-----------±----------±-------------±-------- ----±----------±------------±---------±-------±-----±- ----------±--------+  
2 rows in set (0.93 sec)

mysql\> describe history;  
±-------±--------------------±-----±----±--------±---- --+  
| Field | Type | Null | Key | Default | Extra |  
±-------±--------------------±-----±----±--------±---- --+  
| itemid | bigint(20) unsigned | NO | MUL | 0 | |  
| clock | int(11) | NO | | 0 | |  
| value | double(16,4) | NO | | 0.0000 | |  
±-------±--------------------±-----±----±--------±---- --+

Thats the history table I’m working on.

Here are a few of the sql queries that are showing up in my slow query log:  
select min(clock) from history where itemid=100000000017961;

Another table I’m seeing major problems with:  
mysql\> describe node\_cksum;  
±----------±--------------------±-----±----±--------±- -----+  
| Field | Type | Null | Key | Default | Extra |  
±----------±--------------------±-----±----±--------±- -----+  
| cksumid | bigint(20) unsigned | NO | PRI | 0 | |  
| nodeid | bigint(20) unsigned | NO | MUL | 0 | |  
| tablename | varchar(64) | NO | | | |  
| fieldname | varchar(64) | NO | | | |  
| recordid | bigint(20) unsigned | NO | | 0 | |  
| cksumtype | int(11) | NO | | 0 | |  
| cksum | char(32) | NO | | | |  
±----------±--------------------±-----±----±--------±- -----+  
7 rows in set (0.00 sec)

mysql\> show indexes from node\_cksum;  
±-----------±-----------±-------------------±----------- --±------------±----------±------------±---------±----- --±-----±-----------±--------+  
| Table | Non\_unique | Key\_name | Seq\_in\_index | Column\_name | Collation | Cardinality | Sub\_part | Packed | Null | Index\_type | Comment |  
±-----------±-----------±-------------------±----------- --±------------±----------±------------±---------±----- --±-----±-----------±--------+  
| node\_cksum | 0 | PRIMARY | 1 | cksumid | A | 6776659 | NULL | NULL | | BTREE | |  
| node\_cksum | 1 | node\_cksum\_cksum\_1 | 1 | nodeid | A | 18 | NULL | NULL | | BTREE | |  
| node\_cksum | 1 | node\_cksum\_cksum\_1 | 2 | tablename | A | 18 | NULL | NULL | | BTREE | |  
| node\_cksum | 1 | node\_cksum\_cksum\_1 | 3 | fieldname | A | 18 | NULL | NULL | | BTREE | |  
| node\_cksum | 1 | node\_cksum\_cksum\_1 | 4 | recordid | A | 43440 | NULL | NULL | | BTREE | |  
| node\_cksum | 1 | node\_cksum\_cksum\_1 | 5 | cksumtype | A | 43440 | NULL | NULL | | BTREE | |  
±-----------±-----------±-------------------±----------- --±------------±----------±------------±---------±----- --±-----±-----------±--------+  
6 rows in set (1.19 sec)

query:  
select curr.nodeid,curr.tablename,curr.recordid from node\_cksum curr left join node\_cksum prev on curr.tablename=prev.tablename and curr.recordid=prev.recordid and curr.fieldname=prev.fieldname and curr.nodeid=prev.nodeid and curr.cksumtype\<\>prev.cksumtype where prev.cksumid is null and curr.cksumtype=0;

This table has almost 7million records and the above query takes about 2300-3000 seconds to complete each time it is called.

I didn’t write this code. In fact it comes from the zabbix monitor. I’m just trying to figure out how i can tune the system to run reasonable query responses )

Also, I ran the following  
select count(itemid) from history;  
trying to count an indexed item, and it’s still running 15 minutes.

David