leisegang

Member
Aug 13, 2012
10
0
1
Kristiansand, Norway, Norway
cPanel Access Level
Root Administrator
I currently use:

* PHP 7.2 with Zend Opcache
* Engintron (Nginx plugin for cPanel)
* PHP-FPM

6 core / 12 thread server with hdd and 64gb ram

This is my.cnf:

Code:
[mysqld]
log-error=/var/lib/mysql/server.err
#open_files_limit=2000
performance_schema = ON
bind-address=127.0.0.1
local-infile=0
default-storage-engine=InnoDB
skip-name-resolve
table_open_cache = 9500
max_connections = 400
thread_cache_size = 64
max_allowed_packet=268435456
innodb_buffer_pool_size=8G
innodb_buffer_pool_instances = 4
innodb_file_per_table=1
innodb_log_file_size=2G
interactive_timeout = 100
connect_timeout = 60
wait_timeout = 300
query_cache_size = 256M
query_cache_type = ON
query_cache_limit = 2M
tmp_table_size = 256M
max_heap_table_size = 256M
join_buffer_size=64M
read_buffer_size=8M
sort_buffer_size=8M
read_rnd_buffer_size=4M
key_buffer_size=256M
max_connect_errors=10
myisam_sort_buffer_size=256M
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql_slow.log
long_query_time = 2
log-queries-not-using-indexes
[mysqldump]
quick
max_allowed_packet=128M
[mysql]
no-auto-rehash
[isamchk]
key_buffer=128M
sort_buffer=128M
read_buffer=8M
write_buffer=8M
running the mysqltuner.sh gives me this output. what do i need to change to get MYSQL faster.

Code:
[[email protected] ~]# ./mysqltuner.pl
 >>  MySQLTuner 1.7.1 - 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.2.18-MariaDB-log
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/lib/mysql/server.err(110M)
[OK] Log file /var/lib/mysql/server.err exists
[OK] Log file /var/lib/mysql/server..err is readable.
[OK] Log file /var/lib/mysql/server.err is not empty
[!!] Log file /var/lib/mysql/server..err is bigger than 32 Mb
[!!] /var/lib/mysql/server..err contains 715196 warning(s).
[!!] /var/lib/mysql/server.err contains 4004 error(s).
[--] 1 start(s) detected in /var/lib/mysql/server.err
[--] 1) 2018-11-07 20:41:59 116544447940800 [Note] /usr/sbin/mysqld: ready for connections.
[--] 1 shutdown(s) detected in /var/lib/mysql/server.err
[--] 1) 2018-11-07 20:41:53 119839791691520 [Note] /usr/sbin/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 18K (Tables: 4)
[--] Data in InnoDB tables: 4G (Tables: 2403)
[OK] Total fragmented tables: 0
[!!] failed to execute: SHOW TABLE STATUS FROM \`#mysql50#.ssh\`
[!!] FAIL Execute SQL / return code: 256
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 11d 22h 20m 11s (849M q [823.756 qps], 2M conn, TX: 11764G, RX: 222G)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is disabled
[--] Physical Memory     : 62.9G
[--] Max MySQL memory    : 42.2G
[--] Other process memory: 7.0G
[--] Total buffers: 8.9G global + 84.3M per thread (400 max threads)
[--] P_S Max memory usage: 445M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 16.8G (26.72% of installed RAM)
[OK] Maximum possible memory usage: 42.2G (67.13% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (6M/849M)
[OK] Highest usage of available connections: 22% (91/400)
[OK] Aborted connections: 0.01%  (147/2752649)
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 41.8% (591M cached / 1B selects)
[!!] Query cache prunes per day: 1642008
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 30M sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 57% (5M on disk / 8M total)
[OK] Thread cache hit rate: 99% (119 created / 2M connections)
[OK] Table cache hit rate: 29% (4K open / 16K opened)
[OK] Open file limit used: 0% (62/10K)
[OK] Table locks acquired immediately: 100% (8K immediate / 8K locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 445.5M
[--] Sys schema isn't installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 12 thread(s).
[--] Using default value is good enough for your version (10.2.18-MariaDB-log)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (49M used / 268M cache)
[OK] Key buffer size / total MyISAM indexes: 256.0M/226.0K
[OK] Read Key buffer hit rate: 99.9% (225K cached / 146 reads)
[!!] Write Key buffer hit rate: 83.3% (48 cached / 40 writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 8.0G/4.1G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (50 %): 2.0G * 2/8.0G should be equal 25%
[!!] InnoDB buffer pool instances: 4
[--] Number of InnoDB Buffer Pool Chunk : 64 for 4 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (694159282301 hits/ 694159542795 total)
[!!] InnoDB Write Log efficiency: 89.03% (64259682 hits/ 72176022 total)
[OK] InnoDB log waits: 0.00% (0 waits / 7916340 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 100.0% (15B cached / 4M 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:
    /var/lib/mysql/server.erris > 32Mb, you should analyze why or implement a rotation log strategy such as logrotate!
    Control warning line(s) into /var/lib/mysql/server.err file
    Control error line(s) into /var/lib/mysql/server.err file
    Increasing the query_cache size over 128M may reduce performance
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
    query_cache_type (=0)
    query_cache_size (> 256M) [see warning above]
    innodb_log_file_size * innodb_log_files_in_group should be equals to 1/4 of buffer pool size (=4G) if possible.
    innodb_buffer_pool_instances(=8)
[[email protected] ~]#

wo what can i do to get this to work a bit faster?
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,912
2,241
363
Hello @leisegang,

Can you provide some more information about the current performance with MySQL on your system? For instance, is there a particular area that's performing poorly, are you are simply wanting to make sure your getting the best performance possible? Do your database-driven websites use a common CMS (e.g. WordPress), or does it vary?

Thank you.
 

leisegang

Member
Aug 13, 2012
10
0
1
Kristiansand, Norway, Norway
cPanel Access Level
Root Administrator
Its 95% wordpress sites. The big sites has wp-super cache enabled.
I tried a wp performance test and only got about 60 queries per second from that And mysql is using about 15% when I check daily process list in cpanel.

I need mysql to react faster because it seems like its always waiting for database.

All wordpress sites use innodb.

I just need some edits i believe on my config for sql.
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,912
2,241
363