At what point do you stop optimizing queries and realize it's a database architecture, scaling problem?

We've been having production issues this week because a lot more users with more demands, and I'm going we need more servers... We can't just have one db, we need to parallelize like Hadoop...

Everyone else is going, how do we optimize queries, indexes, reduce the load...

Following because I'm interested.

If they are all asking for the same data you can add a cache, but you might have two problems then.

@bkwilliams It depends on what level cache he wants to have L1 or L2 !?

@bkwilliams so actually, one issue they raised was there's a huge number of page faults.... the solution of course would be load everything into memory...

hey wait a minute.... that's a cache..... not a database...

Can you set up profiling and try to determine if the query response time is slow or if their is just too much backlog?

@mihamil well we had logging and I actually parsed both web server and db logs n normalized them so could filter n pivot in Excel but the issues seem pretty random, just occurs a lot more under high volume and certain data sets...

So again my conclusion is we need to scale, theirs is how do we fix the queries so the ones that take too long return faster (these are needles in a haystack and the performance varies)

What normal form is the data?

@bkwilliams parser the logs into CSV format, then can import into Excel

@billgates I put tabs in my application error logs just to import them into Excel, but I meant your table structure?

@bkwilliams the DB? It's Mongo so JSON which some document schema.

I just realized the problem is the architecture.... We should be hitting a in memory cache first..... Direct db queries should be minimal, only for persistence and loading the data back into cache....

Damn... And this is why system should be designed by really Sr architects... And not just use the hottest new tech....