SEO

I've started a database that's doing wonderfully and I'm watching the tables
grow and a steady clip.

Performance is great, indexes are nice, sql costs are low. As far as I can
tell, I've done a respectable job of setting up the database, tables, sequence,
indexes...


But a little math tells me that I have one table that's particularly ugly.

This is for a total of 6 users.

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?

I can't really expect anyone to have an answer regarding hardware, table size,
performance speeds ... but is there some way I can either monitor for this or
estimate it before it happens?

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On 6/10/07, Tom Allison <tom@tacocat.net> wrote:
> 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.

You don't say anything about what the data is in the table or what
queries you run against it, so there's not much here to give advice
about.

For the monitoring, however, you can log your queries along with
timings and timestamps, and copy them into a tool like R to
statistically analyze your performance over time. You will be able to
predict the point at which your system will be too slow to use, if
indeed the performance degradation is expontential.

You can also periodically look at the pg_stat* tables to count the
number of index scans, table scans etc. on your table (see
http://www.postgresql.org/docs/8.2/i...ng-stats.html).

Alexander.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Tom Allison <tom@tacocat.net> wrote:
>
> I've started a database that's doing wonderfully and I'm watching the tables
> grow and a steady clip.
>
> Performance is great, indexes are nice, sql costs are low. As far as I can
> tell, I've done a respectable job of setting up the database, tables, sequence,
> indexes...
>
>
> But a little math tells me that I have one table that's particularly ugly.
>
> This is for a total of 6 users.
>
> 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?
>
> I can't really expect anyone to have an answer regarding hardware, table size,
> performance speeds ... but is there some way I can either monitor for this or
> estimate it before it happens?

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.

Since you don't describe anything about the schema or application, I can't
say for sure, but over the last six months, every time this has come
up, we've been able to fix the problem by reorganizing the data some
(i.e. materialized data, temp tables, etc)

--
Bill Moran
http://www.potentialtech.com

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Bill Moran wrote:
> Tom Allison <tom@tacocat.net> 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


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Jun 10, 2007, at 2:14 PM, Joe Conway wrote:

>
> Bill Moran wrote:
>> Tom Allison <tom@tacocat.net> 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
>


Good questions. I guess there are two answers. There are times when
I will want aggregate data and I'm not as concerned about the
execution time.
But there are other queries that are part of the application design.
These are always going to be of a type where I know a single specific
primary key value and I want to find all the rows that are related.


First table has a row of
idx serial primary key
Third table has a row of
idx bigserial primary key

and a second table (the billion row table) consistes of two rows:
first_idx integer not null references first(idx) on delete cascade,
third_idx bigint not null references third(idx) on delete cascade,
constraint pkey_first_third primary key (first_idx, third_idx)

The common query will be:

select t.string
from first f, second s, third t
where f.idx = s.first_idx
and s.third_idx = t.idx
and f.idx = 4 (or whatever...).

So, I think the answer is that the data isn't going to be temporal or
otherwise segragated or subsets.
I'll assume this is a lead in for partitions?
The data will be queried very frequently. Probably plan on a query
every 10 seconds and I don't know what idx ranges will be involved.
Would it be possible to partition this by the first_idx value? An
improvement?

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

2007/6/10, Alexander Staubo <alex@purefiction.net>:
> On 6/10/07, Tom Allison <tom@tacocat.net> wrote:
> > 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.
>
> You don't say anything about what the data is in the table or what
> queries you run against it, so there's not much here to give advice
> about.
>
> For the monitoring, however, you can log your queries along with
> timings and timestamps, and copy them into a tool like R to
> statistically analyze your performance over time. You will be able to
> predict the point at which your system will be too slow to use, if
> indeed the performance degradation is expontential.

Could you please share some details about this "tool like R"? Maybe
some links or usage examples?

TIA.



--
Filip Rembiałkowski

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

Filip Rembiałkowski wrote:

>> For the monitoring, however, you can log your queries along with
>> timings and timestamps, and copy them into a tool like R to
>> statistically analyze your performance over time. You will be able to
>> predict the point at which your system will be too slow to use, if
>> indeed the performance degradation is expontential.
>
> Could you please share some details about this "tool like R"? Maybe
> some links or usage examples?


Find R at http://www.r-project.org/. Or use any other analysis and/or
graphing tool of your choosing (gnumeric, OO-calc, gnuplot, roll-your-own).

Cheers,
Steve


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

Alexander Staubo wrote:
> ....
> For the monitoring, however, you can log your queries along with
> timings and timestamps, and copy them into a tool like R to
> statistically analyze your performance over time. You will be able to
> predict the point at which your system will be too slow to use, if
> indeed the performance degradation is expontential.
> ...


In my experience the more common situation is to "go off a cliff."
Everything hums along fine and the increases in table-size and user-base
have very little impact on your response times. Then suddenly you run
out of some resource (usually memory first).You hit swap and as your
few-millisecond query takes seconds or minutes your request queue backs
up, new connections are denied and everything goes downhill fast.

I think that keeping an eye on system resource trends via sar or similar
is more likely to provide the desired warnings of "sudden dropoff ahead".

Cheers,
Steve

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Mon, 11 Jun 2007, Steve Crawford wrote:

> In my experience the more common situation is to "go off a cliff."

Yeah, I think the idea that you'll notice performance degrading and be
able to extrapolate future trends using statistical techniques is a
bit...optimistic.

Anyway, back to the original question here. If you're worried about
catching when performance starts becoming an issue, you need to do some
sort of logging of how long statements are taking to execute. The main
choice is whether to log everything, at which point the logging and
sorting through all the data generated may become its own performance
concern, or whether to just log statements that take a long time and then
count how many of them show up. Either way will give you some sort of
early warning once you get a baseline; it may take a bit of tweaking to
figure out where to draw the line at for what constitutes a "long"
statement if you only want to see how many of those you get.

There are two tools you should look at initially to help process the
logging information you get back: pgFouine and PQA. Here are intros to
each that also mention how to configure the postgresql.conf file:

http://pgfouine.projects.postgresql.org/tutorial.html
http://www.databasejournal.com/featu...le.php/3323561

As they're similar programs, which would work better for you is hard to
say; check out both and see which seems more practical or easier to get
running. For example, if you only have one of PHP/Ruby installed, that
may make one tool or the easier preferred.

If you can get yourself to the point where you can confidently say
something like "yesterday we had 346 statements that took more then 200ms
to execute, which is 25% above this month's average", you'll be in a
positition to catch performance issues before they completely blindside
you; makes you look good in meetings, too.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Greg Smith wrote:
>
> On Mon, 11 Jun 2007, Steve Crawford wrote:
>
>> In my experience the more common situation is to "go off a cliff."
>
> Yeah, I think the idea that you'll notice performance degrading and be
> able to extrapolate future trends using statistical techniques is a
> bit...optimistic.
>
> Anyway, back to the original question here. If you're worried about
> catching when performance starts becoming an issue, you need to do some
> sort of logging of how long statements are taking to execute. The main
> choice is whether to log everything, at which point the logging and
> sorting through all the data generated may become its own performance
> concern, or whether to just log statements that take a long time and
> then count how many of them show up. Either way will give you some sort
> of early warning once you get a baseline; it may take a bit of tweaking
> to figure out where to draw the line at for what constitutes a "long"
> statement if you only want to see how many of those you get.
>
> There are two tools you should look at initially to help process the
> logging information you get back: pgFouine and PQA. Here are intros to
> each that also mention how to configure the postgresql.conf file:
>
> http://pgfouine.projects.postgresql.org/tutorial.html
> http://www.databasejournal.com/featu...le.php/3323561
>
> As they're similar programs, which would work better for you is hard to
> say; check out both and see which seems more practical or easier to get
> running. For example, if you only have one of PHP/Ruby installed, that
> may make one tool or the easier preferred.
>
> If you can get yourself to the point where you can confidently say
> something like "yesterday we had 346 statements that took more then
> 200ms to execute, which is 25% above this month's average", you'll be in
> a positition to catch performance issues before they completely
> blindside you; makes you look good in meetings, too.
>

Starting to sound like a sane idea.
I've been running a test job for almost 24 hours and have accumulated only 8
million rows. That's another 125 days to get to the big 'B'. I think by then
I'll have blown a hard drive or worse. I'm running this on some very old
hardware that I have available (more of this at the bottom).

However, at this point the machine is running all of the SQL at < 0.2 seconds
each. Which I consider just fine for 7,599,519 rows.

Here's some specifics about the tables:
count() from headers: 890300
count() from tokens: 890000
count() from header_token: 7599519


CREATE TABLE header_token (
header_idx integer NOT NULL,
token_idx integer NOT NULL
);

CREATE TABLE headers (
idx serial NOT NULL,
hash character varying(64) NOT NULL
);

CREATE TABLE tokens (
idx bigserial NOT NULL,
hash character varying(64) NOT NULL
);

ALTER TABLE ONLY headers
ADD CONSTRAINT headers_hash_key UNIQUE (hash);
ALTER TABLE ONLY headers
ADD CONSTRAINT headers_pkey PRIMARY KEY (idx);
ALTER TABLE ONLY header_token
ADD CONSTRAINT pkey_header_token PRIMARY KEY (header_idx, token_idx);
ALTER TABLE ONLY tokens
ADD CONSTRAINT tokens_hash_key UNIQUE (hash);
ALTER TABLE ONLY tokens
ADD CONSTRAINT tokens_pkey PRIMARY KEY (idx);
ALTER TABLE ONLY header_token
ADD CONSTRAINT header_token_header_idx_fkey FOREIGN KEY (header_idx)
REFERENCES headers(idx) ON DELETE CASCADE;
ALTER TABLE ONLY header_token
ADD CONSTRAINT header_token_token_idx_fkey FOREIGN KEY (token_idx)
REFERENCES tokens(idx) ON DELETE CASCADE;



The SQL I was timing were:
select t.hash, h.hash
from headers h, header_token ht, tokens t
where h.idx = ht.header_idx
and ht.token_idx = t.idx
and h.idx = ?


insert into header_token
select $header, idx from tokens where idx in (...)

The SELECT was <0.2
The INSERT was easily <.7 (most of the time -- ranged because the idx IN (..)
varied from 200 to 700. The min was <2 and the max was >1.0 from a few minutes
of observation.


All of this was run on a Pentium II 450 MHz with 412MB RAM and a software linear
0 pair or UDMA 66 7200RPM 8MB Cache drives (really old) on seperate IDE channels
with ReiserFS disk format. The actual script was running on a seperate machine
across a 100-base-T full duplex network through a firewall machine between the
two subnets.

I can't imagine how long it would take to run:
delete from tokens;
with the CASCADE option...

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/