Performance problems - hosting company suggested to optimize our MySQL database

Question

Magento 1.9.1
Porto theme
VPS server with the following:
**RAM: 6 GB
SSD Web Space: 150 GB
Bandwidth: 6000 GB
Cores: 8**

We've had this hosting account for years and it mostly worked fine. We are having perfomance problems (slow) for the last week or so. At one time, the tech at the hosting company wrote this to us:

"There was a temporary overload on the server caused by an abusive user hosted on the same environment. We had to suspend the user in question and the server performance is now back to normal. I have already tested your website and it is loading perfectly normal on our end."

Today, the hosting company tried to get us to upgrade to a dedicated server saying:

"By the moment, you are being hosted in a Virtual Server Linux environment, which is affordable, but definitely not the most powerful. If your website requires more resources to work smoother, you might want to consider checking our Dedicated Server offers here:"

I went back to them saying that is not an option we could afford at this time and they offered this suggestion:

"I checked the node hosting your server again here and it doesn't have high load at the moment. We are monitoring it to ensure this stays the case.

As I see that in most cases the slow loading of the site and the higher load originates from the MySQL, I noticed that your MySQL database is quite large:

6.1 GB

I see that the largest tables are mainly logs:

mg_log_visitor_info 1.7 GB
mg_log_url_info 1.6 GB
mg_log_url 1 GB
mg_log_visitor 1 GB

If the MySQL database is optimized and these logs cleared, this should significantly improve the service performance.

Also we can migrate your VPS to a different node, that is doable. I am not sure which user Rusi took action to limit, but at the moment, there is no resource abuse on the current node hosting your VPS. Still if you wish, we can schedule and do a migration."

The migration they are talking about is to another VPS server with the same specs within the same hosting company (we had to do that a few times in all them years we've been with them).

What would be the best course of action here? Migrate or not?

How to optimize the MySQL database? Does that involve deleting those log files as suggested above?

**

mg_log_visitor_info 1.7 GB
mg_log_url_info 1.6 GB
mg_log_url 1 GB
mg_log_visitor 1 GB

**

• MagenX: you need to check all the settngs you have, but if your database is 6gb and you have only 6gb ram, then you need to delete those logs.
• Allysin: @MagenX How does one delete those? Can it be done via FTP or does it need to be done in phpMyAdmin? If in phpMyAdmin - how would that be done?
• MagenX: stackoverflow.com/questions/3638361/clearing-magento-log-data
• MagenX: Possible duplicate of Is cleaning logs from the database a good practice?

Answer 1

Before migrating, I would consider contacting a 2nd hosting provider explaining the issue you have had. It is very rare to see the necessity of having a dedicated server. Your configurations are already very nice for the web. I would definitely listen a 2nd opinion from another provider. Good luck.

Answer 2

I think that the first thing you should decide if you need MySQL or not. There are several alternatives to it: MariaDB, Percona. You may find the advantages of each one for Magento here. Moreover, consider upgrading your database to the latest version or set up layered navigation through Elasticsearch