Re: [PERFORM] query memory consumption
On Mon, 21 Sep 2009, Alan McKay wrote: We have explain and analyze which tell us about the cost of a query time-wise, but what does one use to determine (and trace / predict?) memory consumption? In Postgres, memory consumption for all operations is generally capped at the value of work_mem. However, a given query can consist of more than one operation. Generally, only heavy things like sorts and hashes consume work_mem, so it should be possible to look at the explain to count those, multiply by work_mem, and get the maximum amount of RAM that the query can use. However, sometimes a query will not fit neatly into work_mem. At this point, Postgres will write the data to temporary files on disc. It is harder to predict what size those will be. However, EXPLAIN ANALYSE will sometimes give you a figure of how big a sort was for example. Matthew -- Reality is that which, when you stop believing in it, doesn't go away. -- Philip K. Dick -- 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] Slow select times on select with xpath
astro77 writes: > Kevin Grittner wrote: >> I would try to minimize how many XML values it had to read, parse, and >> search. The best approach that comes to mind would be to use tsearch2 >> techniques (with a GIN or GiST index on the tsvector) to identify >> which rows contain 'fdc3da1f-060f-4c34-9c30-d9334d9272ae', and use AND >> to combine that with your xpath search. > > Thanks Kevin. I thought about using tsearch2 but I need to be able to select > exact values on other numerical queries and cannot use "contains" queries. > It's got to be fast so I cannot have lots of records returned and have to do > secondary processing on the xml for the records which contain the exact > value I'm looking for. This is one of the reasons I moved from using Lucene > for searching. I hope this makes sense. I think he meant something following this skeleton: SELECT ... FROM ( SELECT ... FROM ... WHERE /* insert preliminary filtering here */ ) WHERE /* insert xpath related filtering here */ Hopefully you have a preliminary filtering available that's restrictive enough for the xpath filtering to only have to check few rows. Kevin proposes that this preliminary filtering be based on Tsearch with an adequate index (GiST for data changing a lot, GIN for pretty static set). As you can see the two-steps filtering can be done in a single SQL query. Regards, -- dim -- 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 memory consumption
On Mon, Sep 21, 2009 at 4:08 PM, Robert Haas wrote: > Setting work_mem too high is a frequent cause of problems of this sort, I > think. Too high? How high is too high? -- “Don't eat anything you've ever seen advertised on TV” - Michael Pollan, author of "In Defense of Food" -- 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 memory consumption
On Tue, Sep 22, 2009 at 1:36 PM, Alan McKay wrote: > Too high? How high is too high? in a very simple scenario, you have 100 connections opened, and all of them run the query that was the reason you bumped work_mem to 256M. All of the sudden postgresql starts to complain about lack of ram, because you told it it could use max of work_mem*number_of_connections. Best practice to avoid that, is to bump the work_mem temporarily before the query, and than lower it again, lowers the chance of memory exhaustion. -- GJ -- 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 memory consumption
> Best practice to avoid that, is to bump the work_mem temporarily > before the query, and than lower it again, lowers the chance of memory > exhaustion. Interesting - I can do that dynamically? -- “Don't eat anything you've ever seen advertised on TV” - Michael Pollan, author of "In Defense of Food" -- 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] session servers in ram
On Mon, Sep 21, 2009 at 5:39 PM, Scott Marlowe wrote: > I'm looking at running session servers in ram. All the data is > throw-away data, so my plan is to have a copy of the empty db on the > hard drive ready to go, and have a script that just copies it into ram > and starts the db there. We're currently IO write bound with > fsync=off using a 15k5 seagate SAS drive, so I'm hoping that moving > the db into /dev/shm will help quite a bit here. > > Does anybody any real world experience here or any words of sage > advice before I go off and start testing this? > > I assume you intend to this or some variation of it. mount -t tmpfs -o size=1G tmpfs /pgfast tmpfs file systems, including /dev/shm, ***should not*** be counted on as being RAM only. File systems of this type in Linux, and at least Solaris also, can be swap since the tmpfs type is derived from swap and swap = memory + active swap partitions. I would think that a ram disk or ramfs may be more what you're after. Add ramdisk_size=X, where X is the maximum size in kilobytes, to the kernel line in your grub.conf file. Unlike tmpfs, the ramdisk_size X parameter cannot be more than the memory of your server. When using a ramdisk the ext2 file system would be best with all the fun noatime and like mount parameters. This is good for a fast, volatile, fixed-size ramdisk. OTOH, ramfs can be just as fast but with all the fun system-hanging features like growing until all RAM is consumed. mount -t ramfs ramfs /pgfast Pro ... it will always be as fast, or possibly a hair faster, than tmpfs. Con ... it won't show up in a df output unlike tmpfs or ramdisk. Use the mount command with no parameters to look for it and be sure to unmount it when you're done. Pro/Con ... you can't specify the file system type like with ramdisk. Pro... it will only take away from memory as space is used, i.e. if you have 500M of memory in use and mount the file system but do nothing else then only 500M of memory is in use. If you then copy a 100M file to it then 600M of memory is in use. Delete that 100M file and you're back to 500M of used memory. Pro/Con ... unlike other file systems, it will grow with the need... unchecked. It could attempt to consume all available memory pushing all other processes out to swap and this is a bad, bad thing. I'm sure there are other pro's & con's to ramfs. HTH. Greg
Re: [PERFORM] query memory consumption
On Tue, Sep 22, 2009 at 1:46 PM, Alan McKay wrote: >> Best practice to avoid that, is to bump the work_mem temporarily >> before the query, and than lower it again, lowers the chance of memory >> exhaustion. > > Interesting - I can do that dynamically? you can do set work_mem=128M; select 1; set work_mem=64M; etc, in one query. -- GJ -- 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] session servers in ram
Hello this is maybe off topic. Do you know memcached? We use it without postgresql six or seven months for short-live data with big success. regards Pavel Stehule 2009/9/22 Greg Spiegelberg : > On Mon, Sep 21, 2009 at 5:39 PM, Scott Marlowe > wrote: >> >> I'm looking at running session servers in ram. All the data is >> throw-away data, so my plan is to have a copy of the empty db on the >> hard drive ready to go, and have a script that just copies it into ram >> and starts the db there. We're currently IO write bound with >> fsync=off using a 15k5 seagate SAS drive, so I'm hoping that moving >> the db into /dev/shm will help quite a bit here. >> >> Does anybody any real world experience here or any words of sage >> advice before I go off and start testing this? >> > > I assume you intend to this or some variation of it. > mount -t tmpfs -o size=1G tmpfs /pgfast > > tmpfs file systems, including /dev/shm, ***should not*** be counted on as > being RAM only. File systems of this type in Linux, and at least Solaris > also, can be swap since the tmpfs type is derived from swap and swap = > memory + active swap partitions. > > I would think that a ram disk or ramfs may be more what you're after. Add > ramdisk_size=X, where X is the maximum size in kilobytes, to the kernel line > in your grub.conf file. Unlike tmpfs, the ramdisk_size X parameter cannot > be more than the memory of your server. When using a ramdisk the ext2 file > system would be best with all the fun noatime and like mount parameters. > This is good for a fast, volatile, fixed-size ramdisk. > > OTOH, ramfs can be just as fast but with all the fun system-hanging features > like growing until all RAM is consumed. > mount -t ramfs ramfs /pgfast > > Pro ... it will always be as fast, or possibly a hair faster, than tmpfs. > > Con ... it won't show up in a df output unlike tmpfs or ramdisk. Use the > mount command with no parameters to look for it and be sure to unmount it > when you're done. > > Pro/Con ... you can't specify the file system type like with ramdisk. > > Pro... it will only take away from memory as space is used, i.e. if you have > 500M of memory in use and mount the file system but do nothing else then > only 500M of memory is in use. If you then copy a 100M file to it then 600M > of memory is in use. Delete that 100M file and you're back to 500M of used > memory. > > Pro/Con ... unlike other file systems, it will grow with the need... > unchecked. It could attempt to consume all available memory pushing all > other processes out to swap and this is a bad, bad thing. > > > I'm sure there are other pro's & con's to ramfs. > > HTH. > > Greg > -- 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] session servers in ram
* Scott Marlowe [090921 19:39]: > I'm looking at running session servers in ram. All the data is > throw-away data, so my plan is to have a copy of the empty db on the > hard drive ready to go, and have a script that just copies it into ram > and starts the db there. We're currently IO write bound with > fsync=off using a 15k5 seagate SAS drive, so I'm hoping that moving > the db into /dev/shm will help quite a bit here. > > Does anybody any real world experience here or any words of sage > advice before I go off and start testing this? *If* fsync=off is really meaning that there are no sync commands happening on your pg partitions (and nothing else, like syslog, is causing syncs on them), and you're kernel is tuned to allow the maximum dirty buffers/life, then I'm not sure that's going to gain you anything... If your pg processes are blocked writing, with no syncs, then they are blocked because the kernel has no more buffers available for buffering the writes... Moving your backing store from a disk-based FS to disk-based swap is only going to shift the route of being forced to hit the disk... Of course, details matter, and results trump theory, so test it ;-) a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [PERFORM] statement stats extra load?
On Tue, Sep 22, 2009 at 2:42 AM, Magnus Hagander wrote: > That's not true at all. > > If you have many relations in your cluster that have at some point been > touched, the starts collector can create a *significant* load on the I/o > system. I've come across several cases where the only choice was to disable > the collector completely, even given all the drawbacks from that. Thanks Magnus, I thought that other response sounded a bit fanciful :-) So is there any way to predict the load this will have? Or just try it and hope for the best? :-) Right now on our 8.3 system it is off and we'd like to turn it on -- “Don't eat anything you've ever seen advertised on TV” - Michael Pollan, author of "In Defense of Food" -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] High CPU load on Postgres Server during Peak times!!!!
Dear all I am having a problem of high cpu loads in my postgres server during peak time. Following are the details of my setup (details as per the postgres wiki) . ** PostgreSQL version o Run "select pg_version();" in psql or PgAdmin III and provide the full, exact output.* clusternode2:~ # rpm -qa | grep postgres postgresql-devel-8.1.9-1.2 postgresql-8.1.9-1.2 postgresql-docs-8.1.9-1.2 postgresql-server-8.1.9-1.2 postgresql-libs-64bit-8.1.9-1.2 postgresql-libs-8.1.9-1.2 postgresql-jdbc-8.1-12.2 postgresql-contrib-8.1.9-1.2 * *A description of what you are trying to achieve and what results you expect.* To keep the CPU Load below 10 , Now during peak times the load is nearing to 40 At that time , it is not possible to access the data. ** The EXACT text of the query you ran, if any * The EXACT output of that query if it's short enough to be reasonable to post o If you think the output is wrong, what you think should've been produced instead * The EXACT error message you get, if there is one* As of now , i am unable to locate the exact query, the load shoots up abnormally during peak time is the main problem . ** What program you're using to connect to PostgreSQL* Jakarta Tomcat - Struts with JSP ** What version of the ODBC/JDBC driver you're using, if any* postgresql-jdbc-8.1-12.2 * *What you were doing when the error happened / how to cause the error. Describe in as much detail as possible, step by step, including command lines, SQL output, etc.* When certain tables with more than 3 lakh items are concurrently accessed by more than 300 users, the CPU load shoots up . ** Is there anything remotely unusual in the PostgreSQL server logs? o On Windows these are in your data directory. On a default PostgreSQL install that'll be in C:\Program Files\PostgreSQL\8.4\data\pg_log (assuming you're using 8.4) * The log file /var/log/postgresql has no data . * o On Linux this depends a bit on distro, but you'll usually find them in /var/log/postgresql/. * Operating system and version o Linux users: + Linux distro and version + Kernel details (run "uname -a" on the terminal) * SLES 10 SP3 clusternode2:~ # uname -a Linux clusternode2 2.6.16.46-0.12-ppc64 #1 SMP Thu May 17 14:00:09 UTC 2007 ppc64 ppc64 ppc64 GNU/Linux * * What kind of hardware you have. o CPU manufacturer and model, eg "AMD Athlon X2" or "Intel Core 2 Duo" o Amount and size of RAM installed, eg "2GB RAM" * High Availability Cluster with two IBM P Series Server and one DS4700 Storage IBM P series P52A with 2-core 2.1 Ghz POWER5+ Processor Card , 36 MB L3 Cache ,16 GB of RAM, 73.4 GB 10,000 RPM Ultra320 SCSI Drive for Operating System . * o Storage details (important for performance and corruption questions) + Do you use a RAID controller? If so, what type of controller? eg "3Ware Escalade 8500-8" # Does it have a battery backed cache module? # Is write-back caching enabled? + Do you use software RAID? If so, what software and what version? eg "Linux software RAID (md) 2.6.18-5-686 SMP mod_unload 686 REGPARM gcc-4.1". # In the case of Linux software RAID you can get the details from the "modinfo md_mod" command + Is your PostgreSQL database on a SAN? # Who made it, what kind, etc? Provide what details you can. + How many hard disks are connected to the system and what types are they? You need to say more than just "6 disks". At least give maker, rotational speed and interface type, eg "6 15,000rpm Seagate SAS disks". + How are your disks arranged for storage? Are you using RAID? If so, what RAID level(s)? What PostgreSQL data is on what disks / disk sets? What file system(s) are in use? # eg: "Two disks in RAID 1, with all PostgreSQL data and programs stored on one ext3 file system." # eg: "4 disks in RAID 5 holding the pg data directory on an ext3 file system. 2 disks in RAID 1 holding pg_clog, pg_xlog, the temporary tablespace, and the sort scratch space, also on ext3.". # eg: "Default Windows install of PostgreSQL" + In case of corruption data reports: # Have you had any unexpected power loss lately? # Have you run a file system check? (chkdsk / fsck) # Are there any error messages in the system logs? (unix/linux: "dmesg", "/var/log/syslog" ; Windows: Event Viewer in Control Panel -> Administrative Tools ) * IBM SAN DS4700 Storage with Fibre Channel HDD (73.4 GB * 10) Two Partitions - 73.4 GB * 3 RAID 5 - 134 GB storage partitions (One holding Jakarata tomcat application server and other holding Postgresql Database) . Four Hard disk RAID 5 with ext3 file systems hold the pgdata on SAN
[PERFORM] Hunting Unused Indexes .. is it this simple ?
Hey Everyone, So, I have a nice postgreSQL server (8.4) up and running our database. I even managed to get master->slave going without trouble using the excellent skytools.. however, I want to maximize speed and the hot updates where possible, so, I am wanting to prune unused indexes from the database. is it as simple as taking the output from ; select indexrelname from pg_stat_user_indexes where idx_scan = 0 and idx_tup_read = 0 and idx_tup_fetch = 0 ; And .. dropping ? The reason I ask is, well, the count on that gives me 750 indexes where-as the count on all user_indexes is 1100. About 2/3rds of them are obsolete ? I did do an ETL from mySQL -> postgreSQL but.. that's still a ridiculous amount of (potentially) unused indexes. Regards Stef -- 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] High CPU load on Postgres Server during Peak times!!!!
On Tue, Sep 22, 2009 at 9:54 AM, Shiva Raman wrote: > Dear all > > I am having a problem of high cpu loads in my postgres server during peak > time. Following are the > details of my setup (details as per the postgres wiki) . > > * PostgreSQL version > o Run "select pg_version();" in psql or PgAdmin III and provide the > full, exact output. > > > clusternode2:~ # rpm -qa | grep postgres > postgresql-devel-8.1.9-1.2 > postgresql-8.1.9-1.2 > postgresql-docs-8.1.9-1.2 > postgresql-server-8.1.9-1.2 > postgresql-libs-64bit-8.1.9-1.2 > postgresql-libs-8.1.9-1.2 > postgresql-jdbc-8.1-12.2 > postgresql-contrib-8.1.9-1.2 > > > * A description of what you are trying to achieve and what results you > expect. > > To keep the CPU Load below 10 , Now during peak times the load is nearing to > 40 > At that time , it is not possible to access the data. > > * The EXACT text of the query you ran, if any > > > * The EXACT output of that query if it's short enough to be reasonable to > post > o If you think the output is wrong, what you think should've been > produced instead > > * The EXACT error message you get, if there is one > > As of now , i am unable to locate the exact query, the load shoots up > abnormally during > peak time is the main problem . > > > * What program you're using to connect to PostgreSQL > > Jakarta Tomcat - Struts with JSP > > > * What version of the ODBC/JDBC driver you're using, if any > > postgresql-jdbc-8.1-12.2 > > * What you were doing when the error happened / how to cause the error. > Describe in as much detail as possible, step by step, including command > lines, SQL output, etc. > > When certain tables with more than 3 lakh items are concurrently accessed by > more than 300 > users, the CPU load shoots up . > > * Is there anything remotely unusual in the PostgreSQL server logs? > o On Windows these are in your data directory. On a default > PostgreSQL install that'll be in C:\Program Files\PostgreSQL\8.4\data\pg_log > (assuming you're using 8.4) > > The log file /var/log/postgresql has no data . > > o On Linux this depends a bit on distro, but you'll usually find > them in /var/log/postgresql/. > * Operating system and version > o Linux users: > + Linux distro and version > + Kernel details (run "uname -a" on the terminal) > > SLES 10 SP3 > clusternode2:~ # uname -a > Linux clusternode2 2.6.16.46-0.12-ppc64 #1 SMP Thu May 17 14:00:09 UTC 2007 > ppc64 ppc64 ppc64 GNU/Linux > > > > * What kind of hardware you have. > o CPU manufacturer and model, eg "AMD Athlon X2" or "Intel Core 2 > Duo" > o Amount and size of RAM installed, eg "2GB RAM" > > High Availability Cluster with two IBM P Series Server and one DS4700 > Storage > > IBM P series P52A with 2-core 2.1 Ghz POWER5+ Processor Card , 36 MB L3 > Cache ,16 GB of RAM, > 73.4 GB 10,000 RPM Ultra320 SCSI Drive for Operating System . > > > > o Storage details (important for performance and corruption > questions) > + Do you use a RAID controller? If so, what type of > controller? eg "3Ware Escalade 8500-8" > # Does it have a battery backed cache module? > # Is write-back caching enabled? > + Do you use software RAID? If so, what software and what > version? eg "Linux software RAID (md) 2.6.18-5-686 SMP mod_unload 686 > REGPARM gcc-4.1". > # In the case of Linux software RAID you can get the > details from the "modinfo md_mod" command > + Is your PostgreSQL database on a SAN? > # Who made it, what kind, etc? Provide what details you > can. > + How many hard disks are connected to the system and what > types are they? You need to say more than just "6 disks". At least give > maker, rotational speed and interface type, eg "6 15,000rpm Seagate SAS > disks". > + How are your disks arranged for storage? Are you using > RAID? If so, what RAID level(s)? What PostgreSQL data is on what disks / > disk sets? What file system(s) are in use? > # eg: "Two disks in RAID 1, with all PostgreSQL data > and programs stored on one ext3 file system." > # eg: "4 disks in RAID 5 holding the pg data directory > on an ext3 file system. 2 disks in RAID 1 holding pg_clog, pg_xlog, the > temporary tablespace, and the sort scratch space, also on ext3.". > # eg: "Default Windows install of PostgreSQL" > + In case of corruption data reports: > # Have you had any unexpected power loss lately? > # Have you run a file system check? (chkdsk / fsck) > # Are there any error messages in the system logs? > (unix/linux: "dmesg", "/var/log/syslog" ; Windows: Event Viewer in Control > Panel -> Administrative Tools ) > > > IBM SAN DS4700 Storage with Fibr
Re: [PERFORM] High CPU load on Postgres Server during Peak times!!!!
Shiva Raman wrote: Dear all I am having a problem of high cpu loads in my postgres server during peak time. Following are the details of my setup (details as per the postgres wiki) . *Following is the output of TOP command during offpeak time.* top - 18:36:56 up 77 days, 20:33, 1 user, load average: 12.99, 9.22, 10.37 Tasks: 142 total, 12 running, 130 sleeping, 0 stopped, 0 zombie Cpu(s): 46.1%us, 1.9%sy, 0.0%ni, 6.1%id, 3.0%wa, 0.0%hi, 0.1%si, 42.9%st Mem: 16133676k total, 13657396k used, 2476280k free, 450908k buffers Swap: 14466492k total, 124k used, 14466368k free, 11590056k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 22458 postgres 19 0 2473m 477m 445m R 40 3.0 0:15.49 postmaster 22451 postgres 15 0 2442m 447m 437m S 33 2.8 0:30.44 postmaster 22464 postgres 17 0 2443m 397m 383m R 28 2.5 0:13.78 postmaster 22484 postgres 16 0 2448m 431m 412m S 20 2.7 0:02.73 postmaster 22465 postgres 17 0 2440m 461m 449m R 15 2.9 0:03.52 postmaster 22452 postgres 16 0 2450m 727m 706m R 13 4.6 0:23.46 postmaster 22476 postgres 16 0 2437m 413m 405m S 13 2.6 0:06.11 postmaster 22485 postgres 16 0 2439m 230m 222m R7 1.5 0:05.72 postmaster 22481 postgres 15 0 2436m 175m 169m S7 1.1 0:04.44 postmaster 22435 postgres 17 0 2438m 371m 361m R6 2.4 1:17.92 postmaster 22440 postgres 17 0 2445m 497m 483m R5 3.2 1:44.50 postmaster 22486 postgres 17 0 2432m 84m 81m R4 0.5 0:00.76 postmaster First off, nice report. I see you are on a pretty old version of pg. Are you vacuuming regularly? If you run a 'ps ax|grep post' do you see anything that says 'idle in transaction'? (I hope that old of version will show it. my processes show up as postgres not postmaster) The top looks like you are cpu bound. Have you tried enabling logging slow queries? (again, I hope your version supports that) It could be you have a query or two that are not using indexes, and slowing everything down. Also on the top, it has this: 42.9%st. Are you in a vm? or running vm's on the box? Its weird, you have 6.1% idle and 3.0% waiting for disk and yet you have a load of 13. Load usually means somebody is waiting for something. But you have a little cpu idle time... and you have very low disk waits... you are using very little swap. hum... odd... -Andy -- 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] High CPU load on Postgres Server during Peak times!!!!
Andy Colson wrote: Shiva Raman wrote: Dear all I am having a problem of high cpu loads in my postgres server during peak time. Following are the details of my setup (details as per the postgres wiki) . *Following is the output of TOP command during offpeak time.* top - 18:36:56 up 77 days, 20:33, 1 user, load average: 12.99, 9.22, 10.37 Tasks: 142 total, 12 running, 130 sleeping, 0 stopped, 0 zombie Cpu(s): 46.1%us, 1.9%sy, 0.0%ni, 6.1%id, 3.0%wa, 0.0%hi, 0.1%si, 42.9%st Mem: 16133676k total, 13657396k used, 2476280k free, 450908k buffers Swap: 14466492k total, 124k used, 14466368k free, 11590056k cached First off, nice report. I see you are on a pretty old version of pg. Are you vacuuming regularly? If you run a 'ps ax|grep post' do you see anything that says 'idle in transaction'? (I hope that old of version will show it. my processes show up as postgres not postmaster) The top looks like you are cpu bound. Have you tried enabling logging slow queries? (again, I hope your version supports that) It could be you have a query or two that are not using indexes, and slowing everything down. Also on the top, it has this: 42.9%st. Are you in a vm? or running vm's on the box? Its weird, you have 6.1% idle and 3.0% waiting for disk and yet you have a load of 13. Load usually means somebody is waiting for something. But you have a little cpu idle time... and you have very low disk waits... you are using very little swap. hum... odd... -Andy Looks like I missed an important point. You said this was top during off peak time. So ignore my high load ramblings. But... if this is off peak, and you only have 6% idle cpu... I'd say your cpu bound. (I'm still not sure what the 42.9%st is, so maybe I'm off base with the 6% idle too) -Andy -- 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] Hunting Unused Indexes .. is it this simple ?
On Tue, Sep 22, 2009 at 7:35 PM, Stef Telford wrote:
> Hey Everyone,
> So, I have a nice postgreSQL server (8.4) up and running our database. I
> even managed to get master->slave going without trouble using the excellent
> skytools.. however, I want to maximize speed and the hot updates where
> possible, so, I am wanting to prune unused indexes from the database.
>
> is it as simple as taking the output from ; select indexrelname from
> pg_stat_user_indexes where idx_scan = 0 and idx_tup_read = 0 and
> idx_tup_fetch = 0 ;
>
> And .. dropping ?
>
>
> The reason I ask is, well, the count on that gives me 750 indexes
> where-as the count on all user_indexes is 1100. About 2/3rds of them are
> obsolete ? I did do an ETL from mySQL -> postgreSQL but.. that's still a
> ridiculous amount of (potentially) unused indexes.
>
>
Yes, those numbers can be used reliably to identify unused indexes.
Best regards,
--
Call it Postgres
EnterpriseDB http://www.enterprisedb.com
gurjeet[.sin...@enterprisedb.com
singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
Twitter: singh_gurjeet
Skype: singh_gurjeet
Mail sent from my BlackLaptop device
Re: [PERFORM] Hunting Unused Indexes .. is it this simple ?
Stef Telford wrote: Hey Everyone, So, I have a nice postgreSQL server (8.4) up and running our database. I even managed to get master->slave going without trouble using the excellent skytools.. however, I want to maximize speed and the hot updates where possible, so, I am wanting to prune unused indexes from the database. is it as simple as taking the output from ; select indexrelname from pg_stat_user_indexes where idx_scan = 0 and idx_tup_read = 0 and idx_tup_fetch = 0 ; And .. dropping ? The reason I ask is, well, the count on that gives me 750 indexes where-as the count on all user_indexes is 1100. About 2/3rds of them are obsolete ? I did do an ETL from mySQL -> postgreSQL but.. that's still a ridiculous amount of (potentially) unused indexes. Regards Stef Did you google that? I recall seeing some posts like that on planet postgres. Yea, here it is: http://radek.cc/2009/09/05/psqlrc-tricks-indexes/ google turns up several for "postgres unused indexes". I havent read any of the others, not sure how good they are. -Andy -- 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] High CPU load on Postgres Server during Peak times!!!!
> -Mensaje original- > De: Shiva Raman > Enviado el: Martes, 22 de Septiembre de 2009 10:55 > Para: pgsql-performance@postgresql.org > Asunto: [PERFORM] High CPU load on Postgres Server during > Peak times > > Dear all > > I am having a problem of high cpu loads in my postgres > server during peak time. Some quick advice: > > clusternode2:~ # rpm -qa | grep postgres > postgresql-devel-8.1.9-1.2 > postgresql-8.1.9-1.2 > postgresql-docs-8.1.9-1.2 > postgresql-server-8.1.9-1.2 > postgresql-libs-64bit-8.1.9-1.2 > postgresql-libs-8.1.9-1.2 > postgresql-jdbc-8.1-12.2 > postgresql-contrib-8.1.9-1.2 > > 8.1 is quite old. Consider upgrading as newer versions are faster. Current Postgres version is 8.4. > > High Availability Cluster with two IBM P Series Server and > one DS4700 Storage > > IBM P series P52A with 2-core 2.1 Ghz POWER5+ Processor Card > , 36 MB L3 Cache ,16 GB of RAM, > 73.4 GB 10,000 RPM Ultra320 SCSI Drive for Operating System . > Sounds you are underpowered on cpu for 500 concurrent users. Of course this really depends on what they are doing. > > IBM SAN DS4700 Storage with Fibre Channel HDD (73.4 GB * 10) > Two Partitions - 73.4 GB * 3 RAID 5 - 134 GB storage > partitions (One holding Jakarata tomcat > application server and other holding Postgresql Database) . > Four Hard disk RAID 5 with ext3 file systems hold the pgdata on SAN . > Hard disk rotational speed is 73 GB 15K IBM 2 GB Fibre channel > A more suitable partitioning for an OLTP database would be: 2 x 73.4 GB RAID 1 for App Server + Postgresql and pg_xlog 8 x 73.4 GB RAID 10 for pgdata RAID 5 is strongly discouraged. > > Following is the output of TOP command during offpeak time. > > > top - 18:36:56 up 77 days, 20:33, 1 user, load average: > 12.99, 9.22, 10.37 > Tasks: 142 total, 12 running, 130 sleeping, 0 stopped, 0 zombie > Cpu(s): 46.1%us, 1.9%sy, 0.0%ni, 6.1%id, 3.0%wa, 0.0%hi, > 0.1%si, 42.9%st > Mem: 16133676k total, 13657396k used, 2476280k free, > 450908k buffers > Swap: 14466492k total, 124k used, 14466368k free, > 11590056k cached > > PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ > COMMAND > 22458 postgres 19 0 2473m 477m 445m R 40 3.0 0:15.49 > postmaster > 22451 postgres 15 0 2442m 447m 437m S 33 2.8 0:30.44 > postmaster > 22464 postgres 17 0 2443m 397m 383m R 28 2.5 0:13.78 > postmaster > 22484 postgres 16 0 2448m 431m 412m S 20 2.7 0:02.73 > postmaster > 22465 postgres 17 0 2440m 461m 449m R 15 2.9 0:03.52 > postmaster > 22452 postgres 16 0 2450m 727m 706m R 13 4.6 0:23.46 > postmaster > 22476 postgres 16 0 2437m 413m 405m S 13 2.6 0:06.11 > postmaster > 22485 postgres 16 0 2439m 230m 222m R7 1.5 0:05.72 > postmaster > 22481 postgres 15 0 2436m 175m 169m S7 1.1 0:04.44 > postmaster > 22435 postgres 17 0 2438m 371m 361m R6 2.4 1:17.92 > postmaster > 22440 postgres 17 0 2445m 497m 483m R5 3.2 1:44.50 > postmaster > 22486 postgres 17 0 2432m 84m 81m R4 0.5 0:00.76 > postmaster > Are you running several Postgres clusters on this hardware? Please post Top output showing cmd line arguments (press 'c') > > User Access > Total Number of Users is 500 > Maximum number of Concurrent users will be 500 during peak time > Off Peak time the maximum number of concurrent user will be > around 150 to 200. > A connection pooler like pgpool or pgbouncer would considerably reduce the burden on your system. Regards, Fernando. -- 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] session servers in ram
On Monday 21 September 2009, Scott Marlowe wrote: > I'm looking at running session servers in ram. > Does anybody any real world experience here or any words of sage > advice before I go off and start testing this? Use memcached for session data. -- "No animals were harmed in the recording of this episode. We tried but that damn monkey was just too fast." -- 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] session servers in ram
Alan Hodgson wrote: > On Monday 21 September 2009, Scott Marlowe wrote: >> I'm looking at running session servers in ram. > > Use memcached for session data. IMHO postgres is more appropriate for some types of session data. One of the apps I work on involves session data that consists of geospatial data which we store and index in postgres/postgis. Scott Marlowe wrote: > I'm looking at running session servers in ram. > We're currently IO write bound with > fsync=off using a 15k5 seagate SAS drive, so I'm hoping that moving > the db into /dev/shm will help quite a bit here. "a 15k5 seagate SAS drive" Is this implying that you have "a" == one session server? I bet that it'd be cheaper to throw a bunch of cheap boxes in there and make a pool of session servers rather than one fast one. When a new session is created, your application code can then pick the least loaded session server and put the session-server-number in a cookie. This approach works fine for me - but I suspect I have many fewer, yet probably much larger sessions going through the system. > Does anybody any real world experience here or any words of sage > advice before I go off and start testing this? > -- 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] session servers in ram
On Tue, Sep 22, 2009 at 12:01 PM, Ron Mayer wrote: > Alan Hodgson wrote: >> On Monday 21 September 2009, Scott Marlowe wrote: >>> I'm looking at running session servers in ram. >> >> Use memcached for session data. > > IMHO postgres is more appropriate for some types of session data. > > One of the apps I work on involves session data that consists of > geospatial data which we store and index in postgres/postgis. > > Scott Marlowe wrote: >> I'm looking at running session servers in ram. >> We're currently IO write bound with >> fsync=off using a 15k5 seagate SAS drive, so I'm hoping that moving >> the db into /dev/shm will help quite a bit here. > > "a 15k5 seagate SAS drive" > > Is this implying that you have "a" == one session server? I > bet that it'd be cheaper to throw a bunch of cheap boxes > in there and make a pool of session servers rather than one > fast one. When a new session is created, your application > code can then pick the least loaded session server and put > the session-server-number in a cookie. We already have two using modulus load balancing, and each is handling up to 100,000 sessons each, and an average session object of 10k to 20k. I'm just looking at how to keep from throwing more cheap boxes at it, or having to put more drives in them. We're mostly IO bound on these machines, even with 100 checkpoint segments and a 30 minute checkpoint timeout and a low completion target to reduce checkpointing even more. Even with a move to a ramdisk, I'm guessing with our increasing load we're gonna need to double our session servers eventually. As for memcached (mentioned in another post), I'm not sure if it's the right fit for this or not. We already use it to cache app data and it works well enough, so it's worth testing for this as well I guess. Thanks for all the input from everybody, I'll let you know how it works out. -- 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] Hunting Unused Indexes .. is it this simple ?
Stef, >>is it as simple as taking the output from ; select indexrelname >> from pg_stat_user_indexes where idx_scan = 0 and idx_tup_read = 0 and >> idx_tup_fetch = 0 ; >> >>And .. dropping ? Almost that simple. The caveat is that indexes which are only used for the enforcement of unique constraints (or other constraints) don't count, but you don't want to drop them because they're required for the constraints to work. Also, if you have a large index with very low (but non-zero) scans, you probably want to drop that as well. Full query for that is here: http://it.toolbox.com/blogs/database-soup/finding-useless-indexes-28796 -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
