Debian Question posted in
The oficial documentation can be found here.

MariaDB Server Optimization – reducing I/O operation – Debian

I need help with MariaDB server optimization. I have a lot I/O operations (created tmp disk tables) and I want to reduce it.

Hardware: CPU 20 x 2197mHz, RAM 50 Gb, SSD disks RAID 10

Software: 10.1.26-MariaDB-0+deb9u1 – Debian 9.1

Server handles WordPress databases (~1500).

Stats

Warnings

Config:

key_buffer_size         = 384M
max_allowed_packet      = 5096M

thread_stack            = 192K
thread_cache_size       = 16

myisam_recover_options  = BACKUP
max_connections         = 200
table_cache             = 12000

max_connect_errors      = 20
open_files_limit        = 30000

wait_timeout            = 3600
interactive_timeout     = 3600

query_cache_type        = 0
query_cache_size        = 0
query_cache_limit       = 0

join_buffer_size        = 2M
tmp_table_size          = 1G
max_heap_table_size     = 1G
table_open_cache        = 15000
innodb_buffer_pool_size = 35G
innodb_buffer_pool_instances = 40
Tags:

2

Answers


  1. 0

    Are you using MyISAM? If you are you shouldn’t be. Convert any MyISAM tables outside the mysql schema to InnoDB and set key_buffer_size to 1M.

    max_allowed_packet = 5G is absurdly high.

    thread_cache_size = 0 is the recommended default. Unless you really know what you are doing and have measurements to back it up, you should leave it alone.

    join_buffer_size is another setting you should almost certainly not be touching.

    max_heap_table_size = 1G is almost certainly too large – if you are getting temporary tables that big created in memory with any regularity, your server will run out of memory, grind to a halt and OOM anyway.

    Login or Signup to reply.
  2. 0

    Yes, follow Gordan’s advice.

    Improve WP’s indexing by following the advice here: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

    Turn on the slowlog to identify what queries are the worst: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search