"MySQL server overload" , anyone has similar experience?

[wayfinder]

Hi, I have been using Oscommerce-2.2ms2 for about 2 years now and it was running well. My website is getting more and more traffic.

 

Just few days ago, all of sudden my database was blocked by my hosting company and they said the Mysql database is overloading their server and I need optimize the database before they can restore my access. I don't really trust what they are saying but I have no proof. Is there anybody has similar experience or know what should be optimized for the Mysql database?

 

Thanks a lot!

[usernamenone]

look in your stats for your site and see if there is an outside url that is making constant hits on your site. Sounds like you should move your site to different hosting.

[arietis]

Hi, I have been using Oscommerce-2.2ms2 for about 2 years now and it was running well. My website is getting more and more traffic.

 

Just few days ago, all of sudden my database was blocked by my hosting company and they said the Mysql database is overloading their server and I need optimize the database before they can restore my access. I don't really trust what they are saying but I have no proof. Is there anybody has similar experience or know what should be optimized for the Mysql database?

 

after a couple years of use, your tables could certainly use some cleaning up. use phpmyadmin, select your products table, and click on the 'operations' tab. then, in the lower left of the page you'll see a link for 'optimize table'. this might take a while, depending on the amount of data in the table and how fragmented the indexes are. you might need to do this on some other tables as well, like products_attributes - basically, anything starting with 'products_' as well as 'categories'.

 

you have 2.2ms2, so you should have indexes on your tables. but if you don't you should certainly add them. check this by selecting a table in phpmyadmin and looking in the bottom left area of the screen under the 'indexes:' heading. if the table lists only one index, you should add the others. if you download the rc2 version i think there's something in the readme that describes what indexes should be used.

 

you might also be running up against the limit that your hosting plan allows for database connections. although, if this were the case i would think your host would tell you to upgrade rather than optimize your tables. you can tell this by looking at your stats and getting an idea of how many page views and connections you have per hour and comparing this against what your hosting plan allows.

[wayfinder]

after a couple years of use, your tables could certainly use some cleaning up. use phpmyadmin, select your products table, and click on the 'operations' tab. then, in the lower left of the page you'll see a link for 'optimize table'. this might take a while, depending on the amount of data in the table and how fragmented the indexes are. you might need to do this on some other tables as well, like products_attributes - basically, anything starting with 'products_' as well as 'categories'.

 

you have 2.2ms2, so you should have indexes on your tables. but if you don't you should certainly add them. check this by selecting a table in phpmyadmin and looking in the bottom left area of the screen under the 'indexes:' heading. if the table lists only one index, you should add the others. if you download the rc2 version i think there's something in the readme that describes what indexes should be used.

 

you might also be running up against the limit that your hosting plan allows for database connections. although, if this were the case i would think your host would tell you to upgrade rather than optimize your tables. you can tell this by looking at your stats and getting an idea of how many page views and connections you have per hour and comparing this against what your hosting plan allows.

 

Thanks for all suggestion, I agree I should optimize the database. And according to other similar post I think the ultimate solution will be find another hosting company. Just switched to another company with a better plan. Now problem gone.

 

I just felt irrigated for how my previous hosting company treat a old customer. I have been use them for more then 5 years and now they blocked my database without even give me a notice. Feel so bad...