0

LEMP stack on DigitalOcean (48GB ram, 960GB SSD, 12 vCPU) with WordPress on it. Added a new theme and mysql went through the roof. 400% CPU usage taken up by MySQL itself!

Looked at slow-log to figure out that SQL_CALC_FOUND_ROWS was the slow query. It fired 3 times when someone searched for a term on the blog.

So I replaced SQL_CALC_FOUND_ROWS with COUNT(*) in my functions.php by following this blog.

But now I see that there are entries like

SELECT  COUNT(*)
    FROM  wp_posts
    WHERE  1=1
      AND  (((wp_posts.post_title LIKE '%zenbot%')
                      OR  (wp_posts.post_excerpt LIKE '%zenbot%')
                      OR  (wp_posts.post_content LIKE '%zenbot%'))
              AND  ((wp_posts.post_title LIKE '%bitmex|Bityard.com%')
                      OR  (wp_posts.post_excerpt LIKE '%bitmex|Bityard.com%')
                      OR  (wp_posts.post_content LIKE '%bitmex|Bityard.com%')
                   )
              AND  ((wp_posts.post_title LIKE '%258U%')
                      OR  (wp_posts.post_excerpt LIKE '%258U%')
                      OR  (wp_posts.post_content LIKE '%258U%')
                   )
              AND  ((wp_posts.post_title LIKE '%Bonus%')
                      OR  (wp_posts.post_excerpt LIKE '%Bonus%')
                      OR  (wp_posts.post_content LIKE '%Bonus%'))
           )
      AND  (wp_posts.post_password = '')
      AND  wp_posts.post_type IN ('post', 'page', 'attachment')
      AND  (wp_posts.post_status = 'publish');

What's this zenbot? Is my WordPress getting hit by bots?

There are more like this

SELECT  COUNT(*)
    FROM  wp_posts
    WHERE  1=1
      AND  (((wp_posts.post_title LIKE '%f(x)%')
                      OR  (wp_posts.post_excerpt LIKE '%f(x)%')
                      OR  (wp_posts.post_content LIKE '%f(x)%'))
              AND  ((wp_posts.post_title LIKE '%binance|Bityard.com%')
                      OR  (wp_posts.post_excerpt LIKE '%binance|Bityard.com%')
                      OR  (wp_posts.post_content LIKE '%binance|Bityard.com%')
                   )
              AND  ((wp_posts.post_title LIKE '%258U%')
                      OR  (wp_posts.post_excerpt LIKE '%258U%')
                      OR  (wp_posts.post_content LIKE '%258U%')
                   )
              AND  ((wp_posts.post_title LIKE '%Bonus%')
                      OR  (wp_posts.post_excerpt LIKE '%Bonus%')
                      OR  (wp_posts.post_content LIKE '%Bonus%'))
           )
      AND  (wp_posts.post_password = '')
      AND  wp_posts.post_type IN ('post', 'page', 'attachment')
      AND  (wp_posts.post_status = 'publish');

I have tweaked my my.cnf but nothing seem to be working. Can anyone please guide? Are my my.cnf settings incorrect?

[mysqld]
performance_schema = ON
max_connections         = 100
connect_timeout         = 5
wait_timeout            = 60
max_allowed_packet      = 64M
thread_cache_size                = 128
sort_buffer_size        = 4M
bulk_insert_buffer_size = 16M
tmp_table_size          = 256M
max_heap_table_size     = 256M
query_cache_limit               = 128K
query_cache_size                = 0
query_cache_type                = 0
slow_query_log = 1
slow_query_log_file     = /var/log/mysql/mariadb-slow.log
long_query_time = 3
default_storage_engine  = InnoDB
# you can't just change log file size, requires special procedure
innodb_log_file_size    = 1811M
innodb_buffer_pool_size = 10G
innodb_log_buffer_size  = 3622M
innodb_file_per_table   = 1
innodb_open_files       = 500000
innodb_io_capacity      = 500000
innodb_flush_method     = O_DIRECT
innodb_read_io_threads=16
innodb_write_io_threads=16
innodb_buffer_pool_instances = 11

Here's mysqltuner result

[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in InnoDB tables: 2.1G (Tables: 58)
[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

-------- Performance Metrics -----------------------------------------------------------------------
[--] Reads / Writes: 99% / 1%
[--] Binary logging is disabled
[--] Physical Memory     : 47.2G
[--] Max MySQL memory    : 22.1G
[--] Other process memory: 0B
[--] Total buffers: 15.0G global + 71.5M per thread (100 max threads)
[--] P_S Max memory usage: 95M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 20.9G (44.37% of installed RAM)
[OK] Maximum possible memory usage: 22.1G (46.89% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (584/15M)
[OK] Highest usage of available connections: 83% (83/100)
[OK] Aborted connections: 0.00%  (0/50946)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 6M sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 95% (204K on disk / 214K total)
[OK] Thread cache hit rate: 99% (83 created / 50K connections)
[OK] Table cache hit rate: 98% (389 open / 395 opened)
[OK] table_definition_cache(400) is upper than number of tables(217)
[OK] Open file limit used: 0% (56/500K)
[OK] Table locks acquired immediately: 100% (44 immediate / 44 locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 95.1M
[--] 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.3.23-MariaDB-1:10.3.23+maria~bionic-log)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/124.0K

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 11.0G/2.1G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (32.1555397727273 %): 1.8G * 2/11.0G should be equal to 25%
[OK] InnoDB buffer pool instances: 11
[--] Number of InnoDB Buffer Pool Chunk : 88 for 11 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% (1464055771 hits/ 1464192013 total)
[!!] InnoDB Write Log efficiency: 59.44% (27661 hits/ 46534 total)
[OK] InnoDB log waits: 0.00% (0 waits / 18873 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 98.8% (60M cached / 745K 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: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
    innodb_log_file_size should be (=1G) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
10
  • cpu power is to be used if available, as long you don't experience, some short comings that other things don't work, don't let it bother you – nbk Jul 19 '20 at 15:20
  • It's actually bothering me because it's making the WordPress dashboard very very slow. Also I found that most of these queries are related to bitcoin etc. So either a malware is using system resources to mine cryptocurrency or some crypto bot is constantly hitting the mysql server. How to detect it? – LittleLebowski Jul 19 '20 at 15:24
  • maka ´e a backup of all data, and let the support, destroy the system and give you a new one, dtecting such things cost tome and knowögde, so a complety rebbot is simpler. – nbk Jul 19 '20 at 15:33
  • But what if it's an external bot that hammering the server? Like a bot attack? In that case I would be wasting time setting up another server. – LittleLebowski Jul 19 '20 at 15:39
  • 1
    that is og course possible and you can only ame backups and keep the apps that you run secure is ti patch averything. if you wnat to know it exactly run a pscesslist in mylsql and and see what is running at this time. The next stepo is running the general log to see what was running the last 8 or 12 hours. HAving a server is a busy job, with alomst every day checjing logs and see if there are updates ti be run, by any component. As i said amke a back up see if evrything is running and start a new, if it happens again the support will monitor your system and see if there is something fishy – nbk Jul 19 '20 at 15:52
1

Many things make your query slow.

  • Leading wild card on LIKE (eg LIKE '%Bonus%'). It must carefully scan every row for every LIKE.

  • If this is meant to be a regexp "or", it is not: LIKE '%binance|Bityard.com%'

  • OR is un-optimizable. Checking 3 columns for the same content means 3 times the work.

  • A FULLTEXT index may be better than all those LIKEs.

  • This index on posts might help:

      INDEX(post_status, post_password, post_type)
    

Don't do COUNT(*) or SQL_CALC_FOUND_ROWS by default; make the user ask for that costly operation separately.

You can't tune your way out of a CPU problem.

1
0

Your innodb_log_buffer_size should NEVER be larger than your innodb_log_file_size. You may have the values reversed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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