How to speed up MySQL?

Started by maxikk, Aug 05, 2022, 05:02 AM

Previous topic - Next topic

maxikkTopic starter

Hello.

There is a website on WP, it is constantly filled with content, as a result, the MySQL database has grown significantly to such volumes that the site began to respond for a long time and, accordingly, page loading takes longer.

Optimization, cleaning, caching just doesn't help anymore.

Maybe there is some way to allocate more resources to the base to speed it up? Sorry in advance for a possibly stupid question, but I can't google anything useful. And me myself did not encounter such situations.

Thanks in advance for help.
  •  

vinodkumar

1. Logic. If you have not climbed into the php admin panel before, then everything is working right there.
The only exception can be the old version, you can change it to a new one and then it will work faster regardless of the size of the DB.
2. Based on the first, you need to delve into the admin panel of the engine. WP has some great tools that I would recommend trying together (only with a backup you can restore): WP Optimize and WP Cleaner.
3. Other plugins and their settings may be to blame, here you already need to somehow identify it yourself or find a professional.

Further and in a safer way.
1. The easiest and most correct way to contact your hosting provider and, if necessary, hire a specialist there. The whole profit lies in the fact that there you will definitely find the cause of your problem, because a normal hosting provider makes sure that your websites work as quickly and stably as possible.
2. If this is your personal server, then you need a specialist who will work for you on a permanent basis. Or study everything yourself, but I will say that I would not initially use phpmyadmin, but MariaDB and other progressive tools.

Addition. There are also all kinds of accelerators at the server level, like redis, but these are already such things, the curves are not suitable for everybody. Oh, by the way!
You can replace the commenting system with an external one by importing everything that is already on the website there - this way you are not standard, but you will significantly reduce your DB, because all the comments will already be on a different host.
  •  

richardBranson

Optimization and indexes
First of all, make sure that you use all the standard database features. Proper work with indexes will give huge performance gains. Hundreds and even thousands of times.
Freeing up resources for other tasks at the same time. Today, the cost of hard drives is constantly decreasing, and the speed requirements are constantly increasing.

Indexes are an effective mechanism to transfer the load from the processor to the hard disk in the right proportions.
Do not rush to optimize all queries in advance. Use the slow query log to understand where the real problems exist.

Immediately after installing MySQL, do not forget to optimize the main parameters. The standard setup is very basic and focused on modest hardware and strict safety requirements.

Adjusting the standard parameters will give a significant increase in acceleration not only of read operations, but also of writes.
Caching
Caching is a very popular method of optimizing performance.

Internal MySQL Cache
Before using an external solution, consider whether it is worth using an internal MySQL cache. It makes sense to include it in cases when MySQL works with a very large number of reads (<var>SELECT</var>), but not very large (at least 10 times less) records (<var>INSERT</var>, <var>DELETE</var> and <var>UPDATE</var>).

It is better not to enable the internal Mysql cache in environments with a large number of records/updates.
The cache is configured using the mysql_query_cache_size parameter.

External solutions
A more flexible solution is to use external caching tools, such as Memcache or Redis. There are a number of data caching techniques in applications.

However, be careful. Caching is often not a solution to a problem, but its postponement. A slow query becomes even slower, and its impact (when the cache is reset) is less predictable.

Caching is best used only as intermediate solutions. In the end, you should get rid of slow queries.
Replication
Despite the fact that replication can help to cope with the load, it is better not to use it for this. You need to remember that along with scaling, you will always have a question of availability. If a replica that helps service requests fails, what will happen to the system?

On the other hand, the replica just allows for high availability. One of the approaches looks like this:

Use master-slave replication for each DB server.
The application always works only with the wizard.
If the master fails, the application switches to slave.
At this time, we raise the broken server and turn it into a slave (how to do it correctly).
Thus, in the new scheme, the master and slave swapped places, and the application (that is, its users) did not notice any problems.
Replication should be used only as a backup mechanism. Not for scaling under loads.

Sharding
Sharding is the principle of database scaling when data is shared across different servers. We have two approaches at our disposal:

Vertical sharding
It should be used first. This is a simple distribution of tables across servers. For example, you put the <var>users</var> table on one server, and the <var>orders</var> table on another. In this case, the groups of tables on which <var>JOIN</var> are executed must be located on the same server.

Horizontal sharding
This type of sharding should be used in the next step. At this stage, very large tables that no longer fit on the same server are divided into parts and their different parts are placed on different servers. This complicates the logic of the application, but the world has not yet come up with better scaling mechanisms.

Sharding is the only approach for scaling really big data.
Other tasks
It should be noted that there are tasks with which MySQL copes extremely poorly. One example is sampling unique values in different ranges. Or a full-text search.
Pay attention to Handlersocket, which can be a replacement for any NoSQL solution, if it is used for simple Key-Value operations.

MySQL is a powerful, but not universal solution. Redis, Elastic and other technologies will help solve additional tasks.
The most important thing
MySQL, together with modern approaches to optimization and scaling, is a powerful platform for building huge systems.
Do not forget to use other technologies for related tasks that MySQL does not handle so effectively.
  •  

Harry_99

There is no single answer to this problem. Some activities need to be done.
1. If the server response time exceeds 200 milliseconds, then you need to change the provider (eg Google PageSpeed test).
2. Install the caching plugin, as forum users wrote, and use caching in the browser.
3. Optimize strings. Remove spaces, newlines, comments, and block markup in HTML, JavaScript, and CSS files.
4. Enable smart Gzip compression. This will reduce the size of the responses that are sent from the server to the browsers.
5. Every time you save a new post or page, WordPress automatically saves a new version and stores it in a growing database. Regularly delete unwanted versions, drafts, moderated comments, trash pages, and other unnecessary data.
6.
7.
8. Etc...
  •