date:20080807

Re: [PERFORM] Plz Heeeelp! performance settings

2008-08-07 Thread dforum


Tx for your reply.

You mean that RAID use fsync method for keeping data's copy.

So you invite me to desactivate fsync to increase the performance ?

Desactivating fsync. my second disk will not be uptodate, so if the 
machine crash, I wont be able to get the server working quickly??? But 
if I use a second machine to replicate the database, I escape this 
problem isn't it ?


If I understand right, could you tell me how to do desactivate fsync 
please ?


Best regards

David

Merlin Moncure a écrit :

On Wed, Aug 6, 2008 at 6:12 PM, dforum <[EMAIL PROTECTED]> wrote:
  

Hello to all,


I have a Linux 2.6.24.2--std-ipv4-64 #3 SMP Tue Feb 12 12:27:47 CET 2008
x86_64 Intel(R) Xeon(R) CPU X5355 @ 2.66GHz GenuineIntel GNU/Linux
with 8Gb of memory. Using sata II disk in RAID 1 (I known that is bad, but




this is likely your problem...with fsync on (as you have it), you will
be lucky to get a couple of hundred transactions/sec out of the
database.  you are probably just exceeding your operational
capabilities of the hardware so you probably need to upgrade or turn
off fsync (which means data corruption in event of hard crash).

merlin

  



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

[PERFORM] Query Plan choice with timestamps

2008-08-07 Thread Giorgio Valoti

Hi, I have a timestamptz field that I want to use with a query, but I  
don’t need the full timestamp resolution, so I’ve created a  
day_trunc(timestamptz) immutable function which I’ll use with the  
query and with a new index:


logs=> create index test_idx on blackbox (day_trunc(ts));

However, the query plan doesn’t use the index:

logs=>explain select count(*) from blackbox group by day_trunc(ts)  
order by day_trunc(ts);

QUERY PLAN
--
 GroupAggregate  (cost=98431.58..119773.92 rows=74226 width=8)
   ->  Sort  (cost=98431.58..99050.92 rows=247736 width=8)
 Sort Key: (day_trunc(ts))
 ->  Seq Scan on blackbox  (cost=0.00..72848.36 rows=247736  
width=8)

(4 rows)

while with this index:

logs=>create index test_2_idx on blackbox (ts);

the query plan is the expected one:

logs=>explain select count(*) from blackbox group by ts order by ts;
QUERY PLAN
--
 GroupAggregate  (cost=0.00..19109.66 rows=74226 width=8)
   ->  Index Scan using test_2_idx on blackbox  (cost=0.00..16943.16  
rows=247736 width=8)


But I fail to see why. Any hints?

Thank you in advance
--
Giorgio Valoti
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Unexpectedly Long DELETE Wait

2008-08-07 Thread Richard Huxton


Volkan YAZICI wrote:

Hi,

Below command has been running since ~700 minutes in one of our
PostgreSQL servers.

  DELETE FROM mugpsreglog
WHERE NOT EXISTS (SELECT 1
FROM mueventlog
   WHERE mueventlog.eventlogid = 
mugpsreglog.eventlogid);

   Seq Scan on mugpsreglog  (cost=0.00..57184031821394.73 rows=6590986 width=6)
 Filter: (NOT (subplan))
SubPlan
 ->  Seq Scan on mueventlog  (cost=0.00..4338048.00 rows=1 width=0)
 Filter: (eventlogid = $0)


Ouch - look at the estimated cost on that!


And there isn't any constraints (FK/PK), triggers, indexes, etc. on any
of the tables. (We're in the phase of a migration, many DELETE commands
similar to above gets executed to relax constraints will be introduced.)


Well there you go. Add an index on eventlogid for mugpsreglog.

Alternatively, if you increased your work_mem that might help. Try SET 
work_mem='64MB' (or even higher) before running the explain and see if 
it tries a materialize. For situations like this where you're doing big 
one-off queries you can afford to increase resource limits.


--
  Richard Huxton
  Archonet Ltd

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

Re: [PERFORM] Plz Heeeelp! performance settings

2008-08-07 Thread Richard Huxton

dforum wrote:

Tx for your reply.

You mean that RAID use fsync method for keeping data's copy.

No, Merlin means PostgreSQL will issue a sync to force WAL to actual disk.

So you invite me to desactivate fsync to increase the performance ?

He means you might have to if you can't afford new hardware. Is disk 
activity the problem? Have you looked at the output of "vmstat" to check?

Desactivating fsync. my second disk will not be uptodate, 

No - the RAID stuff is happening in the operating-system.

> so if the
machine crash, I wont be able to get the server working quickly??? 

Not "quickly", perhaps not "at all".

> But
if I use a second machine to replicate the database, I escape this 
problem isn't it ?

You reduce the chance of a single failure causing disaster.

If I understand right, could you tell me how to do desactivate fsync 
please ?

There's an "fsync = on" setting in your postgresql.conf, but don't 
change it yet.

> I have a database of 38Go and take 6Go per week.

What do you mean by "take 6Go per week"? You update/delete that much 
data? It's growing by that amount each week?

> I have a lot of update and insert, especially in 8 tables. 2 tables are
> using for temporary storage, so I right something like 15000 request per
> 2 minutes and empty it into 10 min.

I'm not sure what "15000 request per 2 minutes and empty it into 10 min" 
means.

Do you have 7500 requests per minute?
Are these updates?
To the "temporary storage"?
What is this "temporary storage" - an ordinary table?

> I'm making some update or select on tables including more than 20
> millions of entrance.

Again, I'm not sure what this means.

Oh - *important* - which version of PostgreSQL are you running?
Is an upgrade practical?

Looking at your postgresql.conf settings:

  max_connections = 624

That's an odd number.
Do you usually have that many connections?
What are they doing? They can't all be active, the machine you've got 
wouldn't cope.

  shared_buffers = 25
  work_mem = 9000
  temp_buffers = 500

These three are important. The shared_buffers are workspace shared 
between all backends, and you've allocated about 2GB. You've also set 
work_mem=9MB, which is how much each backend can use for a single sort. 
That means it can use double or triple that in a complex query. If 
you're using temporary tables, then you'll want to make sure the 
temp_buffers setting is correct.

I can't say whether these figures are good or bad without knowing how 
the database is being used.

  effective_cache_size = 625000

That's around 5GB - is that roughly the amount of memory used for 
caching (what does free -m say for buffers/cache)?

  max_prepared_transactions = 200

Do you use a lot of prepared transactions in two-phase commit?
I'm guessing that you don't.

> I'm sure that it could be more optimised. I don't know any thing on
> WAL,
> autovacuum, fsm, bgwriter, kernel process, geqo or planner cost
> settings.

If you run a "vacuum verbose" it will recommend fsm settings at the end 
of its output. I think you probably need to make your autovacuum more 
aggressive, but that's something you'll be able to tell by monitoring 
your database.

It's quite likely that Merlin's right, and you need better hardware to 
cope with the number of updates you're making - that's something where 
you need fast disks. However, he's just guessing because you've not told 
us enough to tell where the problem really lies.

--
  Richard Huxton
  Archonet Ltd

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

Re: [PERFORM] Query Plan choice with timestamps

2008-08-07 Thread Richard Huxton


Giorgio Valoti wrote:
Hi, I have a timestamptz field that I want to use with a query, but I 
don’t need the full timestamp resolution, so I’ve created a 
day_trunc(timestamptz) immutable function which I’ll use with the query 
and with a new index:


logs=> create index test_idx on blackbox (day_trunc(ts));

However, the query plan doesn’t use the index:


Does it use it ever? e.g. with
  SELECT * FROM blackbox WHERE day_trunk(ts) = '...'

--
  Richard Huxton
  Archonet Ltd

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

Re: [PERFORM] Plz Heeeelp! performance settings

2008-08-07 Thread dforums




Richard Huxton a écrit :
> dforum wrote:
>> Tx for your reply.
>>
>> You mean that RAID use fsync method for keeping data's copy.
>
> No, Merlin means PostgreSQL will issue a sync to force WAL to actual 
disk.

>
>> So you invite me to desactivate fsync to increase the performance ?
>
> He means you might have to if you can't afford new hardware. Is disk
> activity the problem? Have you looked at the output of "vmstat" to check?
vmstat is giving :
procs ---memory-- ---swap-- -io --system-- 
cpu
 r  b   swpd   free   buff  cache   si   sobibo   incs us 
sy id wa
 0  2   1540  47388  41684 757897600   131   2590 1  9 
 3 82  7



>
>> Desactivating fsync. my second disk will not be uptodate,
>
> No - the RAID stuff is happening in the operating-system.
>
>  > so if the
>> machine crash, I wont be able to get the server working quickly???
>
> Not "quickly", perhaps not "at all".
Oups
>
>  > But
>> if I use a second machine to replicate the database, I escape this
>> problem isn't it ?
> You reduce the chance of a single failure causing disaster.
Not clear this reply. It's scare me 
>
>> If I understand right, could you tell me how to do desactivate fsync
>> please ?
>
> There's an "fsync = on" setting in your postgresql.conf, but don't
> change it yet.
OK
>
>  > I have a database of 38Go and take 6Go per week.
>
> What do you mean by "take 6Go per week"? You update/delete that much
> data? It's growing by that amount each week?
YES
>
>  > I have a lot of update and insert, especially in 8 tables. 2 
tables are
>  > using for temporary storage, so I right something like 15000 
request per

>  > 2 minutes and empty it into 10 min.
>
> I'm not sure what "15000 request per 2 minutes and empty it into 10 min"
> means.
I insert 15000 datas every 2 min and delete 15000 every  10 min in those 
tables

>
> Do you have 7500 requests per minute?
should be that, But in fact I'm not treating the datas in real time, and 
I buffer the datas and push the data into the database every 2 min

> Are these updates?
during the delete the data are aggregated in other tables which make updates
> To the "temporary storage"?

> What is this "temporary storage" - an ordinary table?
Yes, I thied to use temporary tables but I never been able to connect 
this tables over 2 different session/connection, seems that is a 
functionnality of postgresql, or a misunderstanding from me.

>
>  > I'm making some update or select on tables including more than 20
>  > millions of entrance.
>
> Again, I'm not sure what this means.

To aggregate the data, I have to check the presence of others 
information that are stores in 2 tables which includes 24 millions of 
entrance.

>
>
> Oh - *important* - which version of PostgreSQL are you running?
8.1.11
> Is an upgrade practical?
We are working of trying to upgrade to 8.3.3, but we are not yet ready 
for such migration

>
>
> Looking at your postgresql.conf settings:
>
>   max_connections = 624
>
> That's an odd number.
Now we could decrease this number, it's not so much usefull for now. we 
could decrease is to 350.

> Do you usually have that many connections?
> What are they doing? They can't all be active, the machine you've got
> wouldn't cope.
>
>   shared_buffers = 25
>   work_mem = 9000
>   temp_buffers = 500
>
> These three are important. The shared_buffers are workspace shared
> between all backends, and you've allocated about 2GB. You've also set
> work_mem=9MB, which is how much each backend can use for a single sort.
> That means it can use double or triple that in a complex query

(i now about it).

If
> you're using temporary tables, then you'll want to make sure the
> temp_buffers setting is correct.
I need help for that, I don't know
>
> I can't say whether these figures are good or bad without knowing how
> the database is being used.
>
>   effective_cache_size = 625000
>
> That's around 5GB - is that roughly the amount of memory used for
> caching (what does free -m say for buffers/cache)?
total   used   free sharedbuffers cached
Mem:  7984   7828156  0 38   7349
-/+ buffers/cache:440   7544
Swap:  509  1508


>
>   max_prepared_transactions = 200
>
> Do you use a lot of prepared transactions in two-phase commit?
> I'm guessing that you don't.
I don't
>
>  > I'm sure that it could be more optimised. I don't know any thing on
>  > WAL,
>  > autovacuum, fsm, bgwriter, kernel process, geqo or planner cost
>  > settings.
>
> If you run a "vacuum verbose" it will recommend fsm settings at the end
> of its output. I think you probably need to make your autovacuum more
> aggressive, but that's something you'll be able to tell by monitoring
> your database.
>
> It's quite likely that Merlin's right, and you need better hardware to
> cope with the number of updates you're making - that's something where
> you need fast

Re: [PERFORM] file system and raid performance

2008-08-07 Thread Mark Kirkwood


Mark Kirkwood wrote:
You are right, it does (I may be recalling performance from my other 
machine that has a 3Ware card - this was a couple of years ago...) 
Anyway, I'm thinking for the Hardware raid tests they may need to be 
specified.





FWIW - of course this somewhat academic given that the single disk xfs 
test failed! I'm puzzled - having a Gentoo system of similar 
configuration (2.6.25-gentoo-r6) and running the fio tests a little 
modified for my config (2 cpu PIII 2G RAM with 4x ATA disks RAID0 and 
all xfs filesystems - I changed sizes of files to 4G and no. processes 
to 4) all tests that failed on Marks HP work on my Supermicro P2TDER + 
Promise TX4000. In fact the performance is pretty reasonable on the old 
girl as well (seq read is 142Mb/s and the random read/write is 12.7/12.0 
Mb/s).


I certainly would like to see some more info on why the xfs tests were 
failing - as on most systems I've encountered xfs is a great performer.


regards

Mark

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

[PERFORM] Filesystem setup on new system

2008-08-07 Thread Henrik


Hi list,


I'm helping a customer with their new postgresql server and have some  
questions.


The servers is connected to a SAN with dual raid cards which all have  
512MB cache with BBU.


The configuration they set up is now.
2 SAS 15K drives in RAID 1 on the internal controller for OS.

6 SAS 15K drives in RAID 10 on one of the SAN controllers for database

10 SATA 10K drives in RAID 5 on the second SAN controller for file  
storage.


My first idea was to have one partition on the RAID 10 using ext3 with  
data=writeback, noatime as mount options.


But I wonder if I should have 2 partitions on the RAID 10 one for the  
PGDATA dir using ext3 and one partition for XLOGS using ext2.


Should I do this instead? Is there just a minor speed bump as the  
partitions are on the same channel or?


If this is the way to go how should it be configured?

How big should the xlog partition be?

How should the fstab look like for the XLOG partition?

Any other pointers, hints would be appreciated.

Cheers,
Henke




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

Re: [PERFORM] file system and raid performance

2008-08-07 Thread Mario Weilguni


Mark Kirkwood schrieb:

Mark Kirkwood wrote:
You are right, it does (I may be recalling performance from my other 
machine that has a 3Ware card - this was a couple of years ago...) 
Anyway, I'm thinking for the Hardware raid tests they may need to be 
specified.





FWIW - of course this somewhat academic given that the single disk xfs 
test failed! I'm puzzled - having a Gentoo system of similar 
configuration (2.6.25-gentoo-r6) and running the fio tests a little 
modified for my config (2 cpu PIII 2G RAM with 4x ATA disks RAID0 and 
all xfs filesystems - I changed sizes of files to 4G and no. processes 
to 4) all tests that failed on Marks HP work on my Supermicro P2TDER + 
Promise TX4000. In fact the performance is pretty reasonable on the 
old girl as well (seq read is 142Mb/s and the random read/write is 
12.7/12.0 Mb/s).


I certainly would like to see some more info on why the xfs tests were 
failing - as on most systems I've encountered xfs is a great performer.


regards

Mark

I can second this, we use XFS on nearly all our database servers, and 
never encountered the problems mentioned.



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

Re: [PERFORM] Plz Heeeelp! performance settings

2008-08-07 Thread Richard Huxton

dforums wrote:

vmstat is giving :
procs ---memory-- ---swap-- -io --system-- 
cpu
 r  b   swpd   free   buff  cache   si   sobibo   incs us sy 
id wa
 0  2   1540  47388  41684 757897600   131   2590 1  9 
 3 82  7

This system is practically idle. Either you're not measuring it at a 
useful time, or there isn't a performance problem.

 >  > But
 >> if I use a second machine to replicate the database, I escape this
 >> problem isn't it ?
 > You reduce the chance of a single failure causing disaster.
Not clear this reply. It's scare me 

If server A fails, you still have server B. If server A fails so that 
replication stops working and you don't notice, server B won't help any 
more.

 > What do you mean by "take 6Go per week"? You update/delete that much
 > data? It's growing by that amount each week?
YES

That wasn't a yes/no question. Please choose one of:
Are you updating 6Go per week?
Are you adding 6Go per week?

 > I'm not sure what "15000 request per 2 minutes and empty it into 10 min"
 > means.
I insert 15000 datas every 2 min and delete 15000 every  10 min in those 
tables

 >
 > Do you have 7500 requests per minute?
should be that, But in fact I'm not treating the datas in real time, and 
I buffer the datas and push the data into the database every 2 min

 > Are these updates?
during the delete the data are aggregated in other tables which make 
updates

OK, so every 2 minutes you run one big query that adds 15000 rows.
Every 10 minutes you run one big query that deletes 15000 rows.

 > To the "temporary storage"?

 > What is this "temporary storage" - an ordinary table?
Yes, I thied to use temporary tables but I never been able to connect 
this tables over 2 different session/connection, seems that is a 
functionnality of postgresql, or a misunderstanding from me.

That's correct - temporary tables are private to a backend (connection).

 >  > I'm making some update or select on tables including more than 20
 >  > millions of entrance.
 >
 > Again, I'm not sure what this means.

To aggregate the data, I have to check the presence of others 
information that are stores in 2 tables which includes 24 millions of 
entrance.

OK. I assume you're happy with the plans you are getting on these 
queries, since you've not provided any information about them.

 > Oh - *important* - which version of PostgreSQL are you running?
8.1.11
 > Is an upgrade practical?
We are working of trying to upgrade to 8.3.3, but we are not yet ready 
for such migration

OK

 > Looking at your postgresql.conf settings:
 >
 >   max_connections = 624
 >
 > That's an odd number.
Now we could decrease this number, it's not so much usefull for now. we 
could decrease is to 350.

I don't believe you've got 350 active connections either. It will be 
easier to help if you can provide some useful information.

 >   effective_cache_size = 625000
 >
 > That's around 5GB - is that roughly the amount of memory used for
 > caching (what does free -m say for buffers/cache)?
total   used   free sharedbuffers cached
Mem:  7984   7828156  0 38   7349
-/+ buffers/cache:440   7544
Swap:  509  1508

Not far off - free is showing 7349MB cached. You're not running 350 
clients there though - you're only using 440MB of RAM.

I don't see anything to show a performance problem from these emails.

--
  Richard Huxton
  Archonet Ltd

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

Re: [PERFORM] Query Plan choice with timestamps

2008-08-07 Thread Giorgio Valoti



On 07/ago/08, at 10:35, Richard Huxton wrote:


Giorgio Valoti wrote:
Hi, I have a timestamptz field that I want to use with a query, but  
I don’t need the full timestamp resolution, so I’ve created a  
day_trunc(timestamptz) immutable function which I’ll use with the  
query and with a new index:

logs=> create index test_idx on blackbox (day_trunc(ts));
However, the query plan doesn’t use the index:


Does it use it ever? e.g. with
 SELECT * FROM blackbox WHERE day_trunk(ts) = '...'


It’s used:

logs=> explain select * from blackbox where day_trunc(ts) =  
day_trunc(now());

QUERY PLAN
---
 Bitmap Heap Scan on blackbox  (cost=22.38..3998.43 rows=1239  
width=264)

   Recheck Cond: (day_trunc(ts) = day_trunc(now()))
   ->  Bitmap Index Scan on date_idx  (cost=0.00..22.07 rows=1239  
width=0)

 Index Cond: (day_trunc(ts) = day_trunc(now()))

--
Giorgio Valoti
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Query Plan choice with timestamps

2008-08-07 Thread Richard Huxton

Giorgio Valoti wrote:

On 07/ago/08, at 10:35, Richard Huxton wrote:

Giorgio Valoti wrote:
Hi, I have a timestamptz field that I want to use with a query, but I 
don’t need the full timestamp resolution, so I’ve created a 
day_trunc(timestamptz) immutable function which I’ll use with the 
query and with a new index:

logs=> create index test_idx on blackbox (day_trunc(ts));
However, the query plan doesn’t use the index:

Does it use it ever? e.g. with
 SELECT * FROM blackbox WHERE day_trunk(ts) = '...'

It’s used:

[snip]

OK - so the index is working.

If you disable seq-scans before running the query, does it use it then?

SET enable_seqscan = off;

> logs=>explain select count(*) from blackbox group by day_trunc(ts) order
> by day_trunc(ts);
> QUERY PLAN
> 
-- 

>
>  GroupAggregate  (cost=98431.58..119773.92 rows=74226 width=8)

In particular:
1. Is the estimated cost more or less than 119773.92?
2. How does that match the actual time taken?

--
  Richard Huxton
  Archonet Ltd

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

Re: [PERFORM] Plz Heeeelp! performance settings

2008-08-07 Thread dforums

The performance problem is really only on the insertion and even more on 
the treatment for the aggregation.

To treat the 3000 entrances and to insert, or update the tables it needs 
10 minutes.

As I told you I inject 14000 query every 2 minutes, and it needs 10 
minutes to treat 3000 of those query.

As you can easly understand it's a big narrow section.

I'm not doing the treatment in ones, cause I can't, but all is managed 
by procedure.

> That wasn't a yes/no question. Please choose one of:
> Are you updating 6Go per week? most of update
> Are you adding 6Go per week? less of injection,

This action depend if the data are already present in the database.

>
> OK. I assume you're happy with the plans you are getting on these
> queries, since you've not provided any information about them.

The plan seems ok as it use index as well.
here is the plan :

explain analyse SELECT "insertUpdateTracks"(137,2605, 852, ('2008-08-06 
19:28:54'::text)::date,3,'dailydisplay',2,NULL);

INFO:  method 1
 QUERY PLAN

 Result  (cost=0.00..0.01 rows=1 width=0) (actual time=1.151..1.151 
rows=1 loops=1)

 Total runtime: 1.160 ms
(2 lignes)

 Has you can see the runtime processs for an update in this table.

multiplying this per 1, it is too long.

regards

david

Richard Huxton a écrit :

dforums wrote:

vmstat is giving :
procs ---memory-- ---swap-- -io --system-- 
cpu
 r  b   swpd   free   buff  cache   si   sobibo   incs us 
sy id wa
 0  2   1540  47388  41684 757897600   131   2590 1  9 
 3 82  7

This system is practically idle. Either you're not measuring it at a 
useful time, or there isn't a performance problem.

 >  > But
 >> if I use a second machine to replicate the database, I escape this
 >> problem isn't it ?
 > You reduce the chance of a single failure causing disaster.
Not clear this reply. It's scare me 

If server A fails, you still have server B. If server A fails so that 
replication stops working and you don't notice, server B won't help any 
more.

 > What do you mean by "take 6Go per week"? You update/delete that much
 > data? It's growing by that amount each week?
YES

That wasn't a yes/no question. Please choose one of:
Are you updating 6Go per week?
Are you adding 6Go per week?

 > I'm not sure what "15000 request per 2 minutes and empty it into 10 
min"

 > means.
I insert 15000 datas every 2 min and delete 15000 every  10 min in 
those tables

 >
 > Do you have 7500 requests per minute?
should be that, But in fact I'm not treating the datas in real time, 
and I buffer the datas and push the data into the database every 2 min

 > Are these updates?
during the delete the data are aggregated in other tables which make 
updates

OK, so every 2 minutes you run one big query that adds 15000 rows.
Every 10 minutes you run one big query that deletes 15000 rows.

 > To the "temporary storage"?

 > What is this "temporary storage" - an ordinary table?
Yes, I thied to use temporary tables but I never been able to connect 
this tables over 2 different session/connection, seems that is a 
functionnality of postgresql, or a misunderstanding from me.

That's correct - temporary tables are private to a backend (connection).

 >  > I'm making some update or select on tables including more than 20
 >  > millions of entrance.
 >
 > Again, I'm not sure what this means.

To aggregate the data, I have to check the presence of others 
information that are stores in 2 tables which includes 24 millions of 
entrance.

OK. I assume you're happy with the plans you are getting on these 
queries, since you've not provided any information about them.

 > Oh - *important* - which version of PostgreSQL are you running?
8.1.11
 > Is an upgrade practical?
We are working of trying to upgrade to 8.3.3, but we are not yet ready 
for such migration

OK

 > Looking at your postgresql.conf settings:
 >
 >   max_connections = 624
 >
 > That's an odd number.
Now we could decrease this number, it's not so much usefull for now. 
we could decrease is to 350.

I don't believe you've got 350 active connections either. It will be 
easier to help if you can provide some useful information.

 >   effective_cache_size = 625000
 >
 > That's around 5GB - is that roughly the amount of memory used for
 > caching (what does free -m say for buffers/cache)?
total   used   free sharedbuffers cached
Mem:  7984   7828156  0 38   7349
-/+ buffers/cache:440   7544
Swap:  509  1508

Not far off - free is showing 7349MB cached. You're not running 350 
clients there though - you're only using 440MB of RAM.

I don't see anything to show a performance problem from these emails.

--

*David Bigand
*Président

Re: [PERFORM] Plz Heeeelp! performance settings

2008-08-07 Thread Richard Huxton

dforums wrote:
The performance problem is really only on the insertion and even more on 
the treatment for the aggregation.

To treat the 3000 entrances and to insert, or update the tables it needs 
10 minutes.

As I told you I inject 14000 query every 2 minutes, and it needs 10 
minutes to treat 3000 of those query.

Sorry - I still don't understand. What is this "treatment" you are doing?

 >
 > OK. I assume you're happy with the plans you are getting on these
 > queries, since you've not provided any information about them.

The plan seems ok as it use index as well.
here is the plan :

explain analyse SELECT "insertUpdateTracks"(137,2605, 852, ('2008-08-06 
19:28:54'::text)::date,3,'dailydisplay',2,NULL);

INFO:  method 1
 QUERY PLAN

 Result  (cost=0.00..0.01 rows=1 width=0) (actual time=1.151..1.151 
rows=1 loops=1)

 Total runtime: 1.160 ms

There's nothing to do with an index here - this is a function call.

 Has you can see the runtime processs for an update in this table.

multiplying this per 1, it is too long.

So - are you calling this function 14000 times to inject your data? 
You're doing this in one transaction, yes?

--
  Richard Huxton
  Archonet Ltd

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

Re: [PERFORM] Unexpectedly Long DELETE Wait

2008-08-07 Thread Volkan YAZICI

On Thu, 07 Aug 2008, Richard Huxton <[EMAIL PROTECTED]> writes:
> Volkan YAZICI wrote:
>>   DELETE FROM mugpsreglog
>> WHERE NOT EXISTS (SELECT 1
>> FROM mueventlog
>>WHERE mueventlog.eventlogid = 
>> mugpsreglog.eventlogid);
>>
>>Seq Scan on mugpsreglog  (cost=0.00..57184031821394.73 rows=6590986 
>> width=6)
>>  Filter: (NOT (subplan))
>> SubPlan
>>  ->  Seq Scan on mueventlog  (cost=0.00..4338048.00 rows=1 
>> width=0)
>>  Filter: (eventlogid = $0)
>
> Ouch - look at the estimated cost on that!
>
>> And there isn't any constraints (FK/PK), triggers, indexes, etc. on any
>> of the tables. (We're in the phase of a migration, many DELETE commands
>> similar to above gets executed to relax constraints will be introduced.)
>
> Well there you go. Add an index on eventlogid for mugpsreglog.

Hrm... Adding an INDEX on "eventlogid" column of "mueventlog" table
solved the problem. Anyway, thanks for your kindly help.

> Alternatively, if you increased your work_mem that might help. Try SET
> work_mem='64MB' (or even higher) before running the explain and see if it 
> tries
> a materialize. For situations like this where you're doing big one-off queries
> you can afford to increase resource limits.

None of 64MB, 128MB, 256MB and 512MB settings make a change in the query
plan.


Regards.

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

[PERFORM] Another index related question....

2008-08-07 Thread ries van Twisk


Hey all, I have two tables that look like this:


CREATE TABLE details
(
  cust_code character varying(6) NOT NULL,
  cust_po character varying(20) NOT NULL,
  date_ordd date NOT NULL,
  item_nbr integer NOT NULL,
  orig_qty_ordd integer,
  CONSTRAINT details_pkey PRIMARY KEY (cust_code, cust_po, date_ordd,  
item_nbr)

);
CREATE INDEX idx_details ON details USING btree (cust_code, cust_po,  
date_ordd);



CREATE TABLE status
(
  id serial NOT NULL,
  cust_code character varying(6) NOT NULL,
  cust_po character varying(20) NOT NULL,
  date_ordd date NOT NULL,
  item_nbr integer,
  ext_nbr integer,


...
  CONSTRAINT status_pkey PRIMARY KEY (id)
);
CREATE INDEX idx_status_idx2 ON status USING btree (cust_code,  
cust_po, date_ordd, item_nbr);



Both tables are analyzed full and Table details contains around  
390.000 records and status contains around 580.000 records.



Doing the following SQL:
explain analyze
SELECT
a 
.cust_code 
,a 
.cust_po 
,a.date_ordd,a.item_nbr,b.Lines_part_ordd,a.orig_qty_ordd,b.Ship_via

FROM details a
JOIN status b ON (a.cust_code = b.cust_code AND a.cust_po = b.cust_po  
AND a.date_ordd = b.date_ordd AND  a.item_nbr = b.item_nbr )


Created this execution plan

Merge Join  (cost=0.76..71872.13 rows=331 width=41) (actual  
time=0.393..225404.902 rows=579742 loops=1)
  Merge Cond: (((a.cust_code)::text = (b.cust_code)::text) AND  
((a.cust_po)::text = (b.cust_po)::text) AND (a.date_ordd = b.date_ordd))

  Join Filter: (a.item_nbr = b.item_nbr)
  ->  Index Scan using idx_details on details a  (cost=0.00..23847.74  
rows=389147 width=28) (actual time=0.244..927.752 rows=389147 loops=1)
  ->  Index Scan using idx_status_idx2 on status b   
(cost=0.00..40249.31 rows=579933 width=37) (actual  
time=0.142..84250.016 rows=176701093 loops=1)

Total runtime: 225541.232 ms

Question to myself is why does it want to do a Join Filter on item_nbr?

When drop the index idx_details I get this execution plan:

Merge Join  (cost=0.81..74650.36 rows=331 width=41) (actual  
time=0.106..2159.315 rows=579742 loops=1)
  Merge Cond: (((a.cust_code)::text = (b.cust_code)::text) AND  
((a.cust_po)::text = (b.cust_po)::text) AND (a.date_ordd =  
b.date_ordd) AND (a.item_nbr = b.item_nbr))
  ->  Index Scan using details_pkey on details a   
(cost=0.00..24707.75 rows=389147 width=28) (actual time=0.030..562.234  
rows=389147 loops=1)
  ->  Index Scan using idx_status_idx2 on status b   
(cost=0.00..40249.31 rows=579933 width=37) (actual time=0.069..289.359  
rows=579933 loops=1)

Total runtime: 2226.793 ms

Notice the difference in speed, the second one is about 100 times  
faster.


As per Tom's advice I tried to set random_page_cost to 2, but that  
doesn't change my execution plan, also not with 1...


Now  I add the index idx_details back again...

And when I set my cpu_tuple_cost to 0.25 I get the 'correct' execution  
plan as shown above.


set cpu_tuple_cost=0.25;
explain analyze
SELECT
a 
.cust_code 
,a 
.cust_po 
,a.date_ordd,a.item_nbr,b.Lines_part_ordd,a.orig_qty_ordd,b.Ship_via

FROM acc_sc.details a
JOIN acc_sc.status b ON (a.cust_code = b.cust_code AND a.cust_po =  
b.cust_po AND a.date_ordd = b.date_ordd AND  a.item_nbr = b.item_nbr )


This execution plan is the same as the one above when idx_details was  
removed


Merge Join  (cost=3.45..307306.36 rows=331 width=41) (actual  
time=0.038..2246.726 rows=579742 loops=1)
  Merge Cond: (((a.cust_code)::text = (b.cust_code)::text) AND  
((a.cust_po)::text = (b.cust_po)::text) AND (a.date_ordd =  
b.date_ordd) AND (a.item_nbr = b.item_nbr))
  ->  Index Scan using details_pkey on details a   
(cost=0.00..118103.03 rows=389147 width=28) (actual  
time=0.020..589.779 rows=389147 loops=1)
  ->  Index Scan using idx_status_idx2 on status b   
(cost=0.00..179433.23 rows=579933 width=37) (actual  
time=0.011..305.963 rows=579933 loops=1)

Total runtime: 2318.647 ms



What I am trying to understand is why PostgreSQL want's to use  
idx_details over it's primary_key?
I am sure that 'simply' setting cpu_tuple_cost to 0.25 from 0.01 is  
not a good idea..?




Ries van Twisk


SHOW ALL;
add_missing_from;off;Automatically adds missing table references to  
FROM clauses.
allow_system_table_mods;off;Allows modifications of the structure of  
system tables.
archive_command;(disabled);Sets the shell command that will be called  
to archive a WAL file.

archive_mode;off;Allows archiving of WAL files using archive_command.
archive_timeout;0;Forces a switch to the next xlog file if a new file  
has not been started within N seconds.

array_nulls;on;Enable input of NULL elements in arrays.
authentication_timeout;1min;Sets the maximum allowed time to complete  
client authentication.

autovacuum;on;Starts the autovacuum subprocess.
autovacuum_analyze_scale_factor;0.1;Number of tuple inserts, updates  
or deletes prior to analyze as a fraction of reltuples.
autovacuum_analyze_threshold;50;Minimum number of tuple inserts,  
updates or deletes prior

Re: [PERFORM] Plz Heeeelp! performance settings

2008-08-07 Thread Merlin Moncure

On Thu, Aug 7, 2008 at 9:30 AM, dforums <[EMAIL PROTECTED]> wrote:
> The performance problem is really only on the insertion and even more on the
> treatment for the aggregation.
>
> To treat the 3000 entrances and to insert, or update the tables it needs 10
> minutes.
>
> As I told you I inject 14000 query every 2 minutes, and it needs 10 minutes
> to treat 3000 of those query.
>
> As you can easly understand it's a big narrow section.
>
> I'm not doing the treatment in ones, cause I can't, but all is managed by
> procedure.
>
>> That wasn't a yes/no question. Please choose one of:
>> Are you updating 6Go per week? most of update
>> Are you adding 6Go per week? less of injection,
>
> This action depend if the data are already present in the database.
>
>
>>
>> OK. I assume you're happy with the plans you are getting on these
>> queries, since you've not provided any information about them.
>
> The plan seems ok as it use index as well.
> here is the plan :
>
> explain analyse SELECT "insertUpdateTracks"(137,2605, 852, ('2008-08-06
> 19:28:54'::text)::date,3,'dailydisplay',2,NULL);
> INFO:  method 1
> QUERY PLAN
> 
>  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=1.151..1.151 rows=1
> loops=1)
>  Total runtime: 1.160 ms
> (2 lignes)
>
>  Has you can see the runtime processs for an update in this table.
>
> multiplying this per 1, it is too long.
>

please don't top-post (put your reply after previous comments).

With fsync on, you are lucky to get 10k inserts in 10 minutes on
single sata 1.  The basic issue is that after each time function runs
postgesql tells disk drive to flush, guaranteeing data safety.  You
have few different options here:

*) group multiple inserts into single transaction
*) modify function to take multiple 'update' records at once.
*) disable fsync (extremely unsafe as noted)
*) upgrade to 8.3 and disable synchronized_commit (the 'fsync lite', a
good compromise between fsync on/off)

merlin

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

Re: [PERFORM] Plz Heeeelp! performance settings

2008-08-07 Thread Richard Huxton


dforums wrote:
> The delete is global, the procedure is called for each line/tracks.
>  > So - are you calling this function 14000 times to inject your data?
>  > You're doing this in one transaction, yes?
> NO I have to make it 14000 times cause, I use some inserted information
> for other insert to make links between data.

Why does that stop you putting all 14000 calls in one transaction?


--
  Richard Huxton
  Archonet Ltd

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

Re: [PERFORM] query planner not using the correct index

2008-08-07 Thread Joshua Shanks

On Wed, Aug 6, 2008 at 10:24 PM, Craig Ringer
<[EMAIL PROTECTED]> wrote:
> OK, that's interesting. There are ways to examine Pg's statistics on
> columns, get an idea of which stats might be less than accurate, etc,
> but I'm not really familiar enough with it all to give you any useful
> advice on the details. I can make one suggestion in the vein of shotgun
> throubleshooting, though:
>
> Try altering the statistics targets on the tables of interest, or tweak
> the default_statistics_target, then rerun VACUUM ANALYZE and re-test.
> Maybe start with a stats target of 100 and see what happens.
>
> --
> Craig Ringer

I tried 100, 500, and 1000 for default_statistics_target. I think
below is the right query to examine the stats. None of the levels of
default_statistics_target I tried changed the query planners behavior.

It seems obvious that the stats on attr1 at the current level are
inaccurate as there are over 100,000 unique enteries in the table. But
even tweaking them to be more accurate doesn't seem to add any
benefit.

default_statistics_target = 10

SELECT null_frac, n_distinct, most_common_vals, most_common_freqs FROM
pg_stats WHERE tablename = 'foos' AND attname='attr1';
 null_frac | n_distinct | most_common_vals | most_common_freqs
---++--+---
 0 |   1789 | {""} | {0.625667}

default_statistics_target = 100

SELECT null_frac, n_distinct, most_common_vals, most_common_freqs FROM
pg_stats WHERE tablename = 'foo' AND attname='attr1';
  null_frac  | n_distinct | most_common_vals | most_common_freqs
-++--+---
 0.00027 |  17429 | {""} | {0.6223}

default_statistics_target = 500

SELECT null_frac, n_distinct, most_common_vals, most_common_freqs FROM
pg_stats WHERE tablename = 'foo' AND attname='attr1';
  null_frac  | n_distinct | most_common_vals | most_common_freqs
-++--+---
 0.00029 |   -0.17954 | {""} | {0.62158}

default_statistics_target = 1000

SELECT null_frac, n_distinct, most_common_vals, most_common_freqs FROM
pg_stats WHERE tablename = 'foo' AND attname='attr1';
  null_frac  | n_distinct | most_common_vals | most_common_freqs
-++--+---
 0.00029 |  -0.304907 | {""} | {0.621043}

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

Re: [PERFORM] Query Plan choice with timestamps

2008-08-07 Thread Tom Lane

Giorgio Valoti <[EMAIL PROTECTED]> writes:
>   GroupAggregate  (cost=98431.58..119773.92 rows=74226 width=8)
> ->  Sort  (cost=98431.58..99050.92 rows=247736 width=8)
>   Sort Key: (day_trunc(ts))
>   ->  Seq Scan on blackbox  (cost=0.00..72848.36 rows=247736 width=8)

>   GroupAggregate  (cost=0.00..19109.66 rows=74226 width=8)
> ->  Index Scan using test_2_idx on blackbox  (cost=0.00..16943.16 
> rows=247736 width=8)

These numbers seem pretty bogus: there is hardly any scenario in which a
full-table indexscan should be costed as significantly cheaper than a
seqscan.  Have you put in silly values for random_page_cost?

If you haven't mucked with the cost parameters, the only way I can think
of to get this result is to have an enormously bloated table that's
mostly empty.  Maybe you need to review your vacuuming procedures.

regards, tom lane

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

Re: [PERFORM] Query Plan choice with timestamps

2008-08-07 Thread Giorgio Valoti



On 07/ago/08, at 17:50, Tom Lane wrote:


Giorgio Valoti <[EMAIL PROTECTED]> writes:

 GroupAggregate  (cost=98431.58..119773.92 rows=74226 width=8)
   ->  Sort  (cost=98431.58..99050.92 rows=247736 width=8)
 Sort Key: (day_trunc(ts))
 ->  Seq Scan on blackbox  (cost=0.00..72848.36 rows=247736  
width=8)



 GroupAggregate  (cost=0.00..19109.66 rows=74226 width=8)
   ->  Index Scan using test_2_idx on blackbox   
(cost=0.00..16943.16 rows=247736 width=8)


These numbers seem pretty bogus: there is hardly any scenario in  
which a

full-table indexscan should be costed as significantly cheaper than a
seqscan.  Have you put in silly values for random_page_cost?


No,




If you haven't mucked with the cost parameters, the only way I can  
think

of to get this result is to have an enormously bloated table that's
mostly empty.  Maybe you need to review your vacuuming procedures.


I’ll review them.

Thank you
--
Giorgio Valoti
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Query Plan choice with timestamps

2008-08-07 Thread Giorgio Valoti



On 07/ago/08, at 14:36, Richard Huxton wrote:


Giorgio Valoti wrote:

On 07/ago/08, at 10:35, Richard Huxton wrote:

Giorgio Valoti wrote:
Hi, I have a timestamptz field that I want to use with a query,  
but I don’t need the full timestamp resolution, so I’ve created a  
day_trunc(timestamptz) immutable function which I’ll use with the  
query and with a new index:

logs=> create index test_idx on blackbox (day_trunc(ts));
However, the query plan doesn’t use the index:


Does it use it ever? e.g. with
SELECT * FROM blackbox WHERE day_trunk(ts) = '...'

It’s used:

[snip]

OK - so the index is working.

If you disable seq-scans before running the query, does it use it  
then?


SET enable_seqscan = off;


Yes


[…]

In particular:
1. Is the estimated cost more or less than 119773.92?


   QUERY PLAN
-
 GroupAggregate  (cost=0.00..122309.32 rows=74226 width=8)
   ->  Index Scan using date_idx on blackbox  (cost=0.00..101586.31  
rows=247736 width=8)




2. How does that match the actual time taken?


QUERY  
PLAN

---
 GroupAggregate  (cost=0.00..122309.32 rows=74226 width=8) (actual  
time=0.222..1931.651 rows=428 loops=1)
   ->  Index Scan using date_idx on blackbox  (cost=0.00..101586.31  
rows=247736 width=8) (actual time=0.072..1861.367 rows=247736 loops=1)

 Total runtime: 1931.782 ms

But I haven’t revised the vacuum settings.

Thank you
--
Giorgio Valoti



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

Re: [PERFORM] Query Plan choice with timestamps

2008-08-07 Thread Giorgio Valoti



On 07/ago/08, at 20:37, Giorgio Valoti wrote:



[…]




If you haven't mucked with the cost parameters, the only way I can  
think

of to get this result is to have an enormously bloated table that's
mostly empty.  Maybe you need to review your vacuuming procedures.


I’ll review them.


I’ve manually vacuum’ed the table:
logs=> VACUUM FULL verbose analyze blackbox;
INFO:  vacuuming "public.blackbox"
INFO:  "blackbox": found 0 removable, 247736 nonremovable row versions  
in 8436 pages

DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 137 to 1210 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 894432 bytes.
0 pages are or will become empty, including 0 at the end of the table.
2926 pages containing 564212 free bytes are potential move destinations.
CPU 0.00s/0.04u sec elapsed 0.04 sec.
INFO:  index "blackbox_pkey" now contains 247736 row versions in 1602  
pages

DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.01s/0.00u sec elapsed 0.01 sec.
INFO:  index "vhost_idx" now contains 247736 row versions in 1226 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  index "remoteip_idx" now contains 247736 row versions in 682  
pages

DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "date_idx" now contains 247736 row versions in 547 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "test_2_idx" now contains 247736 row versions in 682 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "blackbox": moved 0 row versions, truncated 8436 to 8436 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  vacuuming "pg_toast.pg_toast_45532"
INFO:  "pg_toast_45532": found 0 removable, 0 nonremovable row  
versions in 0 pages

DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "pg_toast_45532_index" now contains 0 row versions in 1  
pages

DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.blackbox"
INFO:  "blackbox": scanned 3000 of 8436 pages, containing 87941 live  
rows and 0 dead rows; 3000 rows in sample, 247290 estimated total rows

VACUUM

And here the explain results:
logs=> explain select count(*) from blackbox group by day_trunc(ts)  
order by day_trunc(ts);

 QUERY PLAN
-
 Sort  (cost=74210.52..74211.54 rows=407 width=8)
   Sort Key: (day_trunc(ts))
   ->  HashAggregate  (cost=74086.04..74192.88 rows=407 width=8)
 ->  Seq Scan on blackbox  (cost=0.00..72847.36 rows=247736  
width=8)

(4 rows)

logs=> explain select count(*) from blackbox group by ts order by ts;
QUERY PLAN
--
 GroupAggregate  (cost=0.00..18381.54 rows=77738 width=8)
   ->  Index Scan using test_2_idx on blackbox  (cost=0.00..16171.13  
rows=247736 width=8)

(2 rows)

Maybe it’s the silly test queries that prove nothing:

logs=> explain select * from blackbox  where day_trunc(ts) =  
day_trunc(now());

  QUERY PLAN
---
 Index Scan using date_idx on blackbox  (cost=0.50..158.65 rows=569  
width=237)

   Index Cond: (day_trunc(ts) = day_trunc(now()))
(2 rows)

Ciao
--
Giorgio Valoti
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] file system and raid performance

2008-08-07 Thread Mark Wong

On Thu, Aug 7, 2008 at 3:21 AM, Mario Weilguni <[EMAIL PROTECTED]> wrote:
> Mark Kirkwood schrieb:
>>
>> Mark Kirkwood wrote:
>>>
>>> You are right, it does (I may be recalling performance from my other
>>> machine that has a 3Ware card - this was a couple of years ago...) Anyway,
>>> I'm thinking for the Hardware raid tests they may need to be specified.
>>>
>>>
>>
>> FWIW - of course this somewhat academic given that the single disk xfs
>> test failed! I'm puzzled - having a Gentoo system of similar configuration
>> (2.6.25-gentoo-r6) and running the fio tests a little modified for my config
>> (2 cpu PIII 2G RAM with 4x ATA disks RAID0 and all xfs filesystems - I
>> changed sizes of files to 4G and no. processes to 4) all tests that failed
>> on Marks HP work on my Supermicro P2TDER + Promise TX4000. In fact the
>> performance is pretty reasonable on the old girl as well (seq read is
>> 142Mb/s and the random read/write is 12.7/12.0 Mb/s).
>>
>> I certainly would like to see some more info on why the xfs tests were
>> failing - as on most systems I've encountered xfs is a great performer.
>>
>> regards
>>
>> Mark
>>
> I can second this, we use XFS on nearly all our database servers, and never
> encountered the problems mentioned.

I have heard of one or two situations where the combination of the
disk controller caused bizarre behaviors with different journaling
file systems.  They seem so few and far between though.  I personally
wasn't looking forwarding to chasing Linux file system problems, but I
can set up an account and remote management access if anyone else
would like to volunteer.

Regards,
Mark

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

Re: [PERFORM] file system and raid performance

2008-08-07 Thread Andrej Ricnik-Bay

To me it still boggles the mind that noatime should actually slow down
activities on ANY file-system ... has someone got an explanation for
that kind of behaviour?  As far as I'm concerned this means that even
to any read I'll add the overhead of a write - most likely in a disk-location
slightly off of the position that I read the data ... how would that speed
the process up on average?



Cheers,
Andrej

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

Re: [PERFORM] Query Plan choice with timestamps

2008-08-07 Thread Tom Lane

Giorgio Valoti <[EMAIL PROTECTED]> writes:
> On 07/ago/08, at 17:50, Tom Lane wrote:
>> These numbers seem pretty bogus: there is hardly any scenario in  
>> which a
>> full-table indexscan should be costed as significantly cheaper than a
>> seqscan.  Have you put in silly values for random_page_cost?

> No,

I looked at it more closely and realized that the cost discrepancy is
from the evaluation of the function: having to evaluate a SQL or plpgsql
function 247736 times more than explains the cost estimate differential
compared to a query that involves no function call.  Some experiments
here suggest that it hardly matters whether the query uses indexscan or
seqscan because the time is dominated by the function calls anyway.

regards, tom lane

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

Re: [PERFORM] query planner not using the correct index

2008-08-07 Thread Tom Lane

"Joshua Shanks" <[EMAIL PROTECTED]> writes:
> It seems obvious that the stats on attr1 at the current level are
> inaccurate as there are over 100,000 unique enteries in the table.

Well, you haven't told us how big any of these tables are, so it's
hard to tell if the n_distinct value is wrong or not ... but in
any case I don't think that the stats on attr1 have anything to do
with your problem.  The reason that the "fast" query is fast is that
it benefits from the fact that there's only one bars row with
bars_id = 12345.  So the question is how many such rows does the
planner now think there are (try "explain analyze select * from bars
where bars_id = 12345"), and if it's badly wrong, then you need to be
looking at the stats on bars.bars_id to find out why.

regards, tom lane

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

Re: [PERFORM] file system and raid performance

2008-08-07 Thread Scott Marlowe

On Thu, Aug 7, 2008 at 2:59 PM, Andrej Ricnik-Bay
<[EMAIL PROTECTED]> wrote:
> To me it still boggles the mind that noatime should actually slow down
> activities on ANY file-system ... has someone got an explanation for
> that kind of behaviour?  As far as I'm concerned this means that even
> to any read I'll add the overhead of a write - most likely in a disk-location
> slightly off of the position that I read the data ... how would that speed
> the process up on average?

noatime turns off the atime write behaviour.  Or did you already know
that and I missed some weird post where noatime somehow managed to
slow down performance?

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

Re: [PERFORM] file system and raid performance

2008-08-07 Thread Andrej Ricnik-Bay

2008/8/8 Scott Marlowe <[EMAIL PROTECTED]>:
> noatime turns off the atime write behaviour.  Or did you already know
> that and I missed some weird post where noatime somehow managed to
> slow down performance?

Scott, I'm quite aware of what noatime does ... you didn't miss a post, but
if you look at Mark's graphs on
http://wiki.postgresql.org/wiki/HP_ProLiant_DL380_G5_Tuning_Guide
they pretty much all indicate that (unless I completely misinterpret the
meaning and purpose of the labels), independent of the file-system,
using noatime slows read/writes down (on average).




-- 
Please don't top post, and don't use HTML e-Mail :} Make your quotes concise.

http://www.american.edu/econ/notes/htmlmail.htm

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

Re: [PERFORM] file system and raid performance

2008-08-07 Thread Gregory S. Youngblood

> -Original Message-
> From: Mark Wong [mailto:[EMAIL PROTECTED]
> Sent: Thursday, August 07, 2008 12:37 PM
> To: Mario Weilguni
> Cc: Mark Kirkwood; [EMAIL PROTECTED]; [EMAIL PROTECTED]; pgsql-
> [EMAIL PROTECTED]; Gabrielle Roth
> Subject: Re: [PERFORM] file system and raid performance
> 
 
> I have heard of one or two situations where the combination of the
> disk controller caused bizarre behaviors with different journaling
> file systems.  They seem so few and far between though.  I personally
> wasn't looking forwarding to chasing Linux file system problems, but I
> can set up an account and remote management access if anyone else
> would like to volunteer.

[Greg says] 
Tempting... if no one else takes you up on it by then, I might have some
time in a week or two to experiment and test a couple of things.

One thing I've noticed with a Silicon Image 3124 SATA going through a
Silicon Image 3726 port multiplier with the binary-only drivers from Silicon
Image (until the PM support made it into the mainline kernel - 2.6.24 I
think, might have been .25) is that under some heavy loads it might drop a
sata channel and if that channel happens to have a PM on it, it drops 5
drives. I saw this with a card that had 4 channels, 2 connected to a PM w/5
drives and 2 direct. It was pretty random. 

Not saying that's happening in this case, but odd things have been known to
happen under unusual usage patterns.




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

Re: [PERFORM] query planner not using the correct index

2008-08-07 Thread Joshua Shanks

On Thu, Aug 7, 2008 at 4:12 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> Well, you haven't told us how big any of these tables are, so it's
> hard to tell if the n_distinct value is wrong or not ... but in
> any case I don't think that the stats on attr1 have anything to do
> with your problem.  The reason that the "fast" query is fast is that
> it benefits from the fact that there's only one bars row with
> bars_id = 12345.  So the question is how many such rows does the
> planner now think there are (try "explain analyze select * from bars
> where bars_id = 12345"), and if it's badly wrong, then you need to be
> looking at the stats on bars.bars_id to find out why.
>
>regards, tom lane
>

foo is 400,000+ rows
bar is 300,000+ rows

I was just about to write back about this as with all my tinkering
today I figured that to be the root cause.

SELECT null_frac, n_distinct, most_common_vals, most_common_freqs FROM
pg_stats WHERE tablename = 'bars' AND attname='bars_id';
 null_frac | n_distinct |   most_common_vals   | most_common_freqs
---++--+---
 0 | 14 | {145823,47063,24895} | {0.484667,0.257333,0.242}

Those 3 values in reality and in the stats account for 98% of the
rows. actual distinct values are around 350

That plus the information information on
http://www.postgresql.org/docs/8.3/static/indexes-ordering.html make
it all make sense as to why the query planner is doing what it is
doing.

The only problem is we rarely if ever call the query with the where
clause containing those values. I did some testing and the planner
works awesome if we were to call those values but 99.9% of the time we
are calling other values.

It seems like the planner would want to get the result set from
bars.bars_id condition and if it is big using the index on the join to
avoid the separate sorting, but if it is small (0-5 rows which is our
normal case) use the primary key index to join and then just quickly
sort. Is there any reason the planner doesn't do this?

I found a way to run the query as a subselect which is fast for our
normal case but doesn't work for the edge cases so I might just have
to do count on the bars_id and then pick a query based on that.

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

Re: [PERFORM] file system and raid performance

2008-08-07 Thread Scott Marlowe

On Thu, Aug 7, 2008 at 3:57 PM, Andrej Ricnik-Bay
<[EMAIL PROTECTED]> wrote:
> 2008/8/8 Scott Marlowe <[EMAIL PROTECTED]>:
>> noatime turns off the atime write behaviour.  Or did you already know
>> that and I missed some weird post where noatime somehow managed to
>> slow down performance?
>
> Scott, I'm quite aware of what noatime does ... you didn't miss a post, but
> if you look at Mark's graphs on
> http://wiki.postgresql.org/wiki/HP_ProLiant_DL380_G5_Tuning_Guide
> they pretty much all indicate that (unless I completely misinterpret the
> meaning and purpose of the labels), independent of the file-system,
> using noatime slows read/writes down (on average).

Interesting.  While a few of the benchmarks looks noticeably slower
with noatime (reiserfs for instance) most seem faster in that listing.

I am just now setting up our big database server for work and noticed
a MUCH lower performance without noatime.

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

Re: [PERFORM] file system and raid performance

2008-08-07 Thread Mark Mielke


Andrej Ricnik-Bay wrote:

2008/8/8 Scott Marlowe <[EMAIL PROTECTED]>:
  

noatime turns off the atime write behaviour.  Or did you already know
that and I missed some weird post where noatime somehow managed to
slow down performance?



Scott, I'm quite aware of what noatime does ... you didn't miss a post, but
if you look at Mark's graphs on
http://wiki.postgresql.org/wiki/HP_ProLiant_DL380_G5_Tuning_Guide
they pretty much all indicate that (unless I completely misinterpret the
meaning and purpose of the labels), independent of the file-system,
using noatime slows read/writes down (on average)


That doesn't make sense - if noatime slows things down, then the 
analysis is probably wrong.


Now, modern Linux distributions default to "relatime" - which will only 
update access time if the access time is currently less than the update 
time or something like this. The effect is that modern Linux 
distributions do not benefit from "noatime" as much as they have in the 
past. In this case, "noatime" vs default would probably be measuring % 
noise.


Cheers,
mark

--
Mark Mielke <[EMAIL PROTECTED]>

Re: [PERFORM] query planner not using the correct index

2008-08-07 Thread Gregory Stark

"Joshua Shanks" <[EMAIL PROTECTED]> writes:

> Those 3 values in reality and in the stats account for 98% of the
> rows. actual distinct values are around 350

Measuring n_distinct from a sample is inherently difficult and unreliable.
When 98% of your table falls into those categories it's leaving very few
chances for the sample to find many other distinct values. 

I haven't seen the whole thread, if you haven't tried already you could try
raising the statistics target for these columns -- that's usually necessary
anyways when you have a very skewed distribution like this.

> It seems like the planner would want to get the result set from
> bars.bars_id condition and if it is big using the index on the join to
> avoid the separate sorting, but if it is small (0-5 rows which is our
> normal case) use the primary key index to join and then just quickly
> sort. Is there any reason the planner doesn't do this?

Yeah, Heikki's suggested having a kind of "branch" plan node that knows how
where the break-point is between two plans and can call the appropriate one.
We don't have anything like that yet.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's On-Demand Production Tuning

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

Re: [PERFORM] query planner not using the correct index

2008-08-07 Thread Joshua Shanks

On Thu, Aug 7, 2008 at 5:38 PM, Gregory Stark <[EMAIL PROTECTED]> wrote:
> Measuring n_distinct from a sample is inherently difficult and unreliable.
> When 98% of your table falls into those categories it's leaving very few
> chances for the sample to find many other distinct values.
>
> I haven't seen the whole thread, if you haven't tried already you could try
> raising the statistics target for these columns -- that's usually necessary
> anyways when you have a very skewed distribution like this.
>

I did some tweaking on default_statistics_target earlier in the thread
with no luck. I just retried it with default_statistics_target set to
500 and did the VACUUM ANALYZE on the other table this time and
started to see better results and more of the behavior I would expect.

Is there a way to set the stats target for just one column? That seems
like what we might need to do.

> Yeah, Heikki's suggested having a kind of "branch" plan node that knows how
> where the break-point is between two plans and can call the appropriate one.
> We don't have anything like that yet.
>

Is this already on a todo list or is there a bug for it?

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

Re: [PERFORM] query planner not using the correct index

2008-08-07 Thread Tom Lane

"Joshua Shanks" <[EMAIL PROTECTED]> writes:
> SELECT null_frac, n_distinct, most_common_vals, most_common_freqs FROM
> pg_stats WHERE tablename = 'bars' AND attname='bars_id';
>  null_frac | n_distinct |   most_common_vals   | most_common_freqs
> ---++--+---
>  0 | 14 | {145823,47063,24895} | {0.484667,0.257333,0.242}

> Those 3 values in reality and in the stats account for 98% of the
> rows. actual distinct values are around 350

So you need to increase the stats target for this column.  With those
numbers the planner is going to assume that any value that's not one
of the big three appears about (1 - (0.484667+0.257333+0.242)) / 11
of the time, or several hundred times in 300K rows.  If n_distinct were
up around 350 it would be estimating just a dozen or so occurrences,
which should push the join plan into the shape you want.  It's likely
that it won't bother to include any more entries in most_common_vals
no matter how much you raise the target; but a larger sample should
definitely give it a better clue about n_distinct.

regards, tom lane

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

Re: [PERFORM] query planner not using the correct index

2008-08-07 Thread Joshua Shanks

Yeah with default_statistics_target at 500 most_common_vals had 4
values with the fourth having a frequency of 1.5% and distinct have
250+ in it.

How do I increase the stats target for just one column?

On Thu, Aug 7, 2008 at 6:48 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Joshua Shanks" <[EMAIL PROTECTED]> writes:
>> SELECT null_frac, n_distinct, most_common_vals, most_common_freqs FROM
>> pg_stats WHERE tablename = 'bars' AND attname='bars_id';
>>  null_frac | n_distinct |   most_common_vals   | most_common_freqs
>> ---++--+---
>>  0 | 14 | {145823,47063,24895} | {0.484667,0.257333,0.242}
>
>> Those 3 values in reality and in the stats account for 98% of the
>> rows. actual distinct values are around 350
>
> So you need to increase the stats target for this column.  With those
> numbers the planner is going to assume that any value that's not one
> of the big three appears about (1 - (0.484667+0.257333+0.242)) / 11
> of the time, or several hundred times in 300K rows.  If n_distinct were
> up around 350 it would be estimating just a dozen or so occurrences,
> which should push the join plan into the shape you want.  It's likely
> that it won't bother to include any more entries in most_common_vals
> no matter how much you raise the target; but a larger sample should
> definitely give it a better clue about n_distinct.
>
>regards, tom lane
>

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

Re: [PERFORM] query planner not using the correct index

2008-08-07 Thread Tom Lane

"Joshua Shanks" <[EMAIL PROTECTED]> writes:
> How do I increase the stats target for just one column?

Look under ALTER TABLE.

regards, tom lane

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

Re: [PERFORM] Query Plan choice with timestamps

2008-08-07 Thread Giorgio Valoti



On 07/ago/08, at 23:01, Tom Lane wrote:


Giorgio Valoti <[EMAIL PROTECTED]> writes:

On 07/ago/08, at 17:50, Tom Lane wrote:

These numbers seem pretty bogus: there is hardly any scenario in
which a
full-table indexscan should be costed as significantly cheaper  
than a

seqscan.  Have you put in silly values for random_page_cost?



No,


I looked at it more closely and realized that the cost discrepancy is
from the evaluation of the function: having to evaluate a SQL or  
plpgsql
function 247736 times more than explains the cost estimate  
differential

compared to a query that involves no function call.  Some experiments
here suggest that it hardly matters whether the query uses indexscan  
or

seqscan because the time is dominated by the function calls anyway.


I see, thank you Tom. Could it be a good idea adding some notes about  
it in ? As you said, since the function call dominates the query cost, in  
this case, I think there’s no point to use an index expression.


Ciao
--
Giorgio Valoti
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

39 matches

Mail list logo