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?

Teddybugs
  • 177
  • 2
  • 9

1 Answers1

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

Community
  • 1
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536