How to speedup Mysql using free RAM?

[HappyWorld]

I have enough free RAM and sometimes i still experience bottle neck in mysql (i use mariadb).
I can confirm that bottle neck is not on web server.

How to speed up mysql query?
What mysql configuration need to be changed? Is it fine to blindly accept recommendation from script such as mysqltuner?

Thanks.

 

[motowebmaster]

How to speed up mysql query?
What mysql configuration need to be changed? Is it fine to blindly accept recommendation from script such as mysqltuner?

No, it's not. I use mysqltuner, but only as a guide.

 

[dknife]

Have you increased your innodb_buffer_pool_size at least to use the extra RAM?

 

[TickTackk]

How are you using MySQL and what's your environment?

 

[HappyWorld]

Have you increased your innodb_buffer_pool_size at least to use the extra RAM?

Yes, my buffer pool size compared with buffer pool data is fine.

How are you using MySQL and what's your environment?

I am using mariadb mainly for xenforo forum. I use dedicated server.

 

[Sim]

If you have plenty of free RAM - look at your XF caching options as well.

Better to avoid hitting the DB altogether if you can avoid it.

Also - have you considered FPM performance? Your web server may be fine - but if you're running php-fpm, that can also be a bottleneck if not tuned properly - especially if it's frequently spawning new instances as load goes up and down.

 

[HappyWorld]

If you have plenty of free RAM - look at your XF caching options as well.

Better to avoid hitting the DB altogether if you can avoid it.

Also - have you considered FPM performance? Your web server may be fine - but if you're running php-fpm, that can also be a bottleneck if not tuned properly - especially if it's frequently spawning new instances as load goes up and down.

Thank you, i have used XF cache.
I see most of the slow query is on UPDATE / DELETE query. So i don't think it is cache-able, or am i wrong?

 

[TickTackk]

I see most of the slow query is on UPDATE / DELETE query. So i don't think it is cache-able, or am i wrong?

Are all queries that make use of UPDATE / DELETE are slow or just a few? If just a few which queries are those?

 

[Masetrix]

I see most of the slow query is on UPDATE / DELETE query.

Tuning MySQL: 3 Simple Tweaks

If you don’t change the default MySQL configuration, your server is going to perform like a Ferrari that’s stuck in first gear…

medium.com

How to Optimize MySQL Performance Using MySQLTuner

This step-by-step guide shows you how to assess your MySQL database performance using MySQLTuner to ensure optimum resource usage.

www.linode.com

MySQL :: MySQL Performance Tuning and Optimization Resources

www.mysql.com

Home

Database performance tips from Percona data performance experts. Learn to build and maintain high-performing MySQL, NoSQL, MongoDB, big data, cloud deployments

www.percona.com

Hth.