zaquria

Member
Dec 15, 2015
17
1
3
UK
cPanel Access Level
Website Owner
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,

Code:
[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 = <directory>
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

Code:
[[email protected] tmp]# ./tuner.pl
>>  MySQLTuner 1.6.13 - Major Hayden <[email protected]>
>>  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 '[email protected]' 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.
 
Last edited by a moderator:

24x7server

Well-Known Member
Apr 17, 2013
1,911
97
78
India
cPanel Access Level
Root Administrator
Twitter
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.
 

zaquria

Member
Dec 15, 2015
17
1
3
UK
cPanel Access Level
Website Owner
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,
 

zaquria

Member
Dec 15, 2015
17
1
3
UK
cPanel Access Level
Website Owner
Just an update.

We were given this config for MariaDB, but when restarting the Database it throws out an error:

Code:
[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:

Code:
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?
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,904
2,237
463
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.
 

twhiting9275

Well-Known Member
Sep 26, 2002
560
28
178
cPanel Access Level
Root Administrator
Twitter
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
 

zaquria

Member
Dec 15, 2015
17
1
3
UK
cPanel Access Level
Website Owner
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 speeds
 

zaquria

Member
Dec 15, 2015
17
1
3
UK
cPanel Access Level
Website Owner
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
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,904
2,237
463
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.
 

zaquria

Member
Dec 15, 2015
17
1
3
UK
cPanel Access Level
Website Owner
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.

Code:
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   0
 
Last edited by a moderator:

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,904
2,237
463
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.