Re: [PERFORM] Why date index is not used
> Btw. have you run ANALYZE on the table recently? I have autovacuum with default statitics settings running so I expect that it is analyzed. > What is the number > of distinct values in the 'kuupaev' column? select count(distinct kuupaev) from makse returns 61 kuupaev is sales date. So this can contain 365 distinct values per year and max 10 year database, total can be 3650 distinct values after 10 years. Andrus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Why date index is not used
> Actually It looks to me like the sorting is the slow part of this query. > Maybe if you did create an index on both kuupaev and kellaaeg it might > make the sorting faster. Thank you. It makes query fast. > Or maybe you could try increasing the server's > work mem. The sort will be much slower if the server can't do the whole > thing in ram. I have W2K server with 0.5 GB RAM there are only 6 connections open ( 6 point of sales) to this server. shared_buffes is 1 I see approx 10 postgres processes in task manager each taking about 30 MB ram Server prefomance is very slow: Windows swap file size is 1 GB For each sale a new row will be inserted to this table. So the file size grows rapidly every day. Changing work_mem by 1 MB increares memory requirment by 10 MB since I may have 10 processes running. Sorting in memory this table requires very large amout of work_mem for each process address space. I think that if I increase work_mem then swap file will became bigger and perfomance will decrease even more. How to increase perfomance ? Andrus. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Why date index is not used
Tom, > Because it doesn't help --- the system still has to do the sort. It can help a lot in this case. kuupaev is sales date kellaaeg is sales time Postgres can use kuupaev index to fetch first 100 rows plus a number of more rows whose kellaaeg value is equal to kellaaeg in 100 th row. I have 500 sales per day. So it can fetch 600 rows using index on kuupaev column. After that it can sort those 600 rows fast. Currently it sorts blindly all 54000 rows in table. > You'd need a two-column index on both of the ORDER BY columns to avoid > sorting. Thank you. It works. Andrus. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] pgsql_tmp and postgres settings
Hello, During insert or update, potgresql write in pgsql_tmp directory and so performance are very poor. My configuration is: Work mem10240 Effective_cache_size 3 Shared buffers 9000 Max_fsm_pages 35000 Wal Buffers 24 Autovacuum on Manual vacuum analyze and vacuum full analyze every day Server: 1 Xeon processor 2500 MB ram Red Hat Enterprise ES 3 Postgresql (RPM from official website) 8.1.0 Tables are vacuumed frequently and now fsm is very low (only 3000 pages). Updates and inserts on this database are infrequent, and files to import aren't so big (7-50 Mb for 2000-2 record in a txt file). On this server are installed and active also Apache - Tomcat - Java 1.4.2 which provide data to import. Tables interested have only max 4 index. Are parameters adapted? Thanks Domenico Mozzanica ---(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] Regarding pg_dump utility
I think that having an API for backup functionality would definitely be useful. Just my 2 cents... Paul On 6/8/06, Christopher Kings-Lynne <[EMAIL PROTECTED]> wrote: > Personally I think it would be neat. For example the admin-tool guys> would be able to get a dump without invoking an external program. > Second it would really be independent of core releases (other than being> tied to the output format.) pg_dump would be just a simple caller of> such a library, and anyone else would be able to get dumps easily, in > whatever format.What about fully completing our SQL API for dumping?ie. We finish adding pg_get_blahdef() for all objects, add a functionthat returns the proper ordering of all objects in the database, and then somehow drop out a dump with a single JOIN :DChris---(end of broadcast)---TIP 6: explain analyze is your friend
Re: [PERFORM] Why date index is not used
On Fri, Jun 09, 2006 at 12:40:26PM +0300, Andrus wrote: > > Actually It looks to me like the sorting is the slow part of this query. > > Maybe if you did create an index on both kuupaev and kellaaeg it might > > make the sorting faster. > > Thank you. It makes query fast. > > > Or maybe you could try increasing the server's > > work mem. The sort will be much slower if the server can't do the whole > > thing in ram. > > I have W2K server with 0.5 GB RAM > there are only 6 connections open ( 6 point of sales) to this server. > shared_buffes is 1 > I see approx 10 postgres processes in task manager each taking about 30 MB > ram > > Server prefomance is very slow: Windows swap file size is 1 GB > > For each sale a new row will be inserted to this table. So the file size > grows rapidly every day. > Changing work_mem by 1 MB increares memory requirment by 10 MB since I may > have 10 processes running. Sorting in memory this table requires very large > amout of work_mem for each process address space. > > I think that if I increase work_mem then swap file will became bigger and > perfomance will decrease even more. > > How to increase perfomance ? Do you have effective_cache_size set correctly? You might try dropping random_page_cost down to 2 or so. Of course you could just put more memory in the machine, too. -- 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 2: Don't 'kill -9' the postmaster
Re: [PERFORM] pgsql_tmp and postgres settings
On Fri, Jun 09, 2006 at 02:23:04PM +0200, Domenico - Sal. F.lli Riva wrote: > Hello, > > During insert or update, potgresql write in pgsql_tmp directory and so > performance are very poor. pgsql_tmp is used if a query runs out of work_mem, so you can try increasing that. > My configuration is: > > Work mem10240 > > Effective_cache_size 3 You're off by a factor of 10. > Shared buffers 9000 I'd suggest bumping that up to at least 3. > Postgresql (RPM from official website) 8.1.0 You should upgrade to 8.1.4. There's a number of data loss bugs waiting to bite you. -- 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
[PERFORM] pg_xlog on data partition with BBU RAID
AFAIK, the reason why seperating pg_xlog from the base files provides so much performance is because the latency on pg_xlog is critical: a transaction can't commit until all of it's log data is written to disk via fsync, and if you're trying to fsync frequently on the same drive as the data tables are on, you'll have a big problem with the activity on the data drives competing with trying to fsync pg_xlog rapidly. But if you have a raid array with a battery-backed controller, this shouldn't be anywhere near as big an issue. The fsync on the log will return very quickly thanks to the cache, and the controller is then free to batch up writes to pg_xlog. Or at least that's the theory. Has anyone actually done any testing on this? Specifically, I'm wondering if the benefit of adding 2 more drives to a RAID10 outweighs whatever penalties there are to having pg_xlog on that RAID10 with all the rest of the data. -- 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 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] pg_xlog on data partition with BBU RAID
On Fri, 2006-06-09 at 14:41, Jim C. Nasby wrote: > AFAIK, the reason why seperating pg_xlog from the base files provides so > much performance is because the latency on pg_xlog is critical: a > transaction can't commit until all of it's log data is written to disk > via fsync, and if you're trying to fsync frequently on the same drive as > the data tables are on, you'll have a big problem with the activity on > the data drives competing with trying to fsync pg_xlog rapidly. > > But if you have a raid array with a battery-backed controller, this > shouldn't be anywhere near as big an issue. The fsync on the log will > return very quickly thanks to the cache, and the controller is then free > to batch up writes to pg_xlog. Or at least that's the theory. > > Has anyone actually done any testing on this? Specifically, I'm > wondering if the benefit of adding 2 more drives to a RAID10 outweighs > whatever penalties there are to having pg_xlog on that RAID10 with all > the rest of the data. I tested it WAY back when 7.4 first came out on a machine with BBU, and it didn't seem to make any difference HOW I set up the hard drives, RAID-5, 1+0, 1 it was all about the same. With BBU the transactions per second varied very little. If I recall correctly, it was something like 600 or so tps with pgbench (scaling and num clients was around 20 I believe) It's been a while. In the end, that server ran with a pair of 18 Gig drives in a RAID-1 and was plenty fast for what we used it for. Due to corporate shenanigans it was still running pgsql 7.2.x at the time. ugh. I've not got access to a couple of Dell servers I might be able to test this on... After our security audit maybe. ---(end of broadcast)--- TIP 6: explain analyze is your friend
FW: [PERFORM] pg_xlog on data partition with BBU RAID
Forwarding to -performance From: Alan Hodgson [mailto:[EMAIL PROTECTED] On Friday 09 June 2006 12:41, "Jim C. Nasby" <[EMAIL PROTECTED]> wrote: > Has anyone actually done any testing on this? Specifically, I'm > wondering if the benefit of adding 2 more drives to a RAID10 outweighs > whatever penalties there are to having pg_xlog on that RAID10 with all > the rest of the data. I have an external array with 1GB of write-back cache, and testing on it before deployment showed no difference under any workload I could generate between having pg_xlog on a separate RAID-1 or having it share a RAID-10 with the default tablespace. I left it on the RAID-10, and it has been fine there. We have a very write-heavy workload. -- "If a nation expects to be ignorant and free, in a state of civilization, it expects what never was and never will be." -- Thomas Jefferson ---(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
