MySQL taking up 400% CPU

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.