InnoDB memory/cpu use optimization for WordPress with MySQL

I'm not a DBA and need some help here... I have a high traffic WordPress site hosted in a EC2 instance. We are working in a new theme code that will have optimized queries, but right now I need to keep the legacy code with lots of slow queries like this (that are called 3 times in each page load):

# Time: 2017-08-25T17:10:29.753525Z
# User@Host: xxx[xxx] @ localhost []  Id:   442
# Query_time: 13.548223  Lock_time: 0.000147 Rows_sent: 6  Rows_examined: 188232
SET timestamp=1503681029;
SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) WHERE 1=1  AND (
  wp_term_relationships.term_taxonomy_id IN (3)
) AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 6;
/usr/sbin/mysqld, Version: 5.7.19-0ubuntu0.16.04.1-log ((Ubuntu)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock

These are the values I'm using in config

default-storage-engine = InnoDB
key_buffer_size         = 32M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover-options  = BACKUP
max_connections        = 1000
query_cache_type        = 0
query_cache_limit       = 0
query_cache_size        = 0
sort_buffer_size        = 4M
join_buffer_size        = 4M
tmp_table_size = 1G
max_heap_table_size = 1G
table_open_cache = 512M
table_definition-cache = 1024
thread-cache-size              = 50
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 1
innodb-log-file-size           = 1500M
innodb-log-buffer-size         = 8M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 12G
innodb-buffer-pool-instances   = 12
innodb-buffer-pool-dump-at-shutdown = 1
innodb-buffer-pool-load-at-startup = 1
innodb_flush_log_at_trx_commit = 2

And 'mysqltuner.pl' output

```
>> MySQLTuner 1.7.2 - Major Hayden

Sounds like you need an index

ALTER TABLE wp_posts ADD INDEX type_status_date_ndx (post_type,post_status,post_date);

This index is needed because the query has post_type and post_status with static values, while the post_date has a range that that is already ordered.

As for CPU usage, please add this to my.cnf (Restart required)

innodb_read_io_threads=8
innodb_write_io_threads=8

See my post Possible to make MySQL use more than one core? and About single threaded versus multithreaded databases performance for more info

StackExchange Database Administrators Q#184415, answer score: 3