After a bit of research, it's been determined that my Web test code is fine. I fork off 40 independent Web tests that crawl through the site and bring the database servers (Oracle) to their knees. They've also managed to take down the Groupwise server by overwhelming it with error email. It's like everything is trying to delicately sip from a firehose with predictable results. I've wreaked a lot of havoc with a wee bit of stress testing. I can't stop smiling.
We'd love to have more database servers to handle the requests, but Oracle is too expensive. We've considered Postgres, but too many people argue that our clients won't trust anything but Oracle. As a result, Oracle is fast becoming a significant bottleneck. We need to sign up enough clients that we can afford more Oracle licenses, but if we don't have the licenses, we're in danger of not being fast enough to sell our services to more clients.
For those who might think that we just need to tweak Oracle a bit, I'll agree that we need more experience with this. The amount of data we keep is only a few gigabytes, but the insane data requirements make it very difficult for us to tune the SQL. Our software routinely builds 300 to 400 line SQL queries on the fly and watching the huge number of tables, views, and snapshots that the queries span is simply mind-boggling. Caching the queries is not an option because they are different for virtually every customer on the site and often are different for just one customer because of filter changes they choose to apply. And did I mention that it's real-time data we're feeding? This is crazy stuff.
If your databases are only a few GB, you might want to consider putting enough RAM into the database servers to fit everything into memory.
Also, you might want to consider different database technologies. The Cache database, whose "cereal box" you had on your desk, is supposed to be good with high-performance and real-time databases.
Re:Database optimization
Ovid on 2003-12-05T20:59:52
As mentioned previously, we use Oracle because Hollywood allegedly wants us to use Oracle. Mind you, some of the people don't know how to charge a PDA and yell at us if the battery dies (I'm not kidding), so why they think they can insist on Oracle is beyond me. I've vetoed Oracle at more than one company I've worked for. This time the choice was made before I got here.
Re:Database optimization
dws on 2003-12-06T00:32:03
Install Oracle, and use it for something minimal, like collecting summarized data. Then install something else (e.g., Postgress or MySQL) to handle volume. Then you can say with a straight face that you're using Oracle.It wouldn't be the first time this has been done.
Re:Database optimization
iburrell on 2003-12-06T01:02:49
You should definitely check that your stress testing reflects normal loads. It is more important that normal web pages don't stress out the server. A good test suite should test everything but everything probably won't be run frequently.It is possible that one test really slows things down, especially when done in parallel, but the production version only gets run infrequently. For example, creating tables or adding the initial data.
Re:Whats in front
Ovid on 2003-12-09T14:19:21
I'm not overly familiar with the hardware setup (since that end isn't my bag), but it's two load balancers, two Web servers and two database servers. And some preliminary tests yesterday indicate that it might be slower than our current production site with only one Web server and one database server. Whoops.
