4

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 <[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 details from my server that you need to check? Please let me know...

Improve this question

4 Answers 4

Reset to default
3

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

Improve this answer
2
  • @RolandoMySQLDBA thanks for your answer! I've added the values in my.cnf and will monitor if CPU usage improves. About the index, they are already set in the table by WordPress codex.wordpress.org/Database_Description, and applied in my table (output too long for comment... pastebin.com/itiS92JK). Should I change somenthing?
    – Ricardo Gonçalves
    Aug 26, 2017 at 16:36
  • The overall load improved by 5-10% , and now I don't see any MySQL process using more then 100% of CPU, what was common before changing these vars. As a temporary solution, it's working great! Thanks @RolandoMySQLDBA
    – Ricardo Gonçalves
    Aug 27, 2017 at 18:55
1

max_connections = 250 would be ample based on 28 Highest used. thread_cache_size = 50 would support your 301K connections in 20 hours and avoid thread creation on demand. Current suggested limit is to cap thread_cache_size at 100 when workload could use this many threads.

Improve this answer
1
  • Thank you! I didn't pay any attention to thread_cache_size and you're correct. I will teste thread_cache_size with 100, that is the maximum according to docs, and will use the max_connections/100 + 8 formula too. Right now this server is with 44 max_connections...
    – Ricardo Gonçalves
    Aug 31, 2017 at 0:27
0

Well, the WP designers need some more DBA staff. It sounds like you have a large (large for WP, that is) number of Posts?

Your query can be simplified from

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;

to

SELECT  SQL_CALC_FOUND_ROWS wp_posts.ID
    FROM  wp_posts
    WHERE  wp_posts.post_type = 'post'
      AND  wp_posts.post_status = 'publish'
    ORDER BY  wp_posts.post_date DESC
    LIMIT  0, 6;

Notes:

  • wp_term_relationships has no impact on the final list because it is connected via LEFT and no fields from it are in the result set.
  • Once you get rid of the JOIN, the GROUP BY is no longer necessary.
  • Then add a "covering" INDEX(post_type, post_status, post_date, ID) . If you remove SQL_CALC_FOUND_ROWS, this index will let it get to the LIMIT without having to gather all the rows.

Further speedup could be had by getting rid of SQL_CALC_FOUND_ROWS, but that would prevent saying "out of nnn rows". (Yes, it should speed up after doing the above things.)

16013769931 is a huge number of hits for only 20 hours.

These are much too high:

tmp_table_size = 1G          -- 100M
max_heap_table_size = 1G     -- 100M
table_open_cache = 512M      -- This is entries, not bytes! --> 200

Also, since the buffer_pool is bigger than necessary, you could change to

innodb_buffer_pool_size = 2500M

Then, shrinking to a 16GB image should be no problem. And the decrease of CPUs is unlikely to hurt. (MySQL won't use more than one core for a single connection.)

Partitioning -- no.

Log efficiency -- probably not an issue.

Parallelism within a connection is over-rated. If you have lots of connections, then multiple cores are in use. CPU parallelism would be wasted if you were I/O-bound. (You are not I/O-bound.) The overhead of splitting up and recombining the data (etc) adds overhead.

MySQL has always been able to run multiple connections sharing even a single CPU. (The OS takes care of the sharing.)

If you would like another analysis of the tunables, see http://mysql.rjweb.org/doc.php/mysql_analysis

More tips on MySQL: http://mysql.rjweb.org/doc.php/ricksrots

Improve this answer
11
  • Thanks for your answer! The problem is that I can't optimize this query right now... still working on legacy code. I believe that you're wrong about the wp_term_relationships table in the query because it says that the wp_post.ID should be equal to wp_term_relationships.object_id. Your new query would bring much more items then the original one.
    – Ricardo Gonçalves
    Aug 26, 2017 at 16:47
  • About shrinking, the problem is not the RAM... even with a 4GB instance, it would owrk seamslessly... but the CPU usage is always very high... that's my problem here... I don't need 8 cores, but when I'm in a 4 cores instance it gets from 30% to 100% CPU usage very fast...
    – Ricardo Gonçalves
    Aug 26, 2017 at 16:55
  • But LEFT says to deliver NULLs if there is no match. And, since you are just getting post.ID, the nulls won't matter.
    – Rick James
    Aug 26, 2017 at 17:02
  • Having a better index cuts back on CPU. Avoiding a table scan cuts back on CPU. Avoiding a sort and tmp table (GROUP BY) cuts back on CPU.
    – Rick James
    Aug 26, 2017 at 17:05
  • Well, like I said I'm not a DBA, so I tested it in the database... and the results were different... pastebin.com/dsgXNBnK
    – Ricardo Gonçalves
    Aug 26, 2017 at 17:10
0

Changing innodb_log_buffer_size = 8M to 16M would cut your write count in half to this single log file, every hour you are operational.

Improve this answer

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.