Plz Help ME Tuning MySQL

A

AhrimanSefid

Verified User
Joined
Nov 2, 2016
Messages
19
Hi All.
plz Help Me For Tuning MySQL


Code: 
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 17m 40s (15K q [14.195 qps], 1K conn, TX: 19M, RX: 1M)
[--] Reads / Writes: 83% / 17%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory     : 31.0G
[--] Max MySQL memory    : 1.0G
[--] Other process memory: 971.9M
[--] Total buffers: 688.1M global + 2.5M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 721.2M (2.27% of installed RAM)
[OK] Maximum possible memory usage: 1.0G (3.38% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/15K)
[OK] Highest usage of available connections: 8% (13/151)
[OK] Aborted connections: 0.00%  (0/1722)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 14.5% (1K cached / 7K selects)
[OK] Query cache prunes per day: 0
[!!] Sorts requiring temporary tables: 36% (562 temp sorts / 1K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 65% (593 on disk / 912 total)
[OK] Thread cache hit rate: 98% (20 created / 1K connections)
[OK] Table cache hit rate: 58% (166 open / 283 opened)
[OK] Open file limit used: 0% (234/65K)
[OK] Table locks acquired immediately: 99% (6K immediate / 6K locks)
[OK] Binlog cache memory access: 100.00% (270 Memory / 270 Total)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema isn't installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 8 thread(s).
[--] Using default value is good enough for your version (5.5.41-MariaDB-log)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 29.4% (118M used / 402M cache)
[OK] Key buffer size / total MyISAM indexes: 384.0M/116.1M
[OK] Read Key buffer hit rate: 99.4% (7M cached / 41K reads)
[!!] Write Key buffer hit rate: 0.0% (6 cached / 6 writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[!!] InnoDB File per table is not activated
[!!] InnoDB buffer pool / data size: 128.0M/271.8M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (7.8125 %): 5.0M * 2/128.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 98.99% (94795 hits/ 95758 total)
[!!] InnoDB Write Log efficiency: 154.31% (483 hits/ 313 total)
[OK] InnoDB log waits: 0.00% (0 waits / 796 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 100.0% (49M cached / 1K reads)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.

-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.

-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect 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:
    Control warning line(s) into /var/lib/mysql/da.test.com.err file
    Control error line(s) into /var/lib/mysql/da.test.com.err file
    Remove Anonymous User accounts - there are 2 anonymous accounts.
    66 CVE(s) found for your MySQL release. Consider upgrading your version !
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Performance shouldn't be activated for MySQL and MariaDB 5.5 and lower version
    Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
    query_cache_type (=0)
    query_cache_limit (> 512K, or use smaller result sets)
    sort_buffer_size (> 2M)
    read_rnd_buffer_size (> 128K)
    tmp_table_size (> 128K)
    max_heap_table_size (> 128K)
    performance_schema = OFF disable PFS
    innodb_file_per_table=ON
    innodb_buffer_pool_size (>= 271M) if possible.
    innodb_log_file_size * innodb_log_files_in_group should be equals to 1/4 of buffer pool size (=64M) if possible.
 
Here is my.cnf from a tuned 32GB ram + 16 cores CPU server:

Code: 
[mysqld]
local-infile=0
innodb_file_per_table
max_connections = 512
max_user_connections = 100
wait_timeout = 30
interactive_timeout = 30
key_buffer_size = 2048M
max_allowed_packet = 64M
table_open_cache = 15288
table_definition_cache = 15288
open_files_limit = 24576
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
join_buffer_size  = 2M
myisam_sort_buffer_size = 64M
thread_cache_size = 32
#query_cache_size = 80M
#query_cache_limit = 256K
#query_cache_min_res_unit = 2k
#query_cache_type = 1
slow_query_log = 1
slow_query_log_file = /var/log/mysql-slow-queries.log
long_query_time = 10
max_heap_table_size = 512M
tmp_table_size = 512M
innodb_buffer_pool_size = 4096M
skip-name-resolve
innodb_log_file_size = 512M
innodb_buffer_pool_instances = 4


[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Please note that Query Cache is disabled in that setup. You may want to enable it if you have just few heavy websites rather than hundreds of small ones.

Using that setup you are giving approximate a little bit more than 10GB of ram to MySQL:

Code: 
MariaDB [(none)]> SELECT ( @@key_buffer_size + @@query_cache_size +
    -> @@tmp_table_size + @@innodb_buffer_pool_size +
    -> @@innodb_additional_mem_pool_size +
    -> @@innodb_log_buffer_size +
    -> @@max_connections * (
    -> @@read_buffer_size + @@read_rnd_buffer_size +
    -> @@sort_buffer_size + @@join_buffer_size +
    -> @@binlog_cache_size + @@thread_stack
    -> )
    -> ) / 1073741824 AS MAX_MEMORY_GB;
+---------------+
| MAX_MEMORY_GB |
+---------------+
|        9.1812 |
+---------------+
1 row in set (0.00 sec)

The max_connections can be increased and buffers decreased if you have users who are making (for some reason) many connections with one script (100 is actually an overkill like defined above).

Another thing to consider is the types of storage engines that most of the sites are using. If they are innodb, leave more buffers for innodb. In reverse - if they are myisam, leave more buffers for it and decrease innodb.
 
Last edited:
Thank you.

My Server Info And Only one Site Run In Server (Blogs) :

sys.png
 
Maybe a stupid question. I have installed Mariadb and looked into the settings in my.cnf for Mysql. There is only this code in it:

Code: 
!includedir /etc/my.cnf.d

In that directory there is no specific config file for Mariadb/mysql. How/where can I find my.cnf to optimize, or where to add some rules of the examples in this threat?
 
You must log in or register to reply here.
Back
Top