0

Im having trouble to resolve my database errors and how to optimize it. Coz of the slowness of my server (mostly querries i believe), my imports turns with a cloudflare error code. (Cloudflare blocks connections over 15 secs as i remember)

Need advises to fix them.

Im hosting my wordpress website at GCP with 1 vcpu, 4gb ram, 20gb ssd. I use OpenLiteSpeed web server, php 7.4.4, mariadb 15.1. Building an affiliate network site. Hosting the images at google cloud storage. Having trouble about the speed of import. Got over 500k products. Barely imported about 200k. Now im not able to import even one by one or do any other edits via backend. Biggest sized xml is comes with 100k products which is stuck at 96k and not keep moving.

My database size is 3.5gb right now with 200k products. Its going to be double of it with +500k products.

Here are my mysql log and slow log, my.cnf setting, mysqltuner advises and global variables.

This morning i realized that i didnt grant privileges for user@127.0.0.1 after setting up the skip-name-resolve and i did it, i just got 1 mysql error i guess.

Coz of the the mysqltuner keeps offering to increase the amount of installed ram everyday, i converted my all innodb tables to myisam.

Also im looking for help to use indexes with wp_postmeta and wp_posts tables.

Preciate that whoever contribute to fix these issues. Thx a lot.

MY.CNF https://gist.github.com/execash/d2add8445e48c26b3e369b18bcf8a18e

MySql Error Log https://gist.github.com/execash/1870e016c5b4794de3a81bc8635d76f9

Slow logs over 2secs https://gist.github.com/execash/30fbdeb7650a824e140ead3080fa1931

Global variables https://gist.github.com/execash/b6b241b5dc607e6f99b322debd1bb11a

Global status https://gist.github.com/execash/604e2b9625f41d31ce9870eda3935ed5

mysqltuner output https://gist.github.com/execash/aa269f91b5b08d1a4609d5c03d75916a

execash
  • 3
  • 2
  • See https://stackoverflow.com/questions/38536167/is-it-possible-to-increase-cloudflare-time-out. – ceejayoz May 07 '20 at 19:20
  • @execash Please post TEXT results of SHOW GLOBAL STATUS; to go with your SHOW GLOBAL VARIABLES; for workload analysis. – Wilson Hauck May 07 '20 at 21:10
  • 1
    @WilsonHauck added to the post. – execash May 07 '20 at 21:16
  • 1
    @ceejayoz lol. thanks for the advice but im not a paid member of cloudflare thats why asked for help. – execash May 07 '20 at 21:17
  • The link I posted includes things you can do as a non-paid user; namely, moving heavy processing into a backend process that doesn't rely on HTTP requests. – ceejayoz May 07 '20 at 21:18
  • @execash Not every MySQLTuner recommendation should be acted on. Your workload analysis is in process. View my profile, Network profile for contact info and free downloadable Utility Scripts to assist with performance tuning. – Wilson Hauck May 08 '20 at 00:10

1 Answers1

0

Rate Per Second = RPS

Suggestions to consider for your Google Cloud database flags,

log_slow_verbosity=query_plan,explain  # from query_plan for a more useful slow log
read_rnd_buffer_size=128K  # from 2M to reduce handler_read_rnd_next RPS of 67,482
read_buffer_size=512K  # from 4M to reduce handler_read_next RPS of 113,642
key_cache_age_threshold=7200  # from 300 (seconds) to minimize age out, only to read again
key_cache_segments=16  # from 0 to reduce mutex contention

These are only a few of the areas to reduce CPU Busy significantly. For additional assistance, make contact, please.

Observations: Import only 50K rows per 'product migration load' to avoid timeout frustration. Because MyISAM is much slower than innodb for the long term, when migration is completed you will need MORE RAM and cores and additional tuning for innodb data tables. Unless you are actively using the Slow Query Log, there is no need to have it on for more than 15 minutes, get your table indexes created where identified in the EXPLAIN to improve response time. Use of mysqldumpslow will summarize your slow log in a helpful manner. Remember to use multi-column (covering indexes) when appropriate to assist the optimizer.

I will try to find URL to assist with improving wp_postmeta and wp_posts tables. Will post, when found.

Wilson Hauck
  • 472
  • 5
  • 11
  • http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta is one URL by Rick James with suggestions for speeding up wp_postmeta. – Wilson Hauck May 08 '20 at 11:33
  • Hope these are helps a bit. Yeah the main problem for why im not using innodb is ram requirement is getting bigger so as long as my database size keeps growing, innodb_buffer_pool_size is not going to cover it. I'll try the EXPLAIN. What about sort_buffer_size and join_buffer_size values or any other suggestion to reduce cpu load? Does join_buffer_size needs to keep grow whenever mysqltuner suggests it? – execash May 08 '20 at 20:12
  • The use of MYISAM will kill your performance. So what if all innodb does not totally fit in RAM? The way it is built, if it is not data used today, it does not need to be in RAM. You need to be aware or READ counts per second. Our Utility Scripts will help you with awareness of resources being used and freed. No join_buffer_size does not need to increase, you need indexes to avoid table scans and temp storage usage. Every recommendation by MySQLTuner is not to be implemented - unless you are very well funded. – Wilson Hauck May 08 '20 at 21:51
  • Thought that innoDB is suffering coz of out of ram. Well, if its still better than MyIsam i may think about to convert it. However what about to apply innodb_doublewrite=0 to keep the db size lower? – execash May 08 '20 at 22:51
  • If you are concerned about data integrity, you will have equipment strong enough to keep the option enabled. Review this URL, please. https://dev.mysql.com/doc/refman/8.0/en/innodb-doublewrite-buffer.html – Wilson Hauck May 09 '20 at 02:28