Re: [PERFORM] PostgreSQL Configuration Tool for Dummies

Campbell, Lance wrote: > Now I am at the difficult part, what parameters to calculate and how to > calculate them. Everything below has to do with PostgreSQL version 8.2: > > > > The parameters I would think we should calculate are: > > max_connections > > shared_buffers > > work_mem > > m

Re: [PERFORM] Maintenance question / DB size anomaly...

Okay, select * from _my_cluster.sl_log_1 where ctid = '(1,1)'; select * from _my_cluster.sl_log_1 where ctid = '(1,2)'; select * from _my_cluster.sl_log_1 where ctid = '(1,3)'; select * from _my_cluster.sl_log_1 where ctid = '(1,4)'; all returns zero rows. When I do a dump of that file, I

Re: [PERFORM] Performance query about large tables, lots of concurrent access

Scott Marlowe wrote: Karl Wright wrote: Shaun Thomas wrote: On Wednesday 20 June 2007 12:55:20 pm Karl Wright wrote: I am afraid that I did answer this. My largest tables are the ones continually being updated. The smaller ones are updated only infrequently. You know, it actually so

Re: [PERFORM] Performance query about large tables, lots of concurrent access

Karl Wright wrote: Shaun Thomas wrote: On Wednesday 20 June 2007 12:55:20 pm Karl Wright wrote: I am afraid that I did answer this. My largest tables are the ones continually being updated. The smaller ones are updated only infrequently. You know, it actually sounds like you're getting

Re: [PERFORM] Hardware suggestions

[EMAIL PROTECTED] wrote: Hi list members, I have a question regarding hardware issues for a SDI (Spatial data infrastructure). It will consist of PostgreSQL with PostGIS and a UMN Mapserver/pmapper set up. At our institute we are currently establishing a small GIS working group. The data stor

Re: [PERFORM] Performance query about large tables, lots of concurrent access

On Wed, Jun 20, 2007 at 05:29:41PM -0400, Karl Wright wrote: > A nice try, but I had just completed a VACUUM on this database three > hours prior to starting the VACUUM that I gave up on after 27 hours. You keep putting it that way, but your problem is essentially that you have several tables th

Re: [GENERAL] [PERFORM] [ADMIN] Postgres VS Oracle

Andrew Kelly wrote: On Mon, 2007-06-18 at 13:02 -0400, Jonah H. Harris wrote: On 6/18/07, Andreas Kostyrka <[EMAIL PROTECTED]> wrote: As a cynic, I might ask, what Oracle is fearing? As a realist, I might ask, how many times do we have to answer this type of anti-commercial-data

Re: [PERFORM] any way to get rid of Bitmap Heap Scan recheck?

Sergei Shelukhin wrote: Hi. I have the following join condition in a query "posttag inner join tag ON posttag.tagid = tag.id and tag.name = 'blah'" tag.id is PK, I have indexes on posttag.tagid and tag.name both created with all the options set to default. PG version is 8.1. The query is very

Re: [PERFORM] Performance query about large tables, lots of concurrent access

Shaun Thomas wrote: On Wednesday 20 June 2007 12:55:20 pm Karl Wright wrote: I am afraid that I did answer this. My largest tables are the ones continually being updated. The smaller ones are updated only infrequently. You know, it actually sounds like you're getting whacked by the same

Re: [PERFORM] Maintenance question / DB size anomaly...

Kurt Overberg <[EMAIL PROTECTED]> writes: > Drat! I'm wrong again. I thought for sure there wouldn't be a > wraparound problem. Well, I'm not sure what it is now. You showed some invisible tuples with XMINs of XMIN: 1489323584 CMIN: 1 XMAX: 0 CMAX|XVAC: 0 XMIN: 1489323590 CMIN: 2 X

Re: [PERFORM] Volunteer to build a configuration tool

On Wed, 20 Jun 2007, Mark Lewis wrote: as much as some of us might want to make use of every piece of data available to make the planner into a super-brain, there are lots of other folks who just want plan stability. It's not like it has to be on for everybody. I look forward to the day whe

Re: [PERFORM] Maintenance question / DB size anomaly...

Drat! I'm wrong again. I thought for sure there wouldn't be a wraparound problem. So does this affect the entire database server, or just this table? Is best way to proceed to immediately ditch this db and promote one of my slaves to a master? I'm just concerned about the data integrity.

Re: [PERFORM] Performance query about large tables, lots of concurrent access

On Wednesday 20 June 2007 12:55:20 pm Karl Wright wrote: > I am afraid that I did answer this. My largest tables > are the ones continually being updated. The smaller > ones are updated only infrequently. You know, it actually sounds like you're getting whacked by the same problem that got us

Re: [PERFORM] Slow indexscan

"Mikko Partio" <[EMAIL PROTECTED]> writes: > I guess the sort_mem helped, or then part of the rows are in the cache > already. Should increasing sort_mem help here since there are no sorts etc? No, it wouldn't --- this change has to be due to the data being already loaded into cache. There's no o

Re: [PERFORM] Maintenance question / DB size anomaly...

Kurt Overberg <[EMAIL PROTECTED]> writes: > Okay, so I did some digging with pg_filedump, and found the following: > Block 406 > Item 1 -- Length: 472 Offset: 7720 (0x1e28) Flags: USED >XMIN: 1489323584 CMIN: 1 XMAX: 0 CMAX|XVAC

Re: [PERFORM] Performance query about large tables, lots of concurrent access

On Wed, Jun 20, 2007 at 11:14:45AM -0700, Joshua D. Drake wrote: Michael Stone wrote: Is there a sensible way to partition the large table into smaller tables? It entirely depends on your data set. Yes, that's why it was a question rather than a suggestion. :) Mike Stone --

Re: [PERFORM] Performance query about large tables, lots of concurrent access

On Wed, Jun 20, 2007 at 02:01:34PM -0400, Karl Wright wrote: > (FWIW, ANALYZE operations are kicked off after every 30,000 inserts, > updates, or deletes, by the application itself). I don't think you should do it that way. I suspect that automatic VACUUM ANALYSE way more often on each table --

Re: [PERFORM] Performance query about large tables, lots of concurrent access

Michael Stone wrote: Is there a sensible way to partition the large table into smaller tables? It entirely depends on your data set. Joshua D. Drake Mike Stone ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an

Re: [PERFORM] Maintenance question / DB size anomaly...

Dang it, Tom, don't you ever get tired of being right? I guess I had been focusing on the index numbers since they came up first, and its the index files that are > 10Gb. Okay, so I did some digging with pg_filedump, and found the following: . . . . Block 406 **

Re: [PERFORM] Performance query about large tables, lots of concurrent access

Is there a sensible way to partition the large table into smaller tables? Mike Stone ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your

Re: [PERFORM] Performance query about large tables, lots of concurrent access

Karl Wright wrote: > Alvaro Herrera wrote: > >Karl Wright wrote: > >>I am afraid that I did answer this. My largest tables are the ones > >>continually being updated. The smaller ones are updated only > >>infrequently. > > > >Can you afford to vacuum them in parallel? > > Hmm, interesting que

Re: [PERFORM] Performance query about large tables, lots of concurrent access

Alvaro Herrera wrote: Karl Wright wrote: Alvaro Herrera wrote: Karl Wright wrote: (b) the performance of individual queries had already degraded significantly in the same manner as what I'd seen before. You didn't answer whether you had smaller, more frequently updated tables that need more

Re: [PERFORM] Performance query about large tables, lots of concurrent access

Francisco Reyes wrote: Karl Wright writes: Okay - I started a VACUUM with the 8.1 database yesterday morning, having the database remain under load. As of 12:30 today (~27 hours), the original VACUUM was still running. At that point: I don't recall if you said it already, but what is your

Re: [PERFORM] Performance query about large tables, lots of concurrent access

Karl Wright wrote: > Alvaro Herrera wrote: > >Karl Wright wrote: > > > >>(b) the performance of individual queries had already degraded > >>significantly in the same manner as what I'd seen before. > > > >You didn't answer whether you had smaller, more frequently updated > >tables that need more v

Re: [PERFORM] Performance query about large tables, lots of concurrent access

Alvaro Herrera wrote: Karl Wright wrote: (b) the performance of individual queries had already degraded significantly in the same manner as what I'd seen before. You didn't answer whether you had smaller, more frequently updated tables that need more vacuuming. This comment makes me think yo

Re: [PERFORM] Slow indexscan

Mikko, I don't follow this thread, just see familiar spherical coordinates we work with a lot. If you need fast radial query you can use our q3c package available from q3c.sf.net. See some details http://www.sai.msu.su/~megera/wiki/SkyPixelization Oleg On Wed, 20 Jun 2007, Tom Lane wrote: "

Re: [PERFORM] Performance query about large tables, lots of concurrent access

Karl Wright writes: Okay - I started a VACUUM with the 8.1 database yesterday morning, having the database remain under load. As of 12:30 today (~27 hours), the original VACUUM was still running. At that point: I don't recall if you said it already, but what is your maintenance_work_mem?

Re: [PERFORM] Performance query about large tables, lots of concurrent access

Karl Wright wrote: > (b) the performance of individual queries had already degraded > significantly in the same manner as what I'd seen before. You didn't answer whether you had smaller, more frequently updated tables that need more vacuuming. This comment makes me think you do. I think what y

Re: [PERFORM] Performance query about large tables, lots of concurrent access

Karl Wright wrote: So, I guess this means that there's no way I can keep the database adequately vacuumed with my anticipated load and hardware. One thing or the other will have to change. Have you checked your maintenance_work_mem setting? If it's not large enough, vacuum will need to scan

Re: [PERFORM] Performance query about large tables, lots of concurrent access

Francisco Reyes wrote: Alvaro Herrera writes: How large is the database? I must admit I have never seen a database that took 4 days to vacuum. This could mean that your database is humongous, or that the vacuum strategy is wrong for some reason. Specially with 16GB of RAM. I have a setup w

Re: [PERFORM] Volunteer to build a configuration tool

On Wed, 2007-06-20 at 11:21 -0400, Greg Smith wrote: ... > One of the things that was surprising to me when I started looking at the > organization of the PostgreSQL buffer cache is how little gross > information about its contents is available. I kept expecting to find a > summary section wher

Re: [PERFORM] Volunteer to build a configuration tool

Campbell, Lance wrote: It is amazing how many times you can read something before it actually sinks in. There seems to be two possible approaches to optimizing PostgreSQL 8.2: Right. File caching approach: This approach is based on the fact that the OS will cache the necessary PostgreSQL fil

Re: [PERFORM] Slow indexscan

On Jun 20, 2007, at 9:02 , Mikko Partio wrote: Relevant facts: pg version 7.3.4 (yeah very old, we are upgrading asap) There have been many performance improvements—not to mention security and data-eating bug fixes—since then. Upgrading should be one of your highest priorities. And it may

Re: [PERFORM] Slow indexscan

On 6/20/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Mikko Partio" <[EMAIL PROTECTED]> writes: > Adding a new index does not speed up the query (although the planner decides > to use the index): Hm. Lots of dead rows maybe? What's your vacuuming policy? regards, tom lane

Re: [PERFORM] Volunteer to build a configuration tool

It is amazing how many times you can read something before it actually sinks in. There seems to be two possible approaches to optimizing PostgreSQL 8.2: File caching approach: This approach is based on the fact that the OS will cache the necessary PostgreSQL files. The key here is to set the siz

Re: [PERFORM] Slow indexscan

"Mikko Partio" <[EMAIL PROTECTED]> writes: > Adding a new index does not speed up the query (although the planner decides > to use the index): Hm. Lots of dead rows maybe? What's your vacuuming policy? regards, tom lane ---(end of broadcast)-

Re: [PERFORM] Slow indexscan

Mikko Partio wrote: On 6/20/07, *Tom Lane* <[EMAIL PROTECTED] > wrote: "Mikko Partio" <[EMAIL PROTECTED] > writes: > Index Scan using tbl_20070601_pkey on tbl_20070601 t1 > (cost=0.00..365.13rows=13 width=137) (actual time=

Re: [PERFORM] Slow indexscan

On 6/20/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Mikko Partio" <[EMAIL PROTECTED]> writes: > Index Scan using tbl_20070601_pkey on tbl_20070601 t1 > (cost=0.00..365.13rows=13 width=137) (actual time= > 120.83..10752.64 rows=539 loops=1) >Index Cond: ((validtime >= 2007060200::bigint) A

Re: [PERFORM] [PG 8.1.0 / AIX 5.3] Vacuum processes freezing

Hi Tom, thanks for your reply, > Have you looked into pg_locks to see if it's blocked on > someone else's lock? Yes, we looked into pg_locks and the vacuumdb process wasn't blocked. The table showed four locks for vacuum, all with grant=true. In fact, we found that a similar bug has been

Re: [PERFORM] Maintenance question / DB size anomaly...

Kurt Overberg <[EMAIL PROTECTED]> writes: > Okay, so the sl_log_1 TABLE looks okay. Its the indexes that seem to > be messed up, specifically sl_log_1_idx1 seems to think that there's >>> 300,000 rows in the table its associated with. I just want to fix > the index, really. I'm not sure how

Re: [PERFORM] Volunteer to build a configuration tool

On Wed, 20 Jun 2007, PFC wrote: Except planner hints (argh) I see no way to give this information to the machine... since it's mostly in the mind of the DBA. And the mind of the DBA has a funny way of being completely wrong some days about what's really happening under the hood. Maybe a per

Re: [PERFORM] Slow indexscan

"Mikko Partio" <[EMAIL PROTECTED]> writes: > Index Scan using tbl_20070601_pkey on tbl_20070601 t1 > (cost=0.00..365.13rows=13 width=137) (actual time= > 120.83..10752.64 rows=539 loops=1) >Index Cond: ((validtime >= 2007060200::bigint) AND (validtime <= > 20070602235500::bigint) AND (lat

Re: [PERFORM] Replication

On Mon, Jun 18, 2007 at 08:54:46PM +0200, Markus Schiltknecht wrote: > Postgres-R has been the name of the research project by Bettina Kemme et > al. Slony-II was the name Neil and Gavin gave their attempt to continue > that project. This isn't quite true. Slony-II was originally conceived by J

Re: [PERFORM] PostgreSQL Configuration Tool for Dummies

At 4:35p -0400 on 19 Jun 2007, Lance Campbell wrote: The parameters I would think we should calculate are: max_connections shared_buffers work_mem maintenance_work_mem effective_cache_size random_page_cost From an educational/newb standpoint, I notice that the page currently spews out a confi

[PERFORM] Slow indexscan

Hello group I have a problem with a simple index scan using the primary key of a table taking too long. Relevant facts: pg version 7.3.4 (yeah very old, we are upgrading asap) postgresql.conf: shared_buffers = 25000 random_page_cost = 2 effective_cache_size = 20 sort_mem = 2 Table: db=

Re: [PERFORM] Maintenance question / DB size anomaly...

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Kurt Overberg wrote: > OOookay. Since the discussion has wandered a bit I just > wanted to restate things in an effort to clear the problem in my head. > > Okay, so the sl_log_1 TABLE looks okay. Its the indexes that seem to be > mess

Re: [PERFORM] Maintenance question / DB size anomaly...

In response to Kurt Overberg <[EMAIL PROTECTED]>: > OOookay. Since the discussion has wandered a bit I just > wanted to restate things in an effort to clear the problem in my head. > > Okay, so the sl_log_1 TABLE looks okay. Its the indexes that seem to > be messed up, specific

Re: [PERFORM] Maintenance question / DB size anomaly...

OOookay. Since the discussion has wandered a bit I just wanted to restate things in an effort to clear the problem in my head. Okay, so the sl_log_1 TABLE looks okay. Its the indexes that seem to be messed up, specifically sl_log_1_idx1 seems to think that there's > 300,000 row

Re: [PERFORM] cached entities

In response to "Sergey Konoplev" <[EMAIL PROTECTED]>: > Hi > > I'd like to know how to get information about which PG entities are in > kernel cache, if possible. That's going to be specific to the OS you're running. Unless you're talking about PG's shared_buffers -- if that's the case, have a

[PERFORM] cached entities

Hi I'd like to know how to get information about which PG entities are in kernel cache, if possible. -- Regards, Sergey Konoplev ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.

Re: [PERFORM] Short row header

"PFC" <[EMAIL PROTECTED]> writes: > I have this "poll results" table with just 3 integer fields, which is > never updated, only inserted/deleted... > Did the Devs consider an option to have VACUUM reduce the row header > sizes for tuples that are long commited and are currently visibl

Re: [PERFORM] Short row header

PFC wrote: I have this "poll results" table with just 3 integer fields, which is never updated, only inserted/deleted... Did the Devs consider an option to have VACUUM reduce the row header sizes for tuples that are long commited and are currently visible to all transactions ? That

[PERFORM] Short row header

I have this "poll results" table with just 3 integer fields, which is never updated, only inserted/deleted... Did the Devs consider an option to have VACUUM reduce the row header sizes for tuples that are long commited and are currently visible to all transactions ? (even if this makes th

Re: [PERFORM] Volunteer to build a configuration tool

To me, the worst catch-22 we face in this area is that we'd like the optimizer's choices of plan to be stable and understandable, but the real-world costs of queries depend enormously on short-term conditions such as how much of the table has been sucked into RAM recently by other queries. I ha

Re: [PERFORM] Volunteer to build a configuration tool

Greg Smith <[EMAIL PROTECTED]> writes: > On Wed, 20 Jun 2007, Tom Lane wrote: >> I think what would be much more useful in the long run is some serious >> study of the parameters themselves. For instance, random_page_cost is a >> self-admitted oversimplification of reality. > If I could figure