Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

i'm writing a business plan and i have to simulate the cost when my website will be reach from 500.000 unique visitors.

visitors: 500.000 pageviews: 1,500,000 spider pageviews: 500,000 total pageviews: 2,000,000

each page does 50 queries +-

queries per day: 100 Millions per hour: 4 millions per minute: 70,000 per second: 1,200 peak: 3,000

Doing this calculation i need 3,000 queries second... what kind of server can handle it?

the problem is: actually my site is doing 2,000 visits day, and having -+ 150/200 queries / second... starting from this point i'll expect 50,000 queries / second.

how many servers i need in cluster or replication do manage this job?

Thanks simon

share|improve this question
5  
What sort of site does 8k+ queries a visit? –  Ignacio Vazquez-Abrams Jul 28 '10 at 19:23
5  
You need a system design review right away. –  Chopper3 Jul 28 '10 at 21:14
1  
Nowhere near enough information, because you've told us nothing about what really matters - the queries themselves. Nor have to told us about the machine you're running. Is this a 486? The latest and greatest super computer or something in between? All those numbers you've listed are irrelevant to the question. Please provide RELEVANT information. –  John Gardeniers Jul 28 '10 at 21:38
    
>What sort of site does 8k+ queries a visit? i receive 2000 unique visitors but each visitors opens many pages, + i have a lot of spiders inside. 2000 unique users are generating 6000 unique ips opening more than 120.000 pages opened daily. thanks –  Simone Jul 29 '10 at 12:52

6 Answers 6

It all depends on how complex the query is, and how much memory the servers have, and how fast the disks are.

If the queries are very simple, or very well tuned then a single large database server can handle that. If however the queries are very complex (or simple but poorly tuned) then you'll need several servers.

share|improve this answer
    
Or some serious schema changes and reindexing... –  Massimo Jul 28 '10 at 19:22
2  
Tuning is ALWAYS preferred over adding more hardware. Adding more hardware just masks the problem until such a time as the problem is much harder to solve. –  mrdenny Jul 29 '10 at 2:16
    
Thanks for the answer, so i think that 2 servers in parallel + 1 passive for redoundance should be ok, right? i'm talking about 2x quad cores servers with 32 g of ram and quick drives. am i right? remember that i need performances! –  Simone Jul 29 '10 at 12:20
    
everything is well tuned and indexed, i have 1 or 2 slow queries per week (and slow-query-time is just 2 seconds) anyway i'm writing a business plan, and i'd like to know what kind of server pool can manage 12,000,000 pages opened daily generating with 8000 queries / second –  Simone Jul 29 '10 at 12:55
    
8000 queries a second isn't all that much. A single 16 core server will probably do the trick. 64 Gigs of RAM (or more or less depending on how big the database is and how much data needs to be kept in cache at any one time) should do the trick. My DB (granted its SQL Server) is 1 TB on a 16 core 64 Gig of RAM server with 40-50k users hitting it daily up to several times per minute (each) through out the day. –  mrdenny Jul 29 '10 at 21:25

This really can't be estimated without knowing anything about the specific queries you're running, the database scheme and its size.

A simple SELECT on an indexed column is quite a different beast from a couple of JOINs based on non-indexed ones... and of course things change a lot if the involved tables contain 1K records or 1M.

Also:

  • What is your current hardware configuration?
  • How much of its power (CPU, RAM, Disk I/O) is your server using under the current load?
share|improve this answer
    
actually i have a server with 2x quad core with 8 GB of ram. i'm using the full ram and 100 % of processor (it seems i can use 800%, see here: ) cpu: img834.imageshack.us/img834/3483/downloadv.png ram: img442.imageshack.us/i/download2p.png disk: img213.imageshack.us/i/download1x.png thanks –  Simone Jul 29 '10 at 12:28
    
Based on those graphs, you're only using one (or at most two) of your CPU cores; so your application is definitely not CPU-bound... or it is, but it's uncapable of taking advantage of multiple CPUs. Also, all of that memory used for "cache" is not actully needed by anyone, it's just the O.S. taking advantage of it because "it's there". –  Massimo Jul 29 '10 at 14:01
    
how can i find information on using all the cpu cores? i 'm using lamp... –  Simone Jul 30 '10 at 11:12
    
First of all, you should check whether you're not using them because there's just not any need for them (= low load), because your operations can't be properly parallelized, or because your MySQL and/or Apache are not configured to use them. And, since those two programs usually are multithreaded by default, I'd have a look into your server load and into your SQL queries... –  Massimo Jul 30 '10 at 11:23

As Ignacio remarked, you may want to look into caching. In the cms or perhaps even in front of the stack. 50+ queries for every (every!) page truly is a lot.

share|improve this answer
    
yes this is a complex website, it's a community, i can't cache anything, it's changing every second. i tried to cache pages, but the cache hitrate was nearly 0, since every time i cache a page, it can be never read again, or it can change before it's opened again. thanks –  Simone Jul 29 '10 at 12:30
3  
There are very few uncachable sites; if it only changes every second you can still cache for a whole second, like 10 pageviews ;-) Have you considered not caching pages entirely, but rather blocks or specific values etc? You could cache outside the database, on shared memory segments, filesystem, memcached. Also, typically in such a situation ESI could be usefull –  Joris Jul 30 '10 at 18:03

I used to work for an e-commerce company with a website that had several million page hits per day. We had a single DELL PE 1750 with 2 single core CPUs and 2GB of RAM, database size approx. 4GB. At peak times this server handled up to 50k+ queries per second.

Having said this: the database was well structured, all queries were finely tuned (we had weekly sessions analysing the slow query logs and fixing up queries and indexes) and the server setup was also fine-tuned. Caching is definitely a good idea, but MySQL does that anyway, you just have to analyse the performance and then fine tune how your memory is used (query cache vs other options).

From that experience I can tell you that the highest impact is caused by missing indexes, wrong indexes and bad database design (e.g. long string fields as primary keys and similar nonsense).

share|improve this answer

Judging by your comments, the biggest factor will be your data set size, or at least the size of the "hot" data set. 3,000qps or even 8,000qps on a 16-core server is not a problem at all as long as the server rarely has to go to the disk to satisfy the query. Once the active data set exceeds the amount of memory InnoDB is using to cache it, your performance will drop off rapidly.

share|improve this answer

For large "hot" datasets, it is probably worth the investment in time to convert to a "big data" scheme, it is what they are for. For example, if you have a vast quantity of data to retrieve, but you never rewrite, but only append new data, look at Apache Hive. Browse around, their is usually a flavor you can interface easily enough to existing code, that will also prevent the heartburn of running out of cache space.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.