Usage stats & site lagging

O

o hai guyz

New member
Jan 15, 2010
917
8
0
I have a site that's getting about 4k uniques and 35k pageviews a day on a Smart VPS with a single core cpu and 2gb ram. It's an image-sharing site that also allows users to vote and comment on images, see the most popular ones of the day, etc. so there are quite a lot of MySQL queries. Users also receive notifications in a Facebook-style notification panel whenever someone comments on their image or replies to their comment on another user's image (originally I also notified whenever people voted on images too, but it lagged the site so hard that I had to disable that).

Whenever there are more than about 150 people on the site simultaneously, it slows down to the point where it's almost unusable (or at least to where new users would get pissed off and leave). I'm also using about 20gb of bandwidth a day (out of a 1000gb monthly quota) which seems like a lot.

The host recommended staying on the Smart VPS's rather than switching to a dedicated because they said it looks like I'll have to constantly upgrade it to keep up with the traffic growth. They also recommended moving MySQL onto a separate server and having the site itself / Apache on the main server, which is something I've never done before.

I'm curious if this is typical for sites like this or if there's something coded incorrectly or not fully optimized, and it's bogging down the server more than it should. I've been considering hiring someone who specializes in database optimization (is that even a real thing?) to go through the site, the database, the notification system, etc and make sure it's all done correctly. This is the first site I've run in this niche and most of my "sites" up to now have just been redirects and landers where I never had to worry about usage stats and things like that. Any thoughts?
 


you need to setup some sort of performance monitoring for us to even begin to know what's going on. Get a free trial for NewRelic and get that setup and collect some log data. That will help you identify any SQL bottlenecks as well as any other app problems.

Also, as this is an image site: all image processing needs to be offloaded to a background task queue, ideally on a fully separate server. Image processing (resizing, cropping, whatever) is a CPU intensive process and should never happen on the same server as your web app server. Your web app server has one job: to serve up the web app. It should be "dumb" in the sense that no calculations or processing happens on it directly.

Finally: caching. Lot's of it.
 
I'd say number 1 is log your queries, because I think it's likely you don't have proper SQL indexes setup.
 
4k uniques a day for a picture site is nothing.

Single CORE 2GB ram should be able to handle 2x that traffic and 2x that bandwidth if the software behind it is running how it SHOULD be. The problem is code (and DBs) today is so terribly optimized because hardware keeps moving at the pace it is, and most coders disregard optimization testing.... ugh. /rant

Let me find some specs and stats for some of my old image hosting sites... for traffic / resources reference.
 
Thanks, enabled query logging tonight, I'll give it 2 days and post it Friday. The log is growing pretty fast (about 1mb every 2 minutes), is that normal?

@dchuk Aside from automatic watermarks, there's no image processing done on the site. As soon as a user hits the upload button it's done.
 
o hai guyz said:
Thanks, enabled query logging tonight, I'll give it 2 days and post it Friday. The log is growing pretty fast (about 1mb every 2 minutes), is that normal?

@dchuk Aside from automatic watermarks, there's no image processing done on the site. As soon as a user hits the upload button it's done.
Click to expand...

I would assume you're doing image resizing at some point? It's possible that your system is either resizing on demand with php (very very bad) or resizing with html/css (also bad, but not as bad as resize on demand)
 
o hai guyz said:
The log is growing pretty fast (about 1mb every 2 minutes), is that normal?
Click to expand...

What type of log? Slow query log? If so, then no, that's not normal.

Just take a look at the bottom say 30 entries, and you'll see your problem. It's probably only 2 or 3 SQL statements that are causing the issue.
 
RE: fast log growth

you could be running the site/mysql/apache/php in verbose logging mode, which will log literally everything. In production, you really only want to be logging errors and warnings most of the time.
 
Does anyone here who specializes in this type of thing want to take a look through the site's code and database settings and help fix up the things that are causing problems? I have no experience with this type of thing and I'm not really sure what to look for or who to talk to. It will be treated like a job and you'll be paid for your time.
 
just by guessing from your setup the issues are as follows:

1. MySQL
2. Apache
3. PHP ( more to do with processing and coding style )

You need cache, and I would optimize MySQL and maybe look into something other then Apache too.

Of course, switching out Apache and Caching won't fix a poorly coded site.
 
4k uniques and 35k pageviews
Click to expand...

What software is the site running on ?

With the #s you provided it should be running fine on that system so it's def. something not coded properly and/or the queries are coded wrong too.
 
Hoping you are using a wrapper for all your queries you should write a small timing function and create a log function that creates stats for every page load on how long queries took + how many queries and as said before check 'slow_queries_log' your normal log isn't that important.

Also you might want to check your mysql config (for reserved memory) and most important (your problem I think) max concurrent connections.. make this higher or start closing your mysql connection after using them directly!

Some tool to help you: https://github.com/rackerhacker/MySQLTuner-perl

Also last (and super effective but more difficult) get your db in NF (BCNF if possible)
Just google for 'db normal forms'
 
You must log in or register to reply here.
Top Bottom
Back