How to optimize Mysql server performance?

I am trying to test a small VPS for one of my sites, but sometimes mysql server databases are error due to web traffic or queries. Are there any way(s) to optimize Mysql server performance?

Yes you should check what is your version of mysql, as the last (5.6) have really better performance.
And to fix it you should edit the /etc/my.cnf file to accept more queries.

But if you really want performance, you can delete mysql and use mariadb instead. Really faster than mysql.

David Makogon said:

I am trying to test a small VPS for one of my sites, but sometimes mysql server databases are error due to web traffic or queries. Are there any way(s) to optimize Mysql server performance?

Click to expand...

Simply try mysqltuner.pl on your VPS and make the necessary changes in the my.cnf file, for more information you can refer POST

There are two ways of doing this, firstly is the way that @VirtuBox explained it.
and secondly make sure to always keep your databases tidy and as light as possible.

VirtuBox said:

Yes you should check what is your version of mysql, as the last (5.6) have really better performance.
And to fix it you should edit the /etc/my.cnf file to accept more queries.

Click to expand...

Please elaborate this.

what codes do I need to add to increase mysql performance?

VirtuBox said:

But if you really want performance, you can delete mysql and use mariadb instead. Really faster than mysql.

Click to expand...

How to switch to mariadb, I heard more times about this but how to enable it on my blog sites?

I am using mysql databases.

praveenk said:

Simply try mysqltuner.pl on your VPS and make the necessary changes in the my.cnf file, for more information you can refer POST

Click to expand...

Did you or anyone here use mysqltuner.pl to optimize mysql databases?

Luxin Host said:

and secondly make sure to always keep your databases tidy and as light as possible.

Click to expand...

It's hard to keep databases tidy because I used to add content and images to my blog sites regularly so its increased quickly time by time.

@David Makogon
Are you familiar with database maintenance?
If yes the you should start to implement it.
if no, its ideal to start learning about it as it will help you a great deal as your website grows.

I suggest deleting revisions and unnecessary plugins if you use wordpress or other CMS.

What is your blog ran on?

MariaDB is a "drop in replacement" for MySQL which means you will not need to make any changes to your databases or queries.

Check your memory usage. Running out of available RAM can cause problems for MySQL!