MySQL 100% CPU Usage – Reading from a 500,000 Rows Table

mariadbmyisamMySQL

I'm using MariaDB 10.1

My config is the follow

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

key_buffer_size = 2G
read_buffer_size = 2G

symbolic-links=0
skip-name-resolve=1
query_cache_limit       = 4M
query_cache_size        = 256M

expire_logs_days        = 10
max_binlog_size         = 100M

max_connections  = 8096
back_log = 4096
open_files_limit = 20240
innodb_open_files = 20240

table_open_cache = 8096
table_definition_cache = 8096

tmp_table_size = 2G
max_heap_table_size = 2G

innodb_buffer_pool_size = 10G
innodb_buffer_pool_instances = 10
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
performance_schema = 0
innodb-file-per-table = 1
innodb_table_locks = 0

thread_cache_size = 8096
thread_handling = pool-of-threads
thread_pool_size = 20
thread_pool_idle_timeout = 20
thread_pool_max_threads = 2048

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

I have one table MYISAM (DESCRIBE)

MariaDB [ip2proxy]> DESCRIBE ip2proxy_px2;
+--------------+------------------+------+-----+---------+-------+
| Field        | Type             | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-------+
| ip_from      | int(10) unsigned | NO   | PRI | NULL    |       |
| ip_to        | int(10) unsigned | NO   | PRI | NULL    |       |
| proxy_type   | varchar(3)       | YES  |     | NULL    |       |
| country_code | char(2)          | YES  |     | NULL    |       |
| country_name | varchar(64)      | YES  |     | NULL    |       |
+--------------+------------------+------+-----+---------+-------+
5 rows in set (0.03 sec)

MariaDB [ip2proxy]> SELECT COUNT(*) FROM ip2proxy_px2;
+----------+
| COUNT(*) |
+----------+
|   482450 |
+----------+
1 row in set (0.00 sec)

We do more than 8.000 reads / second against that database to search for IPs.
The MySQL Is going to 100% in all Cores (12 Threads) and then it is crashing.

We want all the requests to be executed without any delays.

The query we execute is:

SELECT proxy_type FROM `ip2proxy_px2` WHERE {IPLONG} BETWEEN ip_from AND ip_to LIMIT 1;

Any idea what im doing wrong?

Best Answer

How much RAM? Many of the entries in my.cnf look "too big" unless you have a lot of RAM.

Please use SHOW CREATE TABLE; it is more descriptive than DESCRIBE.

InnoDB would be faster because the data is clustered with the PRIMARY KEY.

What is happening... Each run of that query is scanning half the index, starting with IPLONG=ip_from and going to the end of the index. Meanwhile, it is reaching over to the data (since it is separate in MyISAM) for the other columns.

Are the ip_from..ip_to pairs non-overlapping? If so, you could check one row, then quit. If they are overlapping, there is no hope.

The LIMIT 1 will not be sufficient without ORDER BY ip_from.

You realize that the table cannot handle IPv6?

Make sure that country_code is CHARACTER SET ascii. Don't include country_name in the same table; build a lookup (normalization) table mapping country_code to country_name.

Related Question