Migs, the high priest of Linux and server administration, whom I always look to for advice on such things, told me to write about my latest hosting issues and so I shall. I suppose this is a good way to talk and archive the recent problems I’d been having with the sites and what I do to fix it, and how I’m starting to realize that whatever ‘fix’ I do seems temporary since the site’s traffic increase again anyway that as soon as I enhance / improve performance, traffic increases again. I am therefore plagued, within days, by either mysql inefficiencies or run out of RAM. Anyway here’s a rundown:
Here’s a screenshot of Aug. 30’s mysql issues. WordPress serves that up when it can’t connect to the db. I emailed Rimuhosting about it and they figured out I was having issues with one of my favorite WordPress plugins called Counterize, which allowed me to produce those interesting stats at the footer of KikayEx which says ‘x number of visitors visited today, xx number the past 7 days. xxx number total since forever, and so on…‘. We activated an option on mysql to produce a ‘mysql-slow.log‘ which lists down all the mysql queries that take more than 2 seconds to complete. The results were stunning, as Counterize apparently had queries that take as long as 20 seconds, and at a given morning (not even a whole day), there were 40 queries already. Here’s how it looked:
/usr/sbin/mysqld, Version: 5.0.22-Debian_0ubuntu6.06.3-log. started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
# Time: 070904 6:30:17
# User@Host: kikay[kikay] @ localhost []
# Query_time: 12 Lock_time: 0 Rows_sent: 57768 Rows_examined: 690442
use kikaydb;
SELECT DISTINCT referer FROM wp_Counterize;
# Time: 070904 6:30:20
# User@Host: kikay[kikay] @ localhost []
# Query_time: 11 Lock_time: 0 Rows_sent: 57768 Rows_examined: 690444
SELECT DISTINCT referer FROM wp_Counterize;
# Time: 070904 6:30:23
# User@Host: kikay[kikay] @ localhost []
# Query_time: 11 Lock_time: 0 Rows_sent: 57768 Rows_examined: 690445
SELECT DISTINCT referer FROM wp_Counterize;
# Time: 070904 6:32:09
# User@Host: kikay[kikay] @ localhost []
# Query_time: 19 Lock_time: 0 Rows_sent: 57769 Rows_examined: 690450
SELECT DISTINCT referer FROM wp_Counterize;
# Time: 070904 6:32:16
# User@Host: kikay[kikay] @ localhost []
# Query_time: 20 Lock_time: 0 Rows_sent: 57769 Rows_examined: 690452
SELECT DISTINCT referer FROM wp_Counterize;
# Time: 070904 6:32:22
# User@Host: kikay[kikay] @ localhost []
# Query_time: 28 Lock_time: 0 Rows_sent: 57769 Rows_examined: 690451
SELECT DISTINCT referer FROM wp_Counterize;
# Time: 070904 6:32:23
# User@Host: kikay[kikay] @ localhost []
# Query_time: 33 Lock_time: 0 Rows_sent: 57769 Rows_examined: 690450
SELECT DISTINCT referer FROM wp_Counterize;
# Time: 070904 6:32:25
# User@Host: kikay[kikay] @ localhost []
# Query_time: 21 Lock_time: 0 Rows_sent: 57769 Rows_examined: 690453
SELECT DISTINCT referer FROM wp_Counterize;
# Time: 070904 6:32:28
# User@Host: kikay[kikay] @ localhost []
# Query_time: 14 Lock_time: 0 Rows_sent: 57769 Rows_examined: 690454
SELECT DISTINCT referer FROM wp_Counterize;
# Time: 070904 7:41:52
# User@Host: kikay[kikay] @ localhost []
# Query_time: 16 Lock_time: 0 Rows_sent: 57775 Rows_examined: 690535
SELECT DISTINCT referer FROM wp_Counterize;
# Time: 070904 7:41:55
# User@Host: kikay[kikay] @ localhost []
# Query_time: 22 Lock_time: 0 Rows_sent: 57775 Rows_examined: 690535
SELECT DISTINCT referer FROM wp_Counterize;
# Time: 070904 7:41:56
# User@Host: kikay[kikay] @ localhost []
# Query_time: 25 Lock_time: 0 Rows_sent: 57775 Rows_examined: 690533
SELECT DISTINCT referer FROM wp_Counterize;
# Time: 070904 7:41:58
# User@Host: kikay[kikay] @ localhost []
# Query_time: 21 Lock_time: 0 Rows_sent: 57775 Rows_examined: 690535
SELECT DISTINCT referer FROM wp_Counterize;
# Time: 070904 7:42:01
# User@Host: kikay[kikay] @ localhost []
# Query_time: 28 Lock_time: 0 Rows_sent: 57775 Rows_examined: 690535
SELECT DISTINCT referer FROM wp_Counterize;
# Time: 070904 7:42:06
# User@Host: kikay[kikay] @ localhost []
# Query_time: 11 Lock_time: 0 Rows_sent: 1 Rows_examined: 632760
SELECT COUNT(*) FROM wp_Counterize WHERE timestamp >= ‘2007-09-04’ AND IP = ‘58.71.15.98’;
# Time: 070904 7:57:52
# User@Host: kikay[kikay] @ localhost []
# Query_time: 13 Lock_time: 0 Rows_sent: 57776 Rows_examined: 690554
SELECT DISTINCT referer FROM wp_Counterize;
# Time: 070904 8:09:36
# User@Host: kikay[kikay] @ localhost []
# Query_time: 11 Lock_time: 0 Rows_sent: 57776 Rows_examined: 690563
SELECT DISTINCT referer FROM wp_Counterize;
# Time: 070904 8:10:29
# User@Host: kikay[kikay] @ localhost []
# Query_time: 11 Lock_time: 0 Rows_sent: 57776 Rows_examined: 690565
SELECT DISTINCT referer FROM wp_Counterize;
# Time: 070904 8:10:32
# User@Host: kikay[kikay] @ localhost []
# Query_time: 13 Lock_time: 0 Rows_sent: 57776 Rows_examined: 690565
SELECT DISTINCT referer FROM wp_Counterize;
# Time: 070904 8:12:59
# User@Host: kikay[kikay] @ localhost []
# Query_time: 19 Lock_time: 0 Rows_sent: 57776 Rows_examined: 690569
SELECT DISTINCT referer FROM wp_Counterize;
# Time: 070904 8:13:02
# User@Host: kikay[kikay] @ localhost []
# Query_time: 17 Lock_time: 0 Rows_sent: 57776 Rows_examined: 690570
SELECT DISTINCT referer FROM wp_Counterize;
# Time: 070904 8:13:05
# User@Host: kikay[kikay] @ localhost []
# Query_time: 23 Lock_time: 0 Rows_sent: 57776 Rows_examined: 690570
SELECT DISTINCT referer FROM wp_Counterize;
# Time: 070904 8:19:28
# User@Host: kikay[kikay] @ localhost []
# Query_time: 11 Lock_time: 0 Rows_sent: 57776 Rows_examined: 690579
SELECT DISTINCT referer FROM wp_Counterize;
# Time: 070904 8:19:35
# User@Host: kikay[kikay] @ localhost []
# Query_time: 17 Lock_time: 0 Rows_sent: 57776 Rows_examined: 690579
SELECT DISTINCT referer FROM wp_Counterize;
# Time: 070904 8:19:36
# User@Host: kikay[kikay] @ localhost []
# Query_time: 15 Lock_time: 0 Rows_sent: 57776 Rows_examined: 690580
SELECT DISTINCT referer FROM wp_Counterize;
At any rate, I got rid of the inefficient Counterize and replaced it with Firestats, which still provided me an interesting footer (you can check it out at the bottom area of KE), but certainly not as entertaining. I still haven’t done the same with BallEx and MomEx as of today, so if you wanna see how it originally looked you can check the sidebar of each and compare it.
Firestats was a great improvement and didn’t take as much of a mysql hit, so I basically got my cake and ate it too. Well, at least for another two weeks till today. This is because the site speeded up so much better that hits exponentially increased, bringing Kikay to an almost 50% increase in readership, almost 600 unique hits a day. While that’s big, BallEx regularly hits 800 unique hits a day, but has less pageviews than Kikay. Kikay’s unique hits + pageviews means that people go to it and ‘leaf’ through its pages, meaning they hang around a lot to read everything.
So as soon as the mysql inefficiency was fixed, I was now running out of RAM just from serving up pages. I had only 3 services running, namely Apache, Mysql and Named, which we quickly turned off since it was useless. I have a total of 4 WordPress sites plus 2 phpbb installations, and didn’t even have email so I could save from installing spamassasin which I knew was a mysql hog. 484mb was apparently not enough. Here’s a screenshot of it at 9am, which again happens at approximately 5pm later in the day.
Keep in mind the fact that I probably should do things like remove Counterize from the rest of the sites and find other inefficiences, but doing so requires a lot of time and effort which I’d rather spend on, say, more profitable ventures like trying to get Kikay Shop to earn more. Besides while I’m sure the WordPress Plugins are the ones that are giving me pain, I like the sites the way they are and really dont wanna remove any of them. For example, I really liked Counterize, and if I hadn’t found Firestats I figured I’d rather take a mysql hit than remove it.
Rimu has replied with three things I could do. First, install a php caching app called eaccelerator. As of today, this has already been done. Another is to tweak mysql. I really don’t have time to do that I think. I’ll read through it a bit and if its something I can finish in an hour maybe I will, but I doubt it. The last thing is to move to a different webserver, and they recommended lighttpd or nginx, of which I think lighttpd seems to have a better user base. Lighttpd also happens to be the one used by Youtube, so that’s definitely a good word for it as well.
Of course, I understand changing one’s webserver midstream is like changing your house’s foundation while still living in it. But at the same time I need to act like the boss here and insist it get done no matter how hard it is. Rimu has, like all good geeks will do, implemented the changes piecemeal, starting with eaccelerator. But I’ve already told them I want lighttpd as well, and am asking how much for them to do it. I don’t wanna meddle in the details anymore, a big difference from what I’d have done in the past. A year ago I’d probably be all geeky and installing these on a spare server already just to get a feel of it, and maybe attempt to do it myself. But I’m over that now. I NEED KIKAY EXCHANGE TO GO FASTER. There is nothing more important than my sites loading properly like they should. At the moment, 600 kikays aren’t happy, and so therefore I am unhappy as well.
At any rate, these changes, I assume, will probably be good enough to improve the site to handle approximately 500 more unique hits. Any more than that, and I dunno what else to do because I really dont wanna spend any more money on RAM. At that point, I probably need to start looking into managed hosting, or, like Migs said, shared hosting, but I’m assuming the costs to be astronomical. That’s fine really, as long as there’s business to back it up.
Sigh. Need to get things done fast.