High Traffic crashes the mysql.

Hi guys, i need your help on an issue that I've been facing for a few months.

My website attracts more than 45k unique visitors daily. It is on a vps ( 2 gb ram, 2 Core CPU, Cpanel/WHM, CentOS 64Bit Operating System) and its cms is wordpress.

Especially between 22 pm - 00, the visitors per second exceed 50. Then the website starts giving mysql connection error. After 00, the number of online visitors are getting low, and the problem disappears.

Do you think the vps is quite insufficient for my daily traffic? Or is it about misconfiguration of vps?

If I go on the dedicated server ( 8-16 gb ram and more core cpu), is there any possibility to have the same problem again?

What do you think?

Thanks.

 

I think you should upgrade your VPS. 2GB of RAM really is insufficient.

 

Thank you, i believe so. Sir, i want to ask you another thing. In my cpanel, mysql size shows 1333 mb. But when i download the full backup which includes sql, or only the sql through phpmyadmin, downloaded sql size shows 700 mb. Isnt it a bit strange? I mean, im willing to transfer all data to another server which has sufficient might for my website, but im doubtful whether the sql is fully downloaded or not

It is quite hard to make sure whether i'm running any inefficient sql queries or not. But I did deactive some plugins which may cause this mysql error, yesterday i'd very less errors in compared to other days.

 

Thank you ill try to do it by editing the debug as true in wp-config.php i guess. Have you ever met such a situation before: "In my cpanel, mysql size shows 1333 mb. But when i download the full backup which includes sql, or only the sql through phpmyadmin, downloaded sql size shows 700 mb. Isnt it a bit strange? "

 

I cannot give you an exact answer... However, I would guess that some of what is not being dumped into the SQL backup is data that MySQL uses for itself or perhaps even empty rows, which may continue to take up space in the database (InnoDB does not remove deleted rows, they continue to take up space). That's assuming your dump is not compressed.

I think you can have faith that when you do a full SQL export that all your data is being included.

By the way, are your tables in InnoDB or MyISAM format?

As far as upgrading your VPS, for your amount of visitors and using Wordpress and cPanel, 2GB of RAM is not enough. There is that saying that "servers cost less than programmers". And that is true. If you have 45,000 visitors a day, I am sure you are making enough revenue (or could be if you are not) to pay the extra $10 - $20 a month to upgrade to a VPS with more RAM.

It is not uncommon to have MySQL problems on a system starved for RAM.

 

Thank you for your detailed answer. I will upgrade my vps to dedicated server in a few weeks. The features of dedicated server that im going to upgrade is as following: Intel® Xeon® E3-1240 8MB L3 3.40Ghz + HT - 16GB DDR3 ECC UDIMM - 4 x 500GB SATA2 7.2K 3.5" I believe this would be sufficient much better. Some of the tables is in Innodb, some in My Isam format. I was using two plugins for seeing the traffic statistic of my website. Although i deactivated them, now i saw its tables are still in db. Should i delete them?

I couldnt wait your answer, the statistic tables were about 900 mb, i deleted all Now it seems everything fine XD

 

What sort of errors are you seeing in your log files? Unless you know exactly why it's crashing, you won't know for certain whether a bigger server will solve the problem.

 

I had got the same issues and I partially resolved optimising the tables to get rid of deleted rows.