Server load

[adriangrz]

that one.

After changing file restart mysqld service?

 

[Richard G]

Yes.

 

[cjd]

From others suggestions you may want to make these changes, remember to make backup copies of your config files so if something breaks or is not working as expected you can go back to the old version.

innodb_log is normally about 25% of the pool size, this is if your pool is 6G as has been stated in last messages, it's good to disable name resolution, and on a web server increase the buffer pool instances. over time you need to watch how large your innodb data size is and adjust the buffer pool, and when you do that also adjust the innodb_log size.

innodb_log_file_size = 1536M
innodb_buffer_pool_instances = 16
skip-name-resolve = 1

here are some other options if your still having issues, but note this will delay writes to disk, if there is a power loss data could be lost that was to be written, but will help improve many small writes performance if your storage is a little slow on writes

innodb_flush_log_at_trx_commit = 2
innodb_flush_log_at_timeout = 10

optimizing query cache can help, i wouldn't go much higher than these values (i use these as the max value on my servers), as it has dimminishing returns very quickly much past this point i have found:

query_cache_size = 128M
query_cache_type= 1
query_cache_limit= 5M

 

[ccto]

I believe you have a lot lot lot of records inside wp_postmeta.
In WordPress, post_id shall already have index inside wp_postmeta.

---

One possibility, a few particular web page(s) URL being constantly frequently crawled by (e.g.) bots.
Check it inside web server log.

---

If not, you (or your end-user) need to check your WordPress why the SQL is so slow.
(explain plan may be helpful)

You may run that SQL inside phpMyAdmin to see
- how many second(s) it needs to execute, and
- how many data it returned. Then, check for it.

---

IMHO, changing my.cnf parameters does not help much. It relates to DoS , rate-limit, and/or SQL tuning.

some processes are constantly showing (these marked in red) - why? , other are working normally

 

[cjd]

Only thing I should add to my last post, you should check your swapiness. I would recommend setting it to a value of 1, which roughly means that it won't start using the swap partition/file untill it is absolutly necessary, setting it to zero is not a good idea and may cause strange issues with mysql/mariadb. As long as you have enough ram you should try to avoid swap usage as much as possible to maintain performance. The default linux kernel setting for a web server or database server is not very good, it defaults to starting to use swap when you get down to 60% free ram.

Here is some information on how to change it:

https://linuxize.com/post/how-to-change-the-swappiness-value-in-linux/

There are a lot of other guides out there on how to do it.

 

[Zhenyapan]

innodb_log_file_size = 1536M innodb_buffer_pool_instances = 16

be carefull with such large log, in case of crash - you can crash a lot of DBs, also it slows mysql restart.
instances - ignored in modern mariadb, as I remember

 

[Ohm J]

innodb_buffer_pool_size=1G
innodb_log_file_size=256M

should be fine in many case.

tuning more than this, it will dangerous in future.

 

[adriangrz]

IMHO, changing my.cnf parameters does not help much. It relates to DoS , rate-limit, and/or SQL tuning.

so you suggest that before changing anything in my.cnf I should optimize databases in Wordpress?

 

[adriangrz]

You may run that SQL inside phpMyAdmin to see
- how many second(s) it needs to execute, and
- how many data it returned. Then, check for it.

please write sample queries to adapt and run on my phpmyadmin to check

 

[adriangrz]

-------- MyISAM Metrics ------------------------------------------------------------------------ ----
[!!] Consider migrating 424 following tables to InnoDB:
[--] * InnoDB migration request for testowy_spes.wpuo_usermeta Table: ALTER TABLE testowy_spes.w puo_usermeta ENGINE=InnoDB;
[--] * InnoDB migration request for testowy_spes.wpuo_postmeta Table: ALTER TABLE testowy_spes.w puo_postmeta ENGINE=InnoDB;
[--] * InnoDB migration request for testowy_spes.wpuo_links Table: ALTER TABLE testowy_spes.wpuo _links ENGINE=InnoDB;
[--] * InnoDB migration request for testowy_spes.wpuo_term_relationships Table: ALTER TABLE test owy_spes.wpuo_term_relationships ENGINE=InnoDB;
[--] * InnoDB migration request for testowy_spes.wpuo_options Table: ALTER TABLE testowy_spes.wp uo_options ENGINE=InnoDB;
[--] * InnoDB migration request for testowy_spes.wpuo_terms Table: ALTER TABLE testowy_spes.wpuo _terms ENGINE=InnoDB;
[--] * InnoDB migration request for testowy_spes.wpuo_users Table: ALTER TABLE testowy_spes.wpuo _users ENGINE=InnoDB;
[--] * InnoDB migration request for testowy_spes.wpuo_comments Table: ALTER TABLE testowy_spes.w puo_comments ENGINE=InnoDB;
[--] * InnoDB migration request for testowy_spes.wpuo_posts Table: ALTER TABLE testowy_spes.wpuo _posts ENGINE=InnoDB;
[--] * InnoDB migration request for testowy_spes.wpuo_term_taxonomy Table: ALTER TABLE testowy_s pes.wpuo_term_taxonomy ENGINE=InnoDB;
[--] * InnoDB migration request for testowy_spes.wpuo_commentmeta Table: ALTER TABLE testowy_spe s.wpuo_commentmeta ENGINE=InnoDB;
[--] * InnoDB migration request for tomsyl_ts.wp_comments Table: ALTER TABLE ENG INE=InnoDB;
a lot of THIS ...........

is it something that I should do with this migration suggestion? This can have impact on databases performance? Or I should not touch this and ignore this errors?

 

[ccto]

please write sample queries to adapt and run on my phpmyadmin to check

You need to check this SQL statement why it runs slow, eat CPU.
Too many data returned, or other reason.



Optimize table shall not improve much probably.