Tuning my.cnf and 64GB Ram and 16 WordPress websites

SaeedTJ

Verified User
Joined
Aug 20, 2017
Messages
34
I have a 4x1TB SSD Raid-0 with 2000MB/s Read/Write speed
The memcached is active and works and also The Redis (in WordPress I can choose one of the and it's ok)
web server is OpenLitespeed and the php version is 7.3 and mariadb 1.4 and I'm running about 16 WordPress websites but the speed is not enough.
I see a server with half of sources and even with HDD 5400/rpm and running about 70 WordPress websites is 2X faster than mine.
SO WHATS THE PROBLEM??!!
I asked about it and they said this goes back to my.cnf configuration the default my.cnf config is:
Code:
[mysqld]
local-infile=0
innodb_file_per_table
the mysqltuner is tested and did not solve the problem (wasn't effective)
 
Hello,

The mysqltuner is not intended to fix anything, it shows possible issues and gives recommendations. It's up fully to you to update my.cnf with the recommend values.

And tuning of only MySQL might or not help you. You should take a full overview on what you have there: plugins/addons, calls to remote URLs, possible PHP errors, etc.

Consider enabling cache in WP as well.
 
Hello,

The mysqltuner is not intended to fix anything, it shows possible issues and gives recommendations. It's up fully to you to update my.cnf with the recommend values.

And tuning of only MySQL might or not help you. You should take a full overview on what you have there: plugins/addons, calls to remote URLs, possible PHP errors, etc.

Consider enabling cache in WP as well.

Hello,

The mysqltuner is not intended to fix anything, it shows possible issues and gives recommendations. It's up fully to you to update my.cnf with the recommend values.

And tuning of only MySQL might or not help you. You should take a full overview on what you have there: plugins/addons, calls to remote URLs, possible PHP errors, etc.

Consider enabling cache in WP as well.

the only plugins are woocommerce and LsCache
I put this codes in my.cnf and now it is a little (about 3%) faster:

Code:
[mysqld]
local-infile=0
innodb_file_per_table
query_cache_size=0
query_cache_type=0
query_cache_limit=1M
tmp_table_size=256M
max_heap_table_size=256M
table_definition_cache= -1
performance_schema = ON
innodb_buffer_pool_size=2G
innodb_log_file_size=256M
innodb_buffer_pool_instances=2
skip-name-resolve=1
join_buffer_size=8M

BUT I'm deadly sure there is better config for my.cnf
 
You have this:
Code:
innodb_file_per_table

But it should be like this:
Code:
innodb_file_per_table=1
 
Use

- slow query logging in MySQL/MariaDB to find slow queries
- profiling in MySQL/MariaDB to analyze slow queries

Enable a profiler in WP and see how much time does PHP part, SQL part take. And you might be surprised that SQL queries are the ones which might need to be optimized, and it's not a server's side issue.

p.s. MySQL/MariaDB is not always the one which makes a site to work slow. Hardly can you speed up a SQL-server enough trough editing my.cnf if you have very heavy SQL-queries to a table containing 1,000,000+ items without indexes ;)
 
I'm kinda late in the thread, but my question is more basic, before giving a recoomendation. Please let us know

1. How are you testing speed (which commands, online tool or browser extension)
2. Where is your server located and where is location from which you are testing
3. How did you arrive at that mysql is the issue?
4. Which DB engine are your tables using?
5. What is your current memory usage breakup?

Lastly if you give us your site url and the competitor site url, it woul help.
 
What is your server load?
Location of server is important, make sure you test from nearest location
4x1TB SSD on RAID-0 are sure?
If you provide Server specification it will be helpful to suggest you something
 
I have a 4x1TB SSD Raid-0 with 2000MB/s Read/Write speed
The memcached is active and works and also The Redis (in WordPress I can choose one of the and it's ok)
web server is OpenLitespeed and the php version is 7.3 and mariadb 1.4 and I'm running about 16 WordPress websites but the speed is not enough.
I see a server with half of sources and even with HDD 5400/rpm and running about 70 WordPress websites is 2X faster than mine.
SO WHATS THE PROBLEM??!!
I asked about it and they said this goes back to my.cnf configuration the default my.cnf config is:
Code:
[mysqld]
local-infile=0
innodb_file_per_table
the mysqltuner is tested and did not solve the problem (wasn't effective)

Already try to used another web server? My opinion openlitespeed is not too prefer.
 
Use

- slow query logging in MySQL/MariaDB to find slow queries
- profiling in MySQL/MariaDB to analyze slow queries

Enable a profiler in WP and see how much time does PHP part, SQL part take. And you might be surprised that SQL queries are the ones which might need to be optimized, and it's not a server's side issue.

p.s. MySQL/MariaDB is not always the one which makes a site to work slow. Hardly can you speed up a SQL-server enough trough editing my.cnf if you have very heavy SQL-queries to a table containing 1,000,000+ items without indexes ;)

How can I make them with indexes?
I heard about it but I think WordPress do that automatically
 
I'm kinda late in the thread, but my question is more basic, before giving a recoomendation. Please let us know

1. How are you testing speed (which commands, online tool or browser extension)
2. Where is your server located and where is location from which you are testing
3. How did you arrive at that mysql is the issue?
4. Which DB engine are your tables using?
5. What is your current memory usage breakup?

Lastly if you give us your site url and the competitor site url, it woul help.

answer1: there is no need to use a tool I can sense it its slow when i takes 4 seconds to load.
answer2: The location is Iran and I'm also in Iran
answer3: any other part is okey and as I mentioned 4 different people told me it goes back to mysql config
answer4: MariaDB/ InnoDB engine
answer5: 10GB of 64GB
 
What is your server load?
Location of server is important, make sure you test from nearest location
4x1TB SSD on RAID-0 are sure?
If you provide Server specification it will be helpful to suggest you something

Yes I am deadly sure cause I did it with raid controller p410i you can see this link and it will tell you that it makes the speed about 2000MB/s
and my place is onle 30KM far form the data center.
 
the only plugins are woocommerce and LsCache
I put this codes in my.cnf and now it is a little (about 3%) faster:

Code:
[mysqld]
local-infile=0
innodb_file_per_table
query_cache_size=0
query_cache_type=0
query_cache_limit=1M
tmp_table_size=256M
max_heap_table_size=256M
table_definition_cache= -1
performance_schema = ON
innodb_buffer_pool_size=2G
innodb_log_file_size=256M
innodb_buffer_pool_instances=2
skip-name-resolve=1
join_buffer_size=8M

BUT I'm deadly sure there is better config for my.cnf
Have you managed to find the best config?
Please share if you have, I have similar situation.
 
answer1: there is no need to use a tool I can sense it its slow when i takes 4 seconds to load.
answer2: The location is Iran and I'm also in Iran
answer3: any other part is okey and as I mentioned 4 different people told me it goes back to mysql config
answer4: MariaDB/ InnoDB engine
answer5: 10GB of 64GB

try this

Code:
innodb_buffer_pool_size = 32G 
innodb-buffer-pool-instances = 32
innodb_thread_concurrency = 8
 
Just a quick note that sometimes overcaching makes things worse than no cache at all (in case the cache misses are too much).
 
Just a quick note that sometimes overcaching makes things worse than no cache at all (in case the cache misses are too much).

'overcaching' how do you figure that to be the case if you have 64GB ram with only 10GB utilized?
 
still as @wattie suggest you can monitor the impact after 24 hours of mysql running on load with query

Code:
#mysql

mysql> SELECT CONCAT(FORMAT(DataPages*100.0/TotalPages,2),' %') BufferPoolDataPercentage FROM
(SELECT variable_value DataPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
(SELECT variable_value TotalPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;
 
Back
Top