Re: [PERFORM] Do I need to rebuild php-pgsql for 8.2.3
Hi, Thanks for the info. One more thingI am in rpm hell. When I try to # rpm -Uvh postgresql-libs-8.2.3-1PGDG.i686.rpm I get: error: Failed dependencies: libpq.so.3 is needed by (installed) perl-DBD-Pg-1.31-6.i386 libpq.so.3 is needed by (installed) postgresql-python-7.4.13-2.RHEL4.1.i386 libpq.so.3 is needed by (installed) php-pgsql-4.3.9-3.15.i386 and when I try: # rpm -ivh compat-postgresql-libs-3-3PGDG.i686.rpm I get: error: Failed dependencies: postgresql-libs 8.0.2 conflicts with compat-postgresql-libs-3-3PGDG.i686 gr... should just force the upgrade (ie. --nodeps)? Thanks Mike On 4/10/07, Devrim GÜNDÜZ [EMAIL PROTECTED] wrote: Hi, On Tue, 2007-04-10 at 22:55 +0200, Guillaume Smet wrote: See http://developer.postgresql.org/~devrim/rpms/compat/ and choose the correct package for your architecture. ... or better, each RHEL4 directory in our FTP site has compat package (that directory is not up2date now). Regards, -- Devrim GÜNDÜZ PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/
Re: [PERFORM] Do I need to rebuild php-pgsql for 8.2.3
Here's what I do... 1) Install postgresql-libs from the RHEL source 2) Install compat-postgresql-libs from postgresql.org (install, not upgrade, use rpm -hiv) use force if necessary 3) Install postgresq-libs from postgresql.org (again, install, not upgrade, use rpm-hiv) use force if necessary If done correctly, you'll end up with all 3 client versions: /usr/lib/libpq.so.3 /usr/lib/libpq.so.4 /usr/lib/libpq.so.5 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Michael Dengler Sent: Wednesday, April 11, 2007 12:25 PM To: Devrim GÜNDÜZ Cc: pgsql-performance; Guillaume Smet Subject: Re: [PERFORM] Do I need to rebuild php-pgsql for 8.2.3 Hi, Thanks for the info. One more thingI am in rpm hell. When I try to # rpm -Uvh postgresql-libs-8.2.3-1PGDG.i686.rpm I get: error: Failed dependencies: libpq.so.3 is needed by (installed) perl-DBD-Pg-1.31-6.i386 libpq.so.3 is needed by (installed) postgresql-python-7.4.13-2.RHEL4.1.i386 libpq.so.3 is needed by (installed) php-pgsql-4.3.9-3.15.i386 and when I try: # rpm -ivh compat-postgresql-libs-3-3PGDG.i686.rpm I get: error: Failed dependencies: postgresql-libs 8.0.2 conflicts with compat-postgresql-libs-3-3PGDG.i686 gr... should just force the upgrade (ie. --nodeps)? Thanks Mike On 4/10/07, Devrim GÜNDÜZ [EMAIL PROTECTED] wrote: Hi, On Tue, 2007-04-10 at 22:55 +0200, Guillaume Smet wrote: See http://developer.postgresql.org/~devrim/rpms/compat/ and choose the correct package for your architecture. ... or better, each RHEL4 directory in our FTP site has compat package (that directory is not up2date now). Regards, -- Devrim GÜNDÜZ PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/
Re: [PERFORM] Do I need to rebuild php-pgsql for 8.2.3
Hi, On Wed, 2007-04-11 at 13:25 -0400, Michael Dengler wrote: Thanks for the info. One more thingI am in rpm hell. When I try to # rpm -Uvh postgresql-libs-8.2.3-1PGDG.i686.rpm I get: error: Failed dependencies: libpq.so.3 is needed by (installed) perl-DBD-Pg-1.31-6.i386 libpq.so.3 is needed by (installed) postgresql-python-7.4.13-2.RHEL4.1.i386 libpq.so.3 is needed by (installed) php-pgsql-4.3.9-3.15.i386 and when I try: # rpm -ivh compat-postgresql-libs-3-3PGDG.i686.rpm I get: error: Failed dependencies: postgresql-libs 8.0.2 conflicts with compat-postgresql-libs-3-3PGDG.i686 It seems that you already have PostgreSQL installed on your server. Tı install 8.2.3: * Take a dump using pg_dump(all). * Remove existing RPMS, ignore warnings about libpq.so* * Install compat-3 package * Install 8.2.3 packages. * Reload your dump. Regards, -- Devrim GÜNDÜZ PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
Re: [PERFORM] Do I need to rebuild php-pgsql for 8.2.3
Thanks...worked perfectly! Mike On 4/11/07, Adam Rich [EMAIL PROTECTED] wrote: Here's what I do... 1) Install postgresql-libs from the RHEL source 2) Install compat-postgresql-libs from postgresql.org (install, not upgrade, use rpm -hiv) use force if necessary 3) Install postgresq-libs from postgresql.org (again, install, not upgrade, use rpm-hiv) use force if necessary If done correctly, you'll end up with all 3 client versions: /usr/lib/libpq.so.3 /usr/lib/libpq.so.4 /usr/lib/libpq.so.5 -Original Message- *From:* [EMAIL PROTECTED] [mailto: [EMAIL PROTECTED] *On Behalf Of *Michael Dengler *Sent:* Wednesday, April 11, 2007 12:25 PM *To:* Devrim GÜNDÜZ *Cc:* pgsql-performance; Guillaume Smet *Subject:* Re: [PERFORM] Do I need to rebuild php-pgsql for 8.2.3 Hi, Thanks for the info. One more thingI am in rpm hell. When I try to # rpm -Uvh postgresql-libs-8.2.3-1PGDG.i686.rpm I get: error: Failed dependencies: libpq.so.3 is needed by (installed) perl-DBD-Pg-1.31-6.i386 libpq.so.3 is needed by (installed) postgresql-python-7.4.13-2.RHEL4.1.i386 libpq.so.3 is needed by (installed) php-pgsql-4.3.9-3.15.i386 and when I try: # rpm -ivh compat-postgresql-libs-3-3PGDG.i686.rpm I get: error: Failed dependencies: postgresql-libs 8.0.2 conflicts with compat-postgresql-libs-3-3PGDG.i686 gr... should just force the upgrade (ie. --nodeps)? Thanks Mike On 4/10/07, Devrim GÜNDÜZ [EMAIL PROTECTED] wrote: Hi, On Tue, 2007-04-10 at 22:55 +0200, Guillaume Smet wrote: See http://developer.postgresql.org/~devrim/rpms/compat/http://developer.postgresql.org/%7Edevrim/rpms/compat/and choose the correct package for your architecture. ... or better, each RHEL4 directory in our FTP site has compat package (that directory is not up2date now). Regards, -- Devrim GÜNDÜZ PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/
Re: [PERFORM] Beginner Question
On Monday 09 April 2007 05:09:53 s d wrote: Hi, I am trying to figure out how to debug a performance problem / use psql explain. The table in question is: # \d word_association; Table public.word_association Column | Type | Modifiers ++ word1 | character varying(128) | not null word2 | character varying(128) | not null count | integer| not null default 0 Indexes: word1_word2_comb_unique unique, btree (word1, word2) word1_hash_index hash (word1) word2_hash_index hash (word2) word_association_count_index btree (count) word_association_index1_1 btree (word1) word_association_index2_1 btree (word2) It has multiple indices since i wanted to see which one the planner choses. # explain select * FROM word_association WHERE (word1 = 'bdss' OR word2 = 'bdss') AND count = 10; QUERY PLAN --- - Bitmap Heap Scan on word_association (cost=11.53..1192.09 rows=155 width=22) Recheck Cond: (((word1)::text = 'bdss'::text) OR ((word2)::text = 'bdss'::text)) Filter: (count = 10) - BitmapOr (cost=11.53..11.53 rows=364 width=0) - Bitmap Index Scan on word_association_index1_1 (cost=0.00..5.79 rows=190 width=0) Index Cond: ((word1)::text = 'bdss'::text) - Bitmap Index Scan on word_association_index2_1 (cost=0.00..5.67 rows=174 width=0) Index Cond: ((word2)::text = 'bdss'::text) (8 rows) The questions: 1. i can undestand where the cost=11.53 came from but where did the 1192.09 come form? The values are in milli right ? 2. the query takes in reality much longer than 1 second. In short, it feels like something is very wrong here (i tried vacuum analyze and it didn't do much diff). any ideas ? You need an index on (word1, word2, count). In your current setup it will have to scan all rows that satisfy word1 and word2 to see if count = 10. jan -- -- Jan de Visser [EMAIL PROTECTED] Baruk Khazad! Khazad ai-menu! -- ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[PERFORM] Question about memory allocations
Hey there; I'm trying to tune the memory usage of a new machine that has a -lot- of memory in it (32 gigs). We're upgrading from a machine that had 16 gigs of RAM and using a database that's around 130-some gigs on disc. Our largest tables have in the order of close to 10 million rows. Problem is, the postgres documentation isn't always clear about what different memory things are used for and it's definitely not clear about what 'useful values' would be for various things. Further, looking online, gets a lot of random stuff and most of the configuration information out there is for pre-8.1 versions that don't have all these new and strange values :) This machine exists only for the database. With that in mind, a few questions. - I've set up a configuration (I'll show important values below), and Im wondering if there's any way I can actually see the distribution of memory in the DB and how the memory is being used. - What is temp_buffers used for exactly? Does this matter for, say, nested queries or anything in specific? Is there any case where having this as a large number actually -helps-? - Do full_page_writes and wal_buffers settings matter AT ALL for a machine where fysnc = off ? - What does wal_buffers mean and does increasing this value actually help anything? - Any idea if this is a smart configuration for this machine? It's a Redhat Enterprise Linux machine (kernel 2.6.18), 8 dual-core AMD 64bit processors, 32 gigs of RAM, 4x 176 (or whatever the exact number is) gig SCSI hard drives in a stripe. Only values I have modified are mentioned, everything else left at default: shared_buffers = 16GB temp_buffers = 128MB max_prepared_transactions = 0 # This value is going to probably set off cries of using this as a set # command instead of a big global value; however there's more big queries # than small ones and the number of simultaneous users is very small so # 'for now' this can be set globally big and if it shows improvement # I'll implement it as set commands later. # # Question; does this mean 2 gigs will be immediately allocated to # every query, or is this just how big the work memory is allowed to # grow per transaction? work_mem=2G maintenance_work_mem = 4GB max_stack_depth = 16MB # Vacuum suggested I make this 'over 360' on the old machine, so # I use this value; if it's too big, this is a symptom of another problem, # I'd be interested to know :) max_fsm_pages = 500 # For a lot of reasons, it doesn't make any sense to use fsync for this # DB. Read-only during the day, backed up daily, UPS'd, etc. fsync = off full_page_writes = off wal_buffers = 512MB # Leaving this low makes the DB complain, but I'm not sure what's # reasonable. checkpoint_segments = 128 random_page_cost = 1.5 cpu_tuple_cost = 0.001 cpu_index_tuple_cost = 0.0005 cpu_operator_cost = 0.00025 effective_cache_size = 8GB default_statistics_target = 100 Thanks for all your help! Steve ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] Slow Postgresql server
Hello all, My website has been having issues with our new Linux/PostgreSQL server being somewhat slow. I have done tests using Apache Benchmark and for pages that do not connect to Postgres, the speeds are much faster (334 requests/second v. 1-2 requests/second), so it seems that Postgres is what's causing the problem and not Apache. I did some reserach, and it seems that the bottleneck is in fact the hard drives! Here's an excerpt from vmstat: procs ---memory-- ---swap-- -io --system-- -cpu-- r b swpd free buff cache si sobibo incs us sy id wa st 1 1140 24780 166636 57514400 0 3900 1462 3299 1 4 49 48 0 0 1140 24780 166636 57514400 0 3828 1455 3391 0 4 48 48 0 1 1140 24780 166636 57514400 0 2440 960 2033 0 3 48 48 0 0 1140 24780 166636 57514400 0 2552 1001 2131 0 2 50 49 0 0 1140 24780 166636 57514400 0 3188 1233 2755 0 3 49 48 0 0 1140 24780 166636 57514400 0 2048 868 1812 0 2 49 49 0 0 1140 24780 166636 57514400 0 2720 1094 2386 0 3 49 49 0 As you can see, almost 50% of the CPU is waiting on I/O. This doesn't seem like it should be happening, however, since we are using a RAID 1 setup (160+160). We have 1GB ram, and have upped shared_buffers to 13000 and work_mem to 8096. What would cause the computer to only use such a small percentage of the CPU, with more than half of it waiting on I/O requests? Thanks a lot Jason ---(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] Question about memory allocations
Steve [EMAIL PROTECTED] writes: - What is temp_buffers used for exactly? Temporary tables. Pages of temp tables belonging to your own backend don't ever get loaded into the main shared-buffers arena, they are read into backend-local memory. temp_buffers is the max amount (per backend) of local memory to use for this purpose. - Do full_page_writes and wal_buffers settings matter AT ALL for a machine where fysnc = off ? Yes. - What does wal_buffers mean and does increasing this value actually help anything? It's the amount of space available to buffer WAL log data that's not been written to disk. If you have a lot of short transactions then there's not much benefit to increasing it (because the WAL will be getting forced to disk frequently anyway) but I've heard reports that for workloads involving long single transactions bumping it up to 64 or 100 or so helps. - Any idea if this is a smart configuration for this machine? Um ... you didn't mention which PG version? # This value is going to probably set off cries of using this as a set # command instead of a big global value; No kidding. You do NOT want work_mem that high, at least not without an extremely predictable, simple workload. wal_buffers = 512MB I haven't heard any reports that there's a point in values even as high as 1 meg for this. regards, tom lane ---(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] Slow Postgresql server
Jason Lustig skrev: and work_mem to 8096. What would cause the computer to only use such a small percentage of the CPU, with more than half of it waiting on I/O requests? Do your webpages write things to the database on each connect? Maybe it do a bunch of writes each individually commited? For every commit pg will wait for the data to be written down to the disk platter before it move on. So if you do several writes you want to do them in one transaction so you only need one commit. /Dennis ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Slow Postgresql server
On Wed, 11 Apr 2007, Jason Lustig wrote: Hello all, My website has been having issues with our new Linux/PostgreSQL server being somewhat slow. I have done tests using Apache Benchmark and for pages that do not connect to Postgres, the speeds are much faster (334 requests/second v. 1-2 requests/second), so it seems that Postgres is what's causing the problem and not Apache. I did some reserach, and it seems that the bottleneck is in fact the hard drives! Here's an excerpt from vmstat: procs ---memory-- ---swap-- -io --system-- -cpu-- r b swpd free buff cache si sobibo incs us sy id wa st 1 1140 24780 166636 57514400 0 3900 1462 3299 1 4 49 48 0 0 1140 24780 166636 57514400 0 3828 1455 3391 0 4 48 48 0 1 1140 24780 166636 57514400 0 2440 960 2033 0 3 48 48 0 0 1140 24780 166636 57514400 0 2552 1001 2131 0 2 50 49 0 0 1140 24780 166636 57514400 0 3188 1233 2755 0 3 49 48 0 0 1140 24780 166636 57514400 0 2048 868 1812 0 2 49 49 0 0 1140 24780 166636 57514400 0 2720 1094 2386 0 3 49 49 0 As you can see, almost 50% of the CPU is waiting on I/O. This doesn't seem like it should be happening, however, since we are using a RAID 1 setup (160+160). We have 1GB ram, and have upped shared_buffers to 13000 and work_mem to 8096. What would cause the computer to only use such a small percentage of the CPU, with more than half of it waiting on I/O requests? Well, the simple answer is a slow disk subsystem. Is it hardware or software RAID1? If hardware, what's the RAID controller? Based on your vmstat output, I'd guess that this query activity is all writes since I see only blocks out. Can you identify what the slow queries are? What version of postgres? How large is the database? Can you post the non-default values in your postgresql.conf? I'd suggest you test your disk subsystem to see if it's as performant as you think with bonnie++. Here's some output from my RAID1 test server: Version 1.03 --Sequential Output-- --Sequential Input- --Random- -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks-- MachineSize K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec %CP pgtest 4G 47090 92 52348 11 30954 6 41838 65 73396 8 255.9 1 --Sequential Create-- Random Create -Create-- --Read--- -Delete-- -Create-- --Read--- -Delete-- files /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP 16 894 2 + +++ 854 1 817 2 + +++ 969 2 So, that's 52MB/sec block writes and 73MB/sec block reads. That's typical of a RAID1 on 2 semi-fast SATA drives. If you're doing writes to the DB on every web page, you might consider playing with the commit_delay and commit_siblings parameters in the postgresql.conf. Also, if you're doing multiple inserts as separate transactions, you should consider batching them up in one transaction. -- Jeff Frost, Owner [EMAIL PROTECTED] Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(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
