I have an old website with 5K posts and 14K photos, website not used for 3 years. Now i want to reactive it. Database is about 250mb. I am using Wordpress. I cleanedup and update everything also the theme. Its now very fast and stable with online 20-30 users. But when i try to login admin panel for edit or create a post, it's starting using %200-300 cpu. Can you advise for MySQL settings. I couldnt fix it myself.
VPS Specs: E5-2650v2 (4-CORE) - 8GB Memory - SSD Disc / This VDS using only for this website.
Conf File:
performance_schema=on
tmp_table_size=512M
max_heap_table_size=512M
table_open_cache_instances=16
table_open_cache=2000
table_definition_cache=1000
innodb_log_file_size=128M
innodb_buffer_pool_size=1024M
innodb_log_buffer_size=8M
innodb_buffer_pool_instances=1
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=0
max_connections=100
query_cache_type=0
query_cache_size=0Tuner Output:
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
LANGUAGE = (unset),
LC_ALL = (unset),
LC_CTYPE = "UTF-8",
LANG = "en_US.UTF-8"
are supported and installed on your system.
perl: warning: Falling back to a fallback locale ("en_US.UTF-8").
>> MySQLTuner 1.7.19 - Major Hayden <major@mhtx.net>
>> 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.3.17-MariaDB
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/log/mariadb/mariadb.log exists
[--] Log file: /var/log/mariadb/mariadb.log(11K)
[OK] Log file /var/log/mariadb/mariadb.log is readable.
[OK] Log file /var/log/mariadb/mariadb.log is not empty
[OK] Log file /var/log/mariadb/mariadb.log is smaller than 32 Mb
[!!] /var/log/mariadb/mariadb.log contains 7 warning(s).
[!!] /var/log/mariadb/mariadb.log contains 6 error(s).
[--] 3 start(s) detected in /var/log/mariadb/mariadb.log
[--] 1) 2020-06-14 7:13:47 0 [Note] /usr/libexec/mysqld: ready for connections.
[--] 2) 2020-06-14 7:11:03 0 [Note] /usr/libexec/mysqld: ready for connections.
[--] 3) 2020-06-14 7:03:30 0 [Note] /usr/libexec/mysqld: ready for connections.
[--] 3 shutdown(s) detected in /var/log/mariadb/mariadb.log
[--] 1) 2020-06-14 7:13:44 0 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 2) 2020-06-14 7:11:01 0 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 3) 2020-06-14 7:03:18 0 [Note] /usr/libexec/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in InnoDB tables: 291.0M (Tables: 276)
[OK] Total fragmented tables: 0
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- 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: 43m 29s (56K q [21.557 qps], 1K conn, TX: 280M, RX: 23M)
[--] Reads / Writes: 90% / 10%
[--] Binary logging is disabled
[--] Physical Memory : 7.6G
[--] Max MySQL memory : 3.7G
[--] Other process memory: 0B
[--] Total buffers: 1.8G global + 18.9M per thread (100 max threads)
[--] P_S Max memory usage: 95M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 2.1G (27.89% of installed RAM)
[OK] Maximum possible memory usage: 3.7G (48.46% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/56K)
[OK] Highest usage of available connections: 15% (15/100)
[OK] Aborted connections: 0.00% (0/1605)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 82% (6K on disk / 7K total)
[OK] Thread cache hit rate: 99% (15 created / 1K connections)
[OK] Table cache hit rate: 98% (396 open / 402 opened)
[OK] table_definition_cache(1000) is upper than number of tables(435)
[OK] Open file limit used: 5% (59/1K)
[OK] Table locks acquired immediately: 100% (30 immediate / 30 locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 95.1M
[--] Sys schema isn't installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 4 thread(s).
[--] Using default value is good enough for your version (10.3.17-MariaDB)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/123.0K
[!!] Read Key buffer hit rate: 93.3% (30 cached / 2 reads)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 1.0G/291.0M
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 128.0M * 2/1.0G should be equal to 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 8 for 1 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: 99.99% (155610176 hits/ 155627037 total)
[OK] InnoDB Write log efficiency: 96.94% (39155 hits/ 40391 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1236 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[!!] Aria pagecache hit rate: 91.1% (74K cached / 6K reads)
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /var/log/mariadb/mariadb.log file
Control error line(s) into /var/log/mariadb/mariadb.log file
MySQL was started within the last 24 hours - recommendations may be inaccurate
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
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 for MySQL
Consider installing Sys schema from https://github.com/FromDual/mariadb-sys for MariaDB