Monitoring Server Usage

[xmakina]

So my site is coming along nicely but I've got a little niggle in the back of my mind.

Let's say my site became popular. At the index page for each user an SQL query is run that essentially inner joins the entire database to show them the information they need.

I can imagine that on a popular website this could become quite a demand. So, what I want to know is, will I recieve a warning from x10 if my server usage starts to go up?

They closed my site down for a day when I accidentally forgot to put an index page in (I make facebook apps so I didn't think about it) without a warning of any kind and I'd hate that to happen once my site gets going.

It would be great if I could keep an eye on the server usage myself but when I see server load 13.32 I don't know how much is me and how much is everyone else. If someone knows how to view your *personal* server load that'd be awesome too

[dickey]

I think there are details you can look into in your cpanel. it will give you ip addresses though but if you know what your ip address is (or it's range for dynamic ips) then you can eliminate which one is you and which one are visitors. Sometimes what I do is make my own logging system but it takes some bandwidth to do that though.

[quantum1]

As a past and present database administrator, the term "inner joins the entire database" is a red flag to me. What kind of information are you storing and retrieving that requires querying the entire database?

[xmakina]

First the whole database is only 11 tables and it only exists to serve the websites purpose. I have to inner join the lot because I need all that information. There's nothing extraneous, I'm a software engineer in training so I know to strip things down to their minimum.

I'd rather not divulge specifics until my site is up and running - it's a good idea that no one else has pulled off so I'm keeping the cards very close to my chest.

cPanel was okay for bandwidth and traffic management but I'm more concerned with Server Load because of the ToS about monopolising server usage.

I *think* I'm worrying over nothing. A page never takes more than 0.01 seconds to load (according to my startTime-endTime sum anyway) so I don't imagine I can hurt the server that much regardless of my userbase (at least whilst it's < 200 at any rate). I'd just hate my site to be locked out during those cruical opening weeks.

[freecrm]

First the whole database is only 11 tables and it only exists to serve the websites purpose. I have to inner join the lot because I need all that information. There's nothing extraneous, I'm a software engineer in training so I know to strip things down to their minimum.

I'd rather not divulge specifics until my site is up and running - it's a good idea that no one else has pulled off so I'm keeping the cards very close to my chest.

cPanel was okay for bandwidth and traffic management but I'm more concerned with Server Load because of the ToS about monopolising server usage.

I *think* I'm worrying over nothing. A page never takes more than 0.01 seconds to load (according to my startTime-endTime sum anyway) so I don't imagine I can hurt the server that much regardless of my userbase (at least whilst it's < 200 at any rate). I'd just hate my site to be locked out during those cruical opening weeks.

Inner joins aren't a problem in themselves as I have quite a few on my site.

I think the problem arises when you are querying the whole table content by being lasy!

i.e. SELECT TABLE1.*, TABLE2.* FROM TABLE1 INNER JOIN TABLE2 ON .... etc etc.

You should ideally specify the columns which will significantly reduce the processing.

i.e. SELECT TABLE1.ID, TABLE1.NAME, TABLE1.DETAIL, TABLE2.IDLINK, TABLE2.INFO FROM TABLE1 INNER JOIN .. etc.


Hang on - just thought of something - even if you do query the whole database, isn't that server-side?? Surely the bandwidth is only from results being echo'd to the html???? Can someone clarify?

[xmakina]

I'm almost offended by the claim I do Table1.* :P

Yes, it is all server side. That's why cPanel was useless. I'm not fussed about how much the server pushes out, I know that and I can refine that down easilly if needed. My concern is the one stat I can't track, which is server usage/load.

My site is designed that the 11 table query is ran each time a user views the index page. It only generates a table so bandwidth is a lower concern than server usage.

[quantum1]

You may have already checked this out on cpanel, but there is under the databases row a phpMyAdmin link. If you take that link then on the next page under localhost is a link entitled "Show MySQL runtime information". That link may be useful to you. I haven't checked it out completely, but it has lots of information.