-1

I Run WordPress On my with with Less traffic, 5 or 6 online, but mysql till hits upto 200%, though sometimes it come down to 100 or less, but it still goes up to 200% again, the funniest thing is that there is no query hitting upto 2, its just 0 or 1, But I am still reaching 200%.

Below is my.cnf

[mysqld]
log-error=/var/lib/mysql/vmi195323.contaboserver.net.err
#slow_query_log=1
performance_schema = ON
#collation-server = utf8_unicode_ci
#init-connect='SET NAMES utf8'
#character-set-server = utf8
#default-storage-engine=MyISAM
#max_allowed_packet=268435456
max_allowed_packet      = 32M
open_files_limit=30000
symbolic-links=0


skip-name-resolve=1
#skip-external-locking
key_buffer_size = 2G
#table_open_cache = 4192
#join_buffer_size = 256M
#sort_buffer_size = 8M
#read_buffer_size = 8M
#read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 16M
query_cache_type = 0
#query_cache_limit = 4M
query_cache_size = 0

expire_logs_days=5  # from 0 for limited historical logs
key_cache_age_threshold=64800  # from 300 seconds discard to RD again
key_cache_division_limit=50  # for HOT and WARM usage boundary
key_cache_block_size=16384

#thread_concurrency = 8
#thread_cache_size = 100
tmp_table_size = 72M
max_heap_table_size = 72M

innodb_flush_neighbors=0
innodb_io_capacity=15000

innodb_buffer_pool_size = 7G # (adjust value here, 50%-70% of total RAM)
innodb_log_file_size = 960M
innodb_log_buffer_size=96M
innodb_log_files_in_group = 2

innodb_lru_scan_depth=100
log_warnings=2
max_connect_errors=100
#have_symlink=NO
innodb_purge_threads=4
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_thread_concurrency=0
innodb_flushing_avg_loops=5
innodb_adaptive_max_sleep_delay=10000

innodb_buffer_pool_instances = 7
innodb_flush_log_at_trx_commit = 1 # may change to 2 or 0
innodb_flush_method = O_DIRECT

max_connections=300
#max_user_connections=30
#wait_timeout=40
#interactive_timeout=10
#long_query_time=5

Below is mysql Mysqltuner

>>  MySQLTuner 1.7.10 - 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.17-MariaDB-log
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/lib/mysql/vmi195323.contaboserver.net.err(12M)
[OK] Log file /var/lib/mysql/vmi195323.contaboserver.net.err exists
[OK] Log file /var/lib/mysql/vmi195323.contaboserver.net.err is readable.
[OK] Log file /var/lib/mysql/vmi195323.contaboserver.net.err is not empty
[OK] Log file /var/lib/mysql/vmi195323.contaboserver.net.err is smaller than 32 Mb
[!!] /var/lib/mysql/vmi195323.contaboserver.net.err contains 73427 warning(s).
[!!] /var/lib/mysql/vmi195323.contaboserver.net.err contains 107 error(s).
[--] 29 start(s) detected in /var/lib/mysql/vmi195323.contaboserver.net.err
[--] 1) 2018-11-09 23:43:25 140033329612992 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2018-11-09 23:14:40 140049123117248 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2018-10-31 17:44:53 140575945570496 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2018-10-31  0:52:21 139742272645312 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2018-10-29 15:40:13 140332307847360 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2018-10-22 14:11:57 140273926736064 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2018-10-21  4:45:56 139694061492416 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2018-10-20 21:58:09 139845201819840 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2018-10-05  6:53:08 139745408678080 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2018-10-05  4:28:22 140625873856704 [Note] /usr/sbin/mysqld: ready for connections.
[--] 17 shutdown(s) detected in /var/lib/mysql/vmi195323.contaboserver.net.err
[--] 1) 2018-11-09 23:07:31 140567153977088 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2018-10-31 18:44:15 139731651454720 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2018-09-18 12:04:37 140170985395968 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2018-09-09 23:12:43 140009693865728 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2018-09-09 23:11:36 140024525231872 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2018-09-09 23:10:42 139645632177920 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2018-09-09 23:05:23 140597407938304 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 2018-09-09 22:51:00 139671733114624 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 9) 2018-09-09 22:47:41 140515561195264 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 10) 2018-09-09 22:46:17 139697642653440 [Note] /usr/sbin/mysqld: Shutdown complete

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 408.5M (Tables: 33)
[--] Data in InnoDB tables: 2.4G (Tables: 56)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[!!] User 'root@localhost' has no password set.
[!!] User 'oybqgudp_mimo@%' does not specify hostname restrictions.
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1d 8h 19m 34s (9M q [83.127 qps], 148K conn, TX: 35G, RX: 2G)
[--] Reads / Writes: 89% / 11%
[--] Binary logging is disabled
[--] Physical Memory     : 15.5G
[--] Max MySQL memory    : 10.3G
[--] Other process memory: 1.4G
[--] Total buffers: 9.3G global + 2.9M per thread (300 max threads)
[--] P_S Max memory usage: 112M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 9.4G (60.86% of installed RAM)
[OK] Maximum possible memory usage: 10.3G (66.08% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (3/9M)
[OK] Highest usage of available connections: 5% (15/300)
[OK] Aborted connections: 0.12%  (176/148645)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (1 temp sorts / 1M sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 54% (350K on disk / 644K total)
[OK] Thread cache hit rate: 99% (19 created / 148K connections)
[OK] Table cache hit rate: 97% (228 open / 234 opened)
[OK] Open file limit used: 1% (107/10K)
[OK] Table locks acquired immediately: 100% (65K immediate / 65K locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 112.5M
[--] Sys schema is installed.

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

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 1.4% (30M used / 2B cache)
[OK] Key buffer size / total MyISAM indexes: 2.0G/16.5M
[OK] Read Key buffer hit rate: 99.9% (31K cached / 22 reads)
[OK] Write Key buffer hit rate: 98.9% (8K cached / 8K writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 7.0G/2.4G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 960.0M * 2/7.0G should be equal 25%
[OK] InnoDB buffer pool instances: 7
[--] Number of InnoDB Buffer Pool Chunk : 56 for 7 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% (39506897899 hits/ 39506970499 total)
[!!] InnoDB Write Log efficiency: 59.16% (373182 hits/ 630764 total)
[OK] InnoDB log waits: 0.00% (0 waits / 257582 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 95.3% (7M cached / 350K 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.
[--] Binlog format: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /var/lib/mysql/vmi195323.contaboserver.net.err file
    Control error line(s) into /var/lib/mysql/vmi195323.contaboserver.net.err file
    Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    Restrict Host for user@% to user@SpecificDNSorIp
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
Variables to adjust:
    tmp_table_size (> 72M)
    max_heap_table_size (> 72M)
Improve this question
6
  • Is the system slow? Do you have an actual problem?
    – Vérace
    Nov 11, 2018 at 12:18
  • the system is not actually slow, but sometimes it climbs more than 200% and by then the site becomes slow and everything on it start loading slowly.
    – Godwin Alex Ogbonda
    Nov 11, 2018 at 12:22
  • When does this happen? Regulaly? Time of day/week/month/year?
    – Vérace
    Nov 11, 2018 at 12:52
  • It happens regularly
    – Godwin Alex Ogbonda
    Nov 11, 2018 at 19:29
  • @GodwinAlexOgbonda And HOW LONG does it sit at 200% before it returns to what you consider to be a reasonable %? And what is your reasonable % you are hoping for? Everyone, keep in mind Godwin has 6 cores and 16GB of RAM. Innodb data space grew by 1G in the past week from 1.4 GB to 2.4G per Mysqltuner.pl reporting. There have been NO stop/start of services, just 200% busy for we DO NOT KNOW yet from Godwin how many seconds before CPU % goes back to his expected CPU percentage. Happens regularly needs to be more specific. How many times a day have you observed this 200% CPU busy.
    – Wilson Hauck
    Nov 12, 2018 at 1:59

3 Answers 3

Reset to default
0

High CPU almost always means an inefficient query.

One way to locate such is via SHOW FULL PROCESSLIST;; you might catch it. Another way is to turn on the slowlog, wait a day, analyze the log (pt-query-digest), then ask for help. More here .

Since you are not using MyISAM, lower key_buffer_size from 2G to 50M. That won't speed up things but might avoid wasting RAM that could be better used for something else.

One known problem with WP is its postmeta schema is quite inefficient. See this for how to speed it up. (Without knowing your particular slow queries, I can't say whether this will be the 'answer'.)

Improve this answer
13
  • Yes it has dropped a bit, I use crontab to restart database ever 1 hour. I am just thinking if it is dangerous doing that.... I think bad bot was the cause, I was able to block some via wp plugin. But still need to block it via server which I don't really know how to, any help???
    – Godwin Alex Ogbonda
    Nov 19, 2018 at 12:50
  • @GodwinAlexOgbonda What is result of SELECT @@max_connect_errors? Thanks
    – Wilson Hauck
    Nov 26, 2018 at 18:27
  • @GodwinAlexOgbonda - Are you thinking that someone is hacking into your system?
    – Rick James
    Nov 26, 2018 at 20:02
  • @Rick James, I found many attempt of brute force on my ssh. so I changed my port and everything stopped, but I still dont know why my sql will be going up, though the performance has improve a bit, after adding figures to query_cache_size, type & limit
    – Godwin Alex Ogbonda
    Nov 26, 2018 at 22:19
  • @WilsonHauck here is the output +----------------------+ | @@max_connect_errors | +----------------------+ | 100 | +----------------------+ 1 row in set (0.00 sec)
    – Godwin Alex Ogbonda
    Nov 26, 2018 at 22:21
0

Suggestions to consider for your instance of MySQL to run for a full day

SET GLOBAL innodb_stats_auto_recalc=0;
SET GLOBAL innodb_stats_persistent=0;

per High Performance MySQL by Schwartz, Zaitsev & Thachenko (O'Reilly) page 197 to avoid automatic reindexing (CPU spikes) during your working day. Continue managing your TABLE DEFRAGs periodically with OPTIMIZE (weekly suggested).

and SET GLOBAL innodb_stats_sample_pages=32;
to more accurate cardinality of your indexes.

If NO harm is done make these changes to your my.cnf for next STOP/START to be effective.

Improve this answer
0

It is entirely possible to have a high CPU load with only a few users. It just depends what kind of processes are running on the database.

I've found the following fixes to be useful in cases of high CPU usage:

  1. Enable InnoDB to handle high number of concurrent connections – Check MySQL PROCESSLIST, and if you see a lot of queries in LOCK status, it means a lot queries are put on hold because MyISAM tables are handling other transactions. To fix this convert those tables into InnoDB engine which supports row-level locking.
  2. Enable persistent connections
  3. Block abusive processes
  4. Optimize database queries
Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.