MariaDB tuning help needed
We are currently having issues with high CPU and memory usages. We believe that this could be down to our database.
I was hoping that someone could kindly have a look over our settings and could give us some help in tuning MariaDB for our server?
I have included the pltuner file and our my.cnf as attachments.
Thanks in advance,
[mysqld]
#performance-schema=0
#innodb_file_per_table=1
#innodb_buffer_pool_size=89128960
#max_allowed_packet=268435456
#open_files_limit=10000
#default-storage-engine=MyISAM
max_allowed_packet=268435456
open_files_limit=10000
[client]
#password = [your_password]
#port = 3306
#socket = /var/lib/mysql/mysql.sock
# *** Application-specific options follow here ***
#
# The MariaDB server
#
[mysqld]
# generic configuration options
#port = 3306
#socket = /var/lib/mysql/mysql.sock
back_log = 50
#skip-networking
max_connections = 500
max_connect_errors = 10
table_open_cache = 400
#external-locking
max_allowed_packet=268435456
binlog_cache_size = 1M
max_heap_table_size = 16M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 4
thread_concurrency = 8
query_cache_size = 8M
query_cache_limit = 2M
ft_min_word_len = 4
#memlock
default-storage-engine = InnoDB
thread_stack = 240K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 16M
log-bin=mysql-bin
expire-logs-days = 14
binlog_format=mixed
#log
#log_warnings
slow_query_log
long_query_time = 2
#tmpdir = /tmp
#*** MyISAM Specific options
key_buffer_size = 32M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
# *** INNODB Specific options ***
#skip-innodb
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 512M
innodb_data_file_path = ibdata1:10M:autoextend
#innodb_data_home_dir =
innodb_write_io_threads = 8
innodb_read_io_threads = 8
#innodb_force_recovery=1
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
#innodb_fast_shutdown
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
#innodb_log_group_home_dir
innodb_max_dirty_pages_pct = 90
#innodb_flush_method=O_DSYNC
innodb_lock_wait_timeout = 120
innodb_buffer_pool_instances = 1
[mysqldump]
# Do not buffer the whole result set in memory before writing it to
# file. Required for dumping very large tables quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Only allow UPDATEs and DELETEs that use keys.
#safe-updates
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
# Increase the amount of open files allowed per process. Warning: Make
# sure you have set the global system limit high enough! The high value
# is required for a large number of opened tables
open-files-limit = 8192
innodb_file_per_table
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 0
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1
transaction-isolation = READ-COMMITTED
thread_handling = pool-of-threads
table_open_cache = 1000
join_buffer_size = 2M
read_buffer_size = 128K
sort_buffer_size = 256K
tmp_table_size = 128M
max_heap_table_size = 128M
skip-name-resolve[root@server tmp]# ./tuner.pl
>> MySQLTuner 1.6.13 - Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.1.14-MariaDB
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 47M (Tables: 131)
[--] Data in InnoDB tables: 1G (Tables: 659)
[OK] Total fragmented tables: 0
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[!!] User 'leechprotect@localhost' has no password set.
[!!] There is no basic password file list!
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 3d 7h 1m 54s (4M q [16.460 qps], 186K conn, TX: 39G, RX: 1G)
[--] Reads / Writes: 91% / 9%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory : 6.0G
[--] Max MySQL memory : 17.4G
[--] Other process memory: 1.4G
[--] Total buffers: 720.0M global + 34.2M per thread (500 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 2.1G (35.40% of installed RAM)
[!!] Maximum possible memory usage: 17.4G (288.07% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (1K/4M)
[OK] Highest usage of available connections: 8% (43/500)
[OK] Aborted connections: 0.07% (125/186091)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache efficiency: 33.8% (1M cached / 5M selects)
[!!] Query cache prunes per day: 198037
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 213K sorts)
[!!] Joins performed without indexes: 2634
[!!] Temporary tables created on disk: 74% (24K on disk / 32K total)
[!!] Table cache hit rate: 2% (400 open / 18K opened)
[OK] Open file limit used: 1% (141/8K)
[OK] Table locks acquired immediately: 100% (2M immediate / 2M locks)
[OK] Binlog cache memory access: 100.00% ( 361069 Memory / 361069 Total)
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 2 thread(s).
[--] Using default value is good enough for your version (10.1.14-MariaDB)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.9% (6M used / 33M cache)
[OK] Key buffer size / total MyISAM indexes: 32.0M/13.5M
[!!] Read Key buffer hit rate: 93.2% (189K cached / 12K reads)
[!!] Write Key buffer hit rate: 65.5% (19K cached / 6K writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 99.9% (259M cached / 368K reads)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 512.0M/1.1G
[OK] InnoDB buffer pool instances: 1
[OK] InnoDB Used buffer: 81.54% (26718 used/ 32767 total)
[OK] InnoDB Read buffer efficiency: 100.00% (619034558 hits/ 619059290 total)
[!!] InnoDB Write Log efficiency: 60.25% (741292 hits/ 1230368 total)
[OK] InnoDB log waits: 0.00% (0 waits / 489076 writes)
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
Reduce your overall MySQL memory footprint for system stability
Dedicated this server to your database for highest performance.
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Beware that open_files_limit (8192) variable
should be greater than table_open_cache ( 400)
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (> 8M)
join_buffer_size (> 8.0M, or always use indexes with joins)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_open_cache (> 400)
innodb_buffer_pool_size (>= 1G) if possible.-
Hello :), Have you tried to update this MySQLTuner scripts recommendations on your server? Please update it on your server and monitor mysql server performance for the next 24 hours. Also try to find out any particular user/databases is using high CPU and memory on your server. 0 -
Hello :), Have you tried to update this MySQLTuner scripts recommendations on your server? Please update it on your server and monitor mysql server performance for the next 24 hours. Also try to find out any particular user/databases is using high CPU and memory on your server.
Hi, Thanks for answering :) No we haven't yet, we wanted to make sure that the information given by the Tuner was in the right direction before we started, but to see if there was any other additional tweak, changes or information that could help here. Regards,0 -
Just an update. We were given this config for MariaDB, but when restarting the Database it throws out an error: [mysqld] symbolic-links=0 max_connections=200 max_user_connections=200 wait_timeout=300 interactive_timeout=300 skip-name-resolve max-connect-errors=1000000 #Slow Queries slow_query_log = 0 long_query_time=5 log_slow_verbosity=Query_plan,explain,Innodb #InnoDB innodb_file_per_table innodb_flush_method=O_DIRECT innodb_flush_log_at_trx_commit=0 innodb_buffer_pool_size =2G innodb_log_file_size=256M innodb_read_io_threads=4 innodb_write_io_threads=4 innodb_buffer_pool_instances=2 innodb_buffer_pool_dump_at_shutdown = 1 innodb_buffer_pool_load_at_startup = 1 transaction-isolation = READ-COMMITTED innodb-defragment=0 innodb_file_format=BARRACUDA innodb_large_prefix=1 #MyISAM Aria key_buffer_size=32M aria_pagecache_buffer_size=256M ft_min_word_len=3 #Thread Cache thread_handling = pool-of-threads #Table cache table_open_cache=1000 open_files_limit=2000 #Buffers join_buffer_size=2M read_buffer_size=128K sort_buffer_size=256K #Query Cache query_cache_type=1 query_cache_size=128M query_cache_limit=6M query_cache_strip_comments=1 #Temporary Tables tmp_table_size=256M max_heap_table_size=256M
This is the error that is thrown out:160619 21:47:14 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 2016-06-19 21:47:14 140260490676256 [Note] /usr/sbin/mysqld (mysqld 10.1.14-MariaDB) starting as process 22323 ... 2016-06-19 21:47:14 140260490676256 [Note] InnoDB: Using mutexes to ref count buffer pool pages 2016-06-19 21:47:14 140260490676256 [Note] InnoDB: The InnoDB memory heap is disabled 2016-06-19 21:47:14 140260490676256 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2016-06-19 21:47:14 140260490676256 [Note] InnoDB: Memory barrier is not used 2016-06-19 21:47:14 140260490676256 [Note] InnoDB: Compressed tables use zlib 1.2.3 2016-06-19 21:47:14 140260490676256 [Note] InnoDB: Using Linux native AIO 2016-06-19 21:47:14 140260490676256 [Note] InnoDB: Using SSE crc32 instructions 2016-06-19 21:47:14 140260490676256 [Note] InnoDB: Initializing buffer pool, size = 2.0G InnoDB: mmap(1107296256 bytes) failed; errno 12 InnoDB: Error: Block 0x7f90ecd2e1e8 incorrect state BUF_BLOCK_POOL_WATCH in buf_LRU_block_free_non_file_page() 160619 21:47:14 [ERROR] mysqld got signal 11 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware.
Anyone know why this would be the case?0 -
Hello, You can search for each specific variable listed under "Variables To Adjust" to determine which configuration value it's referring to. For instance, with "query_cache_size", search engine results show a link to this page: MySQL :: MySQL 5.6 Reference Manual :: 8.10.3 The MySQL Query Cache You can review the comments section of this page to see how other users have utilized this option. As far as the issue when MySQL starts, try disabling the InnoDB variables one by one and restarting MySQL to see which one is the culprit. Thank you. 0 -
High load isn't always high CPU. They can be, and often are very different. Start with adding SSD into your server if you can, offload MySQL onto that. Tweak settings, so you're using RAM not disk for storage and buffering These should both help your situation 0 -
High load isn't always high CPU. They can be, and often are very different. Start with adding SSD into your server if you can, offload MySQL onto that. Tweak settings, so you're using RAM not disk for storage and buffering These should both help your situation
Just an update: This was added to the end of the my.cng and once we removed the highlighted line, the issue went away. #Slow Query Log log-slow-queries slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql_slow.log long_query_time = 2 log-queries-not-using-indexes
We already have a SSD on the server. You will have to forgive me, but I was kinda thrown into the deep end when it comes to managing a server and I am learning as I go. The above my.cng was kindly worked out for me by someone else. I was under the impression that we were already using RAM rather than disk already? Some long winded info. Orginally the server we were on (CENTOS 6.8 x86_64 virtuozzo " server) had 1 gig of RAM and 2 gig of swap space while we were setting up. This had OPcache, Memcached and Pagespeed running and we had no memory issues at all. We then asked our hosting company to upgrade to 4gig, which they did and then we started having memory issues in WHM and our Forum software. We then contacted our hosting regarding this and they said that the forgot to add Swap memory to the build. However, we are still having issues with memory and slow server speeds0 -
Sorry the original server we were on was CENTOS 6.7 x86_64 kvm " server, we are now on CENTOS 6.8 x86_64 virtuozzo " server. Mem: 6340608k total, 5489540k used, 851068k free, 0k buffers Swap: 2097152k total, 267932k used, 1829220k free, 843660k cached 0 -
We then asked our hosting company to upgrade to 4gig, which they did and then we started having memory issues in WHM and our Forum software. We then contacted our hosting regarding this and they said that the forgot to add Swap memory to the build. However, we are still having issues with memory and slow server speeds
Hello, Do you notice any VPS resource limits exceeded in the /proc/user_beancounters file (noticeable by fail counts)? Thank you.0 -
Hello, Do you notice any VPS resource limits exceeded in the /proc/user_beancounters file (noticeable by fail counts)? Thank you.
Sorry if a bit long winded.Version: 2.5 uid resource held maxheld barrier limit failcnt 41: kmemsize 218483061 226496512 9223372036854775807 9223372036854775807 0 lockedpages 5970 12070 9223372036854775807 9223372036854775807 0 privvmpages 394736 1585152 1585152 1585152 493 shmpages 8827 91275 9223372036854775807 9223372036854775807 0 dummy 0 0 9223372036854775807 9223372036854775807 0 numproc 295 432 9223372036854775807 9223372036854775807 0 physpages 567035 1094965 9223372036854775807 9223372036854775807 0 vmguarpages 0 0 9223372036854775807 9223372036854775807 0 oomguarpages 273815 387012 9223372036854775807 9223372036854775807 0 numtcpsock 37 97 9223372036854775807 9223372036854775807 0 numflock 435 638 9223372036854775807 9223372036854775807 0 numpty 0 3 9223372036854775807 9223372036854775807 0 numsiginfo 0 75 9223372036854775807 9223372036854775807 0 tcpsndbuf 667344 3159888 9223372036854775807 9223372036854775807 0 tcprcvbuf 610568 862224 9223372036854775807 9223372036854775807 0 othersockbuf 131784 1364560 9223372036854775807 9223372036854775807 0 dgramrcvbuf 0 13080 9223372036854775807 9223372036854775807 0 numothersock 78 199 9223372036854775807 9223372036854775807 0 dcachesize 195823133 196116566 9223372036854775807 9223372036854775807 0 numfile 1962 3185 9223372036854775807 9223372036854775807 0 dummy 0 0 9223372036854775807 9223372036854775807 0 dummy 0 0 9223372036854775807 9223372036854775807 0 dummy 0 0 9223372036854775807 9223372036854775807 0 numiptent 74 74 9223372036854775807 9223372036854775807 00 -
The fail count for "privvmpages" suggests the VPS is running out of memory. You may want to consult with your VPS hosting provider to discuss adding additional memory to the VPS. Thank you. 0
Please sign in to leave a comment.
Comments
10 comments