homepage Welcome to WebmasterWorld Guest from 23.23.12.202
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
How many queries at once?
Mr_Cat

5+ Year Member



 
Msg#: 4413266 posted 10:15 pm on Feb 1, 2012 (gmt 0)

Out of curiosity, anyone any idea just what the typical breaking point is for an sql database?

I mean if you had a snippet of code on every page that updated a datetime field in a log table for the currently logged in user of a site, how many users would you need online at the same time browsing lots of pages and changing the timestamp on all their respective table rows every couple of seconds?

How many queries do you have to send before it starts feeling the pressure... :D

Any thoughts much appreciated

 

Dijkgraaf

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4413266 posted 10:29 pm on Feb 1, 2012 (gmt 0)

It would depend on a multitude of factors.
For example.
The amount of RAM on the database server
The amount of free space on the hard drive(s)
The speed of the hard drive(s).
The size of the database(s).
The number & speed of the CPU's.

Then there is also the database design itself.
The size of the records (too large and it goes over 2 pages).
The creation and use of appropriate indexes.

So in short, there is no simple answer and you would be advised to do some load testing in an environment as similar as possible to your production one to find out.

tec4



 
Msg#: 4413266 posted 7:52 pm on Feb 3, 2012 (gmt 0)

Interesting question and I have been trying to figure out something similar with my current DB structure and server specs.

My situation is not quite the same as your scenario but may find it interesting non-the-less.

I've got a dedicated server (Specs: Intel Xeon X3330 @ 2.66GHz, 4 GB DDR3 RAM, 500 GB SATA2) where I have all of my sites pulling info from a central DB/Talbe. The table is approximately 1.1 Million records and has 82 Columns of data.

From what I have seen, for my situation, I have noticed that everything runs at 100% between 0-8 requests, 9-15 simultaneous request is a little slower...when I get to around 25-35 requests it gets noticeably slower and things start bogging down....this actually happened this morning - was looking at it and had about 45-55 requests (along with everything else on the server...this is the heaviest resource taking though) and the whole server froze up and had to be re-booted.

Trying to figure out a better set up for myself though as obviously my server crashing is not good thing...

enigma1

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4413266 posted 3:56 pm on Feb 14, 2012 (gmt 0)

It also depends how the database is configured. There are settings to specify the maximum number of simultaneous client connections. Then it's the site application code, there are ways to keep track of clients and save resources and you can also check the database activity before it gets out of control and breaks. So different sites with identical hardware specs can handle a totally different number of visitors.

Mr_Cat

5+ Year Member



 
Msg#: 4413266 posted 4:55 pm on Feb 14, 2012 (gmt 0)

Blimey tec4, you're saying 45/55 users requesting info at the same time sent things down!? That's quite shocking. How on earth do the likes of F.B. and such handle things. That's a bit of a depressing stat, but yes I'm sure there are lots of different parameters that could effect things.

I did also wondered whether the db 'queues things up', i.e. if there are 50 requests for the same data from different users does it do them one by one and make people wait, or just get overloaded with everyone 'speaking at the same time' sort of thing.

It's nice to know there are settings to control that, but I guess you need a reasonable element of control over your server, or speak nicely to your service provider, or get a better one.

Hmm, I feel some serious research is in order. It's a tough one to find much info about apparently without being a bit of a db specialist.

tec4



 
Msg#: 4413266 posted 10:57 pm on Feb 14, 2012 (gmt 0)

Ya, its been pretty rough. Since my above post I have been working really hard on query optimization and really trying to to do as much per-calculation so that my pages do more straight primary/index searches and really limiting my calculations on the fly - this is seeming to help quite a bit and have noticed significant improvement. Haven't spend the time to track the amount of users at a single time yet but my total visits have increased 5% since last week and all seems to be running much smoother, so at least that's a plus!

Mr_Cat

5+ Year Member



 
Msg#: 4413266 posted 11:37 pm on Feb 14, 2012 (gmt 0)

Cool! Well you're way ahead of me on the game there, my site isn't unleashed on the public yet and I'm still not sure about the different types of indexing and when/where to use them, being new to database driven sites, but it's good to know I need to do some crucial research and that it really does matter alot!

How/where do you get/analyse your db stats, I'm possibly being lazy in just asking here right now as I haven't even looked into that yet. I use cPpanel and phpMyAdmin, do you find them there?

Cheers

tec4



 
Msg#: 4413266 posted 6:08 pm on Feb 18, 2012 (gmt 0)

Very cool. Ya, doing your research before launch can save some headaches for sure! Unfortunately, I am still fairly new to the game as well so still trying to learn and perfect whenever I can!

I generally use Google Analytics to get a rough estimate of what is going on and such. Their platform is pretty cool and tracks quite a few different things - ever used it? However, to get into the nitty-gritty of what the actual server is doing and such, I am sure on-server statistics would probably report more quality stats. One that I have only touched base with a few times, and need to investigate further, is Webalizer (should be on your Cpanel under "Logs") - so yes, just a bit lazy :P There might be a couple other analytic tools there as well

Mr_Cat

5+ Year Member



 
Msg#: 4413266 posted 11:10 pm on Feb 18, 2012 (gmt 0)

Ha, thanks. I'll check it out, I've used analytics before vaguely but not implimented on this site yet. I'll be sure to let you know what I find if it seems atall useful.

tec4



 
Msg#: 4413266 posted 11:25 pm on Feb 18, 2012 (gmt 0)

Sounds good. Ya, there is the Google Analytics, as well as the Google Webmaster Tools, that are pretty good - both do some different things. Analytics requires a JS snippet to be included on your web pages with a key to identify your site and Webmaster Tools you just have to verify site ownership. Kinda cool though, can see which pages are being indexed and such if you submit sitemaps and such - useful to analyze your sites in regards to SEO, traffic and so forth.

Mr_Cat

5+ Year Member



 
Msg#: 4413266 posted 11:45 pm on Feb 18, 2012 (gmt 0)

cool, I'm getting kind of suspicious of google now days tho. Big bro is watching! :)
I'm not so sure I like their attitude these days. Useful tools that they provide tho......

tec4



 
Msg#: 4413266 posted 11:55 pm on Feb 18, 2012 (gmt 0)

Lol - always got to keep that eye out!

Does get a bit edgy since they are such a dominant force in the marketplace --- never really know how far their reach goes.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved