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,
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:
,
