Bill Moran wrote:
Tom Allison <tom@xxxxxxxxxxx> wrote:
If the user base gets to 100 or more, I'll be hitting a billion rows before too
long. I add about 70,000 rows per user per day. At 100 users this is 7 million
rows per day. I'll hit a billion in 142 days, call it six months for simplicity.
The table itself is small (two columns: bigint, int) but I'm wondering when I'll
start to hit a knee in performance and how I can monitor that. I know where I
work (day job) they have Oracle tables with a billion rows that just plain suck.
I don't know if a billion is bad or if the DBA's were not given the right
opportunity to make their tables work.
But if they are any indication, I'll feeling some hurt when I exceed a billion
rows. Am I going to just fold up and die in six months?
Alot depends on your specific use case.
- Will you be just storing the data for archival purposes, or frequently
querying the data?
- If you need to run queries, are they well bounded to certain subsets
of the data (e.g. a particular range of time for a particular user) or
are they aggregates across the entire billion rows?
- Is the data temporal in nature, and if so do you need to purge it
after some period of time?
As an example, I have an application with temporal data, that needs
periodic purging, and is typically queried for small time ranges (tens
of minutes). We have set up partitioned tables (partitioned by date
range and data source -- akin to your users) using constraint exclusion
that contain 3 or 4 billion rows (total of all partitions), and we have
no problem at all with performance. But I suspect that if we needed to
do an aggregate across the entire thing it would not be particularly
fast ;-)
Why not just create a simulation of 100 users and run it as hard as your
can until it starts to degrade? Then you'll have some real-world experience
to tell you how much you can handle.
This is good advice. Without much more detail, folks on the list won't
be able to help much, but a with simulation such as this you can answer
your own question...
Joe
