MySQL Forums
Forum List  »  Performance

MySQL perfomance
Posted by: Vladislav Kuzmenko
Date: October 10, 2018 12:05PM

I have 2 main tables on the server, with the same structure and data (the only difference is in the names of the tables).

Table #1:214million rows, size 40GB (25gb indexes)

Table #2:26million rows, size 5.5GB (3.5gb indexes)

The first problem. Everything works very quickly when there is a cache in memory. If i clean cache or reboot the server, then mysql queries is very slow. Debian 8 jessie. MySQL stores indexes in memory and takes it as a cache, always? Because after some manipulations, the select queries is very fast. What manipulations need to be done to make the server fly:

If I using duplicate of table #1, then during its execution, as I understand it, a read operation occurs and at the same time the information is cached into memory. Here is a free -m screen at the moment of launching a duplicate table.

root@ns344370:~# free -m
total used free shared buffers cached
Mem: 128965 76802 52163 31 61 21714
-/+ buffers/cache: 55026 73939
Swap: 56141 0 56141

The result, when the duplicate table was created:

root@ns344370:~# free -m
total used free shared buffers cached
Mem: 128965 126414 2551 31 49 65426
-/+ buffers/cache: 60938 68027
Swap: 56141 0 56141
50GB of RAM under the cache. Execution of the query before the duplicate table 105 seconds:

# Query_time: 105.469931 Lock_time: 0.000180 Rows_sent: 41041 Rows_examined: 2097994
After the duplicate table its 0.2 seconds instead 105 secs. I am thinking of changing the disk from SSHD to NVMe M2 (5x faster). But I will have another 4-5 tables of 50GB each, in this case, I just do not have enough RAM. How to be? I have 128GB RAM. In addition to this project, there is one more project. On which 50GB are allocated. The rest is used for this project and MySQL. I use MyISAM and not InnoDB, because on the site only selects and inserts operations. InnoDB is 20 times slower, checked on a home machine, too, with an M.2 disk.... And here is mysql config file:

port = 3306
socket = /var/run/mysqld/mysqld.sock

socket = /var/run/mysqld/mysqld.sock
nice = 0

user = mysql
pid-file = /var/run/mysqld/
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
query_cache_type = DEMAND

key_buffer_size = 55G
sort_buffer_size = 512M
read_buffer_size = 128M

max_allowed_packet = 32M
thread_stack = 192K
thread_cache_size = 4096

#MySQL Tuner
max_heap_table_size = 128M
tmp_table_size = 128M
table_open_cache = 4096

myisam-recover = BACKUP
max_connections = 2000
table_cache = 2048
thread_concurrency = 17
query_cache_limit = 128M
query_cache_size = 256M

log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 1

expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name

innodb_buffer_pool_size = 512M

max_allowed_packet = 16M

#no-auto-rehash # faster start of mysql but no tab completition

key_buffer = 256M
!includedir /etc/mysql/conf.d/

Options: ReplyQuote

Written By
MySQL perfomance
October 10, 2018 12:05PM
October 10, 2018 04:55PM

Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.