New on LowEndTalk? Please Register and read our Community Rules.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
Comments
Give me your popular websites, i will visit fews
MySQLTuner should work as well with MariaDB.
i tried mysqltuner.pl but still no performance,
Do you have installed and turned on (on the module side and web server side) LSCache for Wordpress?
yes, i scan and double checked, lscache is active on all the wordpress sites,
also seems lsphp is all around in htop(monitor) list
You need to first check if MariaDB is indeed the issue.
If it is Wordpress, you will have a lot of reads. See if you actually have a lot of reads.
When you say tons of post, how much does that translate to on the DB,
lot of text could fit under a few hundred MBs or a handful of GBs.
From the amount of RAM you have, you could likely fit your entire DB in RAM.
See what are the most of common slow queries.
See if indexing certain columns help, in most cases it does.
The rule of thumb is to index everything that goes under a 'WHERE' clause.
Ensure cache works as intended, see the difference when the cache is cleared vs a page loaded from cache, Most Wordpress sites just need a good cache.
If none of this helps, then look at optimizing your MySQL instance or even look at introducing a read replica.
I have the same problem, except mine uses MySQL— in a need for query optimization.
A simple SELECT query takes 10 minutes and 100% of CPU for 60k row, I found that the database structure needs to be adjusted to lessen the CPU spike, I have yet to solve this problem.
Do not engage cocky mjj. He demand, not asking.
You should use the ANALYZE and EXPLAIN statements to debug a query, and see what is the bottleneck.
so you ran it, adjusted the mysql config and nothing improved? did you run it again?
do you really think that the db is the problem? have you tried any caching plugins inside of wordpress?
what about the stats? how many posts, comments, visits etc do you have combined?
ps: my biggest wordpress project with about 1,5 mil unique visits per month, just under 70k posts, around 500k comments, with 150 new posts and around 500 comments added monthly, is running on a small hetzner vps.
i tried everything.
yes i have bigs sits with big database, mostly around 500MB to 2GB database size for each site, logs showing Select and join 90%.
and traffic is almost 50- to 100K daily.
i am using litespeed license,(1worker, directadmin, with centos 8 stream);
please let me know
Edit: I'm an idiot
no, you are honey bear!!
It's okay, everybody is welcomed in The Great Whole
@fayaz90, you are not explaining what is wrong, are your sites/servers too slow ? How much too slow ?
yes, my site are slow, sometime connection timed out, i even used the litespeed 2 worker license, but nothing,
now my server load average is 35.10, 29.22, 30.11
Without much more information, it's unfortunately a lot of guessing. Whats your current value for the innodb_buffer_pool_size?
mariadb is - besides innodb_buffer_pool_size and those other few keys - pretty much config-less
databases usally scale linear with cpu threads
if your server runs at 35 load isrsly suggest u optimize your queries
hint: look into slow_query_log and log queries not using indexes
innodb_buffer_pool_size = 40G
innodb_log_file_size = 10G
max_allowed_packet = 256M
open_files_limit = 4000000
innodb_file_per_table = 1
max_connections = 200
join_buffer_size = 64M
tmp_table_size = 256M
max_heap_table_size = 256M
Add indexes!!!! Everything will fly
Give this plugin a try on your busy WordPress sites: https://wordpress.org/plugins/index-wp-mysql-for-speed/
BTW: Do you see
x-litespeed-cache: hit
header in your requests for all websites?Cache. Memcache, redis.
yes, let me check,
Lscache, and redis cache
yes, cache: hit
Did you review your PHP logs?
Did you check with top, or htop, which process is consuming what?
yes,
htop
481% /usr/sbin/mysqld/
40% lsphp