Another InnoDB performance question :)

MySQL & MariaDB Other MySQL® Questions
1

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

2

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.

3

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.

4

—±-----------±----------±-------------±------------±- ---------±------------±---------±-------±-----±-------- —±--------+
| 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