CPU, sql memory and swap memory go high after upgrade key

Thanks for the upload. I see nothing obvious wrong. The task of adding keys temporarily takes a lot of CPU and I/O. If you saw a spike during that rekeying task that subsequently settled down, that’s normal.

Your buffer pool is large: about 50GiB; I assume your server has 64GiB or more of RAM. If your server is smaller you may want to take a look at that. Your server may be overprovisioned: you’re only using 30% of that buffer pool. You might save a pile of money by using a 32GiB server. (If you’re on a shared host please ignore this advice.)

The “null query” (SELECT 1) to your database server takes 60+ milliseconds. That’s quite long. It’s sub-millisecond on many servers.

You’re getting a lot of disk sorts. You may want to increase your sort_buffer_size server variable. It’s now at 2GiB. You have the RAM to go to 4 GiB without problems.

You have run out of database connections a few times. You might increase max_connections from the default 151 to, maybe, 176.

I hope this helps.

I may need to add some of these diagnostics to the About tab.

One other thing: your options table contains a surprisingly large number of rows. Do you have many expired transients? Are there unused items in there, maybe from plugins you have discontinued using?

Hi,

Thanks for your reply, yes the server have 120 G RAM

you say that my sort_buffer_size is 2G

but actually in my.cnf it’s

sort_buffer_size = 2M

I change max connection to 500 but my mysql server does’nt lke it

[mysql]
max_connections = 500

ubuntu@modest-hoover:/var/log/mysql$ mysql
mysql: unknown variable ‘max_connections=500’

I also deleted all transients

I wanted to see results on this website because I have an other one bigger to deal with

wp_commentmeta (83 rows, approximately)
wp_postmeta (7?052?497 rows, approximately)
wp_posts (365?141 rows, approximately)
wp_termmeta (17 rows, approximately)
wp_usermeta (1?794?533 rows, approximately)

Apologies, I should have said your sort buffer size was 2MiB (not 2GiB), and suggested increasing it to 4MiB (not 4GiB).

You need to set max_connections in the [mysqld] section of your .cnf file, not the [mysql] section. The [mysql] section gives parameters for the command-line mysql program.

• This reply was modified 1 year, 1 month ago by OllieJones.

ok I will try bufer size at 4G soon

Yes is sort it out yesterday about max_connection ??

I also deploy it on the other website : z2gEB8tB

Hi Ollie,

I have a huge increase of “Query Creating sort i SELECT l.ID, post_title, post_content, post_name,.....“

Is it related to the use of : Index WP MySQL For Speed ?

This query is a normal one to fetch the contents of posts. Can you tell me more about how you noticed this increase in queries? Query Monitor? The slow query log in your database server? Or what?

It’s possible your redis cache experienced some kind of purge and your site had to refresh its contents.

Hi,

I noticed it, after websites get super slow, then I check my server and saw many of this query on mysql server stat, that took at least 10s to 12s to get result.

I just switch one of this 2 website to an other server.

just dump the db and recreate it on the new server.

Does this required any action or update about index ?