2

I have a big problem with my site (Laravel based), I have from 1000 to 1500 contemporary visitors, and I have a problem with mariadb, practically when people use filters, and other options that require the use of mysql, the server cpu reaches 1000%, following my configurations:

Mysql:

    [client]
socket=/var/lib/mysql/mysql.sock

[mysql]
max_allowed_packet = 128M

[mysqld]
ignore_db_dirs=cmsetiofiotest
local-infile=0
ignore_db_dirs=lost+found
character-set-server=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

bind-address=0.0.0.0
# optimized my.cnf for MariaDB 5.5.x
# by eva2000
# vbtechsupport.com

tmpdir=/home/mysqltmp

innodb=ON
#skip-federated
##skip-pbxt
##skip-pbxt_statistics
#skip-archive
#skip-name-resolve
#old_passwords
back_log = 1024
max_connections = 1500
key_buffer_size = 1024M
myisam_sort_buffer_size = 1024M
myisam_max_sort_file_size = 10240M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 2M
table_definition_cache = 8192
table_open_cache = 8192
thread_cache_size = 384
wait_timeout = 1800
connect_timeout = 10
tmp_table_size = 1536M
max_heap_table_size = 1536M
max_allowed_packet = 128M
#max_seeks_for_key = 4294967295
#group_concat_max_len = 1024
max_length_for_sort_data = 1024
net_buffer_length = 16384
max_connect_errors = 100000
concurrent_insert = 2
read_rnd_buffer_size = 512K
bulk_insert_buffer_size = 8M
# query_cache boost for MariaDB >10.1.2+
# https://community.centminmod.com/posts/30811/
query_cache_limit = 1536K
query_cache_size = 256M
query_cache_type = 1
query_cache_min_res_unit = 2K
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = InnoDB

log_warnings=1
slow_query_log=0
long_query_time=1
slow_query_log_file=/var/lib/mysql/slowq.log
#log-error=/var/log/mysqld.log

# innodb settings
innodb_large_prefix=1
innodb_purge_threads = 4
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_open_files = 2000
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_size = 12288M

## https://mariadb.com/kb/en/mariadb/xtradbinnodb-server-system-variables/#innodb_buffer_pool_instances
innodb_buffer_pool_instances=12

innodb_log_files_in_group = 2
innodb_log_file_size = 1024M
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 2
#innodb_thread_concurrency = 32
innodb_lock_wait_timeout=50
innodb_flush_method = O_DIRECT
innodb_support_xa=1

# 200 * # DISKS
innodb_io_capacity = 1600
innodb_io_capacity_max = 3200
innodb_read_io_threads = 7
innodb_write_io_threads = 7
innodb_flush_neighbors = 0

# mariadb settings
[mariadb]
#thread-handling = pool-of-threads
#thread-pool-size= 20
#mysql --port=3307 --protocol=tcp
#extra-port=3307
#extra-max-connections=1

userstat = 0
key_cache_segments = 1
aria_group_commit = none
aria_group_commit_interval = 0
aria_log_file_size = 1024M
aria_log_purge_type = immediate 
aria_pagecache_buffer_size = 1024M
aria_sort_buffer_size = 256M

[mariadb-5.5]
innodb_file_format = Barracuda
innodb_file_per_table = 1

#ignore_db_dirs=
query_cache_strip_comments=0

innodb_read_ahead = linear
innodb_adaptive_flushing_method = estimate
innodb_flush_neighbor_pages = 1
innodb_stats_update_need_lock = 0
innodb_log_block_size = 512

log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

[mysqld_safe] 
socket=/var/lib/mysql/mysql.sock
#log-error=/var/log/mysqld.log
#nice = -5
open-files-limit = 8192

[mysqldump]
quick
max_allowed_packet = 128M

[myisamchk] 
tmpdir=/home/mysqltmp
key_buffer = 1536M 
sort_buffer = 384M
read_buffer = 384M
write_buffer = 384M

[mysqlhotcopy]
interactive-timeout

[mariadb-10.0]
innodb_file_format = Barracuda
innodb_file_per_table = 1

# 2 variables needed to switch from XtraDB to InnoDB plugins
#plugin-load=ha_innodb
#ignore_builtin_innodb

## MariaDB 10 only save and restore buffer pool pages
## warm up InnoDB buffer pool on server restarts
#innodb_buffer_pool_dump_at_shutdown=1
#innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_populate=0
## Disabled settings
performance_schema=OFF
innodb_stats_on_metadata=OFF
innodb_sort_buffer_size=2M
innodb_online_alter_log_max_size=128M
query_cache_strip_comments=0
log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

[mariadb-10.1]
innodb_file_format = Barracuda
innodb_default_row_format = dynamic
innodb_file_per_table = 1

## wsrep specific
# wsrep_on=OFF
# wsrep_provider
# wsrep_cluster_address
# binlog_format=ROW
# default_storage_engine=InnoDB
# innodb_autoinc_lock_mode=2
# innodb_doublewrite=1
# query_cache_size=0

# 2 variables needed to switch from XtraDB to InnoDB plugins
#plugin-load=ha_innodb
#ignore_builtin_innodb

## MariaDB 10 only save and restore buffer pool pages
## warm up InnoDB buffer pool on server restarts
#innodb_buffer_pool_dump_at_shutdown=1
#innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_populate=0
## Disabled settings
performance_schema=OFF
innodb_stats_on_metadata=OFF
innodb_sort_buffer_size=2M
innodb_online_alter_log_max_size=128M
query_cache_strip_comments=0
log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

# Defragmenting unused space on InnoDB tablespace
innodb_defragment=1
innodb_defragment_n_pages=7
innodb_defragment_stats_accuracy=0
innodb_defragment_fill_factor_n_recs=20
innodb_defragment_fill_factor=0.9
innodb_defragment_frequency=40

And use of the cpu:

1000% CPU MYSQL

My server specifications are: 14 Dedicated Intel® Xeon® Gold 6140 Cores 64gb of ram 1 gigabit connection

I have optimized what I could with redis, but nothing is served, the load is excessive the same,

thanks and best regards

closed as unclear what you're asking by womble Dec 10 '18 at 3:52

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • @Womble - the poor OP was sent here from StackOverflow, where his question was "Closed > Off-topic". The OP clearly has a problem, and he's clearly told us everything he can. Couldn't we at give some additional suggestions as to how he can troubleshoot his/her "100% CPU" situation? The OP needs some GUIDANCE in order to ask a "better question". IMHO... – paulsm4 Dec 10 '18 at 23:17
  • @ZioBomba Additional information request. Post on pastebin.com or here. A) complete (not edited) my.cnf or my.ini Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -xm 5 3 when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, for server tuning analysis and actionable suggestions. View profile, Network profile, please – Wilson Hauck Dec 15 '18 at 22:02
  • @ZioBomba For your consideration of recent success, look at this URL. instagram.com/p/BqicW3Hn_5Y Thanks – Wilson Hauck Dec 17 '18 at 9:50
  • @ZioBomba If you could post the information requested a couple of comments up, MINIMUM of SHOW GLOBAL STATUS; and SHOW GLOBAL VARIABLES; I will try to help you get below 1200% CPU. Merry Christmas – Wilson Hauck Dec 23 '18 at 18:57
  • @zioBomba Suggestions read_buffer_size=256K, read_rnd_buffer_size=192K, query_cache_min_res_unit=512, aria_pagecache_age_threshold=900, key_cache_age_threshold=7200. If you could Skype with me, we can review why these are important Global Variables compared to your current settings. CPU will be reduced and delays should be minimized with these values. Do you have SSD for data storage? – Wilson Hauck Dec 26 '18 at 14:01
0
  1. Clearly you've got a performance problem: all CPUs on your SMP production server are maxed out at 100%. "Not good" :(

  2. Minor quibble: there's no such thing as "1000%". Nevertheless...

  3. Before anybody can help, you need to get a better understanding of the root cause:

    • Maybe there's a bug in your app. Maybe you can tune your database and/or tune your server. Or maybe you just need to buy more hardware.

    • You might consider "dockerizing" your infrastructure, so you can "spin up" more instances as needed to handle the load.

  4. But the FIRST thing you need to do is to establish a "performance baseline", to better understand WHAT is causing the performance deficit. We don't even have enough information yet to determine if the problem is in the database, in Laravel/PHP, or "something else".

    These links might help:

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