1

htop picture

I have 0 slow queries in the log, tried with different my.cnf configurations. Can't see what's the problem. 8 cpu server, 32GB ram, ssd, dedicated mysql server. All cpus > 100%, it's starts slowly, works perfect in the first 15-30 minutes then it goes crazy. High load without reason.

After upgrade i did mysql_upgrade and everything looked ok. Now running 8.0.13 and almost all my tables are MyISAM

[my.cnf] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysqld.log default-authentication-plugin=mysql_native_password sql-mode="" character-set-server=utf8 default-storage-engine = MyISAM wait-timeout=300 long_query_time = 1 slow-query-log = 1 slow-query-log-file = /var/lib/mysql/mysql-slow.log long-query-time = 1 skip_name_resolve = 1 general_log = 0 query_prealloc_size=32K # from default to avoid RAM acquisition all day query_alloc_block_size=32K # from default to manage larger blocks innodb_buffer_pool_size=10G # from 128M to reduce innodb_buffer_pool_reads innodb_lru_scan_depth=128 # from 1024 per REFMAN when ibp_instances=8 expire_logs_days=5 # from 0 for limited historical error,general,slow logs slow_query_log=ON # from OFF to allow awareness of ~ 2000 in 76 days, for improvement of queries thread_cache_size=100 # from 12 to reduce threads_created 353K max_connections=200 # from 400 for max_used_connections 56 x ~ 4 innodb_io_capacity=1000 # from 200 to open the door wider innodb_change_buffer_max_size=10 # from 25% to reduce set aside for CHGS, etc max_write_lock_count=16 # from HUGE number to allow RD after nn lcks join_buffer_size=128M # from 4096 for join capacity increase sort_buffer_size=2M # from 256K to reduce sort_merge_passes ~ 1m updatable_views_with_limit=NO # from YES to reduce handler_external_lock cnt open_files_limit = 32000 table_open_cache = 2000 read_rnd_buffer_size = 2M key-buffer-size=64M

SHOW VARIABLES LIKE '%buffer%' bulk_insert_buffer_size 8388608 innodb_buffer_pool_chunk_size 134217728
innodb_buffer_pool_dump_at_shutdown ON
innodb_buffer_pool_dump_now OFF innodb_buffer_pool_dump_pct 25
innodb_buffer_pool_filename ib_buffer_pool
innodb_buffer_pool_instances 8
innodb_buffer_pool_load_abort OFF innodb_buffer_pool_load_at_startup ON
innodb_buffer_pool_load_now OFF innodb_buffer_pool_size 10737418240 innodb_change_buffer_max_size 10
innodb_change_buffering all innodb_log_buffer_size 16777216
innodb_sort_buffer_size 1048576 join_buffer_size 134217728
key_buffer_size 67108864
myisam_sort_buffer_size 8388608 net_buffer_length 16384
preload_buffer_size 32768
read_buffer_size 131072
read_rnd_buffer_size 2097152 sort_buffer_size 2097152 sql_buffer_result OFF

Improve this question
6
  • What did you see in innotop?
    – Michael Hampton
    Dec 11, 2018 at 13:15
  • 1
    Nothing serious i.imgur.com/6Y9Al4il.jpg
    – maxshop
    Dec 11, 2018 at 13:54
  • 1
    also htop i.imgur.com/CgEuxWV.jpg
    – maxshop
    Dec 11, 2018 at 13:55
  • Look at the rest of the data in innotop. You should be able to find something useful.
    – Michael Hampton
    Dec 11, 2018 at 14:57
  • 1
    Solved the issue. Did a dump, installed a fresh mysql 8.0 and then imported. Everything is perfect now. Thank you very much !
    – maxshop
    Dec 11, 2018 at 16:08

1 Answer 1

Reset to default
0

Suggestion to consider for your my.cnf [mysqld] section

key_cache_age_threshold=7200  # from 300 seconds to reduce key_reads

since most of your tables are MyISAM to improve performance of your instance.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .