• Home
  • Archive
  • Tools
  • Contact Us

The Customize Windows

Technology Journal

  • Cloud Computing
  • Computer
  • Digital Photography
  • Windows 7
  • Archive
  • Cloud Computing
  • Virtualization
  • Computer and Internet
  • Digital Photography
  • Android
  • Sysadmin
  • Electronics
  • Big Data
  • Virtualization
  • Downloads
  • Web Development
  • Apple
  • Android
Advertisement
You are here: Home » MySQL Performance Tuning For WordPress (Percona MySQL)

By Abhishek Ghosh October 13, 2019 10:04 am Updated on October 13, 2019

MySQL Performance Tuning For WordPress (Percona MySQL)

Advertisement

Optimizing MySQL for WordPress running on Virtual Server is not exactly easy. There is no way to predict how the hardware will behave. Most of us deliver optimization tips for the dedicated servers. I saw that cloud server from Rackspace, HP Cloud and IBM Cloud are like dedicated servers. Often for one server setup, we end up with some compromise. If you are using cloud server, then after trial and error of optimization, switch to master-slave replication and use HyperDB, use various methods of caching at application level. We are assuming that you are using InnoDB.

MySQL Performance Tuning For WordPress Percona MySQL

MySQL tuner is the best tool to fix up your database. There are other helpful scripts such as MySQL Tuning primer.

MySQL qery_cache_size was the most misunderstood configuration. The wordings sound like more is better. Funnily, it does instantly increase performance. Setting it to zero :

Advertisement

---

Vim
1
2
query_cache_type                = 0
query_cache_size                = 0

does the best job at long-term. The query cache is deprecated as of MySQL 5.7.20 and is removed in MySQL 8.0. The headache is now gone!

As for Percona MySQL, run /usr/sbin/mysqld --verbose --help command and copy-paste the output on a file to quickly find the default values. Usually, you’ll find one default my.cnf at /etc/mysql/my.cnf and nothing at /etc/my.cnf. Copy it and open in any text-editor to edit :

Vim
1
2
3
4
5
locate my.cnf
cat /etc/mysql/my.cnf
cat /etc/my.cnf
cp /etc/mysql/my.cnf /etc/my.cnf
nano /etc/my.cnf

Change one variable each time and reload the settings by a complete restart :

Vim
1
service mysql restart

Check the front-end for the actual situation. When you’ll reach stable settings, then back up the /etc/mysql/my.cnf file somewhere and use the same my.cnf in both locations.

innodb_buffer_pool_size is the main settings to look at for any installation using InnoDB. The is where data and indexes are cached. You can allocate up to 70% RAM for these settings. 50% is a suggested stable value.

innodb-buffer-pool-instances is the settings to divide the above as separate threads. The number should be 1 for 1.2GB innodb_buffer_pool_size. For 4GB innodb_buffer_pool_size, it should be around 3.

innodb_log_file_size is the size of the redo logs. 1G is a good value for WordPress with one user, not many comment forms.

max_connections does the job as the name suggests. It is practical to have a value between 300 to 800. Too high max_connections like 1000 or more will make the server unresponsive leading to failure. A too low number will force to throw an error.

Newer MySQL are already far better tuned, below is enough good to test (when the server is minimum of 6GB RAM) :

Vim
1
2
3
4
5
6
7
8
9
10
11
12
[mysqld]
# INNODB #
 
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 1G
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 4G
innodb-buffer-pool-instances   = 3
innodb-large-prefix = true
innodb-file-per-table = true

We have kept the example my.cnf on our GitHub repo as well as the default my.cnf. The default io-threads value is 4 for both read and write :

Vim
1
2
innodb-write-io-threads = 4
innodb-read-io-threads = 4

You can increase both to 8. When a lot of innodb_read_io_threads are seen then these settings improve the situation. Do not over-optimize. Do not change all the unknown settings (to you) in a day.

Tagged With mysql tuning for wordpress , tuning wordpress mysql , wordpress mysql query optimization , wordpress performance optimization

This Article Has Been Shared 351 Times!

Facebook Twitter Pinterest
Abhishek Ghosh

About Abhishek Ghosh

Abhishek Ghosh is a Businessman, Surgeon, Author and Blogger. You can keep touch with him on Twitter - @AbhishekCTRL.

Here’s what we’ve got for you which might like :

Articles Related to MySQL Performance Tuning For WordPress (Percona MySQL)

  • Best Tools For Web Server Log Processing & Statistics Running WordPress

    Commonly We Run One Server One WordPress Website Setup. Here Are Some Best Tools For Web Server Log Processing & Statistics Running WordPress Which Are Self-Hosted.

  • Fail2Ban For Web Hosts Without Root Access (PHP Port of Fail2Ban)

    It is Possible to Use Fail2Ban For Web Hosts Without Root Access on Shared Servers of PasS. There is PHP Port of Fail2Ban.

  • Autostart MySQL After a Crash : Solve WordPress Database Connection Error

    WordPress Error establishing a database connection is a frequent problem. Here is one fallback way to autostart MySQL after a crash.

  • Why MySQL Auto Restart Failing in WordPress Cloud Server

    Why MySQL Auto Restart Failing in WordPress Cloud Server Despite Your Efforts of Trying Various Optimization and Scripts to Auto-Restart?

  • WordPress Security : Apache2 Module to Log HTTP POST Method

    Technically We Should Log HTTP Request Methods. Here is How to Apache Module to Log HTTP POST Method, As Example For WordPress Installation.

Additionally, performing a search on this website can help you. Also, we have YouTube Videos.

Take The Conversation Further ...

We'd love to know your thoughts on this article.
Meet the Author over on Twitter to join the conversation right now!

If you want to Advertise on our Article or want a Sponsored Article, you are invited to Contact us.

Contact Us

Subscribe To Our Free Newsletter

Get new posts by email:

Please Confirm the Subscription When Approval Email Will Arrive in Your Email Inbox as Second Step.

Search this website…

 

Popular Articles

Our Homepage is best place to find popular articles!

Here Are Some Good to Read Articles :

  • Cloud Computing Service Models
  • What is Cloud Computing?
  • Cloud Computing and Social Networks in Mobile Space
  • ARM Processor Architecture
  • What Camera Mode to Choose
  • Indispensable MySQL queries for custom fields in WordPress
  • Windows 7 Speech Recognition Scripting Related Tutorials

Social Networks

  • Pinterest (22.1K Followers)
  • Twitter (5.8k Followers)
  • Facebook (5.7k Followers)
  • LinkedIn (3.7k Followers)
  • YouTube (1.3k Followers)
  • GitHub (Repository)
  • GitHub (Gists)
Looking to publish sponsored article on our website?

Contact us

Recent Posts

  • Ways To Make Sure Your Online Course Outshine Others July 3, 2022
  • Will Smart Factories Become the New Assembly Line? July 2, 2022
  • The Cost of Doing Business as a Handyman July 1, 2022
  • Samsung Galaxy S22 Ultra: Long Term Review June 30, 2022
  • How to Make the Most of Your S Pen (S22 Ultra) June 29, 2022

About This Article

Cite this article as: Abhishek Ghosh, "MySQL Performance Tuning For WordPress (Percona MySQL)," in The Customize Windows, October 13, 2019, July 4, 2022, https://thecustomizewindows.com/2019/10/mysql-performance-tuning-for-wordpress/.

Source:The Customize Windows, JiMA.in

This website uses cookies. If you do not want to allow us to use cookies and/or non-personalized Ads, kindly clear browser cookies after closing this webpage.

Read Privacy Policy.

PC users can consult Corrine Chorney for Security.

Want to know more about us? Read Notability and Mentions & Our Setup.

Copyright © 2022 - The Customize Windows | dESIGNed by The Customize Windows

Copyright  · Privacy Policy  · Advertising Policy  · Terms of Service  · Refund Policy