2

I'm doing some testing on a dedicated mediocre server (Core 2 2G ram). Before I upgrade to 8G ram, I am concerned something is very wrong here.

Simple queries have normal performance:

MariaDB [testing]> select count(*) from domains where domain like 'super%';
+----------+
| count(*) |
+----------+
|    10868 |
+----------+
1 row in set (0.26 sec)

MariaDB [testing]> select count(domain) from domains where domain like 'slow%';
+---------------+
| count(domain) |
+---------------+
|           582 |
+---------------+
1 row in set (0.07 sec)

However count(*) and count(domain) are ridiculously slow:

MariaDB [testing]> select count(domain) from domains;
+---------------+
| count(domain) |
+---------------+
|      12802537 |
+---------------+
1 row in set (59 min 45.86 sec)

Explain claims it is using INDEX, yet it still shows full table scan.

MariaDB [testing]> explain select count(domain) from domains;
+------+-------------+---------+-------+---------------+--------+---------+------+----------+-------------+
| id   | select_type | table   | type  | possible_keys | key    | key_len | ref  | rows     | Extra       |
+------+-------------+---------+-------+---------------+--------+---------+------+----------+-------------+
|    1 | SIMPLE      | domains | index | NULL          | domain | 120     | NULL | 12762541 | Using index |
+------+-------------+---------+-------+---------------+--------+---------+------+----------+-------------+
1 row in set (0.00 sec)

Schema:

CREATE TABLE `domains` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `domain` char(120) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `domain` (`domain`)
) ENGINE=InnoDB AUTO_INCREMENT=14782143 DEFAULT CHARSET=latin1;

 MariaDB [testing]> desc domains;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| domain | char(120)        | NO   | UNI |         |                |
+--------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

my.cnf is pretty much default, except for

innodb_buffer_pool_size = 1600M

I am using deadline scheduler. No other queries are running. Nothing else is using the disk. Linux is not swapping.

Aside from ditching InnodDB, is there something else I can try?

3
  • "Explain claims it is using INDEX, yet it still shows full table scan." Actually, this is showing a full index scan, reading all the rows from the domain index, rather than the entire table. This part looks correct. Nov 15, 2016 at 11:13
  • Maybe your bottleneck is in slow HDD. But in any case, your count(*) will not be instantaneous for a table with 15M records. For example SELECT COUNT(*) FROM table spends 170 seconds and returns value 57M and generates read load on desk 60MB/second in my environment (MySQL 5.7, Innodb)
    – Alexey
    Nov 15, 2016 at 20:28
  • Uh, don't use fixed length chars like that
    – eckes
    Jun 4, 2017 at 17:00

2 Answers 2

1

It's reading the entire 12M rows in the index. Yes, it is doing an index scan, not a table scan, but that is really the table (only 2 columns), then the index is as big as the data.

Do not use CHAR(...) unless the data is constant length. I suspect the average length for a "domain" is somewhere around 20. Changing to VARHAR(120) will shrink the data and the index by a factor of maybe 5. This shrinkage will probably speed up the full scan by a factor of 5, and help the other queries some.

What was the setting of innodb_buffer_pool_size? It should not have been more than about 500M. The data is about 2GB, as is the index. So you had to read most or all of the index from disk. That is, the buffer_pool could not have cached it all, so you were I/O-bound.

Switching to 8GB of RAM and increasing innodb_buffer_pool_size to, say, 5500M, will perhaps let it cache the entire index, thereby running the full query in 6 seconds instead of 60. Add to that, my suggestion about CHAR, and it might go below 2 seconds.

0

don't do select count(*) on InnoDB, it is never a good idea.

Even if the table fits into the buffer pool and even if InnoDB executes the query the most optimal way it will be slow and multiple concurrent connections will kill the server.

1
  • I don't like ditching InnoDB either. MyISAM will bring another pile of problems.
    – akuzminsky
    Nov 15, 2016 at 4:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.