Re: [PERFORM] Introducing a new linux readahead framework
On Thu, Apr 20, 2006 at 11:31:47PM -0500, Jim C. Nasby wrote: > > In adaptive readahead, the context based method may be of particular > > interest to postgresql users. It works by peeking into the file cache > > and check if there are any history pages present or accessed. In this > > way it can detect almost all forms of sequential / semi-sequential read > > patterns, e.g. > > - parallel / interleaved sequential scans on one file > > - sequential reads across file open/close > > - mixed sequential / random accesses > > - sparse / skimming sequential read > > > > It also have methods to detect some less common cases: > > - reading backward > > - seeking all over reading N pages > > Are there any ways to inform the kernel that you either are or aren't > doing a sequential read? It seems that in some cases it would be better This call will disable readahead totally for fd: posix_fadvise(fd, any, any, POSIX_FADV_RANDOM); This one will reenable it: posix_fadvise(fd, any, any, POSIX_FADV_NORMAL); This one will enable readahead _and_ set max readahead window to 2*max_readahead_kb: posix_fadvise(fd, any, any, POSIX_FADV_SEQUENTIAL); > to bypass a bunch of tricky logic trying to determine that it's doing a > sequential read. A sequential scan in PostgreSQL would be such a case. You do not need to worry about the detecting `overhead' on sequential scans :) The adaptive readahead framework has a fast code path(the stateful method) to handle normal sequential reads, the detection of which is really trivial. > The opposite example would be an index scan of a highly uncorrelated > index, which would produce mostly random reads from the table. In that > case, reading ahead probably makes very little sense, though your logic > might have a better idea of the access pattern than PostgreSQL does. As for the index scans, the failsafe code path(i.e. the context based one) will normally be used, and it does have a little overhead in looking up the page cache(about 0.4% more CPU time). However, the penalty of random disk access is so large that if ever it helps reducing a small fraction of disk accesses, you wins. Thanks, Wu ---(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] Introducing a new linux readahead framework
Hi, Wu, Wu Fengguang wrote: >>>In adaptive readahead, the context based method may be of particular >>>interest to postgresql users. It works by peeking into the file cache >>>and check if there are any history pages present or accessed. In this >>>way it can detect almost all forms of sequential / semi-sequential read >>>patterns, e.g. >>> - parallel / interleaved sequential scans on one file >>> - sequential reads across file open/close >>> - mixed sequential / random accesses >>> - sparse / skimming sequential read >>> >>>It also have methods to detect some less common cases: >>> - reading backward >>> - seeking all over reading N pages Gread news, thanks! > This call will disable readahead totally for fd: > posix_fadvise(fd, any, any, POSIX_FADV_RANDOM); > > This one will reenable it: > posix_fadvise(fd, any, any, POSIX_FADV_NORMAL); > > This one will enable readahead _and_ set max readahead window to > 2*max_readahead_kb: > posix_fadvise(fd, any, any, POSIX_FADV_SEQUENTIAL); I think that this is an easy, understandable and useful interpretation of posix_fadvise() hints. Are there any rough estimates when this will get into mainline kernel (if you intend to submit)? Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Better way to write aggregates?
Hi, I more or less often come about the problem of aggregating a child table counting it's different states. The cleanest solution I've come up with so far is: BEGIN; CREATE TABLE parent ( id int not null, name text not null, UNIQUE(id) ); CREATE TABLE child ( name text not null, state int not null, parent int not null references parent(id) ); CREATE VIEW parent_childs AS SELECT c.parent, count(c.state) as childtotal, count(c.state) - count(nullif(c.state,1)) as childstate1, count(c.state) - count(nullif(c.state,2)) as childstate2, count(c.state) - count(nullif(c.state,3)) as childstate3 FROM child c GROUP BY parent; CREATE VIEW parent_view AS SELECT p.*, pc.* FROM parent p LEFT JOIN parent_childs pc ON (p.id = pc.parent); COMMIT; Is this the fastest way to build these aggregates (not considering tricks with triggers, etc)? The count(state) - count(nullif(...)) looks a bit clumsy. I also experimented with a pgsql function to sum these up, but considered it as not-so-nice and it also always forces a sequential scan on the data. Thanks for any advice, Jan ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] Little use of CPU ( < 5%)
Hello ,
I have a problem of performance with a query. I use PostgreSQL 8.1.3.
The distribution of Linux is Red Hat Enterprise Linux ES release 4 (Nahant Update 2) and the server is a bi-processor Xeon 2.4GHz with 1 Go of Ram and the size of the database files is about 60 Go.
The problem is that this query uses only a few percentage of the cpu as seen with the top command :
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
3342 postgres 18 0 140m 134m 132m D 5.9 13.3 17:04.06 postmaster
The vm stat command :
procs ---memory-- ---swap-- -io --system-- cpu
r b swpd free buff cache si so bi bo in cs us sy id wa
0 1 184 16804 38104 933516 0 0 3092 55 667 145 12 4 71 14
0 1 184 16528 38140 933480 0 0 2236 0 1206 388 2 1 50 47
0 1 184 15008 38188 935252 0 0 2688 92 1209 396 2 0 49 48
The config of PostgresQL is :
shared_buffers = 16384 (128Mo)
work_mem = 65536 (64 Mo)
maintenance_work_mem = 98304 (96 Mo)
effective_cache_size = 84000
I think that the problem is there are too much %wait that are waiting cause of the really bad rate of lecture (bi) which is only 3 Mo/s .
It is this value I do not understand because whit other queries this rate is about 120 Mo/s. I use SCSI DISK and a RAID 0 hardware system .
This is the query plan of the query :
QUERY PLAN
Aggregate (cost=24582205.20..24582205.22 rows=1 width=13)
-> Nested Loop (cost=2.11..24582054.88 rows=60129 width=13)
Join Filter: ("inner".l_quantity < (subplan))
-> Seq Scan on part (cost=0.00..238744.00 rows=6013 width=4)
Filter: ((p_brand = 'Brand#51'::bpchar) AND (p_container = 'MED JAR'::bpchar))
-> Bitmap Heap Scan on lineitem (cost=2.11..126.18 rows=31 width=27)
Recheck Cond: ("outer".p_partkey = lineitem.l_partkey)
-> Bitmap Index Scan on id_partkey_lineitem (cost=0.00..2.11 rows=31 width=0)
Index Cond: ("outer".p_partkey = lineitem.l_partkey)
SubPlan
-> Aggregate (cost=126.50..126.51 rows=1 width=10)
-> Index Scan using id_partkey_lineitem on lineitem (cost=0.00..126.42 rows=31 width=10)
Index Cond: (l_partkey = $0)
(13 rows)
The number of tuples in Lineitem is 180 000 000.
So my question is what I have to do to increase the rate of the read which improve the execution of the query?
I add that the server is only dedicated for PostgreSQL.
Regards,
Re: [PERFORM] Introducing a new linux readahead framework
Hi Markus, On Fri, Apr 21, 2006 at 09:53:34AM +0200, Markus Schaber wrote: > Are there any rough estimates when this will get into mainline kernel > (if you intend to submit)? I'm not quite sure :) The patch itself has been pretty stable. To get it accepted, we must back it by good benchmarking results for some important applications. I have confirmed that file service via FTP/HTTP/NFS can more or less benefit from it. However, database services have not been touched yet. Oracle/DB2 seem to bypass the readahead code route, while postgresql relies totally on kernel readahead logic. So if postgresql is proved to work well with this patch, it will have good opportunity to go into mainline :) Thanks, Wu ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Better way to write aggregates?
Jan, I write queries like this CREATE VIEW parent_childs AS SELECT c.parent, count(c.state) as childtotal, sum(case when c.state = 1 then 1 else 0 end) as childstate1, sum(case when c.state = 2 then 1 else 0 end) as childstate2, sum(case when c.state = 3 then 1 else 0 end) as childstate3 FROM child c GROUP BY parent; -- Original Message --- From: Jan Dittmer <[EMAIL PROTECTED]> To: pgsql-performance@postgresql.org Sent: Fri, 21 Apr 2006 10:37:10 +0200 Subject: [PERFORM] Better way to write aggregates? > Hi, > > I more or less often come about the problem of aggregating a > child table counting it's different states. The cleanest solution > I've come up with so far is: > > BEGIN; > CREATE TABLE parent ( > id int not null, > name text not null, > UNIQUE(id) > ); > > CREATE TABLE child ( > name text not null, > state int not null, > parent int not null references parent(id) > ); > > CREATE VIEW parent_childs AS > SELECT > c.parent, > count(c.state) as childtotal, > count(c.state) - count(nullif(c.state,1)) as childstate1, > count(c.state) - count(nullif(c.state,2)) as childstate2, > count(c.state) - count(nullif(c.state,3)) as childstate3 > FROM child c > GROUP BY parent; > > CREATE VIEW parent_view AS > SELECT p.*, > pc.* > FROM parent p > LEFT JOIN parent_childs pc ON (p.id = pc.parent); > COMMIT; > > Is this the fastest way to build these aggregates (not considering > tricks with triggers, etc)? The count(state) - count(nullif(...)) looks > a bit clumsy. > I also experimented with a pgsql function to sum these up, but considered > it as not-so-nice and it also always forces a sequential scan on the > data. > > Thanks for any advice, > > Jan > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend --- End of Original Message --- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Better way to write aggregates?
Jim Buttafuoco wrote: Jan, I write queries like this CREATE VIEW parent_childs AS SELECT c.parent, count(c.state) as childtotal, sum(case when c.state = 1 then 1 else 0 end) as childstate1, sum(case when c.state = 2 then 1 else 0 end) as childstate2, sum(case when c.state = 3 then 1 else 0 end) as childstate3 FROM child c GROUP BY parent; It would help if booleans could be casted to integer 1/0 :-) But performance wise it should be about the same? I think I'll run some tests later today with real data. Would an index on NULLIF(state,1) help count(NULLIF(state,1)) ? Can one build an index on (case when c.state = 3 then 1 else 0 end)? Thanks, Jan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Better way to write aggregates?
I don't think an index will help you with this query. -- Original Message --- From: Jan Dittmer <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Cc: pgsql-performance@postgresql.org Sent: Fri, 21 Apr 2006 14:35:33 +0200 Subject: Re: [PERFORM] Better way to write aggregates? > Jim Buttafuoco wrote: > > Jan, > > > > I write queries like this > > > > CREATE VIEW parent_childs AS > > SELECT > > c.parent, > > count(c.state) as childtotal, > > sum(case when c.state = 1 then 1 else 0 end) as childstate1, > > sum(case when c.state = 2 then 1 else 0 end) as childstate2, > > sum(case when c.state = 3 then 1 else 0 end) as childstate3 > > FROM child c > > GROUP BY parent; > > It would help if booleans could be casted to integer 1/0 :-) But > performance wise it should be about the same? I think I'll > run some tests later today with real data. > Would an index on NULLIF(state,1) help count(NULLIF(state,1)) ? > Can one build an index on (case when c.state = 3 then 1 else 0 end)? > > Thanks, > > Jan --- End of Original Message --- ---(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] Takes too long to fetch the data from database
I’ve never used a cursor in Postgres, but I don’t think it will help you a lot. In theory cursors make it easier to do paging, but your main problem is that getting the first page is slow. A cursor isn’t going to be any faster at getting the first page than OFFSET/LIMIT is. Did you try Bruno’s suggestion of: SELECT * FROM wan ORDER BY stime DESC OFFSET 0 LIMIT 50; You should run an EXPLAIN ANALYZE on that query to see if it is using an index scan. Also what version of Postgres are you using? You can run select version(); to check. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of soni de Sent: Thursday, April 20, 2006 11:42 PM To: Merlin Moncure Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Takes too long to fetch the data from database I don't want to query exactly 81900 rows into set. I just want to fetch 50 or 100 rows at a time in a decreasing order of stime.(i.e 50 or 100 rows starting from last to end). if we fetched sequentially, there is also problem in fetching all the records (select * from wan where kname='pluto' order by stime) it is taking more than 4~5 minutes. tried it on same table having more than 326054 records. On 4/20/06, Merlin Moncure <[EMAIL PROTECTED]> wrote: > SELECT * FROM wan ORDER BY stime LIMIT 50 OFFSET 81900; you need to try and solve the problem without using 'offset'. you could do: BEGIN; DECLARE crs cursor FOR SELECT * FROM wan ORDER BY stime; FETCH ABSOLUTE 81900 in crs; FETCH 49 in crs; CLOSE crs; COMMIT; this may be a bit faster but will not solve the fundamental problem. the more interesting question is why you want to query exactly 81900 rows into a set. This type of thinking will always get you into trouble, absolute positioning will not really work in a true sql sense. if you are browsing a table sequentially, there are much better methods. merlin
Re: [PERFORM] Takes too long to fetch the data from database
On 4/21/06, soni de <[EMAIL PROTECTED]> wrote: > > I don't want to query exactly 81900 rows into set. I just want to fetch 50 > or 100 rows at a time in a decreasing order of stime.(i.e 50 or 100 rows > starting from last to end). aha! you need to implement a 'sliding window' query. simplest is when you are ordering one field that is unique: 1st 50: select * from t order by k limit 50; 2nd 50: select * from t where k > k1 order by k limit 50: if you are ordering on two fields or on a field that is not unique, you must do: 1st 50: select * from t order by j, k limit 50; 2nd 50: select * from t where j >= j1 and (j > j1 or k > k1) order by j, k limit 50; 3 fields: select * from t where i >= i1 and (i > i1 or j >= j1) and (i > i1 or j > k1 or k > k1) order by i,j,k limit 50; i1,j1,k1 are the values of the 50th record you pulled out of the last query. if this seems a little complicated, either wait for pg 8.2 or get cvs tip and rewrite as: select * from t where (i,j,k) > (i1,j1,k1) order by i,j,k limit 50; ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Little use of CPU ( < 5%)
Maybe you could post the query and an
EXPLAIN ANALYZE of the query. That would give more information for trying to
decide what is wrong.
So your question is basically why you get
a slower read rate on this query than on other queries? If I had to guess, maybe it could be that
you are scanning an index with a low correlation (The order of the records in
the index is very different then the order of the records on the disk.) causing
your drives to do a lot of seeking.
A possible fix for this might be to cluster the table on the index, but
I would check out the explain analyze first to see which
step is really the slow one.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of luchot
Sent: Friday, April 21, 2006 4:33
AM
To:
pgsql-performance@postgresql.org
Subject: [PERFORM] Little use of
CPU ( < 5%)
Hello ,
I have a problem of performance with a query. I use PostgreSQL 8.1.3.
The distribution of Linux is Red Hat Enterprise
Linux ES release 4 (Nahant Update 2) and the server is a bi-processor Xeon 2.4GHz with 1 Go of Ram and the
size of the database files is about 60 Go.
The problem is that this query uses only a few percentage of the cpu as
seen with the top command :
PID
USER
PR NI VIRT RES
SHR S %CPU %MEM
TIME+ COMMAND
3342 postgres 18 0 140m 134m 132m D 5.9 13.3 17:04.06 postmaster
The vm stat command :
procs ---memory-- ---swap--
-io --system-- cpu
r
b swpd free buff cache
si so bi bo in cs us sy
id wa
0 1 184 16804 38104 933516 0 0 3092 55 667 145 12
4 71 14
0
1 184 16528 38140 933480 0 0 2236 0 1206 388 2 1 50 47
0
1 184 15008 38188 935252 0 0 2688 92 1209 396 2
0 49 48
The config of PostgresQL is
:
shared_buffers = 16384 (128Mo)
work_mem = 65536
(64 Mo)
maintenance_work_mem =
98304 (96 Mo)
effective_cache_size = 84000
I think that the problem is
there are too much %wait that are waiting cause of the really bad
rate of lecture (bi) which is only 3 Mo/s .
It is this value I do not
understand because whit other queries this rate is about 120 Mo/s. I use SCSI
DISK and a RAID 0 hardware system .
This is the query plan of the
query :
QUERY PLAN
Aggregate (cost=24582205.20..24582205.22 rows=1
width=13)
-> Nested Loop (cost=2.11..24582054.88 rows=60129
width=13)
Join Filter: ("inner".l_quantity
< (subplan))
-> Seq Scan on part (cost=0.00..238744.00 rows=6013 width=4)
Filter: ((p_brand = 'Brand#51'::bpchar) AND (p_container = 'MED
JAR'::bpchar))
-> Bitmap Heap Scan on
lineitem (cost=2.11..126.18 rows=31
width=27)
Recheck Cond: ("outer".p_partkey = lineitem.l_partkey)
-> Bitmap Index Scan on
id_partkey_lineitem
(cost=0.00..2.11 rows=31 width=0)
Index Cond: ("outer".p_partkey = lineitem.l_partkey)
SubPlan
-> Aggregate (cost=126.50..126.51 rows=1 width=10)
-> Index Scan using
id_partkey_lineitem on lineitem
(cost=0.00..126.42 rows=31 width=10)
Index Cond: (l_partkey = $0)
(13 rows)
The number of tuples in
Lineitem is 180 000 000.
So my question is what I have
to do to increase the rate of the read which improve the execution of the
query?
I add that the server is only
dedicated for PostgreSQL.
Regards,
Re: [PERFORM] Better way to write aggregates?
Jan Dittmer <[EMAIL PROTECTED]> writes: > It would help if booleans could be casted to integer 1/0 :-) As of 8.1 there is such a cast in the system: regression=# select 't'::bool::int; int4 -- 1 (1 row) In earlier releases you can make your own. As for the original question, though: have you looked at the crosstab functions in contrib/tablefunc? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Hardware: HP StorageWorks MSA 1500
On Thu, 20 Apr 2006, Mikael Carneholm wrote: > We're going to get one for evaluation next week (equipped with dual > 2Gbit HBA:s and 2x14 disks, iirc). Anyone with experience from them, > performance wise? We (Seatbooker) use one. It works well enough. Here's a sample bonnie output: ---Sequential Output ---Sequential Input-- --Random-- -Per Char- --Block--- -Rewrite-- -Per Char- --Block--- --Seeks--- MachineMB K/sec %CPU K/sec %CPU K/sec %CPU K/sec %CPU K/sec %CPU /sec %CPU 16384 41464 30.6 41393 10.0 16287 3.7 92433 83.2 119608 18.3 674.0 0.8 which is hardly bad (on a four 15kRPM disk RAID 10 with 2Gbps FC). Sequential scans on a table produce about 40MB/s of IO with the 'disk' something like 60-70% busy according to FreeBSD's systat. Here's diskinfo -cvt output on a not quite idle system: /dev/da1 512 # sectorsize 59054899200 # mediasize in bytes (55G) 115341600 # mediasize in sectors 7179# Cylinders according to firmware. 255 # Heads according to firmware. 63 # Sectors according to firmware. I/O command overhead: time to read 10MB block 0.279395 sec =0.014 msec/sector time to read 20480 sectors 11.864934 sec =0.579 msec/sector calculated command overhead =0.566 msec/sector Seek times: Full stroke: 250 iter in 0.836808 sec =3.347 msec Half stroke: 250 iter in 0.861196 sec =3.445 msec Quarter stroke: 500 iter in 1.415700 sec =2.831 msec Short forward:400 iter in 0.586330 sec =1.466 msec Short backward: 400 iter in 1.365257 sec =3.413 msec Seq outer: 2048 iter in 1.184569 sec =0.578 msec Seq inner: 2048 iter in 1.184158 sec =0.578 msec Transfer rates: outside: 102400 kbytes in 1.367903 sec =74859 kbytes/sec middle:102400 kbytes in 1.472451 sec =69544 kbytes/sec inside:102400 kbytes in 1.521503 sec =67302 kbytes/sec It (or any FC SAN, for that matter) isn't an especially cheap way to get storage. You don't get much option if you have an HP blade enclosure, though. HP's support was poor. Their Indian call-centre seems not to know much about them and spectacularly failed to tell us if and how we could connect this (with the 2/3-port FC hub option) to two of our blade servers, one of which was one of the 'half-height' ones which require an arbitrated loop. We ended up buying a FC switch. ---(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] Inactive memory Grows unlimited
Hi, I hope you can help me...there's something wrong going on my db server (OS. GNU/linux White box)... here's the problem... The amount of Inactive memory Grows unlimited this happens only when Postgresql (8.1.1) is running... after a few days it "eats" all the RAM memory ... so I've have to restart the server to free RAM Here's the parametres actually active in then postgresql.conf file max_connections = 100 authentication_timeout = 60 password_encryption = on shared_buffers = 1000 work_mem = 131076 maintenance_work_mem = 262152 redirect_stderr = on log_directory = 'pg_log' log_truncate_on_rotation = on log_rotation_age = 1440 log_rotation_size = 0 lc_messages = 'es_ES.UTF-8' lc_monetary = 'es_ES.UTF-8' lc_numeric = 'es_ES.UTF-8' lc_time = 'es_ES.UTF-8' Below you can find the content of meminfo file with 18 users connected (average during working days) and the server has benn running during 2 days 2:19 #cat /proc/meminfo MemTotal: 2074844 kB MemFree: 1371660 kB Buffers: 61748 kB Cached: 555492 kB SwapCached: 0 kB Active: 348604 kB Inactive: 305876 kB HighTotal: 1179440 kB HighFree: 579904 kB LowTotal: 895404 kB LowFree:791756 kB SwapTotal: 2048248 kB SwapFree: 2048248 kB Dirty: 260 kB Writeback: 0 kB Mapped: 54824 kB Slab:35756 kB Committed_AS: 117624 kB PageTables: 3404 kB VmallocTotal: 106488 kB VmallocUsed: 3356 kB VmallocChunk: 102920 kB HugePages_Total: 0 HugePages_Free: 0 Hugepagesize: 2048 kB thanks in advance for your help, Alvaro Arcila pd: sorry for my english It's not very good, I hope I'm clear ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Inactive memory Grows unlimited
ALVARO ARCILA wrote: > Hi, > > I hope you can help me...there's something wrong going on my db server (OS. > GNU/linux White box)... here's the problem... > > The amount of Inactive memory Grows unlimited this happens only when > Postgresql (8.1.1) is running... after a few days it "eats" all the RAM > memory ... so I've have to restart the server to free RAM This is normal Unix behavior. Leave it running for a few more days and you'll that nothing wrong happens. Why do you think you need "free" memory? Only if the swap memory start getting used a lot you need to worry about memory consumption. You should upgrade to 8.1.3 BTW. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Takes too long to fetch the data from database
On Fri, Apr 21, 2006 at 10:12:24 +0530, soni de <[EMAIL PROTECTED]> wrote: > I don't want to query exactly 81900 rows into set. I just want to fetch 50 > or 100 rows at a time in a decreasing order of stime.(i.e 50 or 100 rows > starting from last to end). You can do this efficiently, if stime has an index and you can deal with using stime from the previous query instead of the record count. The idea is to select up 50 or 100 records in descending order where the stime is <= the previous stime. This can give you some overlapping records, so you need some way to deal with this. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Takes too long to fetch the data from database
On Fri, Apr 21, 2006 at 09:44:25AM -0400, Merlin Moncure wrote: > 2nd 50: > select * from t where j >= j1 and (j > j1 or k > k1) order by j, k limit 50; > 3 fields: > select * from t where i >= i1 and (i > i1 or j >= j1) and (i > i1 or j > > k1 or k > k1) order by i,j,k limit 50; Note that in 8.2 you'll be able to do: WHERE (i, j, k) >= (i1, j1, k1) -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Introducing a new linux readahead framework
On Fri, Apr 21, 2006 at 08:20:28PM +0800, Wu Fengguang wrote: > Hi Markus, > > On Fri, Apr 21, 2006 at 09:53:34AM +0200, Markus Schaber wrote: > > Are there any rough estimates when this will get into mainline kernel > > (if you intend to submit)? > > I'm not quite sure :) > > The patch itself has been pretty stable. To get it accepted, we must > back it by good benchmarking results for some important applications. > I have confirmed that file service via FTP/HTTP/NFS can more or less > benefit from it. However, database services have not been touched yet. > Oracle/DB2 seem to bypass the readahead code route, while postgresql > relies totally on kernel readahead logic. So if postgresql is proved > to work well with this patch, it will have good opportunity to go into > mainline :) IIRC Mark from OSDL said he'd try testing this when he gets a chance, but you could also try running dbt2 and dbt3 against it. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Hardware: HP StorageWorks MSA 1500
Your numbers seem quite ok considering the number of disks. We also get a 256Mb battery backed cache module with it, so I'm looking forward to testing the write performance (first using ext3, then xfs). If I get the enough time to test it, I'll test both raid 0+1 and raid 5 configurations although I trust raid 0+1 more. And no, it's not the cheapest way to get storage - but it's only half as expensive as the other option: an EVA4000, which we're gonna have to go for if we(they) decide to stay in bed with a proprietary database. With postgres we don't need replication on SAN level (using slony) so the MSA 1500 would be sufficient, and that's a good thing (price wise) as we're gonna need two. OTOH, the EVA4000 will not give us mirroring so either way, we're gonna need two of whatever system we go for. Just hoping the MSA 1500 is reliable as well... Support will hopefully not be a problem for us as we have a local company providing support, they're also the ones setting it up for us so at least we'll know right away if they're compentent or not :) Regards, Mikael -Original Message- From: Alex Hayward [mailto:[EMAIL PROTECTED] On Behalf Of Alex Hayward Sent: den 21 april 2006 17:25 To: Mikael Carneholm Cc: Pgsql performance Subject: Re: [PERFORM] Hardware: HP StorageWorks MSA 1500 On Thu, 20 Apr 2006, Mikael Carneholm wrote: > We're going to get one for evaluation next week (equipped with dual > 2Gbit HBA:s and 2x14 disks, iirc). Anyone with experience from them, > performance wise? We (Seatbooker) use one. It works well enough. Here's a sample bonnie output: ---Sequential Output ---Sequential Input-- --Random-- -Per Char- --Block--- -Rewrite-- -Per Char- --Block--- --Seeks--- MachineMB K/sec %CPU K/sec %CPU K/sec %CPU K/sec %CPU K/sec %CPU /sec %CPU 16384 41464 30.6 41393 10.0 16287 3.7 92433 83.2 119608 18.3 674.0 0.8 which is hardly bad (on a four 15kRPM disk RAID 10 with 2Gbps FC). Sequential scans on a table produce about 40MB/s of IO with the 'disk' something like 60-70% busy according to FreeBSD's systat. Here's diskinfo -cvt output on a not quite idle system: /dev/da1 512 # sectorsize 59054899200 # mediasize in bytes (55G) 115341600 # mediasize in sectors 7179# Cylinders according to firmware. 255 # Heads according to firmware. 63 # Sectors according to firmware. I/O command overhead: time to read 10MB block 0.279395 sec =0.014 msec/sector time to read 20480 sectors 11.864934 sec =0.579 msec/sector calculated command overhead =0.566 msec/sector Seek times: Full stroke: 250 iter in 0.836808 sec =3.347 msec Half stroke: 250 iter in 0.861196 sec =3.445 msec Quarter stroke: 500 iter in 1.415700 sec =2.831 msec Short forward:400 iter in 0.586330 sec =1.466 msec Short backward: 400 iter in 1.365257 sec =3.413 msec Seq outer: 2048 iter in 1.184569 sec =0.578 msec Seq inner: 2048 iter in 1.184158 sec =0.578 msec Transfer rates: outside: 102400 kbytes in 1.367903 sec =74859 kbytes/sec middle:102400 kbytes in 1.472451 sec =69544 kbytes/sec inside:102400 kbytes in 1.521503 sec =67302 kbytes/sec It (or any FC SAN, for that matter) isn't an especially cheap way to get storage. You don't get much option if you have an HP blade enclosure, though. HP's support was poor. Their Indian call-centre seems not to know much about them and spectacularly failed to tell us if and how we could connect this (with the 2/3-port FC hub option) to two of our blade servers, one of which was one of the 'half-height' ones which require an arbitrated loop. We ended up buying a FC switch. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] WAL logging of SELECT ... INTO command
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Kris Jurka wrote: > > > On Fri, 24 Mar 2006, Jim C. Nasby wrote: > > > On Wed, Mar 22, 2006 at 02:37:28PM -0500, Kris Jurka wrote: > >> > >> On Wed, 22 Mar 2006, Jim C. Nasby wrote: > >> > >>> Ok, I saw disk activity on the base directory and assumed it was pg_xlog > >>> stuff. Turns out that both SELECT INTO and CREATE TABLE AS ignore > >>> default_tablepsace and create the new tables in the base directory. I'm > >>> guessing that's a bug... (this is on 8.1.2, btw). > >> > >> This has been fixed in CVS HEAD as part of a patch to allow additional > >> options to CREATE TABLE AS. > >> > >> http://archives.postgresql.org/pgsql-patches/2006-02/msg00211.php > > > > I'll argue that the current behavior is still a bug and should be fixed. > > Would it be difficult to patch 8.1 (and 8.0 if there were tablespaces > > then...) to honor default_tablespace? > > Here are patches that fix this for 8.0 and 8.1. > > Kris Jurka Content-Description: [ Attachment, skipping... ] Content-Description: [ Attachment, skipping... ] > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] WAL logging of SELECT ... INTO command
On Fri, 2006-04-21 at 19:56 -0400, Bruce Momjian wrote: > Your patch has been added to the PostgreSQL unapplied patches list at: > > http://momjian.postgresql.org/cgi-bin/pgpatches > > It will be applied as soon as one of the PostgreSQL committers reviews > and approves it. This patch should now be referred to as allow CREATE TABLE AS/SELECT INTO to use default_tablespace or something similar. The name of the original thread no longer bears any resemblance to the intention of this patch as submitted in its final form. I've no objection to the patch, which seems to fill a functional gap/bug. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
