5

Im using:

Server version: 10.1.22-MariaDB - Source distribution

server spec:

128GB RAM
CPU: E5-2600 

we have around 80 tables with moderate data below 10k record per table, all of them are InnoDB. I would like to utilize it to use more RAM rather than CPU of the server is it possible?

my.cnf :

[client]
port        = 3306
socket      = /tmp/mysql.sock

[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
user    = mysql
datadir = /www/server/data
basedir = /www/server/mysql
log_error = /www/server/data/mariadb.err
#pid-file = /www/server/data/mariadb.pid
default_storage_engine = InnoDB
#skip-external-locking
#loose-skip-innodb
skip-name-resolve

performance_schema = OFF

key_buffer_size = 1024M
max_allowed_packet = 100G
table_open_cache = 4096
sort_buffer_size = 16M
net_buffer_length = 8K
read_buffer_size = 16M
read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 256M
thread_cache_size = 512
query_cache_size = 512M
tmp_table_size = 512M
max_heap_table_size = 512M
table_definition_cache = 8192
table_cache = 4096
join_buffer_size = 10M
sort_buffer_size = 10M
sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES


#skip-networking
#skip-name-resolve
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535

log-bin=mysql-bin
binlog_format=mixed
server-id   = 1
expire_logs_days = 10

default_storage_engine = InnoDB
innodb_data_home_dir = /www/server/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /www/server/data
innodb_buffer_pool_size = 4096M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 1024M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 30
innodb_log_files_in_group = 2
innodb_thread_concurrency = 20
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_io_capacity = 2000
innodb_file_format = Barracuda
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_log_compressed_pages = 0
innodb_flush_neighbors = 0
innodb_adaptive_hash_index_partitions = 16
innodb_buffer_pool_instances = 4

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 1024M
sort_buffer_size = 16M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

UPDATE: we have alot of this kind of SQL hitting on the site (pagination), but its already indexed

SELECT VID, title, duration, addtime, thumb, thumbs, viewnumber, rate, likes, dislikes, type, hd FROM video WHERE active = '1' AND type = 'public' ORDER BY com_num DESC LIMIT 2952, 36

its show something like this on EXPLAIN:

/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 2 queries: 0.562 sec. */
Using index condition; Using where; Using filesort

do you think that the filesort caused it to do more CPU usage?

enter image description here

the cpu usage on photo above (the red box) will spike to 160% on peak hour, how can we reduce it?

  • There are so many factors why a database service will use more CPU than RAM or vice versa, that it is is impossible to "force" a database service to use more RAM. You can analyse all the queries that are hitting the databases and determine which statements could be tuned to read directly (RAM) rather than calculate stuff (CPU). Why is 36% CPU high in your opinion? Depending on what your RDBMS is doing it could be actually quite low. Please elaborate a bit in your question. Why do you want to achieve a reduction in CPU usage? Why do you think it is high? – John aka hot2use Aug 14 '17 at 6:50
  • the cpu usage spike more that 150% during peak hour with current configuration, yet the traffic only 10k-30k page view, our target is to archive 100k page view, is it good that we able to minimize the mysql cpu usage for that? otherwise the server might crash due to high cpu usage? – Teddybugs Aug 14 '17 at 6:58
  • Better than using OFFEST is to remember where you left off. That will decrease usage of both CPU and memory. – Rick James Aug 18 '17 at 18:37
  • For 128GB or RAM, innodb_buffer_pool_size should not be a paultry 4G; rather something more like 100G. – Rick James Dec 31 '17 at 23:01
  • High CPU usually means inadequate indexes (esp, "composite" indexes) and/or poorly written queries. Turn on the slowlog. – Rick James Dec 31 '17 at 23:02
3

I see you see your innodb_thread_concurrency is 20. That's not good. Why ??? Over the years, I have learned that infinite concurrency is best (See my old post MySQL using too much CPU). Usually, I would recommend setting this to 0.

With recent improvements to InnoDB, I learned that 0 may not be the best value in some cases. A comparison of two values (0 and 64) was done the article Percona Server 5.7 performance improvements. The article states:

For ages (Vadim discussed this ten years ago!) InnoDB has had a universal workaround for most scalability issues: the innodb_thread_concurrency system variable. It allows you to limit the number of active threads within InnoDB and reduce shared resource contention. However, it comes with a trade-off in that the maximum possible performance is also limited.

From this article, I started to realize that 0 may not improve CPU a Percona Server instance, but it can slightly improve MySQL. Setting this to 64 may or may not help. Why may or may not ??? The article compares MySQL with Percona. You are using MariaDB. Therefore, you would have to experiment with this. Since innodb_thread_concurrency is dynamic, you could run

SET GLOBAL innodb_thread_concurrency = 0;

or

SET GLOBAL innodb_thread_concurrency = 64;

an see if the CPU will level off.

CPU is not the only issue. I also noticed your innodb_buffer_pool_size is only 4G (4096M) and you have 128G of RAM. From the looks of the processlist, you seem to have a full stack running on the machine. I would suspect that the innodb_buffer_pool_size is too small and MySQL (MariaDB) is spinning its wheels removing old pages and importing new pages in the InnoDB Buffer Pool. You have the room to increase it, because you have 109G (144515380k) of RAM free.

RECOMMENDATIONS

| improve this answer | |
  • And innodb_buffer_pool_instances to be updated accordingly depending of the new value of innodb_buffer_pool_size. If we keep instances to approx. less than 1G then innodb_buffer_pool_instances should be updated to 8 or 16. – maxime_039 Aug 16 '17 at 10:43
  • Rolando -- false conclusion. When the buffer_pool is too small (which it is), I/O goes up, not CPU. Perhaps the OP has less than 4GB of data+indexes? Or at least a small 'working set'. – Rick James Dec 31 '17 at 23:04

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