I'm not a DBA and need some help here... I have a hight 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 <[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 Please enter your MySQL administrative login: root Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 5.7.19-0ubuntu0.16.04.1-log [OK] Operating on 64-bit architecture -------- Log file Recommendations ------------------------------------------------------------------ [--] Log file: /var/log/mysql/error.log(0B) [OK] Log file /var/log/mysql/error.log exists [OK] Log file /var/log/mysql/error.log is readable. [!!] Log file /var/log/mysql/error.log is empty [OK] Log file /var/log/mysql/error.log is smaller than 32 Mb [OK] /var/log/mysql/error.log doesn't contain any warning. [OK] /var/log/mysql/error.log doesn't contain any error. [--] 0 start(s) detected in /var/log/mysql/error.log [--] 0 shutdown(s) detected in /var/log/mysql/error.log -------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA [--] Data in InnoDB tables: 1G (Tables: 68) [OK] Total fragmented tables: 0 -------- Security Recommendations ------------------------------------------------------------------ [OK] There are no anonymous accounts for any database users [!!] User 'debian-sys-maint@localhost' has no password set. [!!] There is no basic password file list! -------- CVE Security Recommendations -------------------------------------------------------------- [--] Skipped due to --cvefile option undefined -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 20h 12m 2s (6M q [93.845 qps], 301K conn, TX: 8G, RX: 930M) [--] Reads / Writes: 95% / 5% [--] Binary logging is disabled [--] Physical Memory : 31.4G [--] Max MySQL memory : 21.4G [--] Other process memory: 1.0G [--] Total buffers: 13.0G global + 8.6M per thread (1000 max threads) [--] P_S Max memory usage: 72B [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 13.3G (42.24% of installed RAM) [OK] Maximum possible memory usage: 21.4G (68.11% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 0% (0/6M) [OK] Highest usage of available connections: 2% (28/1000) [OK] Aborted connections: 0.00% (4/301157) [!!] 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% (69 temp sorts / 1M sorts) [OK] No joins without indexes [!!] Temporary tables created on disk: 77% (871K on disk / 1M total) [OK] Thread cache hit rate: 99% (28 created / 301K connections) [OK] Table cache hit rate: 99% (1K open / 1K opened) [OK] Open file limit used: 0% (61/1M) [OK] Table locks acquired immediately: 100% (151 immediate / 151 locks) -------- Performance schema ------------------------------------------------------------------------ [--] Memory used by P_S: 72B [--] Sys schema is installed. -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is disabled. -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 18.3% (6M used / 33M cache) [OK] Key buffer size / total MyISAM indexes: 32.0M/43.0K [!!] Read Key buffer hit rate: 92.7% (96 cached / 7 reads) -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [OK] InnoDB File per table is activated [OK] InnoDB buffer pool / data size: 12.0G/1.7G [OK] InnoDB log file size / InnoDB Buffer pool size: 1.5G * 2/12.0G should be equal 25% [OK] InnoDB buffer pool instances: 12 [--] Number of InnoDB Buffer Pool Chunk : 96 for 12 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% (16013769931 hits/ 16013822393 total) [!!] InnoDB Write Log efficiency: 18.62% (55219 hits/ 296557 total) [OK] InnoDB log waits: 0.00% (0 waits / 241338 writes) -------- AriaDB Metrics ---------------------------------------------------------------------------- [--] AriaDB is disabled. -------- 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. [--] This is a standalone server. -------- Recommendations --------------------------------------------------------------------------- General recommendations: Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); ) MySQL started within 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 

I already replaced SQL_CALC_FOUND_ROWSwith COUNT(*)using a hook in WordPress but the queries performance didn't improve.

We could optimize a lot the database to a point that we have an acceptable speed in the page loads, but it's consuming too much CPU and almost nothing of Memory. We are at a 30GB RAM 8 cores box but it's using just 3 GB of memory and 1-2 cores. All the tables are InnoDB and I would like to use more memory and less CPU so we could migrate to a smaller instance with 16GB memory and 4 cores.

So, from what I researched online in Percona and similar blogs and from stackoverflow answers I have some questions:

1. Increase temporary tables created on memory, but I already got tmp_table_size and max_heap_table_size up from 32M to 1GB with no change...
2. Increase write log efficiency... how can I do that?
3. Partition tables would improve the joins/group by/order by?

Are there any data from server that you need to check? Please let me now...