On 3/1/07, Joshua D. Drake <jd@commandprompt.com> wrote:
> Alex Deucher wrote:
> > On 3/1/07, Joshua D. Drake <jd@commandprompt.com> wrote:
> >> \
> >> >> Is the SAN being shared between the database servers and other
> >> >> servers? Maybe
> >> >> it was just random timing that gave you the poor write performance on
> >> >> the old
> >> >> server which might be also yielding occassional poor performance on
> >> >> the new
> >> >> one.
> >> >>
> >> >
> >> > The direct attached scsi discs on the old database server we getting
> >> > 45MBps not the SAN. The SAN got 62/145Mbps, which is not as bad.
> >>
> >> How many spindles you got in that SAN?
> >
> > 105 IIRC.
>
> You have 105 spindles are you are only get 62megs on writes? That seems
> about half what you should be getting. (at least).
>

Take the numbers with grain of salt. They are by no means a thorough
evaluation. I just ran bonnie a couple times to get a rough reference
point. I can do a more thorough analysis.

Alex

> Joshua D. Drake
>
>
> >
> > Alex
> >
>

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

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

* Alex Deucher:

> I have noticed a strange performance regression and I'm at a loss as
> to what's happening. We have a fairly large database (~16 GB).

Sorry for asking, but is this a typo? Do you mean 16 *TB* instead of
16 *GB*?

If it's really 16 GB, you should check if it's cheaper to buy more RAM
than to fiddle with the existing infrastructure.

> however the table structure is almost identical (UTF8 on the new one
> vs. C on the old).

Locale settings make a huge difference for sorting and LIKE queries.
We usually use the C locale and SQL_ASCII encoding, mostly for
performance reasons. (Proper UTF-8 can be enforced through
constraints if necessary.)

--
Florian Weimer <fweimer@bfk.de>
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99

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

Florian Weimer wrote:
> * Alex Deucher:
>
>
>> I have noticed a strange performance regression and I'm at a loss as
>> to what's happening. We have a fairly large database (~16 GB).
>>
>
> Sorry for asking, but is this a typo? Do you mean 16 *TB* instead of
> 16 *GB*?
>
> If it's really 16 GB, you should check if it's cheaper to buy more RAM
> than to fiddle with the existing infrastructure.
>

This brings me to a related question:

Do I need to specifically configure something to take advantage of
such increase of RAM?

In particular, is the amount of things that postgres can do with RAM
limited by the amount of shared_buffers or some other parameter?
Should shared_buffers be a fixed fraction of the total amount of
physical RAM, or should it be the total amount minus half a gigabyte
or so?

As an example, if one upgrades a host from 1GB to 4GB, what would
be the right thing to do in the configuration, assuming 8.1 or 8.2? (at
least what would be the critical aspects?)

Thanks,

Carlos
--


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

On 3/2/07, Florian Weimer <fweimer@bfk.de> wrote:
> * Alex Deucher:
>
> > I have noticed a strange performance regression and I'm at a loss as
> > to what's happening. We have a fairly large database (~16 GB).
>
> Sorry for asking, but is this a typo? Do you mean 16 *TB* instead of
> 16 *GB*?
>
> If it's really 16 GB, you should check if it's cheaper to buy more RAM
> than to fiddle with the existing infrastructure.
>

Yes, 16 GB. I'd rather not shell out for more ram, if I'm not even
sure that will help. The new system should be faster, or at least as
fast, so I'd like to sort out what's going on before I buy more ram.


> > however the table structure is almost identical (UTF8 on the new one
> > vs. C on the old).
>
> Locale settings make a huge difference for sorting and LIKE queries.
> We usually use the C locale and SQL_ASCII encoding, mostly for
> performance reasons. (Proper UTF-8 can be enforced through
> constraints if necessary.)
>

I suppose that might be a factor. How much of a performance
difference do you see between utf-8 and C?


Alex

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

http://archives.postgresql.org

At 08:56 AM 3/2/2007, Carlos Moreno wrote:
>Florian Weimer wrote:
>>* Alex Deucher:
>>
>>
>>>I have noticed a strange performance regression and I'm at a loss as
>>>to what's happening. We have a fairly large database (~16 GB).
>>>
>>
>>Sorry for asking, but is this a typo? Do you mean 16 *TB* instead of
>>16 *GB*?
>>
>>If it's really 16 GB, you should check if it's cheaper to buy more RAM
>>than to fiddle with the existing infrastructure.
>>
>
>This brings me to a related question:
>
>Do I need to specifically configure something to take advantage of
>such increase of RAM?
>
>In particular, is the amount of things that postgres can do with RAM
>limited by the amount of shared_buffers or some other parameter?
>Should shared_buffers be a fixed fraction of the total amount of
>physical RAM, or should it be the total amount minus half a gigabyte
>or so?
>
>As an example, if one upgrades a host from 1GB to 4GB, what would
>be the right thing to do in the configuration, assuming 8.1 or 8.2? (at
>least what would be the critical aspects?)
>
>Thanks,
>
>Carlos

Unfortunately, pg does not (yet! ;-) ) treat all available RAM as a
common pool and dynamically allocate it intelligently to each of the
various memory data structures.

So if you increase your RAM, you will have to manually change the
entries in the pg config file to take advantage of it.
(and start pg after changing it for the new config values to take effect)

The pertinent values are all those listed under "Memory" in the
annotated pg conf file: shared_buffers, work_mem, maintenance_work_mem, etc.
http://www.powerpostgresql.com/Downl...d_conf_80.html

Cheers,
Ron Peacetree


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

http://archives.postgresql.org

On 3/1/07, Jeff Frost <jeff@frostconsultingllc.com> wrote:
> On Thu, 1 Mar 2007, Alex Deucher wrote:
>
> > here are some examples. Analyze is still running on the new db, I'll
> > post results when that is done. Mostly what our apps do is prepared
> > row selects from different tables:
> > select c1,c2,c3,c4,c5 from t1 where c1='XXX';
> >
> > old server:
> > db=# EXPLAIN ANALYZE select c1,c2 from t1 where c2='6258261';
> > QUERY PLAN
> > ---------------------------------------------------------------------------------------------------------------------------
> > Index Scan using t1_c2_index on t1 (cost=0.00..166.89 rows=42
> > width=26) (actual time=5.722..5.809 rows=2 loops=1)
> > Index Cond: ((c2)::text = '6258261'::text)
> > Total runtime: 5.912 ms
> > (3 rows)
> >
> > db=# EXPLAIN ANALYZE select c1,c2 from t1 where c1='6258261';
> > QUERY PLAN
> > ----------------------------------------------------------------------------------------------------------------------------
> > Index Scan using t1_c1_key on t1 (cost=0.00..286.08 rows=72
> > width=26) (actual time=12.423..12.475 rows=12 loops=1)
> > Index Cond: ((c1)::text = '6258261'::text)
> > Total runtime: 12.538 ms
> > (3 rows)
> >
> >
> > new server:
> > db=# EXPLAIN ANALYZE select c1,c2 from t1 where c2='6258261';
> > QUERY PLAN
> > ----------------------------------------------------------------------------------------------------------------------------
> > Index Scan using t1_c2_index on t1 (cost=0.00..37.63 rows=11
> > width=26) (actual time=33.461..51.377 rows=2 loops=1)
> > Index Cond: ((c2)::text = '6258261'::text)
> > Total runtime: 51.419 ms
> > (3 rows)
> >
> > db=# EXPLAIN ANALYZE select c1,c2 from t1 where c1='6258261';
> > QUERY PLAN
> > --------------------------------------------------------------------------------------------------------------------------------
> > Index Scan using t1_c1_index on t1 (cost=0.00..630.45 rows=2907
> > width=26) (actual time=45.733..46.271 rows=12 loops=1)
> > Index Cond: ((c1)::text = '6258261'::text)
> > Total runtime: 46.325 ms
> > (3 rows)
>
> Notice the huge disparity here betwen the expected number of rows (2907) and
> the actual rows? That's indicative of needing to run analyze. The time is
> only about 4x the 7.4 runtime and that's with the analyze running merrily
> along in the background. It's probably not as bad off as you think. At least
> this query isn't 10x. :-)
>
> Run these again for us after analyze is complete.

well, while the DB isn't 10x, the application using the DB shoes a 10x
decrease in performance. Pages that used to take 5 seconds to load
take 50 secs (I supposed the problem is compounded as there are
several queries per page.). Anyway, new numbers after the analyze.
Unfortunately, they are improved, but still not great:

old server:
db=# EXPLAIN ANALYZE select c1,c2 from t1 where c2='6258261';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Index Scan using t1_c2_index on t1 (cost=0.00..166.89 rows=42
width=26) (actual time=0.204..0.284 rows=2 loops=1)
Index Cond: ((c2)::text = '6258261'::text)
Total runtime: 0.421 ms
(3 rows)

db=# EXPLAIN ANALYZE select c1,c2 from t1 where c1='6258261';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Index Scan using t1_c1_key on t1 (cost=0.00..286.08 rows=72
width=26) (actual time=0.299..0.354 rows=12 loops=1)
Index Cond: ((c1)::text = '6258261'::text)
Total runtime: 0.451 ms
(3 rows)



new server:
db=# EXPLAIN ANALYZE select c1,c2 from t1 where c2='6258261';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Index Scan using t1_c2_index on t1 (cost=0.00..37.63 rows=11
width=26) (actual time=0.126..0.134 rows=2 loops=1)
Index Cond: ((c2)::text = '6258261'::text)
Total runtime: 0.197 ms
(3 rows)

db=# EXPLAIN ANALYZE select c1,c2 from t1 where c1='6258261';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Index Scan using t1_c1_index on t1 (cost=0.00..630.45 rows=2907
width=26) (actual time=5.820..5.848 rows=12 loops=1)
Index Cond: ((c1)::text = '6258261'::text)
Total runtime: 5.899 ms
(3 rows)

Here's another example:
old server:
db=# EXPLAIN ANALYZE select c1,c2 from t1 where c2='6000001';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Scan using t1_c2_index on t1 (cost=0.00..166.89 rows=42
width=26) (actual time=4.031..55.349 rows=8 loops=1)
Index Cond: ((c2)::text = '6000001'::text)
Total runtime: 55.459 ms
(3 rows)

db=# EXPLAIN ANALYZE select c1,c2 from t1 where c1='6000001';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Index Scan using t1_c1_key on t1 (cost=0.00..286.08 rows=72
width=26) (actual time=0.183..0.203 rows=4 loops=1)
Index Cond: ((c1)::text = '6000001'::text)
Total runtime: 0.289 ms
(3 rows)


new server:
db=# EXPLAIN ANALYZE select c1,c2 from t1 where c2='6000001';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Index Scan using t1_c2_index on t1 (cost=0.00..37.63 rows=11
width=26) (actual time=115.412..202.151 rows=8 loops=1)
Index Cond: ((c2)::text = '6000001'::text)
Total runtime: 202.234 ms
(3 rows)

db=# EXPLAIN ANALYZE select c1,c2 from t1 where c1='6000001';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Index Scan using t1_c1_index on t1 (cost=0.00..630.45 rows=2907
width=26) (actual time=99.811..99.820 rows=4 loops=1)
Index Cond: ((c1)::text = '6000001'::text)
Total runtime: 99.861 ms
(3 rows)

I haven't gotten a chance to restart postgres this the config changes
you suggested yet. The rows have improved for some but not all and
the times are still slow. Any ideas?

Alex

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

At 10:16 AM 3/2/2007, Alex Deucher wrote:
>On 3/2/07, Florian Weimer <fweimer@bfk.de> wrote:
>>* Alex Deucher:
>>
>> > I have noticed a strange performance regression and I'm at a loss as
>> > to what's happening. We have a fairly large database (~16 GB).
>>
>>Sorry for asking, but is this a typo? Do you mean 16 *TB* instead of
>>16 *GB*?
>>
>>If it's really 16 GB, you should check if it's cheaper to buy more RAM
>>than to fiddle with the existing infrastructure.
>
>Yes, 16 GB. I'd rather not shell out for more ram, if I'm not even
>sure that will help. The new system should be faster, or at least as
>fast, so I'd like to sort out what's going on before I buy more ram.
>
OK. You
a= went from pg 7.4.x to 8.1.4 AND

b= you changed from 4 SPARC CPUs (how many cores? If this is > 4...)
to 2 2C Opterons AND
(SPEC and TPC bench differences between these CPUs?)

c= you went from a Sun box to a "white box" AND
(memory subsystem differences? other differences?)

d= you went from local HD IO to a SAN
(many differences hidden in that one line... ...and is the physical
layout of tables and things like pg_xlog sane on the SAN?)


....and you did this by just pulling over the old DB onto the new HW?

May I suggest that it is possible that your schema, queries, etc were
all optimized for pg 7.x running on the old HW?
(explain analyze shows the old system taking ~1/10 the time per row
as well as estimating the number of rows more accurately)

RAM is =cheap=. Much cheaper than the cost of a detective hunt
followed by rework to queries, schema, etc.
Fitting the entire DB into RAM is guaranteed to help unless this is
an OLTP like application where HD IO is required to be synchronous..
If you can fit the entire DB comfortably into RAM, do it and buy
yourself the time to figure out the rest of the story w/o impacting
on production performance.

Cheers,
Ron Peacetree


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On 3/2/07, Ron <rjpeace@earthlink.net> wrote:
> At 10:16 AM 3/2/2007, Alex Deucher wrote:
> >On 3/2/07, Florian Weimer <fweimer@bfk.de> wrote:
> >>* Alex Deucher:
> >>
> >> > I have noticed a strange performance regression and I'm at a loss as
> >> > to what's happening. We have a fairly large database (~16 GB).
> >>
> >>Sorry for asking, but is this a typo? Do you mean 16 *TB* instead of
> >>16 *GB*?
> >>
> >>If it's really 16 GB, you should check if it's cheaper to buy more RAM
> >>than to fiddle with the existing infrastructure.
> >
> >Yes, 16 GB. I'd rather not shell out for more ram, if I'm not even
> >sure that will help. The new system should be faster, or at least as
> >fast, so I'd like to sort out what's going on before I buy more ram.
> >
> OK. You
> a= went from pg 7.4.x to 8.1.4 AND
>

yes.

> b= you changed from 4 SPARC CPUs (how many cores? If this is > 4...)
> to 2 2C Opterons AND
> (SPEC and TPC bench differences between these CPUs?)
>

4 single core 800 Mhz sparcs to 2 dual core 2.2 Ghz opterons.

> c= you went from a Sun box to a "white box" AND
> (memory subsystem differences? other differences?)
>

The new hardware is Sun as well. X4100s running Linux. It should be
faster all around because the old server is 5 years old.

> d= you went from local HD IO to a SAN
> (many differences hidden in that one line... ...and is the physical
> layout of tables and things like pg_xlog sane on the SAN?)
>
>
> ...and you did this by just pulling over the old DB onto the new HW?
>

We rebuild the DB from scratch on the new server. Same table
structure though. We reloaded from the source material directly.

> May I suggest that it is possible that your schema, queries, etc were
> all optimized for pg 7.x running on the old HW?
> (explain analyze shows the old system taking ~1/10 the time per row
> as well as estimating the number of rows more accurately)
>
> RAM is =cheap=. Much cheaper than the cost of a detective hunt
> followed by rework to queries, schema, etc.
> Fitting the entire DB into RAM is guaranteed to help unless this is
> an OLTP like application where HD IO is required to be synchronous..
> If you can fit the entire DB comfortably into RAM, do it and buy
> yourself the time to figure out the rest of the story w/o impacting
> on production performance.

Perhaps so. I just don't want to spend $1000 on ram and have it only
marginally improve performance if at all. The old DB works, so we can
keep using that until we sort this out.

Alex

>
> Cheers,
> Ron Peacetree
>
>

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

"Alex Deucher" <alexdeucher@gmail.com> writes:
> Anyway, new numbers after the analyze.
> Unfortunately, they are improved, but still not great:

Why are the index names different between the old and new servers?
Is that just cosmetic, or is 8.2 actually picking a different
(and less suitable) index for the c1 queries?

regards, tom lane

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

http://archives.postgresql.org

On 3/2/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Alex Deucher" <alexdeucher@gmail.com> writes:
> > Anyway, new numbers after the analyze.
> > Unfortunately, they are improved, but still not great:
>
> Why are the index names different between the old and new servers?
> Is that just cosmetic, or is 8.2 actually picking a different
> (and less suitable) index for the c1 queries?
>

That's just cosmetic. They are the same.

Alex

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster