Re: [PERFORM] Best suiting OS
However, I have certainly seen some inefficiencies with Linux and large use of shared memory -- and I wouldn't be surprised if these problems don't exist on FreeBSD or OpenSolaris. This came on the freebsd-performance-list a few days ago. http://docs.freebsd.org/cgi/getmsg.cgi?fetch=13001+0+current/freebsd-performance -- regards Claus When lenity and cruelty play for a kingdom, the gentler gamester is the soonest winner. Shakespeare -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Best suiting OS
Claus Guttesen kome...@gmail.com wrote: http://docs.freebsd.org/cgi/getmsg.cgi?fetch=13001+0+current/freebsd-performance Not being particularly passionate about any OS, I've been intrigued by the FreeBSD benchmarks. However, management is reluctant to use boxes which don't have heavily-advertised decals on the front. At the moment they're going with IBM X-series boxes, and FreeBSD isn't supported, so we'd be on our own. Has anyone had any experience with this combination? (In particular, our biggest machines are x3850 M2 boxes.) You can download a live-cd and see if it recognizes disk-controller, nic etc. on HP bce and bge, em GB nics works fine. Oh, and of course I dispute the supremacy of vim as an editor -- why use that when you've got ed? ;-) I have tried edlin on dos 3 or something like that. But don't recall the commands! :-) -- regards Claus When lenity and cruelty play for a kingdom, the gentler gamester is the soonest winner. Shakespeare -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Bad performance of SELECT ... where id IN (...)
I have a big performance problem in my SQL select query: select * from event where user_id in (500,499,498,497,496,495,494,493,492,491,490,489,488,487,486,485,484,483,482,481,480,479,478,477,476,475,474,473,472,471,470,469,468,467,466,465,464,463,462,461,460,459,458,457,456,455,454,453,452,451,450,449,448,447,446,445,444,443,442,441,440,439,438,437,436,435,434,433,432,431,430,429,428,427,426,425,424,423,422,421,420,419,418,417,416,415,414,413,412,411,410,409,408,407,406,405,404,403,402,401, 400,399,398,397,396,395,394,393,392,391,390,389,388,387,386,385,384,383,382,381,380,379,378,377,376,375,374,373,372,371,370,369,368,367,366,365,364,363,362,361,360,359,358,357,356,355,354,353,352,351,350,349,348,347,346,345,344,343,342,341,340,339,338,337,336,335,334,333,332,331,330,329,328,327,326,325,324,323,322,321,320,319,318,317,316,315,314,313,312,311,310,309,308,307,306,305,304,303,302,301, 300,299,298,297,296,295,294,293,292,291,290,289,288,287,286,285,284,283,282,281,280,279,278,277,276,275,274,273,272,271,270,269,268,267,266,265,264,263,262,261,260,259,258,257,256,255,254,253,252,251,250,249,248,247,246,245,244,243,242,241,240,239,238,237,236,235,234,233,232,231,230,229,228,227,226,225,224,223,222,221,220,219,218,217,216,215,214,213,212,211,210,209,208,207,206,205,204,203,202,201, 200,199,198,197,196,195,194,193,192,191,190,189,188,187,186,185,184,183,182,181,180,179,178,177,176,175,174,173,172,171,170,169,168,167,166,165,164,163,162,161,160,159,158,157,156,155,154,153,152,151,150,149,148,147,146,145,144,143,142,141,140,139,138,137,136,135,134,133,132,131,130,129,128,127,126,125,124,123,122,121,120,119,118,117,116,115,114,113,112,111,110,109,108,107,106,105,104,103,102,101, 100,99,98,97,96,95,94,93,92,91,90,89,88,87,86,85,84,83,82,81,80,79,78,77,76,75,74,73,72,71,70,69,68,67,66,65,64,63,62,61,60,59,58,57,56,55,54,53,52,51,50,49,48,47,46,45,44,43,42,41,40,39,38,37,36,35,34,33,32,31,30,29,28,27,26,25,24,23,22,21,20,19,18,17,16,15,14,13,12,11,10,9,8,7,6,5,4,3,2,1,0); What happens if you change the query to select * from event where user_id = 0 and user_id = 500; ? :-) -- regards Claus When lenity and cruelty play for a kingdom, the gentler gamester is the soonest winner. Shakespeare -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Speed while runnning large transactions.
I have a transaction running at the database for around 20 hours .. still isn't done. But during the last hours it has come to the point where it really hurts performance of other queries. Given pg_stat_activity output there seems to be no locks interfering but the overall cpu-usage of all queries continue to rise. iowait numbers are also very low. What can I do to make the system handle other queries better? Can you post the query? Do you 'vacuum analyze' on a regular basis? You can also post your conf-file and post the last five lines from a 'vacuum analyze verbose'. -- regards Claus When lenity and cruelty play for a kingdom, the gentler gamester is the soonest winner. Shakespeare -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Seeking performance advice and explanation for high I/O on 8.3
max_fsm_pages = 280 max_fsm_relations = 16 What does the last couple of lines from a 'vacuum analyze verbose' say? I have max_fsm_pages = 400 and max_fsm_relations = 1500. You can also try to lower random_page_cost to a lower value like 1.2 but I doubt this will help in your case. last couple lines from 'vacuumdb -a -v -z': INFO: free space map contains 114754 pages in 42148 relations DETAIL: A total of 734736 page slots are in use (including overhead). v--- 734736 page slots are required to track all free space. ^--- Current limits are: 280 page slots, 16 relations, using 26810 kB. You can lower your max_fsm_pages setting to a number above 'xyz page slots required ...' to 100 and fsm-relations to like 5. -- regards Claus When lenity and cruelty play for a kingdom, the gentler gamester is the soonest winner. Shakespeare -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Abnormal performance difference between Postgres and MySQL
The execution time has not improved. I am going to increase the shared_buffers now keeping the work_mem same. Have you performed a vacuum analyze? -- regards Claus When lenity and cruelty play for a kingdom, the gentler gamester is the soonest winner. Shakespeare -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Abnormal performance difference between Postgres and MySQL
Query: select A0.Subj, A2.Obj From jena_g1t1_stmt A0, jena_g1t1_stmt A1, jena_g1t1_stmt A2 Where A0.Prop='Uv::http://prismstandard.org/namespaces/1.2/basic/isPartOf' AND A0.Obj='Uv::http://www.utdallas.edu/~farhan.husain/IngentaConnect/issue1_1' AND A0.GraphID=1 AND A0.Subj=A1.Subj AND A1.Prop='Uv::http://www.w3.org/1999/02/22-rdf-syntax-ns#type' AND A1.Obj='Uv::http://metastore.ingenta.com/ns/structure/Article' AND A1.GraphID=1 AND A0.Subj=A2.Subj AND A2.Prop='Uv::http://prismstandard.org/namespaces/1.2/basic/startingPage' AND A2.GraphID=1; Table: Table public.jena_g1t1_stmt Column | Type | Modifiers -+ +--- subj | character varying(250) | not null prop | character varying(250) | not null obj | character varying(250) | not null graphid | integer | Indexes: jena_g1t1_stmt_ixo btree (obj) jena_g1t1_stmt_ixsp btree (subj, prop) Isn't it missing an index on the column prop? select ... where A0.Prop='foo' and ... -- Can you please elaborate a bit? I thought that A0.Prop would ignore the composite index created on the columns subj and prop but this does not seem to be the case. -- regards Claus When lenity and cruelty play for a kingdom, the gentler gamester is the soonest winner. Shakespeare -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Abnormal performance difference between Postgres and MySQL
I am doing a performance comparison between running Jena with MySQL and Postgres. I used the 8.3-community version of Postgres and MySQL 5.0.67. I have run several queries to both MySQL and Postgres and all of them took similar amount of time to execute except one. For the following query to a table having 10,003,728 rows, MySQL takes 0.11 seconds to return results whereas Postgres takes like 1 hour and 20 minutes! Query: select A0.Subj, A2.Obj From jena_g1t1_stmt A0, jena_g1t1_stmt A1, jena_g1t1_stmt A2 Where A0.Prop='Uv::http://prismstandard.org/namespaces/1.2/basic/isPartOf' AND A0.Obj='Uv::http://www.utdallas.edu/~farhan.husain/IngentaConnect/issue1_1' AND A0.GraphID=1 AND A0.Subj=A1.Subj AND A1.Prop='Uv::http://www.w3.org/1999/02/22-rdf-syntax-ns#type' AND A1.Obj='Uv::http://metastore.ingenta.com/ns/structure/Article' AND A1.GraphID=1 AND A0.Subj=A2.Subj AND A2.Prop='Uv::http://prismstandard.org/namespaces/1.2/basic/startingPage' AND A2.GraphID=1; Table: Table public.jena_g1t1_stmt Column | Type | Modifiers -+ +--- subj | character varying(250) | not null prop | character varying(250) | not null obj | character varying(250) | not null graphid | integer | Indexes: jena_g1t1_stmt_ixo btree (obj) jena_g1t1_stmt_ixsp btree (subj, prop) Isn't it missing an index on the column prop? select ... where A0.Prop='foo' and ... -- regards Claus When lenity and cruelty play for a kingdom, the gentler gamester is the soonest winner. Shakespeare -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] UFS 2: soft updates vs. gjournal (AKA: Choosing a filesystem 2.)
Should I use gjournal on FreeBSD 7? Or just soft updates? Here is my opinion: I suspect that gjournal would be much slower than soft updates. Also gjournal is relatively new code, not very well tested. But gjournal is better when the system crashes. Although I have heard that sometimes gjournal will crash the system itself. There are more pros for soft updates I would pefer that. But please let me know if I'm wrong. If softupdates suites your needs why not just use that? :-) Is performance adequate with softupdates? I have a 103 GB db on FreeBSD 7.0 and softupdates and it has survived one unplanned stop when we had a power-outage lasting some hours. -- regards Claus When lenity and cruelty play for a kingdom, the gentler gamester is the soonest winner. Shakespeare -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] You may need to increase mas_loks_per_trasaction
Considering a quad core server processor, 4 GBs of RAM memory, disk Sata 2. What is the recommended setting for the parameters: max_connections:70 Depends on how many clients that access the database. shared_buffers? I have mine at 512 MB but I will lower it and see how it affects performance. I have 16 GB in my server. max_fsm_relations? max_fsm_pages? Perform a vacuum analyze verbose and look at the last few lines. This will tell you whether you need to increase max_fsm_*. Consider lowering random_page_cost so it favoes indexex more often than seq. scans. But if you don't get a decent raid-controller your data will move slow and tuning will only make a minor difference. -- regards Claus When lenity and cruelty play for a kingdom, the gentler gamester is the soonest winner. Shakespeare -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] how does pg handle concurrent queries and same queries
I have taken over the maintenance of a server farm , recently. 2 webserver on db server. They are quite powerful 2 processor xeon w/ 6Gig of ram . Couple of days ago we had a serious performance hit and the db server (pg. v7.4) was overloaded w/ something in a way that operating system was almost not able to respond or in cases it did not. After some analysis i suspect that there is a query that takes up to 1 second and that is the cause. Upon each page loading this query fires and takes the one second and blocks the page to load completly . The load was roughly ~300 connections in one minute . So here are my questions : · Why does the second and the later queries take the whole on second if the dataset is the same . Shouldn't PG realise that the query is the same so i give the user the same resultset ? · How do I know if one query blocks the other ? · Is there a way to log the long running queries in 7.4 ? If not is it available in any newer version ? Can you post the queries? Can you provide an 'analyze explain'? Do you perform a 'vacuum analyze' on a regular basis? -- regards Claus When lenity and cruelty play for a kingdom, the gentlest gamester is the soonest winner. Shakespeare -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] log_statement at postgres.conf
After setting log_statement='all' at postgres.conf, then i'm rebooting OS [freeBSD or CentOS], i can't find where log file created from log_statement='all' located... FYI, location of postgres.conf at /var/lib/pgsql/data/postgres.conf many thanks.. I added the following to FreeBSD: /etc/newsyslog.conf: /var/log/postgresql 600 7 *@T00 JC /etc/syslog.conf: local0.*/var/log/postgresql /usr/local/pgsql/data/postgresql.conf: log_destination = 'syslog' syslog_facility = 'LOCAL0' syslog_ident = 'postgres' log_min_duration_statement = 100# -1 is disabled, 0 logs all statements, in ms. Remember to touch /var/log/postgresql before restarting syslogd (kill -HUP syslog-pid). Chmod 0700 so only root can read the log-file. Adjust log_min_duration_statement to your needs. I found this recipe somewhere, but don't remember where so I can't give credit to the that person. -- regards Claus When lenity and cruelty play for a kingdom, the gentlest gamester is the soonest winner. Shakespeare -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Hardware suggestions for high performance 8.3
We have a database with lots of small simultaneous writes and reads (millions every day) and are looking at buying a good hardware for this. What are your suggestions. What we are currently looking at is. Dual Quad Core Intel 8 - 12 GB RAM 10 disks total. 4 x 146 GB SAS disk in RAID 1+0 for database 6 x 750 GB SATA disks in RAID 1+0 or RAID 5 for OS and transactions logs. Good RAID controller with lots of memory and BBU. I have very positive experiences with HP's DL360 and DL380. The latter slightly more expandable (2U vs. 1U). I have used the internal p400i-controller with 512 MB cache on the DL380 and bought an external p800-controller (512 MB cache as well) and a MSA-70-cabinet. I've have 11 disks in raid-6 (one hotspare). I don't see any reason to mix sas- and sata-disks with different sizes. I'd go for sas-disks, smaller and faster, less power and heat. Raid 1+0 or raid-6 does not seem to make much of a difference today as it used to if you have more than 6-7 disks. The DL380 is a 4-way woodcrest at 3 GHz and 16 GB ram and the DL360 is a two-way woodcrest at 2.66 GHz with 16 GB. My personal preference is FreeBSD and the DL3x0-servers all run without problems on this platform. But choose your OS depending on what you're most comfortable with. And choose hardware according to what your OS supports. Areca-controllers may also be worth looking into but I haven't tried these myself. Our largest table has 85 mill. entries. -- regards Claus When lenity and cruelty play for a kingdom, the gentlest gamester is the soonest winner. Shakespeare -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Hardware suggestions for high performance 8.3
If you have a good RAID controller with BBU cache, then there's no point splitting the discs into two sets. You're only creating an opportunity to under-utilise the system. I'd get ten identical discs and put them in a single array, probably RAID 10. OK, thats good to know. Really want to keep it as simple as possible. Would you turn off fsync if you had a controller with BBU? =) No, don't do that. Leaving this setting on is *highly* recommended unless you have data which can easily be reproduced. :-) -- regards Claus When lenity and cruelty play for a kingdom, the gentlest gamester is the soonest winner. Shakespeare -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Join runs for 10 hours and then fills up 1.3TB of disk space
I have a table with 9,961,914 rows in it (see the describe of bigtab_stats_fact_tmp14 below) I also have a table with 7,785 rows in it (see the describe of xsegment_dim below) I'm running the join shown below and it takes 10 hours and eventually runs out of disk space on a 1.4TB file system I've included below a describe of both tables, the join and an explain plan, any help / suggestions would be much appreciated ! I need to get this beast to run as quickly as possible (without filling up my file system) Thanks in advance... What version of postgresql are you using? According to http://www.postgresql.org/docs/8.2/static/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-MEMORY you may benefit from adjusting work_mem. You also index segment_srcid (in table xsegment_dim) but if you search for NULL and you have enough of those it defaults to a seq. scan: Seq Scan on xsegment_dim segdim (cost=0.00..1354.85 rows=3643 width=40) Filter: (segment_srcid IS NULL) Maby you could insert some default value into segment_srcid (some arbitrary large numbers) instead of NULL and then search for values greater than?? You could also try to lower random_page_cost from default to 2. select f14.xpublisher_dim_id, f14.xtime_dim_id, f14.xlocation_dim_id, f14.xreferrer_dim_id, f14.xsite_dim_id, f14.xsystem_cfg_dim_id, f14.xaffiliate_dim_id, f14.customer_id, pf_dts_id, episode_id, sessionid, bytes_received, bytes_transmitted, total_played_time_sec, segdim.xsegment_dim_id as episode_level_segid from bigtab_stats_fact_tmp14 f14, xsegment_dim segdim where f14.customer_id = segdim.customer_srcid and f14.show_id = segdim.show_srcid and f14.season_id = segdim.season_srcid and f14.episode_id = segdim.episode_srcid and segdim.segment_srcid is NULL; QUERY PLAN --- Merge Join (cost=1757001.74..73569676.49 rows=3191677219 width=118) Merge Cond: ((segdim.episode_srcid = f14.episode_id) AND (segdim.customer_srcid = f14.customer_id) AND (segdim.show_srcid = f14.show_id) AND (segdim.season_srcid = f14.season_id)) - Sort (cost=1570.35..1579.46 rows=3643 width=40) Sort Key: segdim.episode_srcid, segdim.customer_srcid, segdim.show_srcid, segdim.season_srcid - Seq Scan on xsegment_dim segdim (cost=0.00..1354.85 rows=3643 width=40) Filter: (segment_srcid IS NULL) - Sort (cost=1755323.26..1780227.95 rows=9961874 width=126) Sort Key: f14.episode_id, f14.customer_id, f14.show_id, f14.season_id - Seq Scan on bigtab_stats_fact_tmp14 f14 (cost=0.00..597355.74 rows=9961874 width=126) (9 rows) # \d bigtab_stats_fact_tmp14 Table public.bigtab_stats_fact_tmp14 Column |Type | Modifiers --+-+--- pf_dts_id | bigint | pf_device_id | bigint | segment_id | bigint | cdn_id | bigint | collector_id | bigint | digital_envoy_id | bigint | maxmind_id | bigint | quova_id | bigint | website_id | bigint | referrer_id | bigint | affiliate_id | bigint | custom_info_id | bigint | start_dt | timestamp without time zone | total_played_time_sec| numeric(18,5) | bytes_received | bigint | bytes_transmitted| bigint | stall_count | integer | stall_duration_sec | numeric(18,5) | hiccup_count | integer | hiccup_duration_sec | numeric(18,5) | watched_duration_sec | numeric(18,5) | rewatched_duration_sec | numeric(18,5) | requested_start_position | numeric(18,5) | requested_stop_position | numeric(18,5) | post_position| numeric(18,5) | is_vod | numeric(1,0)| sessionid| bigint | create_dt| timestamp without time zone | segment_type_id | bigint | customer_id | bigint | content_publisher_id | bigint | content_owner_id | bigint | episode_id | bigint | duration_sec | numeric(18,5) | device_id| bigint | os_id|
Re: [PERFORM] Installation Steps to migrate to Postgres 8.3.1
We want to migrate from postgres 8.1.3 to postgres 8.3.1. Can anybody list out the installation steps to be followed for migration. Do we require to take care of something specially. Perform a pg_dump, do a restore and validate your sql-queries on a test-server. -- regards Claus When lenity and cruelty play for a kingdom, the gentlest gamester is the soonest winner. Shakespeare -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Pros and Cons of 8.3.1
Can anyone who have started using 8.3.1 list out the pros and cons. I upgraded to 8.3.1 yesterday from 8.3.0. I've used 8.3.0 since it was released and it's working fine. I upgraded from 7.4 (dump/restore) and it was working out of the box. We have somewhat simple sql-queries so there was no need to change/alter these. The largest table has approx. 85 mill. records (image-table). One thing I had newer seen before was that duplicate rows was inserted into our order-table but I don't know whether this is due to changes in the web-app or 8.3.0. Now that I upgraded to 8.3.1 I will wait a few weeks and see if I get the same error before I alter the column and add a unique contraint. So from a 7.4-perspective and fairly simple queries I don't see any issues. -- regards Claus When lenity and cruelty play for a kingdom, the gentlest gamester is the soonest winner. Shakespeare -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Where do a novice do to make it run faster?
1) hardware 2) rewriting my queries and table structures 3) using more predefined queries 4) tweek parameters in the db conf files Of these points: 1) is nothing I can do about right now, but in the future perhaps. 2) will be quite hard right now since there is more code than time. 3) almost like 2 but perhaps more do-able with the current constraints. 4) This seems to be the easiest one to start with... So what should I do/read concerning point 4? If you have other good suggestions I'd be very interested in that. Thank you :-) You can provide information postgresql-version, what type of queries you're running, some explain analyze of those, and what type of hardware you're running and what OS is installed. -- regards Claus When lenity and cruelty play for a kingdom, the gentlest gamester is the soonest winner. Shakespeare -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Planning a new server - help needed
I need to install a new server for postgresql 8.3. It will run two databases, web server and some background programs. We already have a server but it is becoming slow and we would like to have something that is faster. It is a cost sensitive application, and I would like to get your opinion in some questions. The database itself is an OLTP system. There are many smaller tables, and some bigger ones (biggest table with 1.2 million records, table size 966MB, indexes size 790MB). In the bigger tables there are only a few records updated frequently, most of the other records are not changed. The smaller tables are updated continuously. Question 1. We are going to use PostgreSQL 3.1 with FreeBSD. The pg docs say that it is better to use FreeBSD because it can alter the I/O priority of processes dynamically. The latest legacy release is 6.3 which is probably more stable. However, folks say that 7.0 has superior performance on the same hardware. Can I use 7.0 on a production server? I guess you mean postgresql 8.3.1? :-) I use FreeBSD 7 release on a 8-way HP DL360 G5 with a ciss controller. Works out of the box and I haven't had any issue with 7.0 at all. Question 2. SCSI or SATA? I plan to buy a RocketRAID 3520 controller with 8 SATA 2 disks. The operating system would be on another disk pair, connected to the motherboard's controller. I wonder if I can get more performance with SCSI, for the same amount of money? (I can spend about $1500 on the controller and the disks, that would cover 10 SATA 2 disks and the controller.) SAS would probably be the way to go. I haven't tried the rocketraid-controller. I use the built-in p400i-controller on my servers using the ciss-driver. I've heard many positive remarks about areca. Question 3. FreeBSD 7.0 can use the ZFS file system. I suspect that UFS 2 + soft updates will be better, but I'm not sure. Which is better? I'd stick with ufs2 atm. There are some issues with zfs which probably have been ironed out by now but ufs2 has been deployed for a longer time. Performance-wise they are about the same. Question 4. How to make the partitions? This is the hardest question. Here is my plan: - the OS resides on 2 disks, RAID 1 - the databases should go on 8 disks, RAID 0 + 1 If you have enough disks raid-6 should perform almost as good as raid 1+0. I've setup 11 disks in raid-6 plus one hotspare so I can get more space out of it. Enough disks are approx. eight and up. -- regards Claus When lenity and cruelty play for a kingdom, the gentlest gamester is the soonest winner. Shakespeare -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] postgresql performance
Without knowing what a lakhs record is, I had the same question... and Wikipedia gave me the answer : it is an Indian word meaning 10^5, often used in indian english. Thank you (both OP and this post) for enlightening us with this word. -- regards Claus When lenity and cruelty play for a kingdom, the gentlest gamester is the soonest winner. Shakespeare -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-performance
Re: [PERFORM] 4s query want to run faster
The following query takes about 4s to run in a 16GB ram server. Any ideas why it doesn´t use index for the primary keys in the join conditions? Maby random_page_cost is set too high? What version are you using? -- regards Claus When lenity and cruelty play for a kingdom, the gentlest gamester is the soonest winner. Shakespeare ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Benchmark Data requested
There are some results here that show PostgreSQL is slower in some cases than Monet and MySQL. Of course these results were published immediately prior to 8.2 being released, plus run out-of-the-box, so without even basic performance tuning. Would anybody like to repeat these tests with the latest production versions of these databases (i.e. with PGSQL 8.3), and with some sensible tuning settings for the hardware used? It will be useful to get some blind tests with more sensible settings. http://monetdb.cwi.nl/projects/monetdb//SQL/Benchmark/TPCH/ Multiple runs from different people/different hardware is useful since they help to iron-out differences in hardware and test methodology. So don't worry if you see somebody else doing this also. Here is another graph: http://tweakers.net/reviews/649/7 Without monetdb though. -- regards Claus When lenity and cruelty play for a kingdom, the gentlest gamester is the soonest winner. Shakespeare ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] 8x2.5 or 6x3.5 disks
I missed the initial post in this thread, but I haven't seen any 15K rpm 2.5 drives, so if you compare 10K rpm 2.5 drives with 15K rpm 3.5 drives you will see differences (depending on your workload and controller cache) I have some 15K rpm 2.5 sas-drives from HP. Other vendors have them as well. -- regards Claus When lenity and cruelty play for a kingdom, the gentlest gamester is the soonest winner. Shakespeare ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] scheduler
Which scheduler is recommended for a box that is dedicated to running postgres? I've asked google and found no answers. Is it the OS itself? -- regards Claus When lenity and cruelty play for a kingdom, the gentlest gamester is the soonest winner. Shakespeare ---(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] Limited performance on multi core server
Does anyone have any ideas what my bottle neck might be and what I can do about it? Your bottleneck is that you are using a very old version of PostgreSQL. Try 8.2 or (if you can) the 8.3 beta series -- it scales a _lot_ better in this kind of situation. You won't know until you've seen what queries are performed. Changing db-parameters is a short-term solution, upgrading to a newer version does require some planning. -- regards Claus When lenity and cruelty play for a kingdom, the gentlest gamester is the soonest winner. Shakespeare ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Limited performance on multi core server
I have a 4 * dual core 64bit AMD OPTERON server with 16G of RAM, running postgres 7.4.3. This has been recompiled on the server for 64 stored procedure parameters, (I assume this makes postgres 64 bit but are not sure). When the server gets under load from database connections executing reads, lets say 20 - 40 concurrent reads, the CPU's seem to limit at about 30-35% usage with no iowait reported. If I run a simple select at this time it takes 5 seconds, the same query runs in 300 millis when the server is not under load so it seems that the database is not performing well even though there is plenty of spare CPU. There does not appear to be large amounts of disk IO and my database is about 5.5G so this should fit comfortably in RAM. changes to postgresql.sql: max_connections = 500 shared_buffers = 96000 sort_mem = 10240 effective_cache_size = 100 Does anyone have any ideas what my bottle neck might be and what I can do about it? You might want to lower shared_buffers to a lower value. Mine is set at 32768. Is your db performing complex sort? Remember that this value is per connection. Maby 1024. effective_cache_size should also be lowered to something like 32768. As far as I understand shared_buffers and effective_cache_size have to be altered in reverse, ie. when lowering one the other can be raised. HTH. -- regards Claus When lenity and cruelty play for a kingdom, the gentlest gamester is the soonest winner. Shakespeare ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Limited performance on multi core server
Thanks for the information Claus, Why would reducing the effective cache size help the processor usage? It seems that there is plenty of resources on the box although I can see that 10MB of sort space could mount up if we had 500 connections but at the moment we do not have anything like that number. There is a discussion at http://archives.postgresql.org/pgsql-performance/2005-06/msg00477.php which can give a clearer picture. But in general rasing values can be counterproductive. If you know that you won't need more than 250 connections that would be a reasonable value. You may wan't to read http://www.varlena.com/GeneralBits/Tidbits/annotated_conf_e.html as well. This has some rules of thumb on the settings for 7.4.x. -- regards Claus When lenity and cruelty play for a kingdom, the gentlest gamester is the soonest winner. Shakespeare ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] dell versus hp
Apart from the disks, you might also investigate using Opterons instead of Xeons. there appears to be some significant dent in performance between Opteron and Xeon. Xeons appear to spend more time in passing around ownership of memory cache lines in case of a spinlock. It's not yet clear whether or not here has been worked around the issue. You should at least investigate it a bit. We're using a HP DL385 ourselves which performs quite well. Not atm. Until new benchmarks are published comparing AMD's new quad-core with Intel's ditto, Intel has the edge. http://tweakers.net/reviews/657/6 -- regards Claus When lenity and cruelty play for a kingdom, the gentlest gamester is the soonest winner. Shakespeare ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] dell versus hp
All of our existing servers are from Dell, but I want to look at some other options as well. We are currently looking at rack boxes with 8 internal SAS discs. Two mirrored for OS, Two mirrored for WAL and 4 in raid 10 for the base. Here are our current alternatives: 1) Dell 2900 (5U) 8 * 146 GB SAS 15Krpm 3,5 8GB ram Perc 5/i. battery backup. 256MB ram. 2 * 4 Xeon 2,66GHz 2) Dell 2950 (2U) 8 * 146 GB SAS 10Krpm 2,5 (not really selectable, but I think the webshop is wrong..) 8GB ram Perc 5/i. battery backup. 256MB ram. 2 * 4 Xeon 2,66GHz 3) HP ProLiant DL380 G5 (2U) 8 * 146 GB SAS 10Krpm 2,5 8GB ram P400 raid controller. battery backup. 512MB ram. 2 * 2 Xeon 3GHz All of those alternatives cost ca the same. How much (in numbers) better are 15K 3,5 than 10K 2,5? What about the raid controllers? Any other alternatives in that price-range? When writing is important you want to use 15K rpm disks. I personally use the DL380 and is very satisfied with the hardware and the buildin ciss-controller (with 256 MB cache using 10K rpm disks). How much space do you need? 72 GB is the largest 15K 2.5 sas-disk from HP. -- regards Claus When lenity and cruelty play for a kingdom, the gentlest gamester is the soonest winner. Shakespeare ---(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
[PERFORM] hp ciss on freebsd
Hi. We are using a HP DL 380 G5 with 4 sas-disks at 10K rpm. The controller is a built in ciss-controller with 256 MB battery-backed cache. It is partitioned as raid 1+0. Our queries are mainly selects. I will get four 72 GB sas-disks at 15K rpm. Reading the archives suggest raid 1+0 for optimal read/write performance, but with a solid raid-controller raid 5 will also perform very well when reading. Is the ciss-controller found in HP-servers a better raid-controller compared to the areca-raid-controller mentioned on this list? -- regards Claus When lenity and cruelty play for a kingdom, the gentlest gamester is the soonest winner. Shakespeare ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Hardware spec
Get yourself the ability to benchmark your application. This is invaluable^W a requirement for any kind of performance tuning. I'm pretty happy with the performance of the database at this stage. Correct me if I'm wrong, but AFAIK a load of 3.5 on a quad is not overloading it. It also seem to scale well, so if application's demand increases I see a minimal increase in database server load. I was just looking for some pointers as to where to go to ITO hardware for the future, as I can now spec a new machine. I mean is it really worth while going for one of those RAID controllers with the battery backed cache, for instance. If so, are there any specific ones to look out for? Which is better RAID 5, a large RAID 10 or smaller RAID 10's? Should I bother with RAID at all? These issues have been covered before. You may want to search the archives and get the relevant pointers. -- regards Claus When lenity and cruelty play for a kingdom, the gentlest gamester is the soonest winner. Shakespeare ---(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] postgres memory management issues?
I've recently run into problems with my kernel complaining that I ran out of memory, thus killing off postgres and bringing my app to a grinding halt. I'm on a 32-bit architecture with 16GB of RAM, under Gentoo Linux. Naturally, I have to set my shmmax to 2GB because the kernel can't support more (well, I could set it to 3GB, but I use 2GB for safety). Wouldn't it make sense to install an amd64 version with so much RAM? -- regards Claus When lenity and cruelty play for a kingdom, the gentlest gamester is the soonest winner. Shakespeare ---(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] Is it possible to know where is the deadlock
The iostat -c says about 8% of time waiting for IO. I'm afraid this is due to locks between concurrent queries, is there anyway to have more info about? I do believe that if you told what OS you're running, what pg-version you're running, what type of sql-statements you perform the list can provide some help. -- regards Claus When lenity and cruelty play for a kingdom, the gentlest gamester is the soonest winner. Shakespeare ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Hardware suggestions
At our institute we are currently establishing a small GIS working group. The data storage for vector data should be the central PostGIS system. Raster data will be held in file system. Mostly the users are accessing the data base in read only mode. From the client side there is not much write access this only will be done by the admin of the system to load new datasets. A prototype is currently running on an old desktop pc with ubuntu dapper - not very powerfull, of course! We have about 1 € to spend for a new server including the storage. Do you have any recommendations for us? When it comes to server-hardware I'd go for intel's dual-core (woodcrest) or quad-core. They seem to perform better atm. compared to opterons. -- regards Claus When lenity and cruelty play for a kingdom, the gentlest gamester is the soonest winner. Shakespeare ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Best OS for Postgres 8.2
I am about to order a new server for my Postgres cluster. I will probably get a Dual Xeon Quad Core instead of my current Dual Xeon. Which OS would you recommend to optimize Postgres behaviour (i/o access, multithreading, etc) ? I am hesitating between Fedora Core 6, CentOS and Debian. Can anyone help with this ? My only experience is with FreeBSD. My installation is running 6.2 and pg 7.4 on a four-way woodcrest and besides being very stable it's also performing very well. But then FreeBSD 6.x might not scale as well beyond four cores atm. There you probably would need FreeBSD 7 which is the development branch and should require extensive testing. How big will the db be in size? -- regards Claus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Best OS for Postgres 8.2
In #postgresql on freenode, somebody ever mentioned that ZFS from Solaris helps a lot to the performance of pgsql, so dose anyone have information about that? the filesystem you use will affect the performance of postgres significantly. I've heard a lot of claims for ZFS, unfortunantly many of them from people who have prooven that they didn't know what they were talking about by the end of their first or second e-mails. much of the hype for ZFS is it's volume management capabilities and admin tools. Linux has most (if not all) of the volume management capabilities, it just seperates them from the filesystems so that any filesystem can use them, and as a result you use one tool to setup your RAID, one to setup snapshots, and a third to format your filesystems where ZFS does this in one userspace tool. Even though those posters may have proven them selves wrong, zfs is still a very handy fs and it should not be judged relative to these statements. once you seperate the volume management piece out, the actual performance question is a lot harder to answer. there are a lot of people who say that it's far faster then the alternate filesystems on Solaris, but I haven't seen any good comparisons between it and Linux filesystems. One could install pg on solaris 10 and format the data-area as ufs and then as zfs and compare import- and query-times and other benchmarking but comparing ufs/zfs to Linux-filesystems would also be a comparison of those two os'es. -- regards Claus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Wrong plan sequential scan instead of an index one
Hi all, take a look at those plans: Try changing random_page_cost from the default 4 to 2 in postgresql.conf: random_page_cost = 2 The default in postgresql is somewhat conservative. This setting indicates for postgresql how fast your disks are, the lower the faster. Could this setting be changed to 2 as default rather than 4? regards Claus ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Estimate the size of the SQL file generated by pg_dump utility
I'm curious, what problem does the disclaimer cause? I wrote the following TOS for my personal system: https://www.potentialtech.com/cms/node/9 Excerpt of the relevant part: I have no idea if that's legally binding or not, but I've talked to a few associates who have some experience in law, and they all argue that email disclaimers probably aren't legally binding anyway -- so the result is undefined. No, it's not legally binding. Agreements are only binding if both parties agree, and someone sending you email has not consented to your statement. If I send you something with a copyright mark, you'd better respect it unless you have a signed agreement granting you rights. Federal law always wins. Disclaimers are bad for two reasons. First, they're powerless. Just because Acme Corp. attaches a disclaimer doesn't mean they've absolved themselves of responsibility for the actions of their employees. Second, they're insulting to the employees. It's a big red flag saying, We, Acme Corp., hire clowns we don't trust, and THIS person may be one of them! Dear sirs, this is off-topic at best. Pls. discontinue this thread. regards Claus ---(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] which Xeon processors don't have the context switching problem
I recall a reference on the list indicating that newer Xeon processors don't suffer from the context switching problem reported last year. In searching the archives, I can't find any specific info indentifying which Xeon processors don't have this problem. Anyone point me to a reference? We recently migrated to a woodcrest @ 3 GHz from a 2 Ghz opteron. The woodcrest seems to be enjoying doing db-related work. I don't have numbers other than load is much lower now. Is this in any way related to the version of Postgresql one is running? We're headed for 8, but have a bit of work before we can get there. We are currently on 7.4.16. We are at 7.4.14 which works fine atm. regards Claus ---(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] quad or dual core Intel CPUs
I am about to pull the trigger on a new machine after analyzing some discussions I posted here last year. I've been trying to spec out a reliable and powerfull enough machine where I won't have to replace it for some time. Currently I've been using a dual Xeon 3.06ghz with 4GB of ram and utilizing a RAID 1+0 configuration over a total 6 SCSI disks asside from the OS partition. We have about 10GB of data and will probably scale at about 1GB per month. We currently average about 200 queries per second and the 15 minute load average is about .30. I am running FreeBSD 6.1. At the end of last year, I specced out a new machine to replace this box. At that time, the quad core 2.66ghz were not available from my vendor and I was not planning to go that route. Now that they are available, I am considering the option. The main question here is whether FreeBSD 6.X and PostgreSQL 8.1 would be able to take advantage of the quad core and perform better than the 3.0Ghz dual core. The reason I ask is due to some conflicting benchmarking results I see posted on the spec.org website. Here is the full specification of the new box I hope to build and run FreeBSD 6.X and PostgreSQL on: - SuperMicro Dual Xeon X7DBE+ motherboard + 2 x Quad Core X5355 2.66Ghz OR + 2 x Dual Core 5160 3.0Ghz - 8 x 1GB PC2-4200 fully buffered DIMM - LSI MegaRAID SAS 8408E w/BBU 256MB - 16 x 73GB SAS disk So, question #1, to go dual core or quad core? Quad core in theory seems to scale the machine's processing potential by almost a factor of two. We recently migrated from a four way opteron @ 2 GHz with 8 GB to a four way woodcrest @ 3 GHz (HP DL380 G5) with 16 GB ram. I also upgraded FreeBSD from 6.0 to 6.2 and did a minor upgrade of postgresql from 7.4.9 to 7.4.12. The change was tremendous, the first few hours of after it went into production I had to doublecheck that our website worked, since the load was way below 1 whereas the load had been almost 100 during peak. I don't have any financial ties to HP but building a server from scratch may not be worth it, rather than spending time assemling all the different parts yourself I would suggest you get a server from one vendor who build a server according to your specs. The DL380 (also) has a 256 MB bbc controller, the nic works flawlessly with FreeBSD 6.2, all parts are well integrated, the frontbay can accomodate 8 146 GB SAS drives. This server is wellsuited as a postgresql-server. Approx. 200 reqest a sec. should be a problem unless the queries are heavy. regards Claus ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] quad or dual core Intel CPUs
Approx. 200 reqest a sec. should be a problem unless the queries are heavy. Thanks Claus thats good news! I'm having a reputable vendor build the box and test it for me before delivering. The bottom line of your message, did you mean 'should be not a problem'? I wonder what the main reason for your improvement, your ram was increased by a factor of 2, but 4 way opteron vs 4 way woodcrest performance must not be that significant. Sorry, the line should read 'should *not* be a problem', pardon for the confusion. So 200 queries/s should be fine, probably won't make the server sweat. I'm not shure what attributed most to the decrease when the load went from approx. 100 during peak to less than 1! Since the db-server is such a vital part of our infrastructure, I was reluctant to upgrade it, while load was below 10. But in November and December - when we have our most busy time - our website slowed to a crawl, thus phasing a new server in was an easy decision. The woodcrest is a better performer compared to the current opteron, the ciss-disk-controller also has 256 MB cache compared to the 64 MB LSI-logic controller in the former db-server, FreeBSD 6.2 is also a better performer than 6.0, but I haven't done any benchmarking on the same hardware. regards Claus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Worse perfomance on 8.2.0 than on 7.4.14
I have a simple query which uses 32ms on 7.4.14 and 1015ms on 8.2.0. I guess 7.4.14 creates a better execution plan than 8.2.0 for this query but i don't know how to get it to select a better one. Explain analyse output will be found near the end of the e-mail. Explain analyze is run several times to get a stable result so i guess the numbers presented is with as much as possible data in memory buffers. Query: select * from view_subset; run against 7.4.14 server. QUERY PLAN Nested Loop (cost=0.00..1400.86 rows=17 width=8) (actual time=0.161..26.287 rows=68 loops=1) - Index Scan using uut_result_subset_start_date_time_idx on uut_result_subset ur (cost=0.00..63.28 rows=18 width=4) (actual time=0.052..0.195 rows=68 loops=1) Index Cond: (start_date_time '2006-12-11 00:00:00'::timestamp without time zone) - Index Scan using step_result_uut_result_idx on step_result_subset sr (cost=0.00..74.28 rows=2 width=8) (actual time=0.149..0.379 rows=1 loops=68) Index Cond: (outer.id = sr.uut_result) Filter: (step_parent = 0) Total runtime: 26.379 ms Query: select * from view_subset; run against 8.4.0 server. QUERY PLAN -- Hash Join (cost=339.61..77103.61 rows=96 width=8) (actual time=5.249..1010.669 rows=68 loops=1) Hash Cond: (sr.uut_result = ur.id) - Index Scan using step_result_subset_parent_key on step_result_subset sr (cost=0.00..76047.23 rows=143163 width=8) (actual time=0.082..905.326 rows=176449 loops=1) Index Cond: (step_parent = 0) - Hash (cost=339.31..339.31 rows=118 width=4) (actual time=0.149..0.149 rows=68 loops=1) - Bitmap Heap Scan on uut_result_subset ur (cost=4.90..339.31 rows=118 width=4) (actual time=0.060..0.099 rows=68 loops=1) Recheck Cond: (start_date_time '2006-12-11 00:00:00'::timestamp without time zone) - Bitmap Index Scan on uut_result_subset_start_date_time_idx (cost=0.00..4.90 rows=118 width=0) (actual time=0.050..0.050 rows=68 loops=1) Index Cond: (start_date_time '2006-12-11 00:00:00'::timestamp without time zone) Total runtime: 1010.775 ms Did you lower random_page_cost in 8.2 (which defaults to 4.0)? If not try 2. regards Claus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Core 2 or Opteron
We're planning new server or two for PostgreSQL and I'm wondering Intel Core 2 (Woodcrest for servers?) or Opteron is faster for PostgreSQL now? When I look through hardware sites Core 2 wins. But I believe those tests mostly are being done in 32 bits. Does the picture change in 64 bits? We just migrated from a 4-way opteron @ 2 GHz with 8 GB ram to a DL380 G5 with a 4-way woodcrest @ 3 GHz and 16 GB ram. It was like night and day, system load dropped, not just quite a bit, but almost by a factor of 100 in worst case scenarios. Going from a 64 MB diskcontroller to a 256 MB ditto probably helped some and so did a speedup from 2 - 3 GHz, but overall it seems the new woodcrest cpu's feel at home doing db-stuff. This is on FreeBSD 6.2 RC1 and postgresql 7.4.14. And I also remember that in PostgreSQL Opteron earlier had huge advantage over older Xeons. But did Intel manage to change picture now? That was pre-woodcrest, aka. nocona and before. Horrible and the reason I went for opteron to begin with. But AMD probably wont sit idle. The link posted in another reply illustrates the current situation quite well. regards Claus ---(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] Best OS Configuration for Dual Xeon w/4GB Adaptec RAID 2200S
Here is my current configuration: Dual Xeon 3.06Ghz 4GB RAM Adaptec 2200S 48MB cache 4 disks configured in RAID5 FreeBSD 4.11 w/kernel options: options SHMMAXPGS=65536 options SEMMNI=256 options SEMMNS=512 options SEMUME=256 options SEMMNU=256 options SMP # Symmetric MultiProcessor Kernel options APIC_IO # Symmetric (APIC) I/O The OS is installed on the local single disk and postgres data directory is on the RAID5 partition. Maybe Adaptec 2200S RAID5 performance is not as good as the vendor claimed. It was my impression that the raid controller these days are optimized for RAID5 and going RAID10 would not benefit me much. I don't know whether 'systat -vmstat' is available on 4.x, if so try to issue the command with 'systat -vmstat 1' for 1 sec. updates. This will (amongst much other info) show how much disk-transfer you have. Also, I may be overlooking a postgresql.conf setting. I have attached the config file. You could try to lower shared_buffers from 3 to 16384. Setting this value too high can in some cases be counterproductive according to doc's I read. Also try to lower work_mem from 16384 to 8192 or 4096. This setting is for each sort, so it does become expensive in terms of memory when many sorts are being carried out. It does depend on the complexity of your sorts of course. Try to do a vacuum analyse in your crontab. If your aliases-file is set up correctly mails generated by crontab will be forwarded to a human being. I have the following in my (root) crontab (and mail to root forwarded to me): time /usr/local/bin/psql -d dbname -h dbhost -U username -c vacuum analyse verbose; In summary, my questions: 1. Would running PG on FreeBSD 5.x or 6.x or Linux improve performance? Going to 6.x would probably increase overall performance, but you have to try it out first. Many people report increased performance just by upgrading, some report that it grinds to a halt. But SMP-wise 6.x is a more mature release than 4.x is. Changes to the kernel from being giant-locked in 4.x to be fine-grained locked started in 5.x and have improved in 6.x. The disk- and network-layer should behave better. Linux, don't know. If your expertise is in FreeBSD try this first and then move to Linux (or Solaris 10) if 6.x does not meet your expectations. 3. Why isn't postgres using all 4GB of ram for at least caching table for reads? I guess it's related to the usage of the i386-architecture in general. If the zzeons are the newer noconas you can try the amd64-port instead. This can utilize more memory (without going through PAE). 4. Are there any other settings in the conf file I could try to tweak? max_fsm_pages and max_fsm_relations. You can look at the bottom of vacuum analyze and increase the values: INFO: free space map: 153 relations, 43445 pages stored; 45328 total pages needed Raise max_fsm_pages so it meet or exceed 'total pages needed' and max_fsm_relations to relations. This is finetuning though. It's more important to set work- and maintenance-mem correct. hth Claus ---(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] Best OS Configuration for Dual Xeon w/4GB Adaptec RAID 2200S
4. Are there any other settings in the conf file I could try to tweak? One more thing :-) I stumbled over this setting, this made the db (PG 7.4.9) make use of the index rather than doing a sequential scan and it reduced a query from several minutes to some 20 seconds. random_page_cost = 2 (original value was 4). Another thing you ought to do is to to get the four-five most used queries and do an explain analyze in these. Since our website wasn't prepared for this type of statistics I simply did a tcpdump, grep'ed all select's, sorted them and sorted them unique so I could see which queries were used most. regards Claus ---(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
[PERFORM] t1000/t2000 sun-servers
Hi. Has anybody tried the new Sun cool-thread servers t1000/t2000 from Sun? I'd love to see benchmarks with Solaris 10 and pg 8.1. regards Claus ---(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] Very slow queries - please help.
Typical query SELECT n.name FROM node n WHERE n.name LIKE '56x%' AND n.type='H' AND n.usage='TEST' AND n.node_id NOT IN (select n.node_id FROM job_log j INNER JOIN node n ON j.node_id = n.node_id WHERE n.name LIKE '56x%' AND n.type='H' AND n.usage='TEST' AND j.job_name = 'COPY FILES' AND j.job_start = '2005-11-14 00:00:00' AND (j.job_stop = '2005-11-22 09:31:10' OR j.job_stop IS NULL)) ORDER BY n.name Do you have any indexes? regards Claus ---(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] weird performances problem
I forgot to give our non default postgresql.conf parameters: shared_buffers = 28800 sort_mem = 32768 vacuum_mem = 32768 max_fsm_pages = 35 max_fsm_relations = 2000 checkpoint_segments = 16 effective_cache_size = 27 random_page_cost = 2 Isn't sort_mem quite high? Remember that sort_mem size is allocated for each sort, not for each connection. Mine is 4096 (4 MB). My effective_cache_size is set to 27462. What OS are you running? regards Claus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Hardware/OS recommendations for large databases (5TB)
at 5TB data, i'd vote that the application is disk I/O bound, and the difference in CPU speed at the level of dual opteron vs. dual-core opteron is not gonna be noticed. to maximize disk, try getting a dedicated high-end disk system like nstor or netapp file servers hooked up to fiber channel, then use a good high-end fiber channel controller like one from LSI. and go with FreeBSD amd64 port. It is *way* fast, especially the FreeBSD 6.0 disk system. I'm (also) FreeBSD-biased but I'm not shure whether the 5 TB fs will work so well if tools like fsck are needed. Gvinum could be one option but I don't have any experience in that area. regards Claus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Hardware/OS recommendations for large databases (5TB)
Does anyone have recommendations for hardware and/or OS to work with around 5TB datasets? Hardware-wise I'd say dual core opterons. One dual-core-opteron performs better than two single-core at the same speed. Tyan makes some boards that have four sockets, thereby giving you 8 cpu's (if you need that many). Sun and HP also makes nice hardware although the Tyan board is more competetive priced. OS wise I would choose the FreeBSD amd64 port but partititions larger than 2 TB needs some special care, using gpt rather than disklabel etc., tools like fsck may not be able to completely check partitions larger than 2 TB. Linux or Solaris with either LVM or Veritas FS sounds like candidates. I have been working with datasets no bigger than around 30GB, and that (I'm afraid to admit) has been in MSSQL. Well, our data are just below 30 GB so I can't help you there :-) regards Claus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] 8.x index insert performance
We are running some performance tests in which we are attempting to insert about 100,000,000 rows in a database at a sustained rate. About 50M rows in, our performance drops dramatically. This test is with data that we believe to be close to what we will encounter in production. However in tests with purely generated, sequential data, we did not notice this slowdown. I'm trying to figure out what patterns in the real data may be causing us problems. I have log,data and indexes on separate LUNs on an EMC SAN. Prior to slowdown, each partition is writing at a consistent rate. Index partition is reading at a much lower rate. At the time of slowdown, index partition read rate increases, all write rates decrease. CPU utilization drops. I'm doing some test-inserts (albeit with much fewer records) into 8.0.4 (on FreeBSD 6.0 RC1) and the import-time decreased three-fold when I increased the below mentioned values: shared_buffers = 8192 commit_delay = 10 commit_siblings = 1000 When I increased shared_buffers the kernel needed minor tweaking. regards Claus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] effective cache size on FreeBSD (WAS: Performance on SUSE w/ reiserfs)
I have a postgresql 7.4.8-server with 4 GB ram. #effective_cache_size = 1000# typically 8KB each This is computed by sysctl -n vfs.hibufspace / 8192 (on FreeBSD). So I changed it to: effective_cache_size = 27462# typically 8KB each Apparently this formula is no longer relevant on the FreeBSD systems as it can cache up to almost all the available RAM. With 4GB of RAM, one could specify most of the RAM as being available for caching, assuming that nothing but PostgreSQL runs on the server -- certainly 1/2 the RAM would be a reasonable value to tell the planner. (This was verified by using dd: dd if=/dev/zero of=/usr/local/pgsql/iotest bs=128k count=16384 to create a 2G file then dd if=/usr/local/pgsql/iotest of=/dev/null If you run systat -vmstat 2 you will see 0% diskaccess during the read of the 2G file indicating that it has, in fact, been cached) Thank you for your reply. Does this apply to FreeBSD 5.4 or 6.0 on amd64 (or both)? regards Claus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] effective cache size on FreeBSD (WAS: Performance on SUSE w/
Apparently this formula is no longer relevant on the FreeBSD systems as it can cache up to almost all the available RAM. With 4GB of RAM, one could specify most of the RAM as being available for caching, assuming that nothing but PostgreSQL runs on the server -- certainly 1/2 the RAM would be a reasonable value to tell the planner. (This was verified by using dd: dd if=/dev/zero of=/usr/local/pgsql/iotest bs=128k count=16384 to create a 2G file then dd if=/usr/local/pgsql/iotest of=/dev/null If you run systat -vmstat 2 you will see 0% diskaccess during the read of the 2G file indicating that it has, in fact, been cached) Thank you for your reply. Does this apply to FreeBSD 5.4 or 6.0 on amd64 (or both)? Not sure about 6.0 (but I don't know why it would change) but definitely on 5.4 amd64 (and I would imagine i386 as well). Works on FreeBSD 6.0 RC1 as well. Tried using count=4096 on a 1 GB ram box. Same behaviour as you describe above. regards Claus ---(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] Performance on SUSE w/ reiserfs
I have a SUSE 9 box that is running Postgres 8.0.1 compiled from source. Over time, I see the memory usage of the box go way way up (it's got 8GBs in it and by the end of the day, it'll be all used up) with what looks like cached inodes relating to the extreme IO generated by I was wondering if there is something I'm doing wrong with my default settings of postgres that is keeping all that stuff cached, or if I just need to switch to XFS or if there is some setting in postgres that I can tweak that will make this problem go away. It's gone beyond an annoyance and is now into the realm of getting me in trouble if I can't keep this DB server up and running. Even a minute or two of downtime in a restart is often too much. Any help you can give in this would be extrememly helpful as I'm very far from an expert on Linux filesystems and postgres tuning. You may want to submit your postgresql.conf. Upgrading to the latest stable version may also help, although my experience is related to FreeBSD and postgresql 7.4.8. regards Claus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Finding bottleneck
effective_cache_size = 100 # typically 8KB each I have this setting on postgresql 7.4.8 on FreeBSD with 4 GB RAM: effective_cache_size = 27462 So eventhough your machine runs Debian and you have four times as much RAM as mine your effective_cache_size is 36 times larger. You could try lowering this setting. regards Claus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
