2

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?

0

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.

  • Yes i have plenty of RAM. I changed to InnoDB and seems a lot better now. I also removed the country_code and country_name since i don't need them ( it is the database from ip2location). However i have one strange Problem. Can someone tell me why when i run the SAME query(above) multiple times, i get different results? Is something i'm missing? Of course we are talking about the same IP. – OhGodWhy Oct 2 '16 at 22:31
  • Did you add the ORDER BY that I suggested? Without it, the engine is at liberty to deliver any rows that meet the WHERE clause. – Rick James Oct 2 '16 at 22:42
  • with the ORDER BY THE CPU is 100% all the times. Any idea? – OhGodWhy Oct 2 '16 at 22:49
  • 1
    It may sound strange, but the net.ipv4.tcp_tw_reuse = 1 in systsctl.conf fixed my cpu load with 8000 requests / second and now the results are ok ( Some connections to mysql before were failing thats why i was getting different results). Thanks for everything – OhGodWhy Oct 3 '16 at 9:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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