Re: [PERFORM] Hardware spec
Decibel! wrote: On Thu, Sep 06, 2007 at 11:26:46AM +0200, Willo van der Merwe wrote: Richard Huxton wrote: Willo van der Merwe wrote: Hi guys, I'm have the rare opportunity to spec the hardware for a new database server. It's going to replace an older one, driving a social networking web application. The current server (a quad opteron with 4Gb of RAM and 80Gb fast SCSI RAID10) is coping with an average load of ranging between 1.5 and 3.5. The new machine spec I have so far: What's the limiting factor on your current machine - disk, memory, cpup? I'm a bit embarrassed to admit that I'm not sure. The reason we're changing machines is that we might be changing ISPs and we're renting / leasing the machines from the ISP. 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? ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[PERFORM] Hardware spec
Hi guys, I'm have the rare opportunity to spec the hardware for a new database server. It's going to replace an older one, driving a social networking web application. The current server (a quad opteron with 4Gb of RAM and 80Gb fast SCSI RAID10) is coping with an average load of ranging between 1.5 and 3.5. The new machine spec I have so far: 2 x Intel Xeon 2.33 GHz Dual Core Woodcrest Processors 4 Gb RAM 5x73 GB Ultra320 SCSI RAID 5 (288 GB storage) I've heard that RAID 5 is not necessarily the best performer. Also, are there any special tricks when partition the file system? Regards, Willo ---(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] Hardware spec
Richard Huxton wrote: Willo van der Merwe wrote: Hi guys, I'm have the rare opportunity to spec the hardware for a new database server. It's going to replace an older one, driving a social networking web application. The current server (a quad opteron with 4Gb of RAM and 80Gb fast SCSI RAID10) is coping with an average load of ranging between 1.5 and 3.5. The new machine spec I have so far: What's the limiting factor on your current machine - disk, memory, cpup? I'm a bit embarrassed to admit that I'm not sure. The reason we're changing machines is that we might be changing ISPs and we're renting / leasing the machines from the ISP. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Hardware spec
Jean-David Beyer wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Willo van der Merwe wrote: Richard Huxton wrote: Willo van der Merwe wrote: Hi guys, I'm have the rare opportunity to spec the hardware for a new database server. It's going to replace an older one, driving a social networking web application. The current server (a quad opteron with 4Gb of RAM and 80Gb fast SCSI RAID10) is coping with an average load of ranging between 1.5 and 3.5. The new machine spec I have so far: What's the limiting factor on your current machine - disk, memory, cpup? I'm a bit embarrassed to admit that I'm not sure. The reason we're changing machines is that we might be changing ISPs and we're renting / leasing the machines from the ISP. Before you get rid of the current ISP, better examine what is going on with the present setup. It would be good to know if you are memory, processor, or IO limited. That way you could increase what needs to be increased, and not waste money where the bottleneck is not. Good advice. After running a vmstat and iostat, it is clear, to my mind anyway, that the most likely bottleneck is IO, next is probably some more RAM. Here's the output: procs ---memory-- ---swap-- -io --system-- cpu r b swpd free buff cache si sobibo incs us sy id wa 0 0 29688 80908 128308 331579200 8636 8 17 2 80 1 avg-cpu: %user %nice%sys %iowait %idle 17.180.001.930.81 80.08 Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 14.5766.48 506.45 58557617 446072213 sda1 0.60 0.27 4.70 2351224136128 sda2 0.38 0.77 2.27 6787542002576 sda3 2.37 0.4918.61 429171 16389960 sda4 0.00 0.00 0.00 2 0 sda5 0.71 0.66 5.46 5783074807087 sda6 0.03 0.01 0.24 6300 214196 sda7 0.02 0.00 0.19 2622 165992 sda8 60.1964.29 474.98 56626211 418356226 ---(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] Hardware spec
Florian Weimer wrote: You need to run vmstat 10 (for ten-second averages) and report a couple of lines. procs ---memory-- ---swap-- -io --system-- cpu r b swpd free buff cache si sobibo incs us sy id wa 1 0 61732 47388 27908 34313440010651 4 17 2 80 1 5 0 61732 37052 28180 34319560014 987 2320 2021 38 4 56 2 1 0 61620 43076 28356 343225600 0 367 1691 1321 28 3 67 1 3 0 61620 37620 28484 343274000 0 580 4088 6792 40 5 54 1 2 0 61596 33716 28748 34335200024 415 2087 1890 44 4 49 2 3 0 61592 45300 28904 34162003061 403 2282 2154 41 4 54 1 7 0 61592 30172 29092 34169640019 358 2779 3478 31 6 63 1 1 0 61580 62948 29180 34173686027 312 3632 4396 38 4 57 1 1 0 61444 62388 29400 341796400 6 354 2163 1918 31 4 64 1 2 0 61444 53988 29648 341798800 0 553 2095 1687 33 3 63 1 1 0 61444 63988 29832 341834800 6 352 1767 1424 22 3 73 1 1 1 61444 51148 30052 34191480050 349 1524 834 22 3 74 2 1 0 61432 53460 30524 341957270 7 868 4434 6706 43 6 49 2 1 0 61432 58668 30628 342014800 0 284 1785 1628 27 3 69 1 iostat sda8 is the where the pg_data resides, sda3 is /var/log avg-cpu: %user %nice%sys %iowait %idle 17.360.001.960.82 79.86 Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda3 2.38 0.4918.71 432395 16672800 sda8 62.3474.46 491.74 6634 438143794 avg-cpu: %user %nice%sys %iowait %idle 30.500.003.571.70 64.22 Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda3 5.60 0.0044.80 0448 sda8120.20 134.40 956.00 1344 9560 avg-cpu: %user %nice%sys %iowait %idle 20.680.003.431.35 74.54 Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda3 3.30 0.0026.40 0264 sda8 97.90 0.00 783.20 0 7832 avg-cpu: %user %nice%sys %iowait %idle 22.310.002.750.68 74.27 Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda3 2.10 0.0016.78 0168 sda8 60.34 0.80 481.92 8 4824 avg-cpu: %user %nice%sys %iowait %idle 11.650.001.601.03 85.72 Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda3 1.70 0.0013.61 0136 sda8 59.36 0.00 474.87 0 4744 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Performance issue
Hi Guys, Following Tom Lane's advice I upgraded to 8.2, and that solved all my problems. :D Thank you so much for your input, I really appreciate it. Kind regards Willo van der Merwe ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] Performance issue
Hi Guys, I have something odd. I have Gallery2 running on PostgreSQL 8.1, and recently I upgraded to 8.1.9-1.el4s1.1 (64bit). The issue here really is how do I get PostgreSQL to work with their horrible code. The queries they generate look something like : SELECT blah, blah FROM table1, table2 WHERE some relational stuff AND id IN (here a list of 42000+ IDs are listed) On the previous version (which I can't recall what it was, but it was a version 8.1) the queries executed fine, but suddenly now, these queries are taking up-to 4 minutes to complete. I am convinced it's the parsing/handling of the IN clause. It could, of course, be that the list has grown so large that it can't fit into a buffer anymore. For obvious reasons I can't run an EXPLAIN ANALYZE from a prompt. I vacuum and reindex the database daily. I'd prefer not to have to rewrite the code, so any suggestions would be very welcome. Kind regards Willo van der Merwe ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] Performance issue
Hi Guys, I have something odd. I have Gallery2 running on PostgreSQL 8.1, and recently I upgraded to 8.1.9-1.el4s1.1 (64bit). The issue here really is how do I get PostgreSQL to work with their horrible code. The queries they generate look something like : SELECT blah, blah FROM table1, table2 WHERE some relational stuff AND id IN (here a list of 42000+ IDs are listed) On the previous version (which I can't recall what it was, but it was a version 8.1) the queries executed fine, but suddenly now, these queries are taking up-to 4 minutes to complete. I am convinced it's the parsing/handling of the IN clause. It could, of course, be that the list has grown so large that it can't fit into a buffer anymore. For obvious reasons I can't run an EXPLAIN ANALYZE from a prompt. I vacuum and reindex the database daily. I'd prefer not to have to rewrite the code, so any suggestions would be very welcome. Kind regards Willo van der Merwe ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] PostgreSQL performance issues
Merlin Moncure wrote: On 8/29/06, Willo van der Merwe [EMAIL PROTECTED] wrote: and it has 743321 rows and a explain analyze select count(*) from property_values; you have a number of options: All good ideas and I'll be sure to implement them later. I am curious why you need to query the count of records in the log table to six digits of precision. I'm not with you you here. I'm drawing statistic for the my users on a per user basis in real-time, so there are a couple of where clauses attached. merlin Hi Merlin, This was just an example. All queries have slowed down. Could it be that I've reached some cut-off and now my disk is thrashing? Currently the load looks like this: Cpu0 : 96.8% us, 1.9% sy, 0.0% ni, 0.3% id, 0.0% wa, 0.0% hi, 1.0% si Cpu1 : 97.8% us, 1.6% sy, 0.0% ni, 0.3% id, 0.0% wa, 0.0% hi, 0.3% si Cpu2 : 96.8% us, 2.6% sy, 0.0% ni, 0.3% id, 0.0% wa, 0.0% hi, 0.3% si Cpu3 : 96.2% us, 3.2% sy, 0.0% ni, 0.3% id, 0.0% wa, 0.0% hi, 0.3% si ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] PostgreSQL performance issues
Rusty Conover wrote: On Aug 29, 2006, at 7:52 AM, Willo van der Merwe wrote: Hi, We're running PostgreSQL 8.1.4 on CentOS 4 (Linux version 2.6.9-34.0.1.ELsmp). Hardware specs: 2x AMD Dual-Core Opteron 270 Italy 1Ghz HT 2 x 1MB L2 Cache Socket 940 4 GB Registered ECC PC3200 DDR RAM SuperMicro Server-Class 1U AS1020S series system Dual-channel Ultra320 SCSI controller 1 x 73 GB 10,000rpm Ultra320 SCSI drive with 8MB cache I use it to drive a web application. Everything was working fine when all of a sudden today, things went belly up. Load on the server started increasing and query speeds decreased rapidly. After dropping all the clients I did some quick tests and found the following: I have a log table looking like this: Table public.log Column |Type |Modifiers -+-+- site| bigint | not null stamp | timestamp without time zone | default now() type| character(8)| not null default 'log'::bpchar user| text| not null default 'public'::text message | text| Indexes: fki_log_sites btree (site) ix_log_stamp btree (stamp) ix_log_type btree (type) ix_log_user btree (user) Foreign-key constraints: log_sites FOREIGN KEY (site) REFERENCES sites(id) ON UPDATE CASCADE ON DELETE CASCADE and it has 743321 rows and a explain analyze select count(*) from property_values; QUERY PLAN -- Aggregate (cost=55121.95..55121.96 rows=1 width=0) (actual time=4557.797..4557.798 rows=1 loops=1) - Seq Scan on property_values (cost=0.00..51848.56 rows=1309356 width=0) (actual time=0.026..2581.418 rows=1309498 loops=1) Total runtime: 4557.978 ms (3 rows) 4 1/2 seconds for a count(*) ? This seems a bit rough - is there anything else I can try to optimize my Database? You can imagine that slightly more complex queries goes out the roof. Any help appreciated Regards Willo van der Merwe Hi, What about doing a little bit of normalization? With 700k rows you could probably gain some improvements by: * normalizing the type and user columns to integer keys (dropping the 8 byte overhead for storing the field lengths) * maybe change the type column so that its a smallint if there is just a small range of possible values (emulating a enum type in other databases) rather the joining to another table. * maybe move message (if the majority of the rows are big and not null but not big enough to be TOASTed, ergo causing only a small number of rows to fit onto a 8k page) out of this table into a separate table that is joined only when you need the column's content. Doing these things would fit more rows onto each page, making the scan less intensive by not causing the drive to seek as much. Of course all of these suggestions depend on your workload. Cheers, Rusty -- Rusty Conover InfoGears Inc. Hi Rusty, Good ideas and I've implemented some of them, and gained about 10%. I'm still sitting on a load avg of about 60. Any ideas on optimizations on my postgresql.conf, that might have an effect? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] PostgreSQL performance issues
Luke Lonergan wrote: Currently the load looks like this: Cpu0 : 96.8% us, 1.9% sy, 0.0% ni, 0.3% id, 0.0% wa, 0.0% hi, 1.0% si Cpu1 : 97.8% us, 1.6% sy, 0.0% ni, 0.3% id, 0.0% wa, 0.0% hi, 0.3% si Cpu2 : 96.8% us, 2.6% sy, 0.0% ni, 0.3% id, 0.0% wa, 0.0% hi, 0.3% si Cpu3 : 96.2% us, 3.2% sy, 0.0% ni, 0.3% id, 0.0% wa, 0.0% hi, 0.3% si All four CPUs are hammered busy - check top and look for runaway processes. - Luke Yes, the first 463 process are all postgres. In the meanwhile I've done: Dropped max_connections from 500 to 250 and Upped shared_buffers = 5 Without any apparent effect. ---(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] PostgreSQL performance issues
That's exactly what I'm experiencing. Everything was fine until yesterday, when we noticed a considerable site slow-down. Graphs showed the server suddenly spiking to a load of 67. At first I thought somebody executed a ran-away query, so I restarted postgres, but after it came back up, it climbed back up to this load. In the meanwhile I've applied some table level optimizations and the postgres.conf optimizatrions ... nothing Here's the vmstat output, since reboot last night [EMAIL PROTECTED] ~]# vmstat -a procs ---memory-- ---swap-- -io --system-- cpu r b swpd free inact active si sobibo incs us sy id wa 27 0 0 595312 248100 296276400 831 105 7 63 2 35 0 [EMAIL PROTECTED] ~]# vmstat -d disk- reads writes--- -IO-- total merged sectors ms total merged sectors ms cursec ram0 0 0 0 0 0 0 0 0 0 0 ram1 0 0 0 0 0 0 0 0 0 0 ram2 0 0 0 0 0 0 0 0 0 0 ram3 0 0 0 0 0 0 0 0 0 0 ram4 0 0 0 0 0 0 0 0 0 0 ram5 0 0 0 0 0 0 0 0 0 0 ram6 0 0 0 0 0 0 0 0 0 0 ram7 0 0 0 0 0 0 0 0 0 0 ram8 0 0 0 0 0 0 0 0 0 0 ram9 0 0 0 0 0 0 0 0 0 0 ram10 0 0 0 0 0 0 0 0 0 0 ram11 0 0 0 0 0 0 0 0 0 0 ram12 0 0 0 0 0 0 0 0 0 0 ram13 0 0 0 0 0 0 0 0 0 0 ram14 0 0 0 0 0 0 0 0 0 0 ram15 0 0 0 0 0 0 0 0 0 0 sda 197959 38959 4129737 952923 777438 1315162 16839981 39809324 0 2791 fd00 0 0 0 0 0 0 0 0 0 md00 0 0 0 0 0 0 0 0 0 Luke Lonergan wrote: Interesting - in this quick snapshot there is no I/O happening at all. What happens when you track the activity for a longer period of time? How about just capturing vmstat during a period when the queries are slow? Has the load average been this high forever or are you experiencing a growth in workload? 463 processes all doing CPU work will take 100x as long as one query on a 4 CPU box, have you worked through how long you should expect the queries to take? - Luke -Original Message- From: Willo van der Merwe [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 30, 2006 4:35 AM To: Luke Lonergan Cc: Merlin Moncure; pgsql-performance@postgresql.org Subject: Re: [PERFORM] PostgreSQL performance issues Luke Lonergan wrote: Currently the load looks like this: Cpu0 : 96.8% us, 1.9% sy, 0.0% ni, 0.3% id, 0.0% wa, 0.0% hi, 1.0% si Cpu1 : 97.8% us, 1.6% sy, 0.0% ni, 0.3% id, 0.0% wa, 0.0% hi, 0.3% si Cpu2 : 96.8% us, 2.6% sy, 0.0% ni, 0.3% id, 0.0% wa, 0.0% hi, 0.3% si Cpu3 : 96.2% us, 3.2% sy, 0.0% ni, 0.3% id, 0.0% wa, 0.0% hi, 0.3% si All four CPUs are hammered busy - check top and look for runaway processes. - Luke Yes, the first 463 process are all postgres. In the meanwhile I've done: Dropped max_connections from 500 to 250 and Upped shared_buffers = 5 Without any apparent effect. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] PostgreSQL performance issues
Alex Hayward wrote: On Wed, 30 Aug 2006, Willo van der Merwe wrote: Merlin Moncure wrote: On 8/29/06, Willo van der Merwe [EMAIL PROTECTED] wrote: and it has 743321 rows and a explain analyze select count(*) from property_values; you have a number of options: All good ideas and I'll be sure to implement them later. I am curious why you need to query the count of records in the log table to six digits of precision. I'm not with you you here. I'm drawing statistic for the my users on a per user basis in real-time, so there are a couple of where clauses attached. Most of the advice so far has been aimed at improving the performance of the query you gave. If this query isn't representative of your load then you'll get better advice if you post the queries you are actually making along with EXPLAIN ANALYZE output. Hi Merlin, This was just an example. All queries have slowed down. Could it be that I've reached some cut-off and now my disk is thrashing? Currently the load looks like this: Cpu0 : 96.8% us, 1.9% sy, 0.0% ni, 0.3% id, 0.0% wa, 0.0% hi, 1.0% si Cpu1 : 97.8% us, 1.6% sy, 0.0% ni, 0.3% id, 0.0% wa, 0.0% hi, 0.3% si Cpu2 : 96.8% us, 2.6% sy, 0.0% ni, 0.3% id, 0.0% wa, 0.0% hi, 0.3% si Cpu3 : 96.2% us, 3.2% sy, 0.0% ni, 0.3% id, 0.0% wa, 0.0% hi, 0.3% si It seems to be a sort of standing assumption on this list that databases are much larger than memory and that database servers are almost always IO bound. This isn't always true, but as we don't know the size of your database or working set we can't tell. You'd have to look at your OS's IO statistics to be sure, but it doesn't look to me to be likely that you're IO bound. If there are significant writes going on then it may also be interesting to know your context switch rate and whether dropping your foreign key constraint makes any difference. IIRC your foreign key constraint will result in the row in log_sites being locked FOR UPDATE and cause updates and inserts into your log table for a particular site to be serialized (I may be out of date on this, it's a while since I heavily used foreign keys). ---(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 Hi Alex, Yes, I haven't noticed any major I/O waits either. The crazy thing here is that all the queries were running an an acceptable time limit, but then suddenly it went haywire. I did not change any of the queries or fiddle with the server in any way. Previously we've experienced 1 or 2 spikes a day (where load would suddenly spike to 67 or so, but then quickly drop down to below 4) but in this case it stayed up. So I restarted the service and started fiddling with options, with no apparent effect. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] PostgreSQL performance issues
Dave Cramer wrote: On 30-Aug-06, at 7:35 AM, Willo van der Merwe wrote: Luke Lonergan wrote: Currently the load looks like this: Cpu0 : 96.8% us, 1.9% sy, 0.0% ni, 0.3% id, 0.0% wa, 0.0% hi, 1.0% si Cpu1 : 97.8% us, 1.6% sy, 0.0% ni, 0.3% id, 0.0% wa, 0.0% hi, 0.3% si Cpu2 : 96.8% us, 2.6% sy, 0.0% ni, 0.3% id, 0.0% wa, 0.0% hi, 0.3% si Cpu3 : 96.2% us, 3.2% sy, 0.0% ni, 0.3% id, 0.0% wa, 0.0% hi, 0.3% si All four CPUs are hammered busy - check "top" and look for runaway processes. - Luke Yes, the first 463 process are all postgres. In the meanwhile I've done: Dropped max_connections from 500 to 250 and Upped shared_buffers = 5 With 4G of memory you can push shared buffers to double that. effective_cache should be 3/4 of available memory. Can you also check vmstat 1 for high context switches during this query, high being over 100k Dave Without any apparent effect. ---(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 Hi Dave, Ok, I've upped shared_buffers = 15 and effective_cache_size = 10 and restarted the service top now reads: top - 15:08:28 up 20:12, 1 user, load average: 19.55, 22.48, 26.59 Tasks: 132 total, 24 running, 108 sleeping, 0 stopped, 0 zombie Cpu0 : 97.0% us, 1.0% sy, 0.0% ni, 0.3% id, 0.0% wa, 0.3% hi, 1.3% si Cpu1 : 98.3% us, 1.7% sy, 0.0% ni, 0.0% id, 0.0% wa, 0.0% hi, 0.0% si Cpu2 : 98.0% us, 1.7% sy, 0.0% ni, 0.0% id, 0.0% wa, 0.0% hi, 0.3% si Cpu3 : 96.7% us, 3.3% sy, 0.0% ni, 0.0% id, 0.0% wa, 0.0% hi, 0.0% si Mem: 4060084k total, 2661772k used, 1398312k free, 108152k buffers Swap: 4192956k total, 0k used, 4192956k free, 2340936k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 11446 postgres 17 0 1280m 97m 95m R 28.9 2.5 0:03.63 postmaster 11435 postgres 16 0 1279m 120m 117m R 26.9 3.0 0:05.18 postmaster 11438 postgres 16 0 1279m 31m 30m R 24.6 0.8 0:04.43 postmaster 11163 postgres 16 0 1279m 120m 118m R 23.2 3.0 0:42.61 postmaster 11167 postgres 16 0 1279m 120m 118m R 23.2 3.0 0:41.04 postmaster 11415 postgres 15 0 1279m 299m 297m R 22.2 7.5 0:07.07 postmaster 11428 postgres 15 0 1279m 34m 32m R 21.9 0.9 0:05.53 postmaster 11225 postgres 16 0 1279m 31m 30m R 21.6 0.8 0:34.95 postmaster 11298 postgres 16 0 1279m 118m 117m R 21.6 3.0 0:23.82 postmaster 11401 postgres 15 0 1279m 31m 30m R 21.6 0.8 0:08.18 postmaster 11377 postgres 15 0 1279m 122m 120m R 20.9 3.1 0:09.54 postmaster 11357 postgres 17 0 1280m 126m 123m R 19.9 3.2 0:13.98 postmaster 11415 postgres 16 0 1279m 299m 297m R 17.1 7.5 0:06.40 postmaster 11461 postgres 17 0 1279m 81m 78m R 17.1 2.0 0:00.77 postmaster 11357 postgres 15 0 1279m 120m 118m S 16.8 3.0 0:13.38 postmaster 11458 postgres 16 0 1279m 31m 30m R 15.8 0.8 0:00.97 postmaster 11446 postgres 15 0 1279m 31m 30m S 15.5 0.8 0:02.76 postmaster 11428 postgres 15 0 1279m 34m 32m S 15.2 0.9 0:04.87 postmaster 11435 postgres 16 0 1279m 120m 117m R 14.2 3.0 0:04.37 postmaster 11466 postgres 16 0 1279m 33m 32m S 7.9 0.9 0:00.24 postmaster load avg is climbing... vmstat 1 I don't see any cs 100k procs ---memory-- ---swap-- -io --system-- cpu r b swpd free buff cache si so bi bo in cs us sy id wa 33 0 0 1352128 108248 2352604 0 0 7 33 147 26 65 2 33 0 19 0 0 1348360 108264 2352656 0 0 0 348 3588 1408 98 2 0 0 26 0 0 1346024 108264 2352996 0 0 0 80 3461 1154 98 2 0 0 27 0 0 1349496 108264 2352996 0 0 0 100 3611 1199 98 2 0 0 31 0 0 1353872 108264 2353064 0 0 0 348 3329 1227 97 2 0 0 21 0 0 1352528 108264 2353064 0 0 0 80 3201 1437 97 2 0 0 28 0 0 1352096 108280 2353184 0 0 0 64 3579 1073 98 2 0 0 29 0 0 1352096 108284 2353180 0 0 0 0 3538 1293 98 2 0 0 28 0 0 1351776 108288 2353244 0 0 0 36 3339 1313 99 1 0 0 22 0 0 1366392 108288 2353244 0 0 0 588 3663 1303 99 1 0 0 27 0 0 1366392 108288 2353312 0 0 0 84 3276 1028 99 1 0 0 28 0 0 1365504 108296 2353372 0 0 0 140 3500 1164 98 2 0 0 26 0 0 1368272 108296 2353372 0 0 0 68 3268 1082 98 2 0 0 25 0 0 1372232 108296 2353508 0 0 0 260 3261 1278 97 3 0 0 26 0 0 1366056 108296 2353644 0 0 0 0 3268 1178 98 2 0 0 24 1 0 1368704 108296 2353780 0 0 0 1788 3548 1614 97 3 0 0 29 0 0 1367728 108296 2353304 0 0 0 60 3637 1105 99 1 0 0 21 0 0 1365224 108300 2353640 0 0 0 12 3257 918 99 1 0 0 27 0 0 1363944 108300 2354116 0 0 0 72 3052 1365 98 2 0 0 25 0 0 1366968 108300 2354184 0 0 0 212 3314 1696 99 1 0 0 30 0 0 1363552 108300 2354184 0 0 0 72 3147 1420 97 2 0 0 27 0 0 1367792 108300 2354184 0 0 0 184 3245 1310 97 2 0 0 21 0 0 1369088 108308 2354380 0 0 0 140 3306 987 98 2 0 0 11 1 0 1366056 108308 2354448 0 0 0 88 3210 1183 98 1 0 0 27 0 0 1361104 108308 2354516 0 0 0 0 3598
Re: [PERFORM] PostgreSQL performance issues
Dave Dutcher wrote: That's an interesting situation. Your CPU's are pegged, and you're hardly doing any IO. I wonder if there is some ineficient query, or if its just very high query volume. Maybe you could try setting log_min_duration_statement to try to track down the slowest of the queries. Then post the slow queries with an explain analyze to the list. Here is some info on setting up logging: http://www.postgresql.org/docs/8.1/interactive/runtime-config-logging.html Are your queries standard SQL or do you call functions you wrote in PL/pgSQl or PL/Python or anything? It might be a combo of queries and load. My queries use almost exclusively functions, but on an unloaded dev machine performs its queries in aprox 10ms. When is it appropriate to start clustering database servers? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] PostgreSQL performance issues
Hi,
We're running PostgreSQL 8.1.4 on CentOS 4 (Linux version
2.6.9-34.0.1.ELsmp). Hardware specs:
2x AMD Dual-Core Opteron 270 Italy 1Ghz HT 2 x 1MB L2 Cache Socket 940
4 GB Registered ECC PC3200 DDR RAM
SuperMicro Server-Class 1U AS1020S series system
Dual-channel Ultra320 SCSI controller
1 x 73 GB 10,000rpm Ultra320 SCSI drive with 8MB cache
I use it to drive a web
application. Everything
was working fine when all of a sudden today, things went belly up. Load
on the server started increasing and query speeds decreased rapidly.
After dropping all the clients I did some quick tests and found the
following:
I have a log table looking like this:
Table "public.log"
Column | Type | Modifiers
-+-+-
site | bigint | not null
stamp | timestamp without time zone | default now()
type | character(8) | not null default 'log'::bpchar
user | text | not null default 'public'::text
message | text |
Indexes:
"fki_log_sites" btree (site)
"ix_log_stamp" btree (stamp)
"ix_log_type" btree ("type")
"ix_log_user" btree ("user")
Foreign-key constraints:
"log_sites" FOREIGN KEY (site) REFERENCES sites(id) ON UPDATE
CASCADE ON DELETE CASCADE
and it has 743321 rows and a explain analyze select count(*) from
property_values;
QUERY
PLAN
--
Aggregate (cost=55121.95..55121.96 rows=1 width=0) (actual
time=4557.797..4557.798 rows=1 loops=1)
- Seq Scan on property_values (cost=0.00..51848.56
rows=1309356 width=0) (actual time=0.026..2581.418 rows=1309498 loops=1)
Total runtime: 4557.978 ms
(3 rows)
4 1/2 seconds for a count(*) ? This seems a bit rough - is there
anything else I can try to optimize my Database? You can imagine that
slightly more complex queries goes out the roof.
Any help appreciated
Regards
Willo van der Merwe
Re: [PERFORM] PostgreSQL performance issues
Joshua D. Drake wrote: 4 1/2 seconds for a count(*) ? This seems a bit rough - is there anything else I can try to optimize my Database? You can imagine that slightly more complex queries goes out the roof. Well a couple of things. 1. You put all your money in the wrong place.. 1 hard drive!!??!! Yes, I realize 1 hard drive could cause a bottle neck, but on average I'm sitting on a 1-2% wait for IO. 2. What is your maintenance regimen? Vacuum, Analyze I'm doing a daily VACUUM ANALYZE, but just to be on the safe side, I performed one manually before I ran my test, thinking that I might have to up the frequency. Joshua D. Drake Any help appreciated Regards Willo van der Merwe ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
