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.
Take some time to look at your site too - make sure you aren't running any inefficient sql queries etc. I had a problem with one of my sites and there was one query which worked but slowed the whole server down. Fixed it and the query was faster with no impact on the server.
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.
Wordpress? I love it but there are a lot of cowboys creating themes and plugins. You need to go through your site and identify the source of the errors and fix them. If you can't atleast debug the site then you need to get someone who can. If your site is throwing errors because of sloppy coding then buying a bigger server isn't going to help.
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.