[PERFORM] The right SHMMAX and FILE_MAX
Hi. I'm on a 64 Bit CentOS 5 system, quadcore processor, 8GB RAM and tons of data storage (1 TB SATAII disks). The current SHMMAX and SHMMIN are (commas added for legibility) -- kernel.shmmax = 68,719,476,736 kernel.shmall = 4,294,967,296 Now, according to my reading in the PG manual and this list, a good recommended value for SHMMAX is (shared_buffers * 8192) My postgresql.conf settings at the moment are: max_connections = 300 shared_buffers = 300MB effective_cache_size = 2000MB By this calculation, shared_b * 8192 will be: 2,457,600,000,000 That's a humongous number. So either the principle for SHMMAX is amiss, or I am reading this wrongly? Similarly with fs.file_max. There are articles like this one: http://tldp.org/LDP/solrhe/Securing-Optimizing-Linux-RH-Edition-v1.3/chap6sec72.html Is this relevant for PostgreSQL performance at all, or should I skip that? Thanks for any pointers! -- 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] REINDEX takes half a day (and still not complete!)
On Sat, Apr 30, 2011 at 4:07 PM, Greg Smith g...@2ndquadrant.com wrote: On 04/23/2011 03:44 PM, Robert Haas wrote: On Apr 17, 2011, at 11:30 AM, Phoenix Kiulaphoenix.ki...@gmail.com wrote: Postgres is 8.2.9. An upgrade would probably help you a lot, and as others have said it sounds like your hardware is failing, so you probably want to deal with that first. I am a bit surprised, however, that no one seems to have mentioned using CLUSTER rather than VACUUM or REINDEX. Sometimes that's worth a try... Don't know if it was for this reason or not for not mentioning it by others, but CLUSTER isn't so great in 8.2. The whole not MVCC-safe bit does not inspire confidence on a production server. To everyone. Thanks so much for everything, truly. We have managed to salvage the data by exporting it in bits and pieces. 1. First the schema only 2. Then pg_dump of specific small tables 3. Then pg_dump of timed bits of the big mammoth table Not to jinx it, but the newer hardware seems to be doing well. I am on 9.0.4 now and it's pretty fast. Also, as has been mentioned in this thread and other discussions on the list, just doing a dump and then fresh reload has compacted the DB to nearly 1/3rd of its previously reported size! I suppose that's what I am going to do on a periodic basis from now on. There is a lot of DELETE/UPDATE activity. But I wonder if the vacuum stuff really should do something that's similar in function? What do the high-end enterprise folks do -- surely they can't be dumping/restoring every quarter or soor are they? Anyway, many many thanks to the lovely folks on this list. Much appreciated! -- 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] Query Performance with Indexes on Integer type vs. Date type.
On Thu, Apr 28, 2011 at 12:17 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Dhimant Patel drp4...@gmail.com wrote: I am a new comer on postgres world and now using it for some serious (at least for me) projects. I have a need where I am running some analytical + aggregate functions on data where ordering is done on Date type column. From my initial read on documentation I believe internally a date type is represented by integer type of data. This makes me wonder would it make any good to create additional column of Integer type and update it as data gets added and use this integer column for all ordering purposes for my sqls - or should I not hasitate using Date type straight into my sql for ordering? I doubt that this will improve performance, particularly if you ever want to see your dates formatted as dates. Better yet, is there anyway I can verify impact of ordering on Date type vs. Integer type, apart from using \timing and explain plan? You might be better off just writing the code in the most natural way, using the date type for dates, and then asking about any queries which aren't performing as you hope they would. Premature optimization is often counter-productive. If you really want to do some benchmarking of relative comparison speeds, though, see the generate_series function -- it can be good at generating test tables for such things. There is a lot of really good advice here already. I'll just add one thought. If the dates in your tables are static based only on creation (as in only a CREATE_DATE, which will never be modified per row like a MODIFY_DATE for each record), then your thought might have made sense. But in that case you can already use the ID field if you have one? In most real world cases however the DATE field will likely be storing an update time as well. Which would make your thought about numbering with integers pointless. -- 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] REINDEX takes half a day (and still not complete!)
Sorry, rejuvenating a thread that was basically unanswered. I closed the database for any kinds of access to focus on maintenance operations, killed all earlier processes so that my maintenance is the only stuff going on. REINDEX is still taking 3 hours -- and it is still not finished! Similarly, if I cancel the REINDEX and issue a VACUUM ANALYZE VERBOSE, this too seems to just hang there on my big table. I changed the maintenance_work_men to 2GB for this operation. It's highly worrisome -- the above slow times are with 2GB of my server dedicated to Postgresql Surely this is not tenable for enterprise environments? I am on a 64bit RedHat server with dual CPU Intel Woodcrest or whatever that was called. Postgres is 8.2.9. How do DB folks do this with small maintenance windows? This is for a very high traffic website so it's beginning to get embarrassing. Would appreciate any thoughts or pointers. Thanks! On Mon, Mar 21, 2011 at 9:28 PM, Merlin Moncure mmonc...@gmail.com wrote: On Fri, Mar 18, 2011 at 10:07 PM, Phoenix Kiula phoenix.ki...@gmail.com wrote: I have a large table but not as large as the kind of numbers that get discussed on this list. It has 125 million rows. REINDEXing the table takes half a day, and it's still not finished. To write this post I did SELECT COUNT(*), and here's the output -- so long! select count(*) from links; count --- 125418191 (1 row) Time: 1270405.373 ms That's 1270 seconds! I suppose the vaccuum analyze is not doing its job? As you can see from settings below, I have autovacuum set to ON, and there's also a cronjob every 10 hours to do a manual vacuum analyze on this table, which is largest. PG is version 8.2.9. Any thoughts on what I can do to improve performance!? Below are my settings. max_connections = 300 shared_buffers = 500MB effective_cache_size = 1GB max_fsm_relations = 1500 max_fsm_pages = 95 work_mem = 100MB temp_buffers = 4096 authentication_timeout = 10s ssl = off checkpoint_warning = 3600 random_page_cost = 1 autovacuum = on autovacuum_vacuum_cost_delay = 20 vacuum_cost_delay = 20 vacuum_cost_limit = 600 autovacuum_naptime = 10 stats_start_collector = on stats_row_level = on autovacuum_vacuum_threshold = 75 autovacuum_analyze_threshold = 25 autovacuum_analyze_scale_factor = 0.02 autovacuum_vacuum_scale_factor = 0.01 wal_buffers = 64 checkpoint_segments = 128 checkpoint_timeout = 900 fsync = on maintenance_work_mem = 512MB how much memory do you have? you might want to consider raising maintenance_work_mem to 1GB. Are other things going on in the database while you are rebuilding your indexes? Is it possible you are blocked waiting on a lock for a while? How much index data is there? Can we see the table definition along with create index statements? merlin -- 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] REINDEX takes half a day (and still not complete!)
Thanks for these suggestions. I am beginning to wonder if the issue is deeper. I set autovacuum to off, then turned off all the connections to the database, and did a manual vacuum just to see how long it takes. This was last night my time. I woke up this morning and it has still not finished. The maintenance_men given to the DB for this process was 2GB. There is nothing else going on on the server! Now, even REINDEX is just failing in the middle: # REINDEX INDEX new_idx_userid; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. What else could be wrong? On Mon, Apr 18, 2011 at 2:38 AM, Scott Marlowe scott.marl...@gmail.com wrote: On Sun, Apr 17, 2011 at 10:59 AM, Phoenix phoenix.ki...@gmail.com wrote: TOP does not show much beyond postmaster. How should I use TOP and what info can I give you? This is what it looks like: We're basically looking to see if the postmaster process doing the vacuuming or reindexing is stuck in a D state, which means it's waiting on IO. hot the c key while it's running and you should get a little more info on which processes are what. 4799 postgres 15 0 532m 94m 93m D 0.7 1.2 0:00.14 postmaster That is likely the postmaster that is waiting on IO. VMSTAT 10 shows this: r b swpd free buff cache si so bi bo in cs us sy id wa 3 14 99552 17900 41108 7201712 0 0 42 11 0 0 8 34 41 16 2 17 99552 16468 41628 7203012 0 0 1326 84 1437 154810 7 66 12 15 3 7 99476 16796 41056 7198976 0 0 1398 96 1453 156211 7 66 21 6 3 17 99476 17228 39132 7177240 0 0 1325 68 1529 156111 8 65 16 11 So, we're at 11 to 15% io wait. I'm gonna guess you have 8 cores / threads in your CPUs, and 1/8th ot 100% is 12% so looks like you're probably IO bound here. iostat tells us more: The results of iostat -xd 10 is: Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 7.41 0.30 3.50 2.40 87.29 1.20 43.64 23.58 0.13 32.92 10.03 3.81 sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdc 0.00 18.32 158.26 4.10 2519.32 180.98 1259.66 90.49 16.63 13.04 79.91 6.17 100.11 100% IO utilization, so yea, it's likely that your sdc drive is your bottleneck. Given our little data is actually moving through the sdc drive, it's not very fast. Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s 8GB memory in total. 1GB devoted to PGSQL during these operations. Otherwise, my settings are as follows (and yes I did make the vacuum settings more aggressive based on your email, which has had no apparent impact) -- Yeah, as it gets more aggressive it can use more of your IO bandwidth. Since you What else can I share? That's a lot of help. I'm assuming you're running software or motherboard fake-raid on this RAID-1 set? I'd suggest buying a $500 or so battery backed caching RAID controller first, the improvements in performance are huge with such a card. You might wanna try testing the current RAID-1 set with bonnie++ to get an idea of how fast it is. -- 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] REINDEX takes half a day (and still not complete!)
Btw, hardware is not an issue. My db has been working fine for a while. Smaller poorer systems around the web run InnoDB databases. I wouldn't touch that with a barge pole. I have a hardware RAID controller, not fake. It's a good quality battery-backed 3Ware: http://192.19.193.26/products/serial_ata2-9000.asp On Mon, Apr 18, 2011 at 1:14 PM, Phoenix Kiula phoenix.ki...@gmail.com wrote: Thanks for these suggestions. I am beginning to wonder if the issue is deeper. I set autovacuum to off, then turned off all the connections to the database, and did a manual vacuum just to see how long it takes. This was last night my time. I woke up this morning and it has still not finished. The maintenance_men given to the DB for this process was 2GB. There is nothing else going on on the server! Now, even REINDEX is just failing in the middle: # REINDEX INDEX new_idx_userid; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. What else could be wrong? On Mon, Apr 18, 2011 at 2:38 AM, Scott Marlowe scott.marl...@gmail.com wrote: On Sun, Apr 17, 2011 at 10:59 AM, Phoenix phoenix.ki...@gmail.com wrote: TOP does not show much beyond postmaster. How should I use TOP and what info can I give you? This is what it looks like: We're basically looking to see if the postmaster process doing the vacuuming or reindexing is stuck in a D state, which means it's waiting on IO. hot the c key while it's running and you should get a little more info on which processes are what. 4799 postgres 15 0 532m 94m 93m D 0.7 1.2 0:00.14 postmaster That is likely the postmaster that is waiting on IO. VMSTAT 10 shows this: r b swpd free buff cache si so bi bo in cs us sy id wa 3 14 99552 17900 41108 7201712 0 0 42 11 0 0 8 34 41 16 2 17 99552 16468 41628 7203012 0 0 1326 84 1437 154810 7 66 12 15 3 7 99476 16796 41056 7198976 0 0 1398 96 1453 156211 7 66 21 6 3 17 99476 17228 39132 7177240 0 0 1325 68 1529 156111 8 65 16 11 So, we're at 11 to 15% io wait. I'm gonna guess you have 8 cores / threads in your CPUs, and 1/8th ot 100% is 12% so looks like you're probably IO bound here. iostat tells us more: The results of iostat -xd 10 is: Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 7.41 0.30 3.50 2.40 87.29 1.20 43.64 23.58 0.13 32.92 10.03 3.81 sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdc 0.00 18.32 158.26 4.10 2519.32 180.98 1259.66 90.49 16.63 13.04 79.91 6.17 100.11 100% IO utilization, so yea, it's likely that your sdc drive is your bottleneck. Given our little data is actually moving through the sdc drive, it's not very fast. Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s 8GB memory in total. 1GB devoted to PGSQL during these operations. Otherwise, my settings are as follows (and yes I did make the vacuum settings more aggressive based on your email, which has had no apparent impact) -- Yeah, as it gets more aggressive it can use more of your IO bandwidth. Since you What else can I share? That's a lot of help. I'm assuming you're running software or motherboard fake-raid on this RAID-1 set? I'd suggest buying a $500 or so battery backed caching RAID controller first, the improvements in performance are huge with such a card. You might wanna try testing the current RAID-1 set with bonnie++ to get an idea of how fast it is. -- 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] REINDEX takes half a day (and still not complete!)
Thanks Scott. What is the output of running vacuum verbose as a superuser (you can run it on the postgres database so it returns fast.) Here's the output for postgres DB: INFO: free space map contains 110614 pages in 33 relations DETAIL: A total of 110464 page slots are in use (including overhead). 110464 page slots are required to track all free space. Current limits are: 95 page slots, 1500 relations, using 5665 kB. VACUUM Does running it on a postgres database also show the relevant info for other databases? From above it seems fine, right? also, if vacuum can't keep up you can increase the vacuum cost limit, and lower the cost delay. Anything above 1ms is still quite a wait compared to 0. And most systems don't have the real granularity to go that low anyway, so 5ms is about as low as you can go and get a change before 0. Also, if you've got a lot of large relations you might need to increase the max workers as well. I'm not sure I understand this. (1) I should increase max workers. But I am on version 8.2.9 -- did this version have autovacuum_max_workers? It seems to be a more recent thing: http://sn.im/27nxe1 (2) The big table in my database (with 125 million rows) has about 5,000 rows that get DELETEd every day, about 100,000 new INSERTs, and about 12,000 UPDATEs. (3) What's that thing about cost delay. Which values from vacuum should I check to determine the cost delay -- what's the specific formula? Thanks! On Sat, Mar 19, 2011 at 12:58 PM, Scott Marlowe scott.marl...@gmail.com wrote: On Fri, Mar 18, 2011 at 9:07 PM, Phoenix Kiula phoenix.ki...@gmail.com wrote: I have a large table but not as large as the kind of numbers that get discussed on this list. It has 125 million rows. REINDEXing the table takes half a day, and it's still not finished. To write this post I did SELECT COUNT(*), and here's the output -- so long! select count(*) from links; count --- 125418191 (1 row) Time: 1270405.373 ms That's 1270 seconds! I suppose the vaccuum analyze is not doing its job? As you can see from settings below, I have autovacuum set to ON, and there's also a cronjob every 10 hours to do a manual vacuum analyze on this table, which is largest. PG is version 8.2.9. Any thoughts on what I can do to improve performance!? Below are my settings. max_connections = 300 shared_buffers = 500MB effective_cache_size = 1GB max_fsm_relations = 1500 max_fsm_pages = 95 work_mem = 100MB What is the output of running vacuum verbose as a superuser (you can run it on the postgres database so it returns fast.) We're looking for the output that looks like this: INFO: free space map contains 1930193 pages in 749 relations DETAIL: A total of 1787744 page slots are in use (including overhead). 1787744 page slots are required to track all free space. Current limits are: 1000 page slots, 3000 relations, using 58911 kB. If the space needed exceeds page slots then you need to crank up your free space map. If the relations exceeds the available then you'll need to crank up max relations. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] REINDEX takes half a day (and still not complete!)
I have a large table but not as large as the kind of numbers that get discussed on this list. It has 125 million rows. REINDEXing the table takes half a day, and it's still not finished. To write this post I did SELECT COUNT(*), and here's the output -- so long! select count(*) from links; count --- 125418191 (1 row) Time: 1270405.373 ms That's 1270 seconds! I suppose the vaccuum analyze is not doing its job? As you can see from settings below, I have autovacuum set to ON, and there's also a cronjob every 10 hours to do a manual vacuum analyze on this table, which is largest. PG is version 8.2.9. Any thoughts on what I can do to improve performance!? Below are my settings. max_connections = 300 shared_buffers = 500MB effective_cache_size = 1GB max_fsm_relations= 1500 max_fsm_pages= 95 work_mem = 100MB temp_buffers = 4096 authentication_timeout = 10s ssl = off checkpoint_warning = 3600 random_page_cost = 1 autovacuum = on autovacuum_vacuum_cost_delay = 20 vacuum_cost_delay= 20 vacuum_cost_limit= 600 autovacuum_naptime = 10 stats_start_collector= on stats_row_level = on autovacuum_vacuum_threshold = 75 autovacuum_analyze_threshold = 25 autovacuum_analyze_scale_factor = 0.02 autovacuum_vacuum_scale_factor = 0.01 wal_buffers = 64 checkpoint_segments = 128 checkpoint_timeout = 900 fsync= on maintenance_work_mem = 512MB -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] PG 8.3 and server load
I'm on a CentOS 5 OS 64 bit, latest kernel and all of that. PG version is 8.3.7, compiled as 64bit. The memory is 8GB. It's a 2 x Dual Core Intel 5310. Hard disks are Raid 1, SCSI 15 rpm. The server is running just one website. So there's Apache 2.2.11, MySQL (for some small tasks, almost negligible). And then there's PG, which in the top command shows up as the main beast. My server load is going to 64, 63, 65, and so on. Where should I start debugging? What should I see? TOP command does not yield anything meaningful. I mean, even if it shows that postgres user for postmaster and nobody user for httpd (apache) are the main resource hogs, what should I start with in terms of debugging? -- 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] PG 8.3 and server load
On Wed, Aug 19, 2009 at 11:25 PM, Andy Colsona...@squeakycode.net wrote: snip. the first line of vmstat is an average since bootup. Kinda useless. run it as: 'vmstat 4' it will print a line every 4 seconds, which will be a summary of everything that happened in the last 4 seconds. since boot, you've written out an average of 153 blocks (the bo column). Thats very small, so your not io bound. but... you have average 74% idle cpu. So your not cpu bound either? Ahh? I'm not sure what that means. Maybe I'm reading something wrong? -Andy ~ vmstat 4 procs ---memory-- ---swap-- -io --system-- cpu r b swpd free buff cache si sobibo incs us sy id wa 0 2 16128 35056 62800 76974280074 1530 3 10 5 74 12 0 0 16128 38256 62836 769817200 166 219 1386 1440 7 4 85 4 0 1 16128 34704 62872 769891600 119 314 1441 1589 7 4 85 5 0 0 16128 29544 62912 769939600 142 144 1443 1418 6 3 88 2 7 1 16128 26784 62832 769219600 343 241 1492 1671 8 5 83 4 0 0 16128 32840 62880 769318800 253 215 1459 1511 7 4 85 4 0 0 16128 30112 62940 769390800 187 216 1395 1282 6 3 87 4 -- 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] PG 8.3 and server load
On Wed, Aug 19, 2009 at 11:37 PM, Andy Colsona...@squeakycode.net wrote: Phoenix: run top again, and hit the '1' key. It'll show you stats for each cpu. Is one pegged and the others idle? top - 10:38:53 up 29 days, 5 min, 1 user, load average: 64.99, 65.17, 65.06 Tasks: 568 total, 1 running, 537 sleeping, 6 stopped, 24 zombie Cpu0 : 17.7% us, 7.7% sy, 0.0% ni, 74.0% id, 0.7% wa, 0.0% hi, 0.0% si Cpu1 : 6.3% us, 5.6% sy, 0.0% ni, 84.4% id, 3.6% wa, 0.0% hi, 0.0% si Cpu2 : 5.6% us, 5.9% sy, 0.0% ni, 86.8% id, 1.7% wa, 0.0% hi, 0.0% si Cpu3 : 5.6% us, 4.0% sy, 0.0% ni, 74.2% id, 16.2% wa, 0.0% hi, 0.0% si Mem: 8310256k total, 8277416k used,32840k free,61944k buffers Swap: 2096440k total,16128k used, 2080312k free, 7664224k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 9922 nobody15 0 49024 16m 7408 S 3.0 0.2 0:00.52 httpd 9630 nobody15 0 49020 16m 7420 S 2.3 0.2 0:00.60 httpd 9848 nobody16 0 48992 16m 7372 S 2.3 0.2 0:00.51 httpd 10995 nobody15 0 49024 16m 7304 S 2.3 0.2 0:00.35 httpd 11031 nobody15 0 48860 16m 7104 S 2.3 0.2 0:00.34 httpd 6701 nobody15 0 49028 17m 7576 S 2.0 0.2 0:01.50 httpd 10996 nobody15 0 48992 16m 7328 S 2.0 0.2 0:00.31 httpd 12232 nobody15 0 48860 16m 7004 S 1.7 0.2 0:00.05 httpd 9876 nobody15 0 48992 16m 7400 S 1.3 0.2 0:00.73 httpd 12231 nobody15 0 48860 16m 6932 S 1.3 0.2 0:00.04 httpd 12233 nobody16 0 48860 16m 6960 S 1.3 0.2 0:00.04 httpd 20315 postgres 19 0 325m 9732 9380 S 1.0 0.1 0:10.39 postmaster 31573 nobody15 0 49024 17m 7664 S 1.0 0.2 0:03.14 httpd 7954 nobody15 0 49032 16m 7400 S 1.0 0.2 0:01.14 httpd 9918 nobody15 0 48956 16m 7344 S 1.0 0.2 0:00.44 httpd 12298 nobody16 0 48860 16m 6780 S 1.0 0.2 0:00.03 httpd 6479 nobody16 0 49040 16m 7412 S 0.7 0.2 0:01.20 httpd 7950 nobody15 0 49020 16m 7388 S 0.7 0.2 0:00.83 httpd 7951 nobody15 0 49032 16m 7384 S 0.7 0.2 0:01.03 httpd 9875 nobody15 0 48948 16m 7096 S 0.7 0.2 0:00.51 httpd 9916 nobody16 0 48860 16m 7124 S 0.7 0.2 0:00.59 httpd 10969 nobody15 0 49036 16m 7380 S 0.7 0.2 0:00.29 httpd 11752 root 16 0 3620 1288 772 R 0.7 0.0 0:00.14 top 12309 nobody16 0 48860 16m 6844 S 0.7 0.2 0:00.02 httpd 20676 mysql 15 0 182m 20m 2916 S 0.3 0.3 0:00.95 mysqld 20811 root 21 0 47920 14m 5872 S 0.3 0.2 0:00.71 httpd 7952 nobody15 0 49024 16m 7524 S 0.3 0.2 0:00.96 httpd 11036 nobody15 0 48992 16m 7320 S 0.3 0.2 0:00.36 httpd 12230 nobody15 0 48860 16m 6956 S 0.3 0.2 0:00.01 httpd 12297 nobody16 0 48860 16m 6932 S 0.3 0.2 0:00.01 httpd 12299 nobody16 0 48992 16m 7120 S 0.3 0.2 0:00.01 httpd 12301 nobody20 0 48860 16m 6816 S 0.3 0.2 0:00.01 httpd 12307 nobody15 0 48860 16m 6880 S 0.3 0.2 0:00.01 httpd do a 'cat /proc/cpuinfo' and make sure your os is seeing all your cpus. I guess it's using all 4?
[PERFORM] PG performance in high volume environment (many INSERTs and lots of aggregation reporting)
[Ppsted similar note to PG General but I suppose it's more appropriate in this list. Apologies for cross-posting.] Hi. Further to my bafflement with the count(*) queries as described in this thread: http://archives.postgresql.org/pgsql-general/2009-01/msg00804.php It seems that whenever this question has come up, Postgresql comes up very short in terms of count(*) functions. The performance is always slow, because of the planner's need to guess and such. I don't fully understand how the statistics work (and the explanation on the PG website is way too geeky) but he columns I work with already have a stat level of 100. Not helping at all. We are now considering a web based logging functionality for users of our website. This means the table could be heavily INSERTed into. We get about 10 million hits a day, and I'm guessing that we will have to keep this data around for a while. My question: with that kind of volume and the underlying aggregation functions (by product id, dates, possibly IP addresses or at least countries of origin..) will PG ever be a good choice? Or should I be looking at some other kind of tools? I wonder if OLAP tools would be overkill for something that needs to look like a barebones version of google analytics limited to our site.. Appreciate any thoughts. If possible I would prefer to tone down any requests for MySQL and such! Thanks! -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
