[PERFORM] Speed / Server

[anthony]

All:

We have a web-application which is growing ... fast.  We're currently
running on (1) quad-core Xeon 2.0Ghz with a RAID-1 setup, and 8GB of RAM.

Our application collects a lot of sensor data, which means that we have 1
table which has about 8 million rows, and we're adding about 2.5 million
rows per month.

The problem is, this next year we're anticipating significant growth,
where we may be adding more like 20 million rows per month (roughly 15GB
of data).

A row of data might have:
  The system identifier (int)
  Date/Time read (timestamp)
  Sensor identifier (int)
  Data Type (int)
  Data Value (double)

The nasty part of this problem is that the data needs to be "readily"
available for reports, and we cannot consolidate the data for reporting
purposes.

We generate real time graphs from this data, usually running reports
across multiple date/time ranges for any given system.  Reports and graphs
do not span more than 1 system, and we have indexes on the applicable
columns.

I know we need a LOT of RAM (as much as we can afford), and we're looking
at a couple of Nehalem systems w/ a large, and fast, RAID-10 disk set up.

So far, we're seeing some slowness in reading from our table - queries are
in the "seconds" range.  No issues, yet, with inserting volumes of data.

Two questions:

1.  Other than partitioning (by system, and/or date), and splitting up the
data into multiple tables (by data type), what could be done within
Postgresql to help with this type of set up (1 large table)?

2.  Before going out and buying a beast of a system, we'd like to get some
idea of performance on a "high-end" system.  We may need to split this up,
or move into some other type of architecture.  Do you know of anyone who
would let us "play" with a couple of systems to see what would be an
applicable purchase?

Thanks!


--
Anthony


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] How to move pg_xlog to another drive on Windows????

[Anthony Presley]

Hmm ... I'm guessing you'd do it with a shortcut, and then rename the
ShortCut from "Shortcut to pg_xlog" to "pg_xlog".

Haven't done it with PostgreSQL, but it works with a few other programs
I've had to do that with.


--
Anthony Presley
Resolution Software
Owner/Founder
www.resolution.com

On Mon, 2006-12-04 at 18:48 +0100, Joost Kraaijeveld wrote:
> How can I move pg_xlog to another drive on Windows? In Linux I can use a
> symlink, but how do I that on windows?
> 
> --
> Groeten,
> 
> Joost Kraaijeveld
> Askesis B.V.
> Molukkenstraat 14
> 6524NB Nijmegen
> tel: 024-3888063 / 06-51855277
> fax: 024-3608416
> web: www.askesis.nl
> 
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend


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

Re: [PERFORM] Sun Fire T2000 and PostgreSQL 8.1.3

[Anthony Ransley]

Hi Juan Casero,

I've found that serial query clients are best served by PostgreSQL
running on fast single or dual core processors, ( such as the Athlon
FX60 ) rather than expensive n-way systems. I was orginally using an
8-way Xeon computer for a similar serial throughput problem. and i
wasn't supprised to find that at least 6 of the 8 processors were idle.
The point is, for this type client, you are better off spending the
money on the fastest single or dual core processors than a multiway box.

Anthony.

Juan Casero (FL FLC) wrote:

  
  
  Greetings -
   
  I
am testing a Sun Microsystems Sun Fire T2000 demo server at our
company.   I want to know if anyone here has any experience with this
hardware and postgresql 8.1.3.  I installed the copy of postgresql
8.1.3 from blastwave.org onto this demo box and loaded our production
database into it.  This box has a single Ultrasparc T1 cpu with six
execution piplelines that can each handle 4 threads.  With the Unix top
utility the postgresql server appears to bounce around between the
available threads on the system.  For example I run a single large
query and I can see the postgresql server sometimes running on cpu/0,
other times on cpu/1, cpu/3,etc up to cpu/23.   However, never is
the load for the postgres server reported to be higher than 4.16%.  I
did the math and 4.16% x 24 threads = 98.84% cpu load.  So I wonder if
the Solaris 10 kernel is somehow throttling the processes so that any
single virtual processor can do no more than 4.16% load.  We got this
server last week and I was able to install it in our rack just
yesterday.   Now I need to see how I can optimize the postgresql server
to work on this box.  Does anyone have any suggestions?   I know the
postgresql server is not smp aware but I believe parts of it are.  In
particular the buffer manager is supposed to scale the performance
almost linearly with the number of cpu's (including virtual ones).  I
don't know however, if I need to recompile the postgresql server myself
to get those benefits.   I am using the version of postgresql 8.1.3
that is available on blastwave.org.  I am also working with the 64 bit
version of the database server.   This machine has over 8GB of ram so I
was thinking of using the 64 bit version of the postgresql server so I
can access ram beyong the 4gb limit imposed by 32 bit addressing.  Any
help or recommendations for performance tweaking of postgresql is very
much appreciated.
   
   
  Thanks,
  Juan

[PERFORM] Split select completes, single select doesn't and becomes IO bound!

[Anthony Ransley]

Can any one explain why the following query

select f(q) from
(
   select * from times
   where '2006-03-01 00:00:00'<=q and q<'2006-03-08 00:00:00'
   order by q
) v;

never completes, but splitting up the time span into single days does work.

select f(q) from
(
   select * from times
   where '2006-03-01 00:00:00'<=q and q<'2006-03-02 00:00:00'
   order by q
) v;
select f(q) from
(
   select * from times
   where '2006-03-02 00:00:00'<=q and q<'2006-03-03 00:00:00'
   order by q
) v;
...
select f(q) from
(
   select * from times
   where '2006-03-07 00:00:00'<=q and q<'2006-03-08 00:00:00'
   order by q
) v;

The stored procedure f(q) take a timestamp and does a select and a 
calculation and then an update of a results table. The times table 
containes only a 100 rows per day. It is also observed that the cpu 
starts the query with 100% usage and then the slowly swings up and down 
from 100% to 20% over the first half hour, and then by the following 
morning the query is still running and the cpu usage is 3-5%. IO bound 
i'm guessing as the hdd is in constant use at 5 to 15 MB per second usage.
In contrast the query that is split up into days has a 100% cpu usage 
all the way through to its completion, which only takes twenty minutes 
each. The computer is not being used for anything else, and is a dual 
core Athlon 4400+ with 4GB of ram.


Thanks for any information you can give on this.

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

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

[PERFORM] 64-bit vs 32-bit performance ... backwards?

[Anthony Presley]

Hi all!

I had an interesting discussion today w/ an Enterprise DB developer and
sales person, and was told, twice, that the 64-bit linux version of
Enterprise DB (which is based on the 64-bit version of PostgreSQL 8.1)
is SIGNIFICANTLY SLOWER than the 32-bit version.  Since the guys of EDB
are PostgreSQL . has anyone seen that the 64-bit is slower than the
32-bit version?

I was told that the added 32-bits puts a "strain" and extra "overhead"
on the processor / etc which actually slows down the pointers and
necessary back-end "stuff" on the database.

I'm curious if anyone can back this up  or debunk it.  It's about
the polar opposite of everything I've heard from every other database
vendor for the past several years, and would be quite an eye-opener for
me.

Anyone?

Thanks.

--
Anthony


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

[PERFORM] Why is it choosing a different plan?

[Anthony Presley]

Hi all, I'm having some confusion with the 7.4 query planner.

I have two identical queries, whereby the passed (varchar) parameter
appears to be the deciding factor between a sequential or an index scan.


IE, This query:

explain SELECT DISTINCT (a1.ENTRY_ID) AS retrieved FROM OS_CURRENTSTEP
AS a1 , OS_CURRENTSTEP AS a2  WHERE a1.ENTRY_ID = a1.ENTRY_ID AND
a1.ENTRY_ID = a2.ENTRY_ID AND ( a1.OWNER =  'p1'  AND a2.STEP_ID =
1  );
NOTICE:  QUERY PLAN:

Unique  (cost=1175.88..1175.88 rows=1 width=16)
  ->  Sort  (cost=1175.88..1175.88 rows=1 width=16)
->  Nested Loop  (cost=0.00..1175.87 rows=1 width=16)
  ->  Index Scan using idx_9 on os_currentstep a1
(cost=0.00..1172.45 rows=1 width=8)
  ->  Index Scan using idx_8 on os_currentstep a2
(cost=0.00..3.41 rows=1 width=8)

However, this query:

explain SELECT DISTINCT (a1.ENTRY_ID) AS retrieved FROM OS_CURRENTSTEP
AS a1 , OS_CURRENTSTEP AS a2  WHERE a1.ENTRY_ID = a1.ENTRY_ID AND
a1.ENTRY_ID = a2.ENTRY_ID AND ( a1.OWNER =  'GIL'  AND a2.STEP_ID =
1  );
NOTICE:  QUERY PLAN:

Unique  (cost=3110.22..3110.22 rows=1 width=16)
  ->  Sort  (cost=3110.22..3110.22 rows=1 width=16)
->  Nested Loop  (cost=0.00..3110.21 rows=1 width=16)
  ->  Seq Scan on os_currentstep a1  (cost=0.00..3106.78
rows=1 width=8)
  ->  Index Scan using idx_8 on os_currentstep a2
(cost=0.00..3.41 rows=1 width=8)


Thoughts about why changing OWNER from 'p1' to 'GIL' would go from an
Index Scan to a Sequential?

[There is an index on os_currentstep, and it was vacuum analyze'd
recently.]

Running version 7.4 (working on upgrading to 8.0 soon).  Thanks!

--
Anthony


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

   http://archives.postgresql.org

Re: [PERFORM] Why is it choosing a different plan?

[Anthony Presley]

I thought this was related to the TYPE (ie, I could cast it using
something like: attr1=1::int8).  However, I tried a few more values, and
the query planner is confusing me.

With these values, in the owner, I get a Seq Scan:
'GIL', '1122', '2305'

With these values, in the owner, I get an Index Scan:
'p1', 'p2', '2300', '8088', 'CHANGEINVENTION'

The os_currentstep table has about 119,700 rows in it -- and I can't do
too much to actually change the query, since it's coming from something
of a 'black box' application.

Thoughts?

--
Anthony

On Fri, 2006-09-22 at 17:59 -0500, Anthony Presley wrote:
> Hi all, I'm having some confusion with the 7.4 query planner.
> 
> I have two identical queries, whereby the passed (varchar) parameter
> appears to be the deciding factor between a sequential or an index scan.
> 
> 
> IE, This query:
> 
> explain SELECT DISTINCT (a1.ENTRY_ID) AS retrieved FROM OS_CURRENTSTEP
> AS a1 , OS_CURRENTSTEP AS a2  WHERE a1.ENTRY_ID = a1.ENTRY_ID AND
> a1.ENTRY_ID = a2.ENTRY_ID AND ( a1.OWNER =  'p1'  AND a2.STEP_ID =
> 1  );
> NOTICE:  QUERY PLAN:
> 
> Unique  (cost=1175.88..1175.88 rows=1 width=16)
>   ->  Sort  (cost=1175.88..1175.88 rows=1 width=16)
> ->  Nested Loop  (cost=0.00..1175.87 rows=1 width=16)
>   ->  Index Scan using idx_9 on os_currentstep a1
> (cost=0.00..1172.45 rows=1 width=8)
>   ->  Index Scan using idx_8 on os_currentstep a2
> (cost=0.00..3.41 rows=1 width=8)
> 
> However, this query:
> 
> explain SELECT DISTINCT (a1.ENTRY_ID) AS retrieved FROM OS_CURRENTSTEP
> AS a1 , OS_CURRENTSTEP AS a2  WHERE a1.ENTRY_ID = a1.ENTRY_ID AND
> a1.ENTRY_ID = a2.ENTRY_ID AND ( a1.OWNER =  'GIL'  AND a2.STEP_ID =
> 1  );
> NOTICE:  QUERY PLAN:
> 
> Unique  (cost=3110.22..3110.22 rows=1 width=16)
>   ->  Sort  (cost=3110.22..3110.22 rows=1 width=16)
> ->  Nested Loop  (cost=0.00..3110.21 rows=1 width=16)
>   ->  Seq Scan on os_currentstep a1  (cost=0.00..3106.78
> rows=1 width=8)
>   ->  Index Scan using idx_8 on os_currentstep a2
> (cost=0.00..3.41 rows=1 width=8)
> 
> 
> Thoughts about why changing OWNER from 'p1' to 'GIL' would go from an
> Index Scan to a Sequential?
> 
> [There is an index on os_currentstep, and it was vacuum analyze'd
> recently.]
> 
> Running version 7.4 (working on upgrading to 8.0 soon).  Thanks!
> 
> --
> Anthony
> 
> 
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
> 
>http://archives.postgresql.org


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

Re: [PERFORM] Why is it choosing a different plan?

[Anthony Presley]

Doh!

Bad kharma.  I apologize.  Too late, and not enuf caffeine.  I posted
here because this query is taking 2+ minutes on a production machine,
and under 4 seconds on a development machine.

For posterity sakes  the seq scan is because of the distribution of
those values.  GIL is in about 1/2 of the records.  The others are very
common.  Cheaper to do a Sequential than to do an index.  The other
values are present in only a few spotted cases (1 to 3000), and the
index is better.

Also helps when the production machine has all of its indexes in place
to actually do the reading.

Sorry to be a bother!

--
Anthony

On Fri, 2006-09-22 at 18:58 -0500, Anthony Presley wrote:
> I thought this was related to the TYPE (ie, I could cast it using
> something like: attr1=1::int8).  However, I tried a few more values, and
> the query planner is confusing me.
> 
> With these values, in the owner, I get a Seq Scan:
>  'GIL', '1122', '2305'
> 
> With these values, in the owner, I get an Index Scan:
>  'p1', 'p2', '2300', '8088', 'CHANGEINVENTION'
> 
> The os_currentstep table has about 119,700 rows in it -- and I can't do
> too much to actually change the query, since it's coming from something
> of a 'black box' application.
> 
> Thoughts?
> 
> --
> Anthony
> 
> On Fri, 2006-09-22 at 17:59 -0500, Anthony Presley wrote:
> > Hi all, I'm having some confusion with the 7.4 query planner.
> >
> > I have two identical queries, whereby the passed (varchar) parameter
> > appears to be the deciding factor between a sequential or an index scan.
> >
> >
> > IE, This query:
> >
> > explain SELECT DISTINCT (a1.ENTRY_ID) AS retrieved FROM OS_CURRENTSTEP
> > AS a1 , OS_CURRENTSTEP AS a2  WHERE a1.ENTRY_ID = a1.ENTRY_ID AND
> > a1.ENTRY_ID = a2.ENTRY_ID AND ( a1.OWNER =  'p1'  AND a2.STEP_ID =
> > 1  );
> > NOTICE:  QUERY PLAN:
> >
> > Unique  (cost=1175.88..1175.88 rows=1 width=16)
> >   ->  Sort  (cost=1175.88..1175.88 rows=1 width=16)
> > ->  Nested Loop  (cost=0.00..1175.87 rows=1 width=16)
> >   ->  Index Scan using idx_9 on os_currentstep a1
> > (cost=0.00..1172.45 rows=1 width=8)
> >   ->  Index Scan using idx_8 on os_currentstep a2
> > (cost=0.00..3.41 rows=1 width=8)
> >
> > However, this query:
> >
> > explain SELECT DISTINCT (a1.ENTRY_ID) AS retrieved FROM OS_CURRENTSTEP
> > AS a1 , OS_CURRENTSTEP AS a2  WHERE a1.ENTRY_ID = a1.ENTRY_ID AND
> > a1.ENTRY_ID = a2.ENTRY_ID AND ( a1.OWNER =  'GIL'  AND a2.STEP_ID =
> > 1  );
> > NOTICE:  QUERY PLAN:
> >
> > Unique  (cost=3110.22..3110.22 rows=1 width=16)
> >   ->  Sort  (cost=3110.22..3110.22 rows=1 width=16)
> > ->  Nested Loop  (cost=0.00..3110.21 rows=1 width=16)
> >   ->  Seq Scan on os_currentstep a1  (cost=0.00..3106.78
> > rows=1 width=8)
> >   ->  Index Scan using idx_8 on os_currentstep a2
> > (cost=0.00..3.41 rows=1 width=8)
> >
> >
> > Thoughts about why changing OWNER from 'p1' to 'GIL' would go from an
> > Index Scan to a Sequential?
> >
> > [There is an index on os_currentstep, and it was vacuum analyze'd
> > recently.]
> >
> > Running version 7.4 (working on upgrading to 8.0 soon).  Thanks!
> >
> > --
> > Anthony
> >
> >
> > ---(end of broadcast)---
> > TIP 4: Have you searched our list archives?
> >
> >http://archives.postgresql.org
> 
> 
> ---(end of broadcast)---
> TIP 2: Don't 'kill -9' the postmaster


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

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

Re: [PERFORM] Speeding up a query.

[Anthony Presley]

On the DB side of things, you will want to make sure that your caching
as much as possible - putting a front-end like memcached could help.  I
assume you have indexes on the appropriate tables?  What does the
EXPLAIN ANALYZE on that query look like?

Not necessarily a "postgres" solution, but I'd think this type of
solution would work really, really well inside of say a a mixed integer
or integer solver ... something like glpk or cbc.  You'd need to
reformulate the problem, but we've built applications using these tools
which can crunch through multiple billions of combinations in under 1 or
2 seconds.

(Of course, you still need to store the results, and feed the input,
using a database of some kind).


--
Anthony Presley

On Tue, 2009-06-16 at 14:35 -0400, Hartman, Matthew wrote:
> Good afternoon.
> 
> I have developed an application to efficiently schedule chemotherapy
> patients at our hospital. The application takes into account several
> resource constraints (available chairs, available nurses, nurse coverage
> assignment to chairs) as well as the chair time and nursing time
> required for a regimen.
> 
> The algorithm for packing appointments in respects each constraint and
> typically schedules a day of treatments (30-60) within 9-10 seconds on
> my workstation, down from 27 seconds initially. I would like to get it
> below 5 seconds if possible.
> 
> I think what's slowing is down is simply the number of rows and joins.
> The algorithm creates a scheduling matrix with one row per 5 minute
> timeslot, per unit, per nurse assigned to the unit. That translates to
> 3,280 rows for the days I have designed in development (each day can
> change). 
> 
> To determine the available slots, the algorithm finds the earliest slot
> that has an available chair and a count of the required concurrent
> intervals afterwards. So a 60 minute regimen requires 12 concurrent
> rows. This is accomplished by joining the table on itself. A second
> query is ran for the same range, but with respect to the nurse time and
> an available nurse. Finally, those two are joined against each other.
> Effectively, it is:
> 
> Select *
> From   (
>   Select *
>   From matrix m1, matrix m2
>   Where m1.x = m2.x
>   ) chair,
>   (
>   Select *
>   From matrix m1, matrix m2
>   Where m1.x = m2.x
>   ) nurse
> Where chair.id = nurse.id
> 
> With matrix having 3,280 rows. Ugh.
> 
> I have tried various indexes and clustering approachs with little
> success. Any ideas?
> 
> Thanks,
> 
> Matthew Hartman
> Programmer/Analyst
> Information Management, ICP
> Kingston General Hospital
> (613) 549- x4294 
> 
> 


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Speed / Server

[Anthony Presley]

On Tue, 2009-10-06 at 17:16 -0400, Merlin Moncure wrote:
> On Sun, Oct 4, 2009 at 6:45 PM,   wrote:
> > All:
> >
> > We have a web-application which is growing ... fast.  We're currently
> > running on (1) quad-core Xeon 2.0Ghz with a RAID-1 setup, and 8GB of RAM.
> >
> > Our application collects a lot of sensor data, which means that we have 1
> > table which has about 8 million rows, and we're adding about 2.5 million
> > rows per month.
> >
> > The problem is, this next year we're anticipating significant growth,
> > where we may be adding more like 20 million rows per month (roughly 15GB
> > of data).
> >
> > A row of data might have:
> >  The system identifier (int)
> >  Date/Time read (timestamp)
> >  Sensor identifier (int)
> >  Data Type (int)
> >  Data Value (double)
> 
> One approach that can sometimes help is to use arrays to pack data.
> Arrays may or may not work for the data you are collecting: they work
> best when you always pull the entire array for analysis and not a
> particular element of the array.  Arrays work well because they pack
> more data into index fetches and you get to skip the 20 byte tuple
> header.  That said, they are an 'optimization trade off'...you are
> making one type of query fast at the expense of others.
> 
> In terms of hardware, bulking up memory will only get you so
> far...sooner or later you have to come to terms with the fact that you
> are dealing with 'big' data and need to make sure your storage can cut
> the mustard.  Your focus on hardware upgrades should probably be size
> and quantity of disk drives in a big raid 10.
> 
> Single user or 'small number of user'  big data queries tend to
> benefit more from fewer core, fast cpus.
> 
> Also, with big data, you want to make sure your table design and
> indexing strategy is as tight as possible.

Thanks for all of the input.  One thing we're going to try is to slice
up the data based on the data type ... so that we can spread the data
rows into about 15 different tables.  This should produce 15 tables, the
largest which will have about 50% of the data, with the rest having an
uneven distribution of the remaining data.

Most of the graphs / reports that we're doing need to only use one type
of data at a time, but several will need to stitch / combine data from
multiple data tables.

These combined with some new processors, and a fast RAID-10 system
should give us what we need going forward.

Thanks again!


--
Anthony


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] strange query plan with LIMIT

[anthony . shipman]

 Limit  (cost=0.00..16.75 rows=1 width=114) (actual time=90344.384..90344.385 
rows=1 loops=1)
   ->  Index Scan using tdiag_pkey on tdiag  (cost=0.00..19114765.76 
rows=1141019 width=114)
(actual time=90344.380..90344.380 rows=1 loops=1)
 Filter: ((create_time >= '2011-06-03 19:49:04+10'::timestamp with 
time zone) AND
(create_time < '2011-06-06 19:59:04+10'::timestamp with time zone))
 Total runtime: 90344.431 ms


How do I make this both fast and simple?
-- 
Anthony Shipman | flailover systems: When one goes down it 
anthony.ship...@symstream.com   | flails about until the other goes down too.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] strange query plan with LIMIT

[anthony . shipman]

) (actual time=90344.384..90344.385 
rows=1 loops=1)
   ->  Index Scan using tdiag_pkey on tdiag  (cost=0.00..19114765.76 
rows=1141019 width=114)
(actual time=90344.380..90344.380 rows=1 loops=1)
 Filter: ((create_time >= '2011-06-03 19:49:04+10'::timestamp with 
time zone) AND
(create_time < '2011-06-06 19:59:04+10'::timestamp with time zone))
 Total runtime: 90344.431 ms


How do I make this both fast and simple?
-- 
Anthony Shipman | Tech Support: The guys who follow the 
anthony.ship...@symstream.com   | 'Parade of New Products' with a shovel. 

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] strange query plan with LIMIT

[anthony . shipman]

On Wednesday 08 June 2011 02:40, t...@fuzzy.cz wrote:
> Hi,
>
> why are you reposting this? Pavel Stehule already recommended you to run
> ANALYZE on the tdiag table - have you done that? What was the effect?

The mailing list system hiccupped and I ended up with two posts.

VACUUM ANALYZE was done, more than once.
Setting the statistics value on the diag_id column to 1000 seemed to only make 
the query a bit slower.

>
> The stats are off - e.g. the bitmap scan says
>
>    ->  Bitmap Heap Scan on tdiag  (cost=25763.48..638085.13 rows=1141019
> width=114) (actual time=43.232..322.441 rows=86530 loops=1)
>
> so it expects to get 1141019 rows but it gets 86530, i.e. about 7% of the
> expected number. That might be enough to cause bad plan choice and thus
> performance issues.

What seems odd to me is that the only difference between the two is the limit 
clause:

select * from tdiag where (create_time >= '2011-06-03
09:49:04.00+0' and create_time < '2011-06-06 09:59:04.00+0') order by 
diag_id limit 1;

select * from tdiag where (create_time >= '2011-06-03
09:49:04.00+0' and create_time < '2011-06-06 09:59:04.00+0') order by 
diag_id;

and yet the plan completely changes.

I think that I have to force the evaluation order to get a reliably fast 
result:

begin; create temporary table tt on commit drop as
select diag_id from tdiag where create_time >= '2011-06-03 09:49:04.00+0' 
 and create_time < '2011-06-06 09:59:04.00+0';
select * from tdiag where diag_id in (select * from tt)
 order by diag_id limit 10; commit;

QUERY PLAN
---
 Limit  (cost=3566.24..3566.27 rows=10 width=112) (actual 
time=1800.699..1800.736 rows=10 loops=1)
   ->  Sort  (cost=3566.24..3566.74 rows=200 width=112) (actual 
time=1800.694..1800.708 rows=10 loops=1)
 Sort Key: tdiag.diag_id
 Sort Method:  top-N heapsort  Memory: 18kB
 ->  Nested Loop  (cost=1360.00..3561.92 rows=200 width=112) (actual 
time=269.087..1608.324 rows=86530 loops=1)
   ->  HashAggregate  (cost=1360.00..1362.00 rows=200 width=4) 
(actual time=269.052..416.898 rows=86530 loops=1)
 ->  Seq Scan on tt  (cost=0.00..1156.00 rows=81600 
width=4) (actual time=0.020..120.323 rows=86530 loops=1)
   ->  Index Scan using tdiag_pkey on tdiag  (cost=0.00..10.99 
rows=1 width=112) (actual time=0.006..0.008 rows=1 loops=86530)
 Index Cond: (tdiag.diag_id = tt.diag_id)
 Total runtime: 1801.290 ms

>
> And yet another recommendation - the sort is performed on disk, so give it
> more work_mem and it should be much faster (should change from "merge
> sort" to "quick sort"). Try something like work_mem=20MB and see if it
> does the trick.

This certainly speeds up the sorting.

>
> regards
> Tomas

-- 
Anthony Shipman | What most people think about
anthony.ship...@symstream.com   | most things is mostly wrong.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] strange query plan with LIMIT

[anthony . shipman]

On Wednesday 08 June 2011 17:39, Claudio Freire wrote:
> Of course optimally executing a plan with limit is a lot different
> than one without.

I imagined that limit just cuts out a slice of the query results. 
If it can find 8 rows in 0.5 seconds then I would have thought that 
returning just the first 100 of them should be just as easy.

>
> Just... why are you sorting by diag_id?
>
> I believe you would be better off sorting by timestamp than diag_id,
> but I don't know what the query is supposed to do.

The timestamp is only almost monotonic. I need to scan the table in slices and 
I use limit and offset to select the slice.

I've forced the query order with some pgsql like:

declare
query   character varying;
rec record;
begin
-- PG 8.3 doesn't have the 'using' syntax nor 'return query execute'

execute 'create temporary table tt on commit drop as ' ||
'select diag_id from tdiag ' || v_where;

query = 'select * from tdiag where diag_id in (select * from tt) ' ||
'order by diag_id ' || v_limit || ' ' || v_offset;

for rec in execute query loop
return next rec;
end loop;
end;

-- 
Anthony Shipman | Life is the interval
anthony.ship...@symstream.com   | between pay days.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] strange query plan with LIMIT

[anthony . shipman]

On Wednesday 08 June 2011 18:39, Pavel Stehule wrote:
> if you use FOR statement, there should be a problem in using a
> implicit cursor - try to set a GUC cursor_tuple_fraction to 1.0.
Alas this is mammoth replicator, equivalent to PG 8.3 and it doesn't have that 
parameter.
-- 
Anthony Shipman | It's caches all the way 
anthony.ship...@symstream.com   | down.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] strange query plan with LIMIT

[anthony . shipman]

On Wednesday 08 June 2011 19:47, t...@fuzzy.cz wrote:
> Have you tried to create a composite index on those two columns? Not sure
> if that helps but I'd try that.
>
> Tomas

Do you mean 
   create index tdiag_index2   ON tdiag(diag_id, create_time);
Should this be in addition to or instead of the single index on create_time?



I must be doing something really wrong to get this to happen:

symstream2=> select count(*) from tdiag where create_time <= '2011-05-23 
03:51:00.131597+0';
 count
---
 0
(1 row)

symstream2=> explain analyze select count(*) from tdiag where create_time 
<= '2011-05-23 03:51:00.131597+0';
   QUERY PLAN

 Aggregate  (cost=863867.21..863867.22 rows=1 width=0) (actual 
time=58994.078..58994.080 rows=1 loops=1)
   ->  Seq Scan on tdiag  (cost=0.00..844188.68 rows=7871413 width=0) (actual 
time=58994.063..58994.063 rows=0 loops=1)
 Filter: (create_time <= '2011-05-23 13:51:00.131597+10'::timestamp 
with time zone)
 Total runtime: 58994.172 ms
(4 rows)

symstream2=> \d tdiag
Table "public.tdiag"
   Column|   Type   | Modifiers
-+--+---
 diag_id | integer  | not null default 
nextval(('diag_id_seq'::text)::regclass)
 create_time | timestamp with time zone | default now()
 diag_time   | timestamp with time zone | not null
 device_id   | integer  |
 fleet_id| integer  |
 customer_id | integer  |
 module  | character varying|
 node_kind   | smallint |
 diag_level  | smallint |
 message | character varying| not null default ''::character 
varying
 options | text |
 tag | character varying| not null default ''::character 
varying
Indexes:
"tdiag_pkey" PRIMARY KEY, btree (diag_id)
"tdiag_create_time" btree (create_time)


-- 
Anthony Shipman | Programming is like sex: One mistake and 
anthony.ship...@symstream.com   | you're providing support for a lifetime.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] strange query plan with LIMIT

[anthony . shipman]

On Thursday 09 June 2011 16:04, anthony.ship...@symstream.com wrote:
> I must be doing something really wrong to get this to happen:
Yes I did. Ignore that.
-- 
Anthony Shipman | flailover systems: When one goes down it 
anthony.ship...@symstream.com   | flails about until the other goes down too.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] strange query plan with LIMIT

[anthony . shipman]

On Wednesday 08 June 2011 19:47, t...@fuzzy.cz wrote:
> Have you tried to create a composite index on those two columns? Not sure
> if that helps but I'd try that.
>
> Tomas

This finally works well enough

CREATE TABLE tdiag (
diag_id integer DEFAULT nextval('diag_id_seq'::text),
create_time timestamp with time zone default now(),

   PRIMARY KEY (diag_id)
);

--  COMPOSITE INDEX
create index tdiag_id_create on tdiag(diag_id, create_time);

alter table tdiag alter column diag_id set statistics 1000;
alter table tdiag alter column create_time set statistics 1000;

and then just do the original query

symstream2=> explain analyze select * from tdiag where
symstream2-> (create_time >= '2011-06-07 02:00:00.00+0' and create_time 
< '2011-06-10 07:58:03.00+0') and  diag_level <= 1
symstream2-> order by diag_id LIMIT 100 OFFSET 800;

QUERY PLAN  
--
 Limit  (cost=6064.19..6822.21 rows=100 width=112) (actual 
time=1496.644..1497.094 rows=100 loops=1)
   ->  Index Scan using tdiag_id_create on tdiag  (cost=0.00..1320219.58 
rows=174166 width=112) (actual time=1409.285..1495.831 rows=900 loops=1)
 Index Cond: ((create_time >= '2011-06-07 12:00:00+10'::timestamp with 
time zone) AND (create_time < '2011-06-10 17:58:03+10'::timestamp with time 
zone))
 Filter: (diag_level <= 1)
 Total runtime: 1497.297 ms


If I had set the primary key to (diag_id, create_time) would simple queries on
diag_id still work well i.e.
select * from tdiag where diag_id = 1234;

-- 
Anthony Shipman | -module(erlang).
anthony.ship...@symstream.com   | ''(_)->0. %-)

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Infinite Cache

[Anthony Presley]

All:

Was curious if there was some sort of Open Source version of Infinite Cache,
and/or a memcache layer that can be "dropped" in front of PostgreSQL without
application changes (which seems to be the "key" piece of Infinite Cache),
or is this something that EnterpriseDB owns and you have to buy their
version of the software to use?

I'm fine with piecing together a few different OS projects, but would prefer
to not modify the app too much.

Thanks!


-- 
Anthony Presley

[PERFORM] RAID Controller (HP P400) beat by SW-RAID?

[Anthony Presley]

We've currently got PG 8.4.4 running on a whitebox hardware set up, with (2)
5410 Xeon's, and 16GB of RAM.  It's also got (4) 7200RPM SATA drives, using
the onboard IDE controller and ext3.

A few weeks back, we purchased two refurb'd HP DL360's G5's, and were hoping
to set them up with PG 9.0.2, running replicated.  These machines have (2)
5410 Xeon's, 36GB of RAM, (6) 10k SAS drives, and are using the HP SA P400i
with 512MB of BBWC.  PG is running on an ext4 (noatime) partition, and they
drives configured as RAID 1+0 (seems with this controller, I cannot do
JBOD).  I've spent a few hours going back and forth benchmarking the new
systems, and have set up the DWC, and the accelerator cache using hpacucli.
 I've tried accelerator caches of 25/75, 50/50, and 75/25.

To start with, I've set the "relevant" parameters in postgresql.conf the
same on the new config as the old:

  max_connections = 150
  shared_buffers = 6400MB (have tried as high as 20GB)
  work_mem = 20MB (have tried as high as 100MB)
  effective_io_concurrency = 6
  fsync = on
  synchronous_commit = off
  wal_buffers = 16MB
  checkpoint_segments = 30  (have tried 200 when I was loading the db)
  random_page_cost = 2.5
  effective_cache_size = 10240MB  (have tried as high as 16GB)

First thing I noticed is that it takes the same amount of time to load the
db (about 40 minutes) on the new hardware as the old hardware.  I was really
hoping with the faster, additional drives and a hardware RAID controller,
that this would be faster.  The database is only about 9GB with pg_dump
(about 28GB with indexes).

Using pgfouine I've identified about 10 "problematic" SELECT queries that
take anywhere from .1 seconds to 30 seconds on the old hardware.  Running
these same queries on the new hardware is giving me results in the .2 to 66
seconds.  IE, it's twice as slow.

I've tried increasing the shared_buffers, and some other parameters
(work_mem), but haven't yet seen the new hardware perform even at the same
speed as the old hardware.

I was really hoping that with hardware RAID that something would be faster
(loading times, queries, etc...).  What am I doing wrong?

About the only thing left that I know to try is to drop the RAID1+0 and go
to RAID0 in hardware, and do RAID1 in software.  Any other thoughts?

Thanks!


--
Anthony

Re: [PERFORM] RAID Controller (HP P400) beat by SW-RAID?

[Anthony Presley]

On Sun, Sep 11, 2011 at 6:17 PM, Tomas Vondra  wrote:

> Dne 12.9.2011 00:44, Anthony Presley napsal(a):
> > We've currently got PG 8.4.4 running on a whitebox hardware set up,
> > with (2) 5410 Xeon's, and 16GB of RAM.  It's also got (4) 7200RPM
> > SATA drives, using the onboard IDE controller and ext3.
> >
> > A few weeks back, we purchased two refurb'd HP DL360's G5's, and
> > were hoping to set them up with PG 9.0.2, running replicated.  These
> > machines have (2) 5410 Xeon's, 36GB of RAM, (6) 10k SAS drives, and
> > are using the HP SA P400i with 512MB of BBWC.  PG is running on an
> > ext4 (noatime) partition, and they drives configured as RAID 1+0
> > (seems with this controller, I cannot do JBOD).  I've spent a few
> > hours going back and forth benchmarking the new systems, and have set
> > up the DWC, and the accelerator cache using hpacucli.  I've tried
> > accelerator caches of 25/75, 50/50, and 75/25.
>
> Whas is an 'accelerator cache'? Is that the cache on the controller?
> Then give 100% to the write cache - the read cache does not need to be
> protected by the battery, the page cache at the OS level can do the same
> service.
>

It is the cache on the controller.  I've tried giving 100% to that cache.


> Provide more details about the ext3/ext4 - there are various data modes
> (writeback, ordered, journal), various other settings (barriers, stripe
> size, ...) that matter.
>

ext3 (on the old server) is using CentOS 5.2 defaults for mounting.

ext4 (on the new server) is using noatime,barrier=0



> According to the benchmark I've done a few days back, the performance
> difference between ext3 and ext4 is rather small, when comparing equally
> configured file systems (i.e. data=journal vs. data=journal) etc.
>
> With read-only workload (e.g. just SELECT statements), the config does
> not matter (e.g. journal is just as fast as writeback).
>
> See for example these comparisons
>
>   read-only workload: http://bit.ly/q04Tpg
>   read-write workload: http://bit.ly/qKgWgn
>
> The ext4 is usually a bit faster than equally configured ext3, but the
> difference should not be 100%.
>

Yes - it's very strange.


> > To start with, I've set the "relevant" parameters in postgresql.conf
> > the same on the new config as the old:
> >
> > max_connections = 150 shared_buffers = 6400MB (have tried as high as
> > 20GB) work_mem = 20MB (have tried as high as 100MB)
> > effective_io_concurrency = 6 fsync = on synchronous_commit = off
> > wal_buffers = 16MB checkpoint_segments = 30  (have tried 200 when I
> > was loading the db) random_page_cost = 2.5 effective_cache_size =
> > 10240MB  (have tried as high as 16GB)
> >
> > First thing I noticed is that it takes the same amount of time to
> > load the db (about 40 minutes) on the new hardware as the old
> > hardware.  I was really hoping with the faster, additional drives and
> > a hardware RAID controller, that this would be faster.  The database
> > is only about 9GB with pg_dump (about 28GB with indexes).
> >
> > Using pgfouine I've identified about 10 "problematic" SELECT queries
> > that take anywhere from .1 seconds to 30 seconds on the old
> > hardware. Running these same queries on the new hardware is giving me
> > results in the .2 to 66 seconds.  IE, it's twice as slow.
> >
> > I've tried increasing the shared_buffers, and some other parameters
> > (work_mem), but haven't yet seen the new hardware perform even at
> > the same speed as the old hardware.
>
> In that case some of the assumptions is wrong. For example the new RAID
> is slow for some reason. Bad stripe size, slow controller, ...
>
> Do the basic hw benchmarking, i.e. use bonnie++ to benchmark the disk,
> etc. Only if this provides expected results (i.e. the new hw performs
> better) it makes sense to mess with the database.
>
> Tomas
>



-- 
Anthony Presley

Re: [PERFORM] RAID Controller (HP P400) beat by SW-RAID?

[Anthony Presley]

Mark,

On Sun, Sep 11, 2011 at 10:10 PM, mark  wrote:

>
>
> >From: pgsql-performance-ow...@postgresql.org
> [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Anthony
> Presley
> >Sent: Sunday, September 11, 2011 4:45 PM
> >To: pgsql-performance@postgresql.org
> >Subject: [PERFORM] RAID Controller (HP P400) beat by SW-RAID?
>
> >We've currently got PG 8.4.4 running on a whitebox hardware set up, with
> (2) 5410 Xeon's, and 16GB of RAM.  It's also got (4) 7200RPM SATA drives,
> using the onboard IDE controller and ext3.
>
> >A few weeks back, we purchased two refurb'd HP DL360's G5's, and were
> hoping to set them up with PG 9.0.2, running replicated.  These machines
> have (2) 5410 Xeon's, 36GB of RAM, (6) 10k SAS drives, and are using the HP
> SA P400i with 512MB of BBWC.  PG is running on an ext4 (noatime) partition,
> and they drives configured as RAID 1+0 (seems with this controller, I
> cannot
> do JBOD).  I've spent a few hours going back and forth benchmarking the new
> systems, and have set up the DWC, and the accelerator cache using hpacucli.
>  I've tried accelerator caches of 25/75, 50/50, and 75/25.
> >
>
>
> I would start of by recommending a more current version of 9.0...like 9.0.4
> since you are building a new box. The rumor mill says 9.0.5 and 9.1.0 might
> be out soon (days?). but that is just rumor mill. Don't bank on it.
>

Looks like 9.1 was released today - I may upgrade to that for our testing.
 I was just using whatever is in the repo.


> What kernel are you on ?
>

2.6.18-238.19.1.el5


> Long time HP user here, for better and worse... so here are a few other
> little things I recommend.
>

Thanks!


> Check the bios power management. Make sure it is set where you want it.
> (IIRC the G5s have this, I know G6s and G7s do). This can help with nasty
> latency problems if the box has been idle for a while then needs to start
> doing work.
>

I've checked those, they look ok.


> The p400i is not a great card, compared to more modern one, but you should
> be able to beat the old setup with what you have. Faster clocked cpu's more
> spindles, faster RPM spindles.
>

I've upgraded the CPU's to be X5470 today, to see if that helps with the
speed of


> Assuming the battery is working, with XFS or ext4 you can use nobarrier
> mount option and you should see some improvement.
>

I've been using:
  noatime,data=writeback,defaults

I will try:
  noatime,data=writeback,barrier=0,defaults


> Make sure the raid card's firmware is current. I can't stress this enough.
> HP fixed a nasty bug with Raid 1+0 a few months ago where you could eat
> your
> data... They also seem to be fixing a lot of other bugs along the way as
> well. So do yourself a big favor and make sure that firmware is current. It
> might just head off headache down the road.
>

I downloaded the latest firmware DVD on Thursday and ran that - everything
is up to date.


> Also make sure you have a 8.10.? (IIRC the version number right) or better
> version of hpacucli... there have been some fixes to that utility as well.
> IIRC most of the fixes in this have been around recognizing newere cards
> (812s and 410s) but some interface bugs have been fixed as well.   You may
> need new packages for HP health. (I don't recall the official name, but new
> versions if hpacucli might not play well with old versions of hp health.
>

I got that as well - thanks!


> Its HP so they have a new version about every month for firmware and their
> cli utility... that’s HP for us.
>
> Anyways that is my fast input.
>
> Best of luck,


Thanks!


-- 
Anthony Presley

Re: [PERFORM] RAID Controller (HP P400) beat by SW-RAID?

[Anthony Presley]

So, today, I did the following:

  - Swapped out the 5410's (2.3Ghz) for 5470's (3.33Ghz)
  - Set the ext4 mount options to be noatime,barrier=0,data=writeback
  - Installed PG 9.1 from the yum repo

Item one:
  With the accelerator cache set to 0/100 (all 512MB for writing), loading
the db / creating the indexes was about 8 minutes faster.  Was hoping for
more, but didn't get it.  If I split the CREATE INDEXes into separate psql
instances, will that be done in parallel?

Item two:
  I'm still getting VERY strange results in my SELECT queries.

For example, on the new server:
  http://explain.depesz.com/s/qji - This takes 307ms, all the time.  Doesn't
matter if it's "cached", or fresh from a reboot.

Same query on the live / old server:
  http://explain.depesz.com/s/8Pd - This can take 2-3s the first time, but
then takes 42ms once it's cached.

Both of these servers have the same indexes, and almost identical data.
 However, the old server is doing some different planning than the new
server.

What did I switch (or should I unswitch)?


--
Anthony

On Sun, Sep 11, 2011 at 9:12 PM, Alan Hodgson  wrote:

> On September 11, 2011 03:44:34 PM Anthony Presley wrote:
> > First thing I noticed is that it takes the same amount of time to load
> the
> > db (about 40 minutes) on the new hardware as the old hardware.  I was
> > really hoping with the faster, additional drives and a hardware RAID
> > controller, that this would be faster.  The database is only about 9GB
> > with pg_dump (about 28GB with indexes).
>
> Loading the DB is going to be CPU-bound (on a single) core, unless your
> disks
> really suck, which they don't. Most of the time will be spent building
> indexes.
>
> I don't know offhand why the queries are slower, though, unless you're not
> getting as much cached before testing as on the older box.
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Databases optimization

[Anthony Presley]

You may want to try pgreplay ... we've tried it for a similar scenario, and
so far, it's pretty promising.

I do wish it was able to be loaded from a pgfouine formatted log file, or
from another db ... but that's OK.


-- 
Anthony Presley

On Mon, Sep 12, 2011 at 6:28 PM, Hany ABOU-GHOURY  wrote:

>
> I have a production PostGres v8.2 database on luinx and a test PostGres
> V9.0 database on a test linux server
> I am going to do migration but do not want to do that before making sure
> the performance of the new test Postgres 9.0 database performance is as good
> as the current production Postgres 8.2
>
> My question is:
>
> Is there a script that I can run on Postgres V8.2 and PostGres 9.0 that
> allows me test performance and make comparisons
>
>
>
> Thanks guys
>
>
>
> On Mon, Sep 12, 2011 at 2:46 PM, J Sisson  wrote:
>
>> On Sun, Sep 11, 2011 at 5:22 PM, Maciek Sakrejda 
>> wrote:
>>
>>> performance guidelines, I recommend Greg Smith's "PostgreSQL 9.0 High
>>> Performance" [1] (disclaimer: I used to work with Greg and got a free
>>> copy)
>>>
>>> I'll second that.  "PostgreSQL 9.0 High Performance" is an excellent
>> resource
>> (I recommend it even for non-PostgreSQL admins because it goes so in-depth
>>
>> on Linux tuning) so whether you get it for free or not, it's worth the
>> time it takes
>> to read and absorb the info.
>>
>> I've never run PostgreSQL virtualized, but I can say that if it's anything
>> like
>> running SQL Server virtualized, it's not a terribly good idea.
>>
>

[PERFORM] PG 9.x prefers slower Hash Joins?

[Anthony Presley]

In relation to my previous thread (about SW RAID vs. HW RAID on a P400), I
was able to narrow down the filesystem speed and in general, our new system
(running PG 9.1) is about 3x - 5x faster on the IO.

In looking at the query plans in more depth, it appears that PG 9.0 and 9.1
are both preferring to do hash joins, which seem to have a "linear" time and
are slower than PG 8.4 doing an index scan.

For example, on PG 9.x:
  http://explain.depesz.com/s/qji - This takes 307ms, all the time.  Doesn't
matter if it's "cached", or fresh from a reboot.

Same query on PG 8.4:
  http://explain.depesz.com/s/8Pd - This can take 2-3s the first time, but
then takes 42ms once it's cached.

Both of these servers have the same indexes, similar postgresql.conf, and
almost identical data.  However, the old server is doing some different
planning than the new server.  I've run analyze on both of these databases.
 Some relevant PG parameters:

  max_connections = 150
  shared_buffers = 6400MB (have tried as high as 20GB)
  work_mem = 20MB (have tried as high as 100MB)
  effective_io_concurrency = 6
  fsync = on
  synchronous_commit = off
  wal_buffers = 16MB
  checkpoint_segments = 30  (have tried 200 when I was loading the db)
  random_page_cost = 2.5
  effective_cache_size = 10240MB  (have tried as high as 16GB)

If I disable the hashjoin, I get massive improvements on PG 9.x ... as fast
(or faster) than our PG 8.4 instance.


-- 
Anthony Presley

Re: [PERFORM] RAID Controller (HP P400) beat by SW-RAID?

[Anthony Presley]

On Tue, Sep 13, 2011 at 1:22 AM, Arjen van der Meijden <
acmmail...@tweakers.net> wrote:

>
> On 12-9-2011 0:44 Anthony Presley wrote:
>
>> A few weeks back, we purchased two refurb'd HP DL360's G5's, and were
>> hoping to set them up with PG 9.0.2, running replicated.  These machines
>> have (2) 5410 Xeon's, 36GB of RAM, (6) 10k SAS drives, and are using the
>> HP SA P400i with 512MB of BBWC.  PG is running on an ext4 (noatime)
>> partition, and they drives configured as RAID 1+0 (seems with this
>> controller, I cannot do JBOD).
>>
>
> If you really want a JBOD-setup, you can try a RAID0 for each available
> disk, i.e. in your case 6 separate RAID0's. That's how we configured our
> Dell H700 - which doesn't offer JBOD as well - for ZFS.
>

That's a pretty good idea ... I'll try that on our second server today.  In
the meantime, after tweaking it a bit, we were able to get (with iozone):



Old New  Initial write
75.85 220.68  Rewrite
63.95 253.07  Read
45.04 171.35  Re-read
45 2405.23  Random read
27.56 1733.46  Random write
50.7 239.47

Not as fas as I'd like, but faster than the old disks, for sure.

--
Anthony

[PERFORM] overzealous sorting?

[anthony . shipman]

In Mammoth Replicator (PG 8.3) I have a table described as 

   Table "public.tevent_cdr"
 Column |   Type   | Modifiers
+--+
 event_id   | integer  | not null default 
nextval(('event_id_seq'::text)::regclass)
 timestamp  | timestamp with time zone | not null
 classification | character varying| not null
 area   | character varying| not null
 kind   | character varying|
 device_id  | integer  |
 device_name| character varying|
 fleet_id   | integer  |
 fleet_name | character varying|
 customer_id| integer  |
 customer_name  | character varying|
 event  | text |
Indexes:
"tevent_cdr_event_id" UNIQUE, btree (event_id)
"tevent_cdr_timestamp" btree ("timestamp")
Check constraints:
"tevent_cdr_classification_check" CHECK (classification::text 
= 'cdr'::text)
Inherits: tevent


This simple query puzzles me. Why does it need to sort the records? Don't they 
come from the index in order?

 "explain analyze select * from tevent_cdr where timestamp >= 
'2011-09-09 12:00:00.00+0' and timestamp < '2011-09-09 
13:00:00.00+0' and classification = 'cdr' order by timestamp;"

QUERY PLAN

  Sort  (cost=9270.93..9277.12 rows=2477 width=588) (actual 
time=9.219..11.489 rows=2480 loops=1)
Sort Key: "timestamp"
Sort Method:  quicksort  Memory: 2564kB
->  Bitmap Heap Scan on tevent_cdr  (cost=57.93..9131.30 rows=2477 
width=588) (actual time=0.440..3.923 rows=2480 loops=1)
  Recheck Cond: (("timestamp" >= '2011-09-09 
22:00:00+10'::timestamp with time zone) AND ("timestamp" < '2011-09-09 
23:00:00+10'::timestamp with time zone))
  Filter: ((classification)::text = 'cdr'::text)
  ->  Bitmap Index Scan on tevent_cdr_timestamp  
(cost=0.00..57.31 rows=2477 width=0) (actual time=0.404..0.404 rows=2480 
loops=1)
Index Cond: (("timestamp" >= '2011-09-09 
22:00:00+10'::timestamp with time zone) AND ("timestamp" < '2011-09-09 
23:00:00+10'::timestamp with time zone))
  Total runtime: 13.847 ms
(9 rows)
-- 
Anthony Shipman | flailover systems: When one goes down it 
anthony.ship...@symstream.com   | flails about until the other goes down too.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] overzealous sorting?

[anthony . shipman]

On Monday 26 September 2011 19:39, Marc Cousin wrote:
> Because Index Scans are sorted, not Bitmap Index Scans, which builds a
> list of pages to visit, to be then visited by the Bitmap Heap Scan step.
>
> Marc.

Where does this bitmap index scan come from? It seems to negate the advantages 
of b-tree indexes described in the section "Indexes and ORDER BY" of the 
manual. If I do "set enable_bitmapscan = off;" the query runs a bit faster 
although with a larger time range it reverts to a sequential scan.

-- 
Anthony Shipman | Consider the set of blacklists that
anthony.ship...@symstream.com   | do not blacklist themselves...

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] overzealous sorting?

[anthony . shipman]

On Tuesday 27 September 2011 18:54, Marc Cousin wrote:
> The thing is, the optimizer doesn't know if your data will be in cache
> when you will run your query… if you are sure most of your data is in
> the cache most of the time, you could try to tune random_page_cost
> (lower it) to reflect that data is cached. But if the win is small on
> this query, it may not be worth it.

What I really want is to just read a sequence of records in timestamp order 
between two timestamps. The number of records to be read may be in the 
millions totalling more than 1GB of data so I'm trying to read them a slice 
at a time but I can't get PG to do just this.

If I use offset and limit to grab a slice of the records from a large 
timestamp range then PG will grab all of the records in the range, sort them 
on disk and return just the slice I want. This is absurdly slow. 

The query that I've shown is one of a sequence of queries with the timestamp 
range progressing in steps of 1 hour through the timestamp range. All I want 
PG to do is find the range in the index, find the matching records in the 
table and return them. All of the planner's cleverness just seems to get in 
the way.

-- 
Anthony Shipman | Consider the set of blacklists that
anthony.ship...@symstream.com   | do not blacklist themselves...

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] overzealous sorting?

[anthony . shipman]

On Tuesday 27 September 2011 19:22, Mark Kirkwood wrote:
> > The query that I've shown is one of a sequence of queries with the
> > timestamp range progressing in steps of 1 hour through the timestamp
> > range. All I want PG to do is find the range in the index, find the
> > matching records in the table and return them. All of the planner's
> > cleverness just seems to get in the way.
>
> It is not immediately clear that the planner is making the wrong choices
> here. Index scans are not always the best choice, it depends heavily on
> the correlation of the column concerned to the physical order of the
> table's heap file. I suspect the reason for the planner choosing the
> bitmap scan is that said correlation is low (consult pg_stats to see).
> Now if you think that the table's heap data is cached anyway, then this
> is not such an issue - but you have to tell the planner that by reducing
> random_page_cost (as advised previously). Give it a try and report back!
>
> regards
>
> Mark

I don't expect that any of it is cached. It is supposed to be a once-a-day 
linear scan of a slice of the table. The correlation on the timestamp is 
reported as 0.0348395. I can't use cluster since it would lock the table for 
too long.

I would try a cursor but my framework for this case doesn't support cursors. 
In a later version of the framework I've tried cursors and haven't found them 
to be faster than reading in slices, in the tests I've done.

Anyway at the moment it is fast enough. 

Thanks
-- 
Anthony Shipman | It's caches all the way 
anthony.ship...@symstream.com   | down.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] 8.4.4, 9.0, and 9.1 Planner Differences

[Anthony Presley]

Hi there!

We have a dev machine running 9.0.1 (an i3 laptop, with a regular hard disk,
with 4GB of RAM, and a mostly untuned postgresql.conf file).  The changed
lines are:
  shared_buffers = 512MB
  temp_buffers = 48MB
  work_mem = 32MB
  maintenance_work_mem = 348MB
  checkpoint_segments = 10
  effective_cache_size = 512MB

The same database is loaded onto a production server running 9.1.1 (dual QC
processors, RAID-10 SAS drives, 36GB of RAM), which replicates to a backup
server.  This has a lot of changed properties:
  shared_buffers = 8500MB
  work_mem = 35MB
  maintenance_work_mem = 512MB
  wal_level = hot_standby
  checkpoint_segments = 50
  max_wal_senders = 3
  wal_keep_segments = 144
  random_page_cost = 1.0
  effective_cache_size = 16384MB
  effective_io_concurrency = 6

The same DB is loaded on both the production and the dev environment, and in
all cases (about 5000 distinct different queries), the production
environment is about 500x faster, except for one type of query (both
databases were loaded from the same pg_dump on an 8.4.4 database):

On the dev box, we have:  http://explain.depesz.com/s/rwU   - about 131
seconds
On the production box, we have:  http://explain.depesz.com/s/3dt  -
about .25 seconds

For the life of me, I don't understand why it would be slower.  What can we
do to speed up this one query?

By the way, on 8.4.4, the query took about 84 seconds.  I cannot understand
why the 9.0 is so blazing fast, but 8.4.4 and 9.1.1 are slower.  We've
checked the query results (they are identical) to make sure we're not
missing any data.


-- 
Anthony

Re: [PERFORM] 8.4.4, 9.0, and 9.1 Planner Differences

[Anthony Presley]

On Sat, Oct 22, 2011 at 10:58 AM, Tom Lane  wrote:

> Anthony Presley  writes:
> > We have a dev machine running 9.0.1 (an i3 laptop, with a regular hard
> disk,
> > with 4GB of RAM, and a mostly untuned postgresql.conf file).  The changed
> > lines are:
> >   shared_buffers = 512MB
> >   temp_buffers = 48MB
> >   work_mem = 32MB
> >   maintenance_work_mem = 348MB
> >   checkpoint_segments = 10
> >   effective_cache_size = 512MB
>
> > The same database is loaded onto a production server running 9.1.1 (dual
> QC
> > processors, RAID-10 SAS drives, 36GB of RAM), which replicates to a
> backup
> > server.  This has a lot of changed properties:
> >   shared_buffers = 8500MB
> >   work_mem = 35MB
> >   maintenance_work_mem = 512MB
> >   wal_level = hot_standby
> >   checkpoint_segments = 50
> >   max_wal_senders = 3
> >   wal_keep_segments = 144
> >   random_page_cost = 1.0
> >   effective_cache_size = 16384MB
> >   effective_io_concurrency = 6
>
> That random_page_cost setting is going to have a huge effect on the
> planner's choices, and the larger effective_cache_size setting will
> likely affect plans too.  I don't find it surprising in the least
> that you're getting different plan choices ... and even less so when
> your "dev" and "production" DBs aren't even the same major version.
> You might want to think about making your dev environment more like
> your production.
>

Tom - thanks for your input.

Upgrading to 9.1.1 on the dev box is certainly next on our list ... I like
to make sure that the dev team uses a MUCH slower box than the production
server, making sure that if the developers are making things fast for the
machines, it's really fast on the production box.  For all of our queries
except this one, this strategy is "working".

> The same DB is loaded on both the production and the dev environment, and
> in
> > all cases (about 5000 distinct different queries), the production
> > environment is about 500x faster, except for one type of query (both
> > databases were loaded from the same pg_dump on an 8.4.4 database):
>
> > On the dev box, we have:  http://explain.depesz.com/s/rwU   - about
> 131
> > seconds
> > On the production box, we have:  http://explain.depesz.com/s/3dt  -
> > about .25 seconds
>
> Did you mislabel these?  Because if you didn't, the numbers are right
> in line with what you say above.  But anyway, the problem with the
> slower query appears to be poor rowcount estimates, leading the planner
> to use a nestloop join when it shouldn't.  You haven't provided nearly
> enough context to let anyone guess why the estimates are off, other
> than boilerplate suggestions like making sure the tables have been
> ANALYZEd recently, and maybe increasing the statistics targets.
>

I *did* mis-label them.  The 131 seconds is actually the production box.
 IE:
  production is ... http://explain.depesz.com/s/rwU

The .25 seconds is the development box.  IE:
  development is ... http://explain.depesz.com/s/3dt

I wasn't surprised that the plans are different.  I was surprised that the
development box *spanked* the production system.

Here's the actual query:
select
preference0_.*
from
preference preference0_, location location1_, employee employee2_
where
preference0_.employee_id=employee2_.id
and preference0_.location_id=location1_.id
and location1_.corporation_id=41197
and employee2_.deleted='N'
and preference0_.deleted='N'
and
(preference0_.id not in (
select preference3_.id from preference preference3_, location
location4_, employee employee5_
where preference3_.employee_id=employee5_.id and
preference3_.location_id=location4_.id
and location4_.corporation_id=41197 and employee5_.deleted='N' and
preference3_.deleted='N'
and (preference3_.startDate>'2011-11-03 00:00:00' or
preference3_.endDate<'2011-11-02 00:00:00'))
) and
(preference0_.employee_id in
(select employee6_.id from employee employee6_ inner join app_user
user7_ on employee6_.user_id=user7_.id
inner join user_location userlocati8_ on user7_.id=userlocati8_.user_id,
location location9_
where userlocati8_.location_id=location9_.id and
userlocati8_.location_id=6800 and userlocati8_.deleted='N'
and location9_.deleted='N' and employee6_.deleted='N')
) order by preference0_.date_created;

I have tried setting the statistics on employee.user_id to be 100 and 1000,
and the rest are the default (100).

I've run both an "ANALYZE" and a "VACUUM ANALYZE" on the production system -
both "generally", and on each of the above tables (employee, app_user,
location, preference).

Here's an updated explain of the most recent attempt.  About 5 minutes after
I analyzed them:
  http://explain.depesz.com/s/G32

What else would I need to provide?


-- 
Anthony Presley

[PERFORM] Improve DB Size / Performance with Table Refactoring

[Anthony Presley]

Hi there!

We currently have a database table that's laid out something like this:
  id int
  date1 date
  belongs_to date
  type varchar(1)
  type_fk int
  start_time time
  end_time time
  location_fk int
  department_fk int
  value decimal

Where each row represents some data throughout the day (96 data points for
each 15-minute period) - and each "type_fk", department, and location can
have up to say, 3 rows for a given start / end time and date (based on the
"type").

This table has rapidly grown - we're adding about 1 - 2 million rows per
month - and almost all of our queries actually sum up the values based on
the belongs_to date and the location_id, however, for other statistics we
need to keep the values separate.  The db is now more than 60% of our
database, and we want to come up with a better way to store it.  (To speed
up other queries, we actually roll this table up into a daily table).

We're considering changing the structure of this table into one of the
following structures:

Option [A]:
  id int
  date1 date
  belongs_to date
  type_fk int
  location_fk int
  department_fk int
  value_type1_0 decimal
  
  value_type1_96 decimal
  value_type2_0 decimal
  
  value_type2_96 decimal
  value_type3_0 decimal
  
  value_type3_96 decimal

or, as an alternative:

Option [B]:
  id int
  date1 date
  belongs_to date
  type varchar(1)
  type_fk int
  location_fk int
  department_fk int
  value_type_0 decimal
  
  value_type_96 decimal

We're having a hard time choosing between the two options.  We'll
definitely partition either one by the date or belongs_to column to speed
up the queries.

Option A would mean that any given date would only have a single row, with
all three "types".  However, this table would have 6+96*3 columns, and in
many cases at least 96 of those columns would be empty.  More often than
not, however, at least half of the columns would be empty (most location's
aren't open all day).

Option B would only create rows if the type had data in it, but the other 6
columns would be redundant.  Again, many of the columns might be empty.

... From a space / size perspective, which option is a better choice?

How does PostgreSQL handle storing empty columns?

Thanks!

--
Anthony

Re: [PERFORM] Rollback table data.

[Anthony Sotolongo]

HI, If you dont vaccum the table, You can read data modified with 
pg_dirtyread extension, but be carefull ;-)


https://github.com/omniti-labs/pgtreats/tree/master/contrib/pg_dirtyread

Regards
On 07/06/17 07:33, Dinesh Chandra 12108 wrote:


Dear Expert,

Is there any way to rollback table data in PostgreSQL?

*Regards,*

*Dinesh Chandra*

*|Database administrator (Oracle/PostgreSQL)| Cyient Ltd. Noida.*

*--*

Mobile: +91-9953975849 | Ext 1078 |dinesh.chan...@cyient.com 



Plot No. 7, NSEZ, Phase-II ,Noida-Dadri Road, Noida - 201 305,India.




DISCLAIMER:

This email may contain confidential information and is intended only 
for the use of the specific individual(s) to which it is addressed. If 
you are not the intended recipient of this email, you are hereby 
notified that any unauthorized use, dissemination or copying of this 
email or the information contained in it or attached to it is strictly 
prohibited. If you received this message in error, please immediately 
notify the sender at Infotech or mail.ad...@cyient.com and delete the 
original message.

Re: [PERFORM] How to access data of SQL server database from PostgreSQL

[Anthony Sotolongo]

Hi Daulat


This is not the list for that (check https://www.postgresql.org/list/), 
but if you want to access sql server look at tds_fdw( 
https://github.com/tds-fdw/tds_fdw), I do not know if it installs on 
windows, I've only used it on linux, and function very well.


Regards
Anthony

On 01/08/17 00:25, Daulat Ram wrote:


Hi team,

I need to connect to MS-SQL server 2008/2012 from PostgreSQL 9.5 in 
Windows7 environment to fetch the tables of SQL server.


Please help on this.

Regards,

Daulat




DISCLAIMER:

This email message is for the sole use of the intended recipient(s) 
and may contain confidential and privileged information. Any 
unauthorized review, use, disclosure or distribution is prohibited. If 
you are not the intended recipient, please contact the sender by reply 
email and destroy all copies of the original message. Check all 
attachments for viruses before opening them. All views or opinions 
presented in this e-mail are those of the author and may not reflect 
the opinion of Cyient or those of our affiliates.

Re: [PERFORM] [ADMIN] 2 server with same configuration but huge difference in performance

[Anthony Sotolongo]

Hi Sumeet Shukla

While script is running check the pg_stat_activity, this view can be util


Regards

Anthony


On 01/08/17 10:16, Keith wrote:


On Tue, Aug 1, 2017 at 9:41 AM, Sumeet Shukla 
mailto:sumeet.k.shu...@gmail.com>> wrote:


Hi,

I have 2 PG servers with same h/w and configuration and they are
not in replication.

On server A it takes 20 minutes to execute the script.
On server B it takes more than 20 hours. (Seems to be stuck with
create index and and create foreign key steps)

Any guidance to troubleshoot this would be highly appreciated.

Thanks & Regards,
Sumeet Shukla


Check for long running queries on the server that is taking longer. If 
it's things like CREATE INDEX or ALTER TABLE statements that are being 
blocked, a transaction running on the table involved will cause those 
commands to be held until those transactions complete.


If it's normal read/write queries to that are taking longer, ensure 
the database statistics are up to date by running an analyze.


Keith

Re: [PERFORM] Index-Advisor Tools

[Anthony Sotolongo]

Hi Neto,  maybe HypoPG
Can help you:

https://github.com/dalibo/hypopg

El 31 oct. 2017 2:13 PM, "Neto pr"  escribió:

>
> Hello All I'm researching on Index-Advisor Tools to be applied in SQL
> queries. At first I found this: - EnterpriseDB -
> https://www.enterprisedb.com/docs/en/9.5/asguide/EDB_
> Postgres_Advanced_Server_Guide.1.56.html Someone would know of other
> tools for this purpose. I'd appreciate it if you can help me.
>
> Best Regards
> Neto
>