[PERFORM] How to determine cause of performance problem?
Hi,
I must convert an old table into a new table. The conversion goes at ~
100 records per second. Given the fact that I must convert 40 million
records, it takes too much time: more hours than the 48 hour weekend I
have for the conversion;-).
The tables are rather simple: both tables only have a primary key
constraint (of type text) and no other indexes. I only copy 3 columns. I
use Java for the conversion. For the exact code see below.
During the conversion my processor load is almost non existant. The
harddisk throughput is ~ 6 megabyte/second max (measured with iostat).
My platform is Debian Sarge AMD64. My hardware is a Tyan Thunder K8W
2885 motherboard, 2 Opteron 248 processors, 2 GB RAM, a SATA bootdisk
with / and swap, and a 3Ware 9500S-8 RAID-5 controller with 5 attached
SATA disks with /home and /var. /var contains *all* PostgreSQL log and
database files (default Debian installation).
Output of hdparm -Tt /dev/sdb (sdb is the RAID opartition)
/dev/sdb:
Timing cached reads: 1696 MB in 2.00 seconds = 846.86 MB/sec
Timing buffered disk reads: 246 MB in 3.01 seconds = 81.79 MB/sec
I want to determine the cause of my performance problem (if it is one).
1. Is this a performance I can expect?
2. If not, how can I determine the cause?
3. Can I anyhow improve the performance without replacing my hardware,
e.g. by tweaking the software?
4. Is there a Linux (Debian) tool that I can use to benchmark write
performance?
--
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: [EMAIL PROTECTED]
web: www.askesis.nl
The Java code I use for the conversion :
ResultSet resultSet = selectStatement.executeQuery(
select ordernummer, orderdatum, klantnummer from odbc.orders order by
ordernummer);
connection.setAutoCommit(false);
PreparedStatement ordersInsertStatement =
connection.prepareStatement(insert into prototype.orders
(objectid,ordernumber,orderdate,customernumber) values (?,?,?,?));
while( resultSet.next() )
{
if( (++record % 100) == 0){
System.err.println( handling record: + record);
}
// the next line can do 1.000.000 objectId/sec
String orderObjectId = ObjectIdGenerator.newObjectId();
ordersInsertStatement.setString(1,orderObjectId);
ordersInsertStatement.setInt(2,resultSet.getInt(ordernummer));
ordersInsertStatement.setDate(3,resultSet.getDate(orderdatum));
ordersInsertStatement.setInt(4,resultSet.getInt(klantnummer));
ordersInsertStatement.execute();
}
connection.commit();
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [PERFORM] SELECT LIMIT 1 VIEW Performance Issue
Thank you all for your suggestions. I' tried, with some variations too, but still no success. The times given are the best of a few repeated tries on an 8.1 beta 2 db freshly migrated from 8.0.3 on Windows. For reference, only the following gets the record quickly: esdt= explain analyze select PlayerID,AtDate from Player a where PlayerID='0' and AtDate = (select b.AtDate from Player b where b.PlayerID = '0' order by b.PlayerID desc, b.AtDate desc LIMIT 1); Index Scan using pk_player on player a (cost=0.75..4.26 rows=1 width=23) (actual time=0.054..0.057 rows=1 loops=1) Index Cond: (((playerid)::text = '0'::text) AND ((atdate)::text = ($0)::text)) InitPlan - Limit (cost=0.00..0.75 rows=1 width=23) (actual time=0.027..0.028 rows=1 loops=1) - Index Scan Backward using pk_player on player b (cost=0.00..1323.05 rows=1756 width=23) (actual time=0.023..0.023 rows=1 loops=1) Index Cond: ((playerid)::text = '0'::text) Total runtime: 0.132 ms At 02:19 05/09/23, Kevin Grittner wrote: Have you tried the best choice pattern -- where you select the set of candidate rows and then exclude those for which a better choice exists within the set? I often get better results with this pattern than with the alternatives. esdt= explain analyze select PlayerID,AtDate from Player a where PlayerID='0' and not exists (select * from Player b where b.PlayerID = a.PlayerID and b.AtDate a.AtDate); Index Scan using pk_player on player a (cost=0.00..3032.46 rows=878 width=23) (actual time=35.820..35.823 rows=1 loops=1) Index Cond: ((playerid)::text = '0'::text) Filter: (NOT (subplan)) SubPlan - Index Scan using pk_player on player b (cost=0.00..378.68 rows=389 width=776) (actual time=0.013..0.013 rows=1 loops=1743) Index Cond: (((playerid)::text = ($0)::text) AND ((atdate)::text ($1)::text)) Total runtime: 35.950 ms Note that it is faster than the LIMIT 1: esdt= explain analyze select PlayerID,AtDate from Player a where PlayerID='0' and AtDate = (select b.AtDate from Pl ayer b where b.PlayerID = a.PlayerID order by b.PlayerID desc, b.AtDate desc LIMIT 1); Index Scan using pk_player on player a (cost=0.00..2789.07 rows=9 width=23) (actual time=41.366..41.371 rows=1 loops=1) Index Cond: ((playerid)::text = '0'::text) Filter: ((atdate)::text = ((subplan))::text) SubPlan - Limit (cost=0.00..0.83 rows=1 width=23) (actual time=0.013..0.014 rows=1 loops=1743) - Index Scan Backward using pk_player on player b (cost=0.00..970.53 rows=1166 width=23) (actual time=0.008..0.008 rows=1 loops=1743) Index Cond: ((playerid)::text = ($0)::text) Total runtime: 41.490 ms At 02:07 05/09/23, Merlin Moncure wrote: Here is a trick I use sometimes with views, etc. This may or may not be effective to solve your problem but it's worth a shot. Create one small SQL function taking date, etc. and returning the values and define it immutable. Now in-query it is treated like a constant. esdt= create or replace function player_max_atdate (varchar(32)) returns varchar(32) as $$ esdt$ select atdate from player where playerid = $1 order by playerid desc, AtDate desc limit 1; esdt$ $$ language sql immutable; CREATE FUNCTION esdt= create or replace view VCurPlayer3 as select * from Player where AtDate = player_max_atdate(PlayerID); CREATE VIEW esdt= explain analyze select PlayerID,AtDate from VCurPlayer3 where PlayerID='0'; Index Scan using pk_player on player (cost=0.00..1331.83 rows=9 width=23) (actual time=65.434..65.439 rows=1 loops=1) Index Cond: ((playerid)::text = '0'::text) Filter: ((atdate)::text = (player_max_atdate(playerid))::text) Total runtime: 65.508 ms While it says loops=1, the time suggests that it is going through all 1743 records for that PlayerID. I tried to simulate the fast subquery inside the function, but it is taking almost twice as much time: esdt= create or replace function player_max_atdate (varchar(32)) returns varchar(32) as $$ esdt$ select atdate from player a where playerid = $1 and AtDate = (select b.AtDate from Player b esdt$ where b.PlayerID = $1 order by b.PlayerID desc, b.AtDate desc LIMIT 1); esdt$ $$ language sql immutable; CREATE FUNCTION esdt= explain analyze select PlayerID,AtDate from VCurPlayer3 where PlayerID='0'; Index Scan using pk_player on player (cost=0.00..1331.83 rows=9 width=23) (actual time=119.369..119.373 rows=1 loops=1) Index Cond: ((playerid)::text = '0'::text) Filter: ((atdate)::text = (player_max_atdate(playerid))::text) Total runtime: 119.441 ms Adding another LIMIT 1 inside the function makes it even slower: esdt= create or replace function player_max_atdate (varchar(32)) returns varchar(32) as $$ esdt$ select atdate from player where playerid = $1 and AtDate = (select b.AtDate from Player b esdt$ where b.PlayerID = $1 order by b.PlayerID
Re: [PERFORM] How to determine cause of performance problem?
Hi Joost,
why do you convert programmatically? I would do something like
create sequence s_objectid;
insert into
prototype.orders(objectid,ordernumber,orderdate,customernumber)
select next_val('s_objectid'),ordernummer, orderdatum, klantnummer from
odbc.orders
Sounds a lot faster to me.
/Ulrich
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[PERFORM] Releasing memory during External sorting?
I have concerns about whether we are overallocating memory for use in external sorts. (All code relating to this is in tuplesort.c) When we begin a sort we allocate (work_mem | maintenance_work_mem) and attempt to do the sort in memory. If the sort set is too big to fit in memory we then write to disk and begin an external sort. The same memory allocation is used for both types of sort, AFAICS. The external sort algorithm benefits from some memory but not much. Knuth says that the amount of memory required is very low, with a value typically less than 1 kB. I/O overheads mean that there is benefit from having longer sequential writes, so the optimum is much larger than that. I've not seen any data that indicates that a setting higher than 16 MB adds any value at all to a large external sort. I have some indications from private tests that very high memory settings may actually hinder performance of the sorts, though I cannot explain that and wonder whether it is the performance tests themselves that have issues. Does anyone have any clear data that shows the value of large settings of work_mem when the data to be sorted is much larger than memory? (I am well aware of the value of setting work_mem higher for smaller sorts, so any performance data needs to reflect only very large sorts). If not, I would propose that when we move from qsort to tapesort mode we free the larger work_mem setting (if one exists) and allocate only a lower, though still optimal setting for the tapesort. That way the memory can be freed for use by other users or the OS while the tapesort proceeds (which is usually quite a while...). Feedback, please. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] How to determine cause of performance problem?
On Fri, Sep 23, 2005 at 08:49:27AM +0200, Joost Kraaijeveld wrote: 3. Can I anyhow improve the performance without replacing my hardware, e.g. by tweaking the software? It's not clear what your object id generator does. If it's just a sequence, it's not clear that you need this program at all--just use a SELECT INTO and make the object id a SERIAL. If you do need to control the object id or do some other processing before putting the data into the new table, rewrite to use a COPY instead of an INSERT. Mike Stone ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] How to determine cause of performance problem?
On Fri, 2005-09-23 at 05:55 -0400, Michael Stone wrote: It's not clear what your object id generator does. If it's just a sequence, it's not clear that you need this program at all--just use a SELECT INTO and make the object id a SERIAL. It generates a GUID (and no, I do not want to turn this in a discussion about GUIDs). As in the Java code comment: it is not the generation of the GUID that is the problem (that is, I can generate millions of them per second.) If you do need to control the object id or do some other processing before putting the data into the new table, rewrite to use a COPY instead of an INSERT. It is actually the shortest piece of code that gives me a poor performance. The conversion problem is much, much larger and much much more complicated. I suspect that either my hardware is to slow (but then again, see the specs), or my Debian is to slow, or my PostgreSQL settings are wrong. But I have no clue where to begin with determining the bottleneck (it even may be a normal performance for all I know: I have no experience with converting such (large) database). Any suggestions? -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] How to determine cause of performance problem?
On Fri, Sep 23, 2005 at 12:21:15PM +0200, Joost Kraaijeveld wrote: On Fri, 2005-09-23 at 05:55 -0400, Michael Stone wrote: It's not clear what your object id generator does. If it's just a sequence, it's not clear that you need this program at all--just use a SELECT INTO and make the object id a SERIAL. It generates a GUID (and no, I do not want to turn this in a discussion about GUIDs). As in the Java code comment: it is not the generation of the GUID that is the problem (that is, I can generate millions of them per second.) I didn't say it was, did I? If you use a SELECT INTO instead of SELECTing each record and then reINSERTing it you avoid a round trip latency for each row. There's a reason I said if it's just a sequence. If you do need to control the object id or do some other processing before putting the data into the new table, rewrite to use a COPY instead of an INSERT. It is actually the shortest piece of code that gives me a poor performance. The conversion problem is much, much larger and much much more complicated. Ok, that's great, but you didn't respond to the suggestion of using COPY INTO instead of INSERT. But I have no clue where to begin with determining the bottleneck (it even may be a normal performance for all I know: I have no experience with converting such (large) database). Any suggestions? Respond to the first suggestion? Mike Stone ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] SELECT LIMIT 1 VIEW Performance Issue
On Thu, 2005-09-22 at 18:40 +0800, K C Lau wrote: esdt= explain analyze select PlayerID,AtDate from Player a where PlayerID='0' and AtDate = (select b.AtDate from Player b where b.PlayerID = a.PlayerID order by b.PlayerID desc, b.AtDate desc LIMIT 1); I think you should try: select distinct on (PlayerID) PlayerID,AtDate from Player a where PlayerID='0' order by PlayerId, AtDate Desc; Does that work for you? Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] How to determine cause of performance problem?
On 23-9-2005 13:05, Michael Stone wrote: On Fri, Sep 23, 2005 at 12:21:15PM +0200, Joost Kraaijeveld wrote: Ok, that's great, but you didn't respond to the suggestion of using COPY INTO instead of INSERT. But I have no clue where to begin with determining the bottleneck (it even may be a normal performance for all I know: I have no experience with converting such (large) database). Any suggestions? Respond to the first suggestion? Another suggestion: How many indexes and constraints are on the new table? Drop all of them and recreate them once the table is filled. Of course that only works if you know your data will be ok (which is normal for imports of already conforming data like database dumps of existing tables). This will give major performance improvements, if you have indexes and such on the new table. Best regards, Arjen ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] tsearch2 seem very slow
On Fri, 23 Sep 2005, Ahmad Fajar wrote:
Hi Oleg,
I didn't deny on the third repeat or more, it can reach 600 msec. It is
only because the result still in postgres cache, but how about in the first
run? I didn't dare, the values is un-acceptable. Because my table will grows
rapidly, it's about 10 rows per-week. And the visitor will search
anything that I don't know, whether it's the repeated search or new search,
or whether it's in postgres cache or not.
if you have enoush shared memory postgresql will keep index pages there.
I just compare with http://www.postgresql.org, the search is quite fast, and
I don't know whether the site uses tsearch2 or something else. But as fas as
I know, if the rows reach 100 milion (I have try for 200 milion rows and it
seem very slow), even if don't use tsearch2, only use simple query like:
select f1, f2 from table1 where f2='blabla',
and f2 is indexes, my postgres still slow on the first time, about 10 sec.
because of this I tried something brand new to fullfill my needs. I have
used fti, and tsearch2 but still slow.
I don't know what's going wrong with my postgres, what configuration must I
do to perform the query get fast result. Or must I use enterprisedb 2005 or
pervasive postgres (both uses postgres), I don't know very much about these
two products.
you didn't show us your configuration (hardware,postgresql and tsearch2),
explain analyze of your queries, so we can't help you.
How big is your database, tsearch2 index size ?
Regards,
ahmad fajar
-Original Message-
From: Oleg Bartunov [mailto:[EMAIL PROTECTED]
Sent: Jumat, 23 September 2005 14:36
To: Ahmad Fajar
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] tsearch2 seem very slow
Ahmad,
how fast is repeated runs ? First time system could be very slow.
Also, have you checked my page
http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_Notes
and some info about tsearch2 internals
http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_internals
Oleg
On Thu, 22 Sep 2005, Ahmad Fajar wrote:
I have about 419804 rows in my article table. I have installed tsearch2
and
its gist index correctly.
My table structure is:
CREATE TABLE tbarticles
(
articleid int4 NOT NULL,
title varchar(250),
mediaid int4,
datee date,
content text,
contentvar text,
mmcol float4 NOT NULL,
sirkulasi float4,
page varchar(10),
tglisidata date,
namapc varchar(12),
usere varchar(12),
file_pdf varchar(255),
file_pdf2 varchar(50),
kolom int4,
size_jpeg int4,
journalist varchar(120),
ratebw float4,
ratefc float4,
fti tsvector,
CONSTRAINT pk_tbarticles PRIMARY KEY (articleid)
) WITHOUT OIDS;
Create index fti_idx1 on tbarticles using gist (fti);
Create index fti_idx2 on tbarticles using gist (datee, fti);
But when I search something like:
Select articleid, title, datee from tbarticles where fti @@
to_tsquery('susilobambangyudhoyonojusufkalla');
It takes about 30 sec. I run explain analyze and the index is used
correctly.
Then I try multi column index to filter by date, and my query something
like:
Select articleid, title, datee from tbarticles where fti @@
to_tsquery('susilobambangyudhoyonojusufkalla') and datee =
'2002-01-01'
and datee = current_date
An it still run about 25 sec. I do run explain analyze and my multicolumn
index is used correctly.
This is not acceptable if want to publish my website if the search took
very
longer.
I have run vacuum full analyze before doing such query. What going wrong
with my query?? Is there any way to make this faster?
I have try to tune my postgres configuration, but it seem helpless. My
linux
box is Redhat 4 AS, and
the hardware: 2 GB RAM DDR 400, 2x200 GB Serial ATA 7200RPM and configure
as
RAID0 (just for postgres data), my sistem run at EIDE 80GB 7200 RPM.
Please.help.help.
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [PERFORM] SELECT LIMIT 1 VIEW Performance Issue
At 19:15 05/09/23, Simon Riggs wrote: select distinct on (PlayerID) PlayerID,AtDate from Player a where PlayerID='0' order by PlayerId, AtDate Desc; Does that work for you? Best Regards, Simon Riggs esdt= explain analyze select distinct on (PlayerID) PlayerID,AtDate from Player a where PlayerID='0' order by PlayerId, AtDate Desc; Unique (cost=1417.69..1426.47 rows=2 width=23) (actual time=31.231..36.609 rows=1 loops=1) - Sort (cost=1417.69..1422.08 rows=1756 width=23) (actual time=31.129..32.473 rows=1743 loops=1) Sort Key: playerid, atdate - Index Scan using pk_player on player a (cost=0.00..1323.05 rows=1756 width=23) (actual time=0.035..6.575 rows=1743 loops=1) Index Cond: ((playerid)::text = '0'::text) Total runtime: 36.943 ms The sort was eliminated with: order by PlayerId Desc, AtDate Desc: esdt= explain analyze select distinct on (PlayerID) PlayerID,AtDate from Player a where PlayerID='0' order by PlayerId Desc, AtDate Desc; Unique (cost=0.00..1327.44 rows=2 width=23) (actual time=0.027..8.438 rows=1 loops=1) - Index Scan Backward using pk_player on player a (cost=0.00..1323.05 rows=1756 width=23) (actual time=0.022..4.950 rows=1743 loops=1) Index Cond: ((playerid)::text = '0'::text) Total runtime: 8.499 ms That is the fastest of all queries looping the 1743 rows. I do get the desired result by adding LIMIT 1: esdt= explain analyze select distinct on (PlayerID) PlayerID,AtDate from Player a where PlayerID='0' order by PlayerId Desc, AtDate Desc LIMIT 1; Limit (cost=0.00..663.72 rows=1 width=23) (actual time=0.032..0.033 rows=1 loops=1) - Unique (cost=0.00..1327.44 rows=2 width=23) (actual time=0.028..0.028 rows=1 loops=1) - Index Scan Backward using pk_player on player a (cost=0.00..1323.05 rows=1756 width=23) (actual time=0.022..0.022 rows=1 loops=1) Index Cond: ((playerid)::text = '0'::text) Total runtime: 0.094 ms However, when I use that within a function in a view, it is slow again: esdt= create or replace function player_max_atdate (varchar(32)) returns varchar(32) as $$ esdt$ select distinct on (PlayerID) AtDate from player where PlayerID= $1 order by PlayerID desc, AtDate desc limit 1; esdt$ $$ language sql immutable; CREATE FUNCTION esdt= create or replace view VCurPlayer3 as select * from Player where AtDate = player_max_atdate(PlayerID); CREATE VIEW esdt= explain analyze select PlayerID,AtDate from VCurPlayer3 where PlayerID='0'; Index Scan using pk_player on player (cost=0.00..1331.83 rows=9 width=23) (actual time=76.660..76.664 rows=1 loops=1) Index Cond: ((playerid)::text = '0'::text) Filter: ((atdate)::text = (player_max_atdate(playerid))::text) Total runtime: 76.716 ms Why wouldn't the function get the row as quickly as the direct sql does? Best regards, KC. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] SELECT LIMIT 1 VIEW Performance Issue
At 02:07 05/09/23, Merlin Moncure wrote:
Here is a trick I use sometimes with views, etc. This may or may
not
be
effective to solve your problem but it's worth a shot. Create
one
small
SQL function taking date, etc. and returning the values and
define it
immutable. Now in-query it is treated like a constant.
esdt= create or replace function player_max_atdate (varchar(32))
returns
varchar(32) as $$
esdt$ select atdate from player where playerid = $1 order by
playerid
desc, AtDate desc limit 1;
esdt$ $$ language sql immutable;
Can you time just the execution of this function and compare vs. pure
SQL version? If the times are different, can you do a exaplain analyze
of a prepared version of above?
prepare test(character varying) as select atdate from player where
playerid = $1 order by playerid desc, AtDate desc limit 1;
explain analyze execute test('0');
CREATE FUNCTION
esdt= create or replace view VCurPlayer3 as select * from Player
where
AtDate = player_max_atdate(PlayerID);
CREATE VIEW
This is wrong, it should have been
create or replace view VCurPlayer3 as select *,
player_max_atdate(PlayerID) as max_date from Player;
I did a test on a table with 124k records and a two part key, ID date.
esp# select count(*) from parts_order_file;
count
124158
(1 row)
esp=# select count(*) from parts_order_file where pr_dealer_no =
'000500';
count
---
27971
(1 row)
created same function, view v, etc.
esp=# explain analyze select * from v where pr_dealer_no = '000500'
limit 1;
QUERY PLAN
Limit (cost=0.00..3.87 rows=1 width=10) (actual time=1.295..1.297
rows=1 loops=1)
- Index Scan using parts_order_file_pr_dealer_no_key on
parts_order_file (cost=0.00..109369.15
rows=28226 width=10) (actual time=1.287..1.287 rows=1 loops=1)
Index Cond: (pr_dealer_no = '000500'::bpchar)
Total runtime: 1.413 ms
(4 rows)
Something is not jiving here. However, if the server plan still does
not come out correct, try the following (p.s. why is function returning
varchar(32) and not date?):
create or replace function player_max_atdate (varchar(32)) returns date
as
$$
DECLARE
player_record record;
return date date;
BEGIN
for player_record in execute
'select atdate from player where playerid = \'' || $1 || '\'
order by playerid desc, AtDate desc limit 1;' loop
return_date = player_record.atdate;
end loop;
return return_date;
END;
$ language plpgsql immutable;
Merlin
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [PERFORM] How to determine cause of performance problem?
On Fri, 2005-09-23 at 13:19 +0200, Arjen van der Meijden wrote: Another suggestion: How many indexes and constraints are on the new table? As mentioned in the first mail: in this tables only primary key constraints, no other indexes or constraints. Drop all of them and recreate them once the table is filled. Of course that only works if you know your data will be ok (which is normal for imports of already conforming data like database dumps of existing tables). This will give major performance improvements, if you have indexes and such on the new table. I will test this a for perfomance improvement, but still, I wonder if ~ 100 inserts/second is a reasonable performance for my software/hardware combination. -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] optimization downgrade perfomance?
eVl [EMAIL PROTECTED] writes: When executing this SELECT (see SELECT.A above) it executes in about 700 ms, but when I want wipe out all info about local traffic, with query like this: SELECT * FROM ( SELECT.A ) a WHERE type = 'global'; It executes about 1 ms - more then 10 TIMES SLOWER! Why this could be? You tell us --- let's see EXPLAIN ANALYZE results for both cases. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] How to determine cause of performance problem?
On Fri, 2005-09-23 at 07:05 -0400, Michael Stone wrote: On Fri, Sep 23, 2005 at 12:21:15PM +0200, Joost Kraaijeveld wrote: On Fri, 2005-09-23 at 05:55 -0400, Michael Stone wrote: I didn't say it was, did I? No, you did not. But only last week someon'es head was (luckely for him only virtually) almost chopped off for suggesting the usage of GUIDs ;-) Ok, that's great, but you didn't respond to the suggestion of using COPY INTO instead of INSERT. Part of the code I left out are some data conversions (e.g. from path-to-file to blob, from text to date (not castable because of the homebrew original format)). I don't believe that I can do these in a SQL statement, can I (my knowledge of SQL as a langage is not that good)? . However I will investigate if I can do the conversion in two steps and check if it is faster. But still, I wonder if ~100 inserts/second is a reasonable performance for my software/hardware combination. -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] How to determine cause of performance problem?
On 23-9-2005 15:35, Joost Kraaijeveld wrote: On Fri, 2005-09-23 at 13:19 +0200, Arjen van der Meijden wrote: Drop all of them and recreate them once the table is filled. Of course that only works if you know your data will be ok (which is normal for imports of already conforming data like database dumps of existing tables). This will give major performance improvements, if you have indexes and such on the new table. I will test this a for perfomance improvement, but still, I wonder if ~ 100 inserts/second is a reasonable performance for my software/hardware combination. For the hardware: no, I don't think it is for such a simple table/small recordsize. I did a few batch-inserts with indexes on tables and was very disappointed about the time it took. But with no indexes and constraints left it flew and the table of 7 million records (of 3 ints and 2 bigints) was imported in 75 seconds, on a bit simpler hardware. That was done using a simple pg_dump-built sql-file which was then fed to psql as input. And of course that used the local unix socket, not the local network interface (I don't know which jdbc takes). But generating a single transaction (as you do) with inserts shouldn't be that much slower. So I don't think its your hardware, nor your postgresql, although a bit extra maintenance_work_mem may help, if you haven't touched that. Leaving the queries, the application and the driver. But I don't have that much experience with jdbc and postgresql-performance. In php I wouldn't select all the 40M records at once, the resultset would be in the clients-memory and that may actually cause trouble. But I don't know how that is implemented in JDBC, it may of course be using cursors and it would be less of a problem than perhaps. You could try writing the inserts to file and see how long that takes, to eliminate the possibility of your application being slow on other parts than the inserting of data. If that is fast enough, a last resort may be to write a csv-file from java and use that with a copy-statement in psql ;) Best regards, Arjen ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] VACUUM FULL vs CLUSTER
On Tue, Sep 20, 2005 at 14:53:19 -0400, Markus Benne [EMAIL PROTECTED] wrote: I have a table that is purged by 25% each night. I'd like to do a vacuum nightly after the purge to reclaim the space, but I think I'll still need to do a vacuum full weekly. Would there be any benefit to doing a cluster instead of the vacuum? If you have a proper FSM setting you shouldn't need to do vacuum fulls (unless you have an older version of postgres where index bloat might be an issue). ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] How to determine cause of performance problem?
Joost Kraaijeveld [EMAIL PROTECTED] writes: I will test this a for perfomance improvement, but still, I wonder if ~ 100 inserts/second is a reasonable performance for my software/hardware combination. Is the client code running on the same machine as the database server? If not, what's the network delay and latency between them? The major problem you're going to have here is at least one network round trip per row inserted --- possibly more, if the jdbc driver is doing helpful stuff behind your back like starting/committing transactions. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] How to determine cause of performance problem?
On Fri, 2005-09-23 at 10:33 -0400, Tom Lane wrote: Is the client code running on the same machine as the database server? If not, what's the network delay and latency between them? Yes, it is running on the same machine. The major problem you're going to have here is at least one network round trip per row inserted --- possibly more, if the jdbc driver is doing helpful stuff behind your back like starting/committing transactions. OK, I will look into that. But do you maybe know a pointer to info, or tools that can measure, what my machine is doing during all the time it is doing nothing? Something like the performance monitor in Windows but than for Linux? -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] How to determine cause of performance problem?
Joost Kraaijeveld [EMAIL PROTECTED] writes: But do you maybe know a pointer to info, or tools that can measure, what my machine is doing during all the time it is doing nothing? Something like the performance monitor in Windows but than for Linux? top, vmstat, iostat, sar, strace, oprofile, ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] SELECT LIMIT 1 VIEW Performance Issue
Dear Merlin,
At 20:34 05/09/23, Merlin Moncure wrote:
Can you time just the execution of this function and compare vs. pure
SQL version? If the times are different, can you do a exaplain analyze
of a prepared version of above?
esdt= prepare test(character varying) as select atdate from player where
esdt- playerid = $1 order by playerid desc, AtDate desc limit 1;
PREPARE
esdt= explain analyze execute test('0');
Limit (cost=0.00..0.83 rows=1 width=23) (actual time=0.032..0.033 rows=1
loops=1)
- Index Scan Backward using pk_player on player (cost=0.00..970.53
rows=1166 width=23) (actual time=0.027..0.027 rows=1 loops=1)
Index Cond: ((playerid)::text = ($1)::text)
Total runtime: 0.088 ms
The prepared SQL timing is similar to that of a direct SQL.
esdt= create or replace view VCurPlayer3 as select * from Player where
AtDate = player_max_atdate(PlayerID);
This is wrong, it should have been
create or replace view VCurPlayer3 as select *,
player_max_atdate(PlayerID) as max_date from Player;
Your suggestion returns all the records plus a max AtDate column for each
PlayerID.
What I want to get with the view is the record that has the max value of
AtDate for each PlayerID.
The AtDate is a varchar(23) field containing a string date of format
'mmddhh', not the SQL Date field. Sorry if that confused you.
Something is not jiving here. However, if the server plan still does
not come out correct, try the following (p.s. why is function returning
varchar(32) and not date?):
esdt= create or replace function player_max_atdate (varchar(32)) returns
varchar(32) as $$
esdt$ DECLARE
esdt$player_record record;
esdt$return_date varchar(32);
esdt$ BEGIN
esdt$for player_record in execute
esdt$'select atdate from player where playerid = \'' || $1 ||
'\' order by playerid desc, AtDate desc limit 1;' loop
esdt$return_date = player_record.atdate;
esdt$end loop;
esdt$return return_date;
esdt$ END;
esdt$ $$ language plpgsql immutable;
CREATE FUNCTION
esdt= create or replace view VCurPlayer3 as select * from Player where
AtDate = player_max_atdate(PlayerID);
CREATE VIEW
esdt= explain analyze select PlayerID,AtDate from VCurPlayer3 where
PlayerID='0';
Index Scan using pk_player on player (cost=0.00..1331.83 rows=9
width=23) (actual time=849.021..849.025 rows=1 loops=1)
Index Cond: ((playerid)::text = '0'::text)
Filter: ((atdate)::text = (player_max_atdate(playerid))::text)
Total runtime: 849.078 ms
Your suggested plpgsql function seems to be even slower, with a best time
of 849 ms after several tries. Is that expected?
Thanks again and best regards,
KC.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [PERFORM] How to determine cause of performance problem?
On Fri, Sep 23, 2005 at 03:49:25PM +0200, Joost Kraaijeveld wrote: On Fri, 2005-09-23 at 07:05 -0400, Michael Stone wrote: Ok, that's great, but you didn't respond to the suggestion of using COPY INTO instead of INSERT. Part of the code I left out are some data conversions (e.g. from path-to-file to blob, from text to date (not castable because of the homebrew original format)). I don't believe that I can do these in a SQL statement, can I (my knowledge of SQL as a langage is not that good)? . However I will investigate if I can do the conversion in two steps and check if it is faster. I'm not sure what you're trying to say. You're currently putting rows into the table by calling INSERT INTO for each row. The sample code you send could be rewritten to use COPY INTO instead. For bulk inserts like you're doing, the copy approach will be a lot faster. Instead of inserting one row, waiting for a reply, and inserting the next row, you just cram data down a pipe to the server. See: http://www.postgresql.org/docs/8.0/interactive/sql-copy.html http://www.faqs.org/docs/ppbook/x5504.htm Mike Stone ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] slow database, queries accumulating
Hi We are experiencing consistent slowness on the database for one application. This is more a reporting type of application, heavy on the bytea data type usage (gets rendered into PDFs in the app server). A lot of queries, mostly selects and a few random updates, get accumulated on the server with increasing volume of users on the application. Below is a snapshot of top, with about 80 selects and 3 or 4 updates. Things get better eventually if I cancel (SIGINT) some of the oldest queries. I also see a few instances of shared locks not being granted during this timeI dont even see high iowait or memory starvation during these times, as indicated by top. -bash-2.05b$ psql -c select * from pg_locks; dbname | grep f | | 77922136 | 16761 | ShareLock | f We (development) are looking into the query optimization (explain analyze, indexes, etc), and my understanding is that the queries when run for explain analyze execute fast, but during busy times, they become quite slow, taking from a few seconds to a few minutes to execute. I do see in the log that almost all queries do have either ORDER BY, or GROUP BY, or DISTINCT. Does it hurt to up the sort_mem to 3MB or 4MB? Should I up the effective_cache_size to 5 or 6GB? The app is does not need a lot of connections on the database, I can reduce it down from 600. Based on the description above and the configuration below does any thing appear bad in config? Is there anything I can try in the configuration to improve performance? The database size is about 4GB. This is PG 7.4.7, RHAS3.0 (u5), Local 4 spindle RAID10 (15KRPM), and logs on a separate set of drives, RAID10. 6650 server, 4 x XEON, 12GB RAM. Vacuum is done every night, full vacuum done once a week. I had increased the shared_buffers and sort_memory recently, which didnt help. Thanks, Anjan 10:44:51 up 14 days, 13:38, 2 users, load average: 0.98, 1.14, 1.12 264 processes: 257 sleeping, 7 running, 0 zombie, 0 stopped CPU states: cpu user nice system irq softirq iowait idle total 14.4% 0.0% 7.4% 0.0% 0.0% 0.0% 77.9% cpu00 15.7% 0.0% 5.7% 0.0% 0.1% 0.0% 78.2% cpu01 15.1% 0.0% 7.5% 0.0% 0.0% 0.1% 77.0% cpu02 10.5% 0.0% 5.9% 0.0% 0.0% 0.0% 83.4% cpu03 9.9% 0.0% 5.9% 0.0% 0.0% 0.0% 84.0% cpu04 7.9% 0.0% 3.7% 0.0% 0.0% 0.0% 88.2% cpu05 19.3% 0.0% 12.3% 0.0% 0.0% 0.0% 68.3% cpu06 20.5% 0.0% 9.5% 0.0% 0.0% 0.1% 69.7% cpu07 16.1% 0.0% 8.5% 0.0% 0.1% 0.3% 74.7% Mem: 12081736k av, 7881972k used, 4199764k free, 0k shrd, 82372k buff 4823496k actv, 2066260k in_d, 2036k in_c Swap: 4096532k av, 0k used, 4096532k free 6888900k cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND 16773 postgres 15 0 245M 245M 240M S 0.0 2.0 1:16 7 postmaster 16880 postgres 15 0 245M 245M 240M S 0.1 2.0 0:49 6 postmaster 16765 postgres 15 0 245M 245M 240M S 0.0 2.0 1:16 0 postmaster 16825 postgres 15 0 245M 245M 240M S 0.0 2.0 1:02 5 postmaster 16774 postgres 15 0 245M 245M 240M S 0.1 2.0 1:16 0 postmaster 16748 postgres 15 0 245M 245M 240M S 0.0 2.0 1:19 5 postmaster 16881 postgres 15 0 245M 245M 240M S 0.1 2.0 0:50 7 postmaster 16762 postgres 15 0 245M 245M 240M S 0.0 2.0 1:14 4 postmaster max_connections = 600 shared_buffers = 3 #=234MB, up from 21760=170MB min 16, at least max_connections*2, 8KB each sort_mem = 2048 # min 64, size in KB vacuum_mem = 32768 # up from 16384 min 1024, size in KB # - Free Space Map - #max_fsm_pages = 2 # min max_fsm_relations*16, 6 bytes each #max_fsm_relations = 1000 # min 100, ~50 bytes each #fsync = true # turns forced synchronization on or off #wal_sync_method = fsync # the default varies across platforms: # fsync, fdatasync, open_sync, or open_datasync #wal_buffers = 8 # min 4, 8KB each # - Checkpoints - checkpoint_segments = 125 # in logfile segments, min 1, 16MB each checkpoint_timeout = 600 # range 30-3600, in seconds #checkpoint_warning = 30 # 0 is off, in seconds #commit_delay = 0 # range 0-10, in microseconds #commit_siblings = 5 # range 1-1000 # - Planner Method Enabling - #enable_hashagg = true #enable_hashjoin = true #enable_indexscan = true #enable_mergejoin = true #enable_nestloop = true #enable_seqscan = true #enable_sort = true #enable_tidscan = true # - Planner Cost Constants - effective_cache_size = 262144 # =2GB typically 8KB each #random_page_cost = 4 # units are one sequential page fetch cost #cpu_tuple_cost = 0.01 # (same) #cpu_index_tuple_cost = 0.001 # (same) #cpu_operator_cost = 0.0025 # (same) # - Genetic Query Optimizer - #geqo = true #geqo_threshold = 11 #geqo_effort = 1 #geqo_generations = 0 #geqo_pool_size = 0 # default based on tables in statement, # range 128-1024 #geqo_selection_bias = 2.0 # range 1.5-2.0 # - Other Planner Options - #default_statistics_target = 10 # range 1-1000 #from_collapse_limit = 8 #join_collapse_limit = 8 # 1 disables collapsing of explicit JOINs
Re: [PERFORM] VACUUM FULL vs CLUSTER
Bruno Wolff III mentioned : = If you have a proper FSM setting you shouldn't need to do vacuum fulls = (unless you have an older version of postgres where index bloat might = be an issue). What version of postgres was the last version that had the index bloat problem? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] tsearch2 seem very slow
Hi Oleg,
For single index I try this query:
explain analyze
select articleid, title, datee from articles
where fti @@ to_tsquery('bankindonesia');
analyze result:
Index Scan using fti_idx on articles (cost=0.00..862.97 rows=420 width=51)
(actual time=0.067..183761.324 rows=46186 loops=1)
Index Cond: (fti @@ '\'bank\' \'indonesia\''::tsquery)
Total runtime: 183837.826 ms
And for multicolumn index I try this query:
explain analyze
select articleid, title, datee from articles
where fti @@ to_tsquery('bankmega');
analyze result:
Index Scan using articles_x1 on articles (cost=0.00..848.01 rows=410
width=51) (actual time=52.204..37914.135 rows=1841 loops=1)
Index Cond: ((datee = '2002-01-01'::date) AND (datee =
('now'::text)::date) AND (fti @@ '\'bank\' \'mega\''::tsquery))
Total runtime: 37933.757 ms
The table structure is as mention on the first talk. If you wanna know how
much table in my database, it's about 100 tables or maybe more. Now I
develop the version 2 of my web application, you can take a look at:
http://www.mediatrac.net, so it will hold many datas. But the biggest table
is article's table. On develop this version 2 I just use half data of the
article's table (about 419804 rows). May be if I import all of the article's
table data it will have 1 million rows. The article's table grows rapidly,
about 10 rows per-week. My developing database size is 28 GB (not real
database, coz I still develop the version 2 and I use half of the data for
play around). I just wanna to perform quick search (fulltext search) on my
article's table not other table. On version 1, the current running version I
use same hardware spesification as mention below, but there is no fulltext
search. So I develop the new version with new features, new interface and
include the fulltext search.
I do know, if the application finish, I must use powerfull hardware. But how
can I guarantee the application will run smooth, if I do fulltext search on
419804 rows in a table it took a long time to get the result.
Could you or friends in this maling-list help meplz..plzz
Tsearch2 configuration:
-
I use default configuration, english stop word file as tsearch2 provide,
stem dictionary as default (coz I don't know how to configure and add new
data to stem dictionary) and I add some words to the english stop word file.
Postgresql configuration
-
max_connections = 32
shared_buffers = 32768
sort_mem = 8192
vacuum_mem = 65536
work_mem = 16384
maintenance_work_mem = 65536
max_fsm_pages = 3
max_fsm_relations = 1000
max_files_per_process = 10
checkpoint_segments = 15
effective_cache_size = 192000
random_page_cost = 2
geqo = true
geqo_threshold = 50
geqo_effort = 5
geqo_pool_size = 0
geqo_generations = 0
geqo_selection_bias = 2.0
from_collapse_limit = 10
join_collapse_limit = 15
OS configuration:
--
I use Redhat 4 AS, kernel 2.6.9-11
kernel.shmmax=1073741824
kernel.sem=250 32000 100 128
fs.aio-max-nr=5242880
the server I configure just only for postgresql, no other service is running
like: www, samba, ftp, email, firewall
hardware configuration:
Motherboard ASUS P5GD1
Processor P4 3,2 GHz
Memory 2 GB DDR 400,
2x200 GB Serial ATA 7200 RPM UltraATA/133, configure as RAID0 for postgresql
data and the partition is EXT3
1x80 GB EIDE 7200 RPM configure for system and home directory and the
partiton is EXT3
Did I miss something?
Regards,
ahmad fajar
-Original Message-
From: Oleg Bartunov [mailto:[EMAIL PROTECTED]
Sent: Jumat, 23 September 2005 18:26
To: Ahmad Fajar
Cc: pgsql-performance@postgresql.org
Subject: RE: [PERFORM] tsearch2 seem very slow
On Fri, 23 Sep 2005, Ahmad Fajar wrote:
Hi Oleg,
I didn't deny on the third repeat or more, it can reach 600 msec. It is
only because the result still in postgres cache, but how about in the
first
run? I didn't dare, the values is un-acceptable. Because my table will
grows
rapidly, it's about 10 rows per-week. And the visitor will search
anything that I don't know, whether it's the repeated search or new
search,
or whether it's in postgres cache or not.
if you have enoush shared memory postgresql will keep index pages there.
I just compare with http://www.postgresql.org, the search is quite fast,
and
I don't know whether the site uses tsearch2 or something else. But as fas
as
I know, if the rows reach 100 milion (I have try for 200 milion rows and
it
seem very slow), even if don't use tsearch2, only use simple query like:
select f1, f2 from table1 where f2='blabla',
and f2 is indexes, my postgres still slow on the first time, about 10
sec.
because of this I tried something brand new to fullfill my needs. I have
used fti, and tsearch2 but still slow.
I don't know what's going wrong with my postgres, what configuration must
I
do to perform the query get fast result. Or must I use enterprisedb 2005
or
pervasive
Re: [PERFORM] VACUUM FULL vs CLUSTER
On Fri, Sep 23, 2005 at 06:16:44PM +0200, Stef wrote: Bruno Wolff III mentioned : = If you have a proper FSM setting you shouldn't need to do vacuum fulls = (unless you have an older version of postgres where index bloat might = be an issue). What version of postgres was the last version that had the index bloat problem? The worst problems were solved in 7.4. There are problems in certain limited circumstances even with current releases. -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34 The ability to monopolize a planet is insignificant next to the power of the source ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] VACUUM FULL vs CLUSTER
On Fri, Sep 23, 2005 at 18:16:44 +0200, Stef [EMAIL PROTECTED] wrote: Bruno Wolff III mentioned : = If you have a proper FSM setting you shouldn't need to do vacuum fulls = (unless you have an older version of postgres where index bloat might = be an issue). What version of postgres was the last version that had the index bloat problem? You can check the release notes to be sure, but my memory is that the unbounded bloat problem was fixed in 7.4. There still are usage patterns that can result in bloating, but it is limited to some constant multiplier of the minimum index size. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Query slower on 8.0.3 (Windows) vs 7.3 (cygwin)
After further investigation I have found that the reason why the query is slower on 8.0.3 is that the hash and hash joins are slower on the 8.0.3. So the question comes down to : Why are hash and hash joins slower? Is this a postgres configuration setting that I am missing? Is the locale still screwing me up? I have set the locale to 'C' without any improvements. Is it because the column type is a varchar that the hash is slower? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] VACUUM FULL vs CLUSTER
Bruno Wolff III mentioned : = = If you have a proper FSM setting you shouldn't need to do vacuum fulls = = (unless you have an older version of postgres where index bloat might = = be an issue). Thanks Alvaro and Bruno I just want to clarify something that I also couldn't find a clear cut answer for before. What is a proper fsm setting? Someone told me to set max_fsm_relations to the number of relations in pg_class plus a few more to allow for new relations. And max_fsm_pages to the number of rows in the biggest table I want to vacuum, plus a few 1000's for extra room? Where does this free space map sit? On the disk somewhere, or in memory, or both. I once set the max_fsm_pages very high by mistake, and postgres then started up and used a _lot_ of shared memory, and I had to increase shmmax. Is there abything to watch out for when bumping this setting up a lot? Kind Regards Stefan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Releasing memory during External sorting?
Ron Peacetree [EMAIL PROTECTED] writes: 2= No optimal external sorting algorithm should use more than 2 passes. 3= Optimal external sorting algorithms should use 1 pass if at all possible. A comparison-based sort must use at least N log N operations, so it would appear to me that if you haven't got approximately log N passes then your algorithm doesn't work. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Releasing memory during External sorting?
operations != passes. If you were clever, you could probably write a modified bubble-sort algorithm that only made 2 passes. A pass is a disk scan, operations are then performed (hopefully in memory) on what you read from the disk. So there's no theoretical log N lower-bound on the number of disk passes. Not that I have anything else useful to add to this discussion, just a tidbit I remembered from my CS classes back in college :) -- Mark On Fri, 2005-09-23 at 13:17 -0400, Tom Lane wrote: Ron Peacetree [EMAIL PROTECTED] writes: 2= No optimal external sorting algorithm should use more than 2 passes. 3= Optimal external sorting algorithms should use 1 pass if at all possible. A comparison-based sort must use at least N log N operations, so it would appear to me that if you haven't got approximately log N passes then your algorithm doesn't work. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] VACUUM FULL vs CLUSTER
[EMAIL PROTECTED] (Stef) writes: Bruno Wolff III mentioned : = If you have a proper FSM setting you shouldn't need to do vacuum fulls = (unless you have an older version of postgres where index bloat might = be an issue). What version of postgres was the last version that had the index bloat problem? I believe that was fixed in 7.3; it was certainly resolved by 7.4... -- (format nil [EMAIL PROTECTED] cbbrowne cbbrowne.com) http://www.ntlug.org/~cbbrowne/spiritual.html MICROS~1 has brought the microcomputer OS to the point where it is more bloated than even OSes from what was previously larger classes of machines altogether. This is perhaps Bill's single greatest accomplishment. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Releasing memory during External sorting?
Mark Lewis [EMAIL PROTECTED] writes: operations != passes. If you were clever, you could probably write a modified bubble-sort algorithm that only made 2 passes. A pass is a disk scan, operations are then performed (hopefully in memory) on what you read from the disk. So there's no theoretical log N lower-bound on the number of disk passes. Given infinite memory that might be true, but I don't think I believe it for limited memory. If you have room for K tuples in memory then it's impossible to perform more than K*N useful comparisons per pass (ie, as each tuple comes off the disk you can compare it to all the ones currently in memory; anything more is certainly redundant work). So if K logN it's clearly not gonna work. It's possible that you could design an algorithm that works in a fixed number of passes if you are allowed to assume you can hold O(log N) tuples in memory --- and in practice that would probably work fine, if the constant factor implied by the O() isn't too big. But it's not really solving the general external-sort problem. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Releasing memory during External sorting?
Yep. Also, bear in mind that the lg(n!)= ~ nlgn - n lower bound on the number of comparisions: a= says nothing about the amount of data movement used. b= only holds for generic comparison based sorting algorithms. As Knuth says (vol 3, p180), Distribution Counting sorts without ever comparing elements to each other at all, and so does Radix Sort. Similar comments can be found in many algorithms texts. Any time we know that the range of the data to be sorted is substantially restricted compared to the number of items to be sorted, we can sort in less than O(lg(n!)) time. DB fields tend to take on few values and are therefore substantially restricted. Given the proper resources and algorithms, O(n) sorts are very plausible when sorting DB records. All of the fastest external sorts of the last decade or so take advantage of this. Check out that URL I posted. Ron -Original Message- From: Mark Lewis [EMAIL PROTECTED] Sent: Sep 23, 2005 1:43 PM To: Tom Lane [EMAIL PROTECTED] Subject: Re: [PERFORM] Releasing memory during External sorting? operations != passes. If you were clever, you could probably write a modified bubble-sort algorithm that only made 2 passes. A pass is a disk scan, operations are then performed (hopefully in memory) on what you read from the disk. So there's no theoretical log N lower-bound on the number of disk passes. Not that I have anything else useful to add to this discussion, just a tidbit I remembered from my CS classes back in college :) -- Mark On Fri, 2005-09-23 at 13:17 -0400, Tom Lane wrote: Ron Peacetree [EMAIL PROTECTED] writes: 2= No optimal external sorting algorithm should use more than 2 passes. 3= Optimal external sorting algorithms should use 1 pass if at all possible. A comparison-based sort must use at least N log N operations, so it would appear to me that if you haven't got approximately log N passes then your algorithm doesn't work. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Releasing memory during External sorting?
From: Tom Lane [EMAIL PROTECTED] Sent: Sep 23, 2005 2:15 PM Subject: Re: [PERFORM] Releasing memory during External sorting? Mark Lewis [EMAIL PROTECTED] writes: operations != passes. If you were clever, you could probably write a modified bubble-sort algorithm that only made 2 passes. A pass is a disk scan, operations are then performed (hopefully in memory) on what you read from the disk. So there's no theoretical log N lower-bound on the number of disk passes. Given infinite memory that might be true, but I don't think I believe it for limited memory. If you have room for K tuples in memory then it's impossible to perform more than K*N useful comparisons per pass (ie, as each tuple comes off the disk you can compare it to all the ones currently in memory; anything more is certainly redundant work). So if K logN it's clearly not gonna work. Actually, it's far better than that. I recall a paper I saw in one of the algorithms journals 15+ years ago that proved that if you knew the range of the data, regardless of what that range was, and had n^2 space, you could sort n items in O(n) time. Turns out that with very modest constraints on the range of the data and substantially less extra space (about the same as you'd need for Replacement Selection + External Merge Sort), you can _still_ sort in O(n) time. It's possible that you could design an algorithm that works in a fixed number of passes if you are allowed to assume you can hold O(log N) tuples in memory --- and in practice that would probably work fine, if the constant factor implied by the O() isn't too big. But it's not really solving the general external-sort problem. If you know nothing about the data to be sorted and must guard against the worst possible edge cases, AKA the classic definition of the general external sorting problem, then one can't do better than some variant of Replacement Selection + Unbalanced Multiway Merge. OTOH, ITRW things are _not_ like that. We know the range of the data in our DB fields or we can safely assume it to be relatively constrained. This allows us access to much better external sorting algorithms. For example Postman Sort (the 2005 winner of the PennySort benchmark) is basically an IO optimized version of an external Radix Sort. Ron ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Query slower on 8.0.3 (Windows) vs 7.3 (cygwin)
Gurpreet Aulakh [EMAIL PROTECTED] writes: After further investigation I have found that the reason why the query is slower on 8.0.3 is that the hash and hash joins are slower on the 8.0.3. So the question comes down to : Why are hash and hash joins slower? I looked into this a bit and determined that the problem seems to have been introduced here: 2002-12-30 10:21 tgl * src/: backend/executor/nodeHash.c, backend/executor/nodeHashjoin.c, backend/optimizer/path/costsize.c, include/executor/nodeHash.h: Better solution to integer overflow problem in hash batch-number computation: reduce the bucket number mod nbatch. This changes the association between original bucket numbers and batches, but that doesn't matter. Minor other cleanups in hashjoin code to help centralize decisions. (which means it's present in 7.4 as well as 8.0). The code now groups tuples into hash batches according to (hashvalue % totalbuckets) % nbatch When a tuple that is not in the first batch is reloaded, it is placed into a bucket according to (hashvalue % nbuckets) This means that if totalbuckets, nbatch, and nbuckets have a common factor F, the buckets won't be evenly used; in fact, only one in every F buckets will be used at all, the rest remaining empty. The ones that are used accordingly will contain about F times more tuples than intended. The slowdown comes from having to compare these extra tuples against the outer-relation tuples. 7.3 uses a different algorithm for grouping tuples that avoids this problem, but it has performance issues of its own (in particular, to avoid integer overflow we have to limit the number of batches we can have). So just reverting this patch doesn't seem very attractive. The problem no longer exists in 8.1 because of rewrites undertaken for another purpose, so I'm sort of tempted to do nothing. To fix this in the back branches we'd have to develop new code that won't ever go into CVS tip and thus will never get beta-tested. The risk of breaking things seems higher than I'd like. If we did want to fix it, my first idea is to increment nbatch looking for a value that has no common factor with nbuckets. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PERFORM] Index use in BETWEEN statement...
Hello pals, I have the following table in Postgresql 8.0.1
Mydb# \d geoip_block
Table public.geoip_block
Column| Type | Modifiers
-++---
locid | bigint |
start_block | inet |
end_block | inet |
mydb# explain analyze select locid from geoip_block where
'216.230.158.50'::inet between start_block and end_block;
QUERY PLAN
---
Seq Scan on geoip_block (cost=0.00..142772.86 rows=709688 width=8) (actual
time=14045.384..14706.927 rows=1 loops=1)
Filter: (('216.230.158.50'::inet = start_block) AND
('216.230.158.50'::inet = end_block))
Total runtime: 14707.038 ms
Ok, now I decided to create a index to speed a little the query
Mydb# create index idx_ipblocks on geoip_block(start_block, end_block);
CREATE INDEX
clickad=# explain analyze select locid from geoip_block where
'216.230.158.50'::inet between start_block and end_block;
QUERY PLAN
--
Seq Scan on geoip_block (cost=0.00..78033.96 rows=230141 width=8) (actual
time=12107.919..12610.199 rows=1 loops=1)
Filter: (('216.230.158.50'::inet = start_block) AND
('216.230.158.50'::inet = end_block))
Total runtime: 12610.329 ms
(3 rows)
I guess the planner is doing a sequential scan in the table, why not use the
compound index? Do you have any idea in how to speed up this query?
Thanks a lot!
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [PERFORM] SELECT LIMIT 1 VIEW Performance Issue
K C Lau wrote: Thank you all for your suggestions. I' tried, with some variations too, but still no success. The times given are the best of a few repeated tries on an 8.1 beta 2 db freshly migrated from 8.0.3 on Windows. A small denormalization, where you mark the row with the latest atdate for each playerid may get you the performance you want. e.g: (8.1beta1) ALTER TABLE player ADD islastatdate boolean; UPDATE player SET islastatdate = true where (playerid,atdate) IN (SELECT playerid, atdate FROM vcurplayer); CREATE OR REPLACE VIEW vcurplayer AS SELECT * FROM player a WHERE islastatdate; CREATE INDEX player_id_lastatdate ON player(playerid, islastatdate) WHERE islastatdate; ANALYZE player; Generating some test data produced: EXPLAIN ANALYZE SELECT playerid,atdate FROM vcurplayer WHERE playerid='0'; QUERY PLAN Index Scan using player_id_lastatdate on player a (cost=0.00..4.33 rows=1 width=13) (actual time=0.142..0.149 rows=1 loops=1) Index Cond: ((playerid = '0'::text) AND (lastatdate = true)) Filter: lastatdate Total runtime: 0.272 ms (4 rows) Whereas with the original view definition: CREATE OR REPLACE VIEW vcurplayer AS SELECT * FROM player a WHERE a.atdate = ( SELECT max(b.atdate) FROM player b WHERE a.playerid = b.playerid); EXPLAIN ANALYZE SELECT playerid,atdate FROM vcurplayer WHERE playerid='0'; QUERY PLAN - Index Scan using player_id_date on player a (cost=0.00..7399.23 rows=11 width=13) (actual time=121.738..121.745 rows=1 loops=1) Index Cond: (playerid = '0'::text) Filter: (atdate = (subplan)) SubPlan - Result (cost=1.72..1.73 rows=1 width=0) (actual time=0.044..0.047 rows=1 loops=2000) InitPlan - Limit (cost=0.00..1.72 rows=1 width=4) (actual time=0.028..0.031 rows=1 loops=2000) - Index Scan Backward using player_id_date on player b (cost=0.00..3787.94 rows=2198 width=4) (actual time=0.019..0.019 rows=1 loops=2000) Index Cond: ($0 = playerid) Filter: (atdate IS NOT NULL) Total runtime: 121.916 ms (11 rows) Note that my generated data has too many rows for each playerid, but the difference in performance should illustrate the idea. Cheers Mark ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] SELECT LIMIT 1 VIEW Performance Issue
Dear Mark, Thank you. That seems like a more manageable alternative if nothing else works out. It should cover many of the OLTP update transactions. But it does mean quite a bit of programming changes and adding another index on all such tables, and it would not cover those cases when we need to get the latest record before a certain time, for example. I'm wondering if this performance issue is common enough for other users to merit a fix in pg, especially as it seems that with MVCC, each of the data records need to be accessed in addition to scanning the index. Best regards, KC. At 09:40 05/09/24, Mark Kirkwood wrote: A small denormalization, where you mark the row with the latest atdate for each playerid may get you the performance you want. e.g: (8.1beta1) ALTER TABLE player ADD islastatdate boolean; UPDATE player SET islastatdate = true where (playerid,atdate) IN (SELECT playerid, atdate FROM vcurplayer); CREATE OR REPLACE VIEW vcurplayer AS SELECT * FROM player a WHERE islastatdate; CREATE INDEX player_id_lastatdate ON player(playerid, islastatdate) WHERE islastatdate; ANALYZE player; Generating some test data produced: EXPLAIN ANALYZE SELECT playerid,atdate FROM vcurplayer WHERE playerid='0'; QUERY PLAN Index Scan using player_id_lastatdate on player a (cost=0.00..4.33 rows=1 width=13) (actual time=0.142..0.149 rows=1 loops=1) Index Cond: ((playerid = '0'::text) AND (lastatdate = true)) Filter: lastatdate Total runtime: 0.272 ms (4 rows) Whereas with the original view definition: CREATE OR REPLACE VIEW vcurplayer AS SELECT * FROM player a WHERE a.atdate = ( SELECT max(b.atdate) FROM player b WHERE a.playerid = b.playerid); EXPLAIN ANALYZE SELECT playerid,atdate FROM vcurplayer WHERE playerid='0'; QUERY PLAN - Index Scan using player_id_date on player a (cost=0.00..7399.23 rows=11 width=13) (actual time=121.738..121.745 rows=1 loops=1) Index Cond: (playerid = '0'::text) Filter: (atdate = (subplan)) SubPlan - Result (cost=1.72..1.73 rows=1 width=0) (actual time=0.044..0.047 rows=1 loops=2000) InitPlan - Limit (cost=0.00..1.72 rows=1 width=4) (actual time=0.028..0.031 rows=1 loops=2000) - Index Scan Backward using player_id_date on player b (cost=0.00..3787.94 rows=2198 width=4) (actual time=0.019..0.019 rows=1 loops=2000) Index Cond: ($0 = playerid) Filter: (atdate IS NOT NULL) Total runtime: 121.916 ms (11 rows) Note that my generated data has too many rows for each playerid, but the difference in performance should illustrate the idea. Cheers Mark ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] SELECT LIMIT 1 VIEW Performance Issue
K C Lau wrote: I'm wondering if this performance issue is common enough for other users to merit a fix in pg, especially as it seems that with MVCC, each of the data records need to be accessed in addition to scanning the index. Yes - there are certainly cases where index only access (or something similar, like b+tree tables) would be highly desirable. From what I have understood from previous discussions, there are difficulties involved with producing a design that does not cause new problems... regards Mark ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] [HACKERS] Releasing memory during External sorting?
Simon Riggs [EMAIL PROTECTED] writes: If not, I would propose that when we move from qsort to tapesort mode we free the larger work_mem setting (if one exists) and allocate only a lower, though still optimal setting for the tapesort. That way the memory can be freed for use by other users or the OS while the tapesort proceeds (which is usually quite a while...). On most platforms it's quite unlikely that any memory would actually get released back to the OS before transaction end, because the memory blocks belonging to the tuplesort context will be intermixed with blocks belonging to other contexts. So I think this is pretty pointless. (If you can't afford to have the sort using all of sort_mem, you've set sort_mem too large, anyway.) regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] [HACKERS] Releasing memory during External sorting?
On Fri, 2005-09-23 at 10:09 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: If not, I would propose that when we move from qsort to tapesort mode we free the larger work_mem setting (if one exists) and allocate only a lower, though still optimal setting for the tapesort. That way the memory can be freed for use by other users or the OS while the tapesort proceeds (which is usually quite a while...). On most platforms it's quite unlikely that any memory would actually get released back to the OS before transaction end, because the memory blocks belonging to the tuplesort context will be intermixed with blocks belonging to other contexts. So I think this is pretty pointless. I take it you mean pointless because of the way the memory allocation works, rather than because giving memory back isn't worthwhile ? Surely the sort memory would be allocated in contiguous chunks? In some cases we might be talking about more than a GB of memory, so it'd be good to get that back ASAP. I'm speculating (If you can't afford to have the sort using all of sort_mem, you've set sort_mem too large, anyway.) Sort takes care to allocate only what it needs as starts up. All I'm suggesting is to take the same care when the sort mode changes. If the above argument held water then we would just allocate all the memory in one lump at startup, because we can afford to, so I don't buy that. Since we know the predicted size of the sort set prior to starting the sort node, could we not use that information to allocate memory appropriately? i.e. if sort size is predicted to be more than twice the size of work_mem, then just move straight to the external sort algorithm and set the work_mem down at the lower limit? That is, unless somebody has evidence that having a very large memory has any performance benefit for external sorting? Best Regards, Simon Riggs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] [HACKERS] Releasing memory during External sorting?
Simon Riggs [EMAIL PROTECTED] writes: Since we know the predicted size of the sort set prior to starting the sort node, could we not use that information to allocate memory appropriately? i.e. if sort size is predicted to be more than twice the size of work_mem, then just move straight to the external sort algorithm and set the work_mem down at the lower limit? Have you actually read the sort code? During the run-forming phase it's definitely useful to eat all the memory you can: that translates directly to longer initial runs and hence fewer merge passes. During the run-merging phase it's possible that using less memory would not hurt performance any, but as already stated, I don't think it will actually end up cutting the backend's memory footprint --- the sbrk point will be established during the run forming phase and it's unlikely to move back much until transaction end. Also, if I recall the development of that code correctly, the reason for using more than minimum memory during the merge phase is that writing or reading lots of tuples at once improves sequentiality of access to the temp files. So I'm not sure that cutting down the memory wouldn't hurt performance. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Releasing memory during External sorting?
From: Simon Riggs [EMAIL PROTECTED] Sent: Sep 23, 2005 5:37 AM Subject: [PERFORM] Releasing memory during External sorting? I have concerns about whether we are overallocating memory for use in external sorts. (All code relating to this is in tuplesort.c) A decent external sorting algorithm, say a Merge Sort + Radix (or Distribution Counting) hybrid with appropriate optimizations for small sub- files, should become more effective / efficient the more RAM you give it. The external sort algorithm benefits from some memory but not much. That's probably an artifact of the psql external sorting code and _not_ due to some fundamental external sorting issue. Knuth says that the amount of memory required is very low, with a value typically less than 1 kB. Required means the external sort can operate on that little memory. How Much memory is required for optimal performance is another matter. I/O overheads mean that there is benefit from having longer sequential writes, so the optimum is much larger than that. I've not seen any data that indicates that a setting higher than 16 MB adds any value at all to a large external sort. It should. A first pass upper bound would be the amount of RAM needed for Replacement Selection to create a run (ie sort) of the whole file. That should be ~ the amount of RAM to hold 1/2 the file in a Replacement Selection pass. At the simplest, for any file over 32MB the optimum should be more than 16MB. I have some indications from private tests that very high memory settings may actually hinder performance of the sorts, though I cannot explain that and wonder whether it is the performance tests themselves that have issues. Hmmm. Are you talking about amounts so high that you are throwing the OS into paging and swapping thrash behavior? If not, then the above is weird. Does anyone have any clear data that shows the value of large settings of work_mem when the data to be sorted is much larger than memory? (I am well aware of the value of setting work_mem higher for smaller sorts, so any performance data needs to reflect only very large sorts). This is not PostgreSQL specific, but it does prove the point that the performance of external sorts benefits greatly from large amounts of RAM being available: http://research.microsoft.com/barc/SortBenchmark/ Looking at the particulars of the algorithms listed there should shed a lot of light on what a good external sorting algorithm looks like: 1= HD IO matters the most. 1a= Seeking behavior is the largest factor in poor performance. 2= No optimal external sorting algorithm should use more than 2 passes. 3= Optimal external sorting algorithms should use 1 pass if at all possible. 4= Use as much RAM as possible, and use it as efficiently as possible. 5= The amount of RAM needed to hide the latency of a HD subsytem goes up as the _square_ of the difference between the bandwidth of the HD subsystem and memory. 6= Be cache friendly. 7= For large numbers of records whose sorting key is substantially smaller than the record itself, use a pointer + compressed key representation and write the data to HD in sorted order (Replace HD seeks with RAM seeks. Minimize RAM seeks). 8= Since your performance will be constrained by HD IO first and RAM IO second, up to a point it is worth it to spend more CPU cycles to save on IO. Given the large and growing gap between CPU IO, RAM IO, and HD IO, these issues are becoming more important for _internal_ sorts as well. Feedback, please. Best Regards, Simon Riggs Hope this is useful, Ron ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
