FW: [PERFORM] can't handle large number of INSERT/UPDATEs
>>Eliminate that contention point, and you will have solved your problem. I agree, If your updates are slow then you will get a queue building up. Make sure that:- 1) all your indexing is optimised. 2) you are doing regular vacuuming (bloated tables will cause a slow down due to swapping). 3) your max_fsm_pages setting is large enough - it needs to be big enough to hold all the transactions between vacuums (+ some spare for good measure). 4) do a full vacuum - do one to start and then do one after you have had 2&3 (above) in place for a while - if the full vacuum handles lots of dead tuples then your max_fsm_pages setting is too low. 5) Also try reindexing or drop/recreate the indexes in question as... "PostgreSQL is unable to reuse B-tree index pages in certain cases. The problem is that if indexed rows are deleted, those index pages can only be reused by rows with similar values. For example, if indexed rows are deleted and newly inserted/updated rows have much higher values, the new rows can't use the index space made available by the deleted rows. Instead, such new rows must be placed on new index pages. In such cases, disk space used by the index will grow indefinitely, even if VACUUM is run frequently. " Are your updates directly executed or do you use stored procs? We had a recent problem with stored procs as they store a "one size fits all" query plan when compiled - this can be less than optimum in some cases. We have a similar sounding app to yours and if tackled correctly then all the above will make a massive difference in performance. Rod -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Rod Taylor Sent: 25 October 2004 22:19 To: Anjan Dave Cc: Postgresql Performance Subject: Re: [PERFORM] can't handle large number of INSERT/UPDATEs On Mon, 2004-10-25 at 16:53, Anjan Dave wrote: > Hi, > > > > I am dealing with an app here that uses pg to handle a few thousand > concurrent web users. It seems that under heavy load, the INSERT and > UPDATE statements to one or two specific tables keep queuing up, to > the count of 150+ (one table has about 432K rows, other has about > 2.6Million rows), resulting in ?wait?s for other queries, and then This isn't an index issue, it's a locking issue. Sounds like you have a bunch of inserts and updates hitting the same rows over and over again. Eliminate that contention point, and you will have solved your problem. Free free to describe the processes involved, and we can help you do that. ---(end of broadcast)--- TIP 3: 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PERFORM] Measuring server performance with psql and pgAdmin
Hi all, I am (stilll) converting a database from a Clarion Topspeed database to Postgresql 7.4.5 on Debian Linux 2.6.6-1. The program that uses the database uses a query like "select * from table" to show the user the contents of a table. This query cannot be changed (it is generated by Clarion and the person in charge of the program cannot alter that behaviour). Now I have a big performance problem with reading a large table ( 96713 rows). The query that is send to the database is "select * from table". "explain" and "explain analyze", using psql on cygwin: munt=# explain select * from klt_alg; QUERY PLAN - Seq Scan on klt_alg (cost=0.00..10675.13 rows=96713 width=729) munt=# explain analyze select * from klt_alg; QUERY PLAN --- Seq Scan on klt_alg (cost=0.00..10675.13 rows=96713 width=729) (actual time=13.172..2553.328 rows=96713 loops=1) Total runtime: 2889.109 ms (2 rows) Running the query (with pgAdmin III): -- Executing query: select * from klt_alg; Total query runtime: 21926 ms. Data retrieval runtime: 72841 ms. 96713 rows retrieved. QUESTIONS: GENERAL: 1. The manual says about "explain analyze" : "The ANALYZE option causes the statement to be actually executed, not only planned. The total elapsed time expended within each plan node (in milliseconds) and total number of rows it actually returned are added to the display." Does this time include datatransfer or just the time the database needs to collect the data, without any data transfer? 2. If the time is without data transfer to the client, is there a reliable way to measure the time needed to run the query and get the data (without the overhead of a program that does something with the data)? PGADMIN: 1. What does the "Total query runtime" really mean? (It was my understanding that it was the time the database needs to collect the data, without any data transfer). 2. What does the "Data retrieval runtime" really mean? (Is this including the filling of the datagrid/GUI, or just the datatransfer?) TIA Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Large Database Performance suggestions
Thanks for all of your help so far. Here is some of the information you guys were asking for: Test System: 2x AMD Opteron 244 (1.8Ghz) 8GB RAM 7x 72GB SCSI HDD (Raid 5) postrgesql.conf information: #--- # RESOURCE USAGE (except WAL) #--- # - Memory - shared_buffers = 1000 # min 16, at least max_connections*2, 8KB each #sort_mem = 1024# min 64, size in KB #vacuum_mem = 8192 # min 1024, size in KB sort_mem = 4096000 vacuum_mem = 1024000 # - Free Space Map - #max_fsm_pages = 2 # min max_fsm_relations*16, 6 bytes each #max_fsm_relations = 1000 # min 100, ~50 bytes each # - Kernel Resource Usage - #max_files_per_process = 1000 # min 25 #preload_libraries = '' #--- # WRITE AHEAD LOG #--- # - Settings - #fsync = true # turns forced synchronization on or off #wal_sync_method = fsync# the default varies across platforms: # fsync, fdatasync, open_sync, or open_datasync #wal_buffers = 8# min 4, 8KB each # - Checkpoints - #checkpoint_segments = 3# in logfile segments, min 1, 16MB each #checkpoint_timeout = 300 # range 30-3600, in seconds #checkpoint_warning = 30# 0 is off, in seconds #commit_delay = 0 # range 0-10, in microseconds #commit_siblings = 5# range 1-1000 Everything else are at their defaults. I actually think the WAL options are set to defaults as well, but I don't recall exactly :) As for the queries and table, The data we store is confidential, but it is essentially an account number with a bunch of boolean fields that specify if a person applies to criteria. So a query may look something like: SELECT acctno FROM view_of_data WHERE has_name AND is_active_member AND state = 'OH'; which is explained as something like this: QUERY PLAN - Seq Scan on view_of_data (cost=0.00..25304.26 rows=22054 width=11) Filter: (has_name AND is_active_member AND ((state)::text = 'OH'::text)) (2 rows) Occasionally, because we store data from several sources, we will have requests for data from several sources. We simply intersect the view_of_data table with a sources table that lists what acctno belong to what source. This query would look something like this: SELECT acctno FROM view_of_data WHERE has_name AND is_active_member AND state = 'OH' INTERSECT SELECT acctno FROM sources_data WHERE source = 175; which is explained as follows: QUERY PLAN --- SetOp Intersect (cost=882226.14..885698.20 rows=69441 width=11) -> Sort (cost=882226.14..883962.17 rows=694411 width=11) Sort Key: acctno -> Append (cost=0.00..814849.42 rows=694411 width=11) -> Subquery Scan "*SELECT* 1" (cost=0.00..25524.80 rows=22054 width=11) -> Seq Scan on view_of_data (cost=0.00..25304.26 rows=22054 width=11) Filter: (has_name AND is_active_member AND ((state)::text = 'OH'::text)) -> Subquery Scan "*SELECT* 2" (cost=0.00..789324.62 rows=672357 width=11) -> Seq Scan on sources_data (cost=0.00..782601.05 rows=672357 width=11) Filter: (source = 23) Again, we see our biggest bottlenecks when we get over about 50 million records. The time to execute grows exponentially from that point. Thanks again for all of your help! -Josh On Fri, 22 Oct 2004 07:38:49 -0400, Dave Cramer <[EMAIL PROTECTED]> wrote: > Josh, > > Your hardware setup would be useful too. It's surprising how slow some > big name servers really are. > If you are seriously considering memory sizes over 4G you may want to > look at an opteron. > > Dave > > > > Joshua Marsh wrote: > > >Hello everyone, > > > >I am currently working on a data project that uses PostgreSQL > >extensively to store, manage and maintain the data. We haven't had > >any problems regarding database size until recently. The three major > >tables we use never get bigger than 10 million records. With this > >size, we can do things like storing the indexes or even the tables in > >memory to allow faster access. > > > >Recently, we have found customers who are wanting to use our service > >with data files between 100 million and 300 million records. At that > >size, each of the three major tables will hold between 150 million and > >700 million records. At this size, I can't expect it to run queries > >in 10-15 seconds (what we can do with 10 million re
Re: [PERFORM] Large Database Performance suggestions
Joshua Marsh <[EMAIL PROTECTED]> writes: > shared_buffers = 1000 # min 16, at least max_connections*2, 8KB each This is on the small side for an 8G machine. I'd try 1 or so. > sort_mem = 4096000 Yikes. You do realize you just said that *each sort operation* can use 4G? (Actually, it's probably overflowing internally; I dunno what amount of sort space you are really ending up with but it could be small.) Try something saner, maybe in the 10 to 100MB range. > vacuum_mem = 1024000 This is probably excessive as well. > #max_fsm_pages = 2 # min max_fsm_relations*16, 6 bytes each > #max_fsm_relations = 1000 # min 100, ~50 bytes each You will need to bump these up a good deal to avoid database bloat. > Occasionally, because we store data from several sources, we will have > requests for data from several sources. We simply intersect the > view_of_data table with a sources table that lists what acctno belong > to what source. This query would look something like this: > SELECT acctno FROM view_of_data WHERE has_name AND is_active_member > AND state = 'OH' INTERSECT SELECT acctno FROM sources_data WHERE > source = 175; IMHO you need to rethink your table layout. There is simply no way that that query is going to be fast. Adding a source column to view_of_data would work much better. If you're not in a position to redo the tables, you might try it as a join: SELECT acctno FROM view_of_data JOIN sources_data USING (acctno) WHERE has_name AND is_active_member AND state = 'OH' AND source = 175; but I'm not really sure if that will be better or not. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] can't handle large number of INSERT/UPDATEs
It probably is locking issue. I got a long list of locks held when we ran select * from pg_locks during a peak time. relation | database | transaction | pid | mode | granted --+--+-+---+--+- 17239 |17142 | | 3856 | AccessShareLock | t | |21196323 | 3875 | ExclusiveLock| t 16390 |17142 | | 3911 | AccessShareLock | t 16595 |17142 | | 3782 | AccessShareLock | t 17227 |17142 | | 3840 | AccessShareLock | t 17227 |17142 | | 3840 | RowExclusiveLock | t ... ... Vmstat would show a lot of disk IO at the same time. Is this pointing towards a disk IO issue? (to that end, other than a higher CPU speed, and disabling HT, only thing changed is that it's RAID5 volume now, instead of a RAID10) -anjan -Original Message- From: Rod Taylor [mailto:[EMAIL PROTECTED] Sent: Monday, October 25, 2004 5:19 PM To: Anjan Dave Cc: Postgresql Performance Subject: Re: [PERFORM] can't handle large number of INSERT/UPDATEs On Mon, 2004-10-25 at 16:53, Anjan Dave wrote: > Hi, > > > > I am dealing with an app here that uses pg to handle a few thousand > concurrent web users. It seems that under heavy load, the INSERT and > UPDATE statements to one or two specific tables keep queuing up, to > the count of 150+ (one table has about 432K rows, other has about > 2.6Million rows), resulting in ʽwaitʼs for other queries, and then This isn't an index issue, it's a locking issue. Sounds like you have a bunch of inserts and updates hitting the same rows over and over again. Eliminate that contention point, and you will have solved your problem. Free free to describe the processes involved, and we can help you do that. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] can't handle large number of INSERT/UPDATEs
On Tue, 2004-10-26 at 13:42, Anjan Dave wrote: > It probably is locking issue. I got a long list of locks held when we ran select * > from pg_locks during a peak time. > > relation | database | transaction | pid | mode | granted > --+--+-+---+--+- > 17239 |17142 | | 3856 | AccessShareLock | t How many have granted = false? > Vmstat would show a lot of disk IO at the same time. > > Is this pointing towards a disk IO issue? Not necessarily. Is your IO reaching the limit or is it just heavy? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] can't handle large number of INSERT/UPDATEs
None of the locks are in state false actually. I don't have iostat on that machine, but vmstat shows a lot of writes to the drives, and the runnable processes are more than 1: procs memory swap io system cpu r b swpd free buff cache si sobibo incs us sy wa id 1 2 0 3857568 292936 279187600 0 44460 1264 2997 23 13 22 41 2 2 0 3824668 292936 279188400 0 25262 1113 4797 28 12 29 31 2 3 0 3784772 292936 279189600 0 38988 1468 6677 28 12 48 12 2 4 0 3736256 292936 279190400 0 50970 1530 5217 19 12 49 20 4 2 0 3698056 292936 279190800 0 43576 1369 7316 20 15 35 30 2 1 0 3667124 292936 279192000 0 39174 1444 4659 25 16 35 24 6 1 0 3617652 292936 279192800 0 52430 1347 4681 25 19 20 37 1 3 0 352 292936 279086800 0 40156 1439 4394 20 14 29 37 6 0 0 3797488 292936 256864800 0 17706 2272 21534 28 23 19 30 0 0 0 3785396 292936 256873600 0 1156 1237 14057 33 8 0 59 0 0 0 3783568 292936 256873600 0 704 512 1537 5 2 1 92 1 0 0 3783188 292936 256875200 0 842 613 1919 6 1 1 92 -anjan -Original Message- From: Rod Taylor [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 26, 2004 1:49 PM To: Anjan Dave Cc: Postgresql Performance Subject: RE: [PERFORM] can't handle large number of INSERT/UPDATEs On Tue, 2004-10-26 at 13:42, Anjan Dave wrote: > It probably is locking issue. I got a long list of locks held when we ran select * from pg_locks during a peak time. > > relation | database | transaction | pid | mode | granted > --+--+-+---+--+- > 17239 |17142 | | 3856 | AccessShareLock | t How many have granted = false? > Vmstat would show a lot of disk IO at the same time. > > Is this pointing towards a disk IO issue? Not necessarily. Is your IO reaching the limit or is it just heavy? ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] can't handle large number of INSERT/UPDATEs
Anjan, > It probably is locking issue. I got a long list of locks held when we ran > select * from pg_locks during a peak time. Do the back-loaded tables have FKs on them? This would be a likely cause of lock contention, and thus serializing inserts/updates to the tables. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] can't handle large number of INSERT/UPDATEs
I don't have iostat on that machine, but vmstat shows a lot of writes to the drives, and the runnable processes are more than 1: 6 1 0 3617652 292936 279192800 0 52430 1347 4681 25 19 20 37 Assuming that's the output of 'vmstat 1' and not some other delay, 50MB/second of sustained writes is usually considered 'a lot'. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Measuring server performance with psql and pgAdmin
Joost, > 1. The manual says about "explain analyze" : "The ANALYZE option causes the > statement to be actually executed, not only planned. The total elapsed time > expended within each plan node (in milliseconds) and total number of rows > it actually returned are added to the display." Does this time include > datatransfer or just the time the database needs to collect the data, > without any data transfer? Correct. It's strictly backend time. > 2. If the time is without data transfer to the > client, is there a reliable way to measure the time needed to run the query > and get the data (without the overhead of a program that does something > with the data)? in PSQL, you can use \timing -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Sequential Scan with LIMIT
--- John Meinel <[EMAIL PROTECTED]> escribió: > Curt Sampson wrote: > > On Sun, 24 Oct 2004, John Meinel wrote: > > > > > >>I was looking into another problem, and I found > something that surprised > >>me. If I'm doing "SELECT * FROM mytable WHERE col > = 'myval' LIMIT 1.". > >>Now "col" is indexed... > >>The real purpose of this query is to check to see > if a value exists in > >>the column,... > > > > > > When you select all the columns, you're going to > force it to go to the > > table. If you select only the indexed column, it > ought to be able to use > > just the index, and never read the table at all. > You could also use more > > standard and more set-oriented SQL while you're at > it: > > > > SELECT DISTINCT(col) FROM mytable WHERE col = > 'myval' > > > > cjs > > Well, what you wrote was actually much slower, as it > had to scan the > whole table, grab all the rows, and then distinct > them in the end. > > However, this query worked: > > > SELECT DISTINCT(col) FROM mytable WHERE col = > 'myval' LIMIT 1; > > > Now, *why* that works differently from: > > SELECT col FROM mytable WHERE col = 'myval' LIMIT 1; > or > SELECT DISTINCT(col) FROM mytable WHERE col = > 'myval'; > > I'm not sure. They all return the same information. of course, both queries will return the same but that's just because you forced it. LIMIT and DISTINCT are different things so they behave and are plenned different. > > What's also weird is stuff like: > SELECT DISTINCT(NULL) FROM mytable WHERE col = > 'myval' LIMIT 1; why do you want to do such a thing? regards, Jaime Casanova _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] can't handle large number of INSERT/UPDATEs
On Mon, 2004-10-25 at 16:53 -0400, Anjan Dave wrote: > Hi, > > > > I am dealing with an app here that uses pg to handle a few thousand > concurrent web users. It seems that under heavy load, the INSERT and > UPDATE statements to one or two specific tables keep queuing up, to > the count of 150+ (one table has about 432K rows, other has about > 2.6Million rows), resulting in âwaitâs for other queries, and then > everything piles up, with the load average shooting up to 10+. Hi, We saw a similar problem here that was related to the locking that can happen against referred tables for referential integrity. In our case we had referred tables with very few rows (i.e. < 10) which caused the insert and update on the large tables to be effectively serialised due to the high contention on the referred tables. We changed our app to implement those referential integrity checks differently and performance was hugely boosted. Regards, Andrew. - Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/PHYS: Level 2, 150-154 Willis St DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267 Chicken Little was right. - signature.asc Description: This is a digitally signed message part
Re: [PERFORM] can't handle large number of INSERT/UPDATEs
Andrew/Josh, Josh also suggested to check for any FK/referential integrity checks, but I am told that we don't have any foreign key constraints. Thanks, anjan -Original Message- From: Andrew McMillan [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 26, 2004 4:51 PM To: Anjan Dave Cc: [EMAIL PROTECTED] Subject: Re: [PERFORM] can't handle large number of INSERT/UPDATEs On Mon, 2004-10-25 at 16:53 -0400, Anjan Dave wrote: > Hi, > > > > I am dealing with an app here that uses pg to handle a few thousand > concurrent web users. It seems that under heavy load, the INSERT and > UPDATE statements to one or two specific tables keep queuing up, to > the count of 150+ (one table has about 432K rows, other has about > 2.6Million rows), resulting in 'wait's for other queries, and then > everything piles up, with the load average shooting up to 10+. Hi, We saw a similar problem here that was related to the locking that can happen against referred tables for referential integrity. In our case we had referred tables with very few rows (i.e. < 10) which caused the insert and update on the large tables to be effectively serialised due to the high contention on the referred tables. We changed our app to implement those referential integrity checks differently and performance was hugely boosted. Regards, Andrew. - Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/PHYS: Level 2, 150-154 Willis St DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267 Chicken Little was right. - ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] can't handle large number of INSERT/UPDATEs
That is 1 or maybe 2 second interval. One thing I am not sure is why 'bi' (disk writes) stays at 0 mostly, it's the 'bo' column that shows high numbers (reads from disk). With so many INSERT/UPDATEs, I would expect it the other way around... -anjan -Original Message- From: Matt Clark [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 26, 2004 2:29 PM To: Anjan Dave Cc: Rod Taylor; Postgresql Performance Subject: Re: [PERFORM] can't handle large number of INSERT/UPDATEs >I don't have iostat on that machine, but vmstat shows a lot of writes to >the drives, and the runnable processes are more than 1: > > 6 1 0 3617652 292936 279192800 0 52430 1347 4681 25 >19 20 37 > > Assuming that's the output of 'vmstat 1' and not some other delay, 50MB/second of sustained writes is usually considered 'a lot'. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] can't handle large number of INSERT/UPDATEs
"Anjan Dave" <[EMAIL PROTECTED]> writes: > None of the locks are in state false actually. In that case you don't have a locking problem. > I don't have iostat on that machine, but vmstat shows a lot of writes to > the drives, and the runnable processes are more than 1: I get the impression that you are just saturating the write bandwidth of your disk :-( It's fairly likely that this happens during checkpoints. Look to see if the postmaster has a child that shows itself as a checkpointer in "ps" when the saturation is occurring. You might be able to improve matters by altering the checkpoint frequency parameters (though beware that either too small or too large will likely make matters even worse). regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] can't handle large number of INSERT/UPDATEs
It just seems that the more activity there is (that is when there's a lot of disk activity) the checkpoints happen quicker too. Here's a snapshot from the /var/log/messages - Oct 26 17:21:22 vl-pe6650-003 postgres[13978]: [2-1] LOG: recycled transaction log file "000B007E" Oct 26 17:21:22 vl-pe6650-003 postgres[13978]: [3-1] LOG: recycled transaction log file "000B007F" ... Oct 26 17:26:25 vl-pe6650-003 postgres[14273]: [2-1] LOG: recycled transaction log file "000B0080" Oct 26 17:26:25 vl-pe6650-003 postgres[14273]: [3-1] LOG: recycled transaction log file "000B0081" Oct 26 17:26:25 vl-pe6650-003 postgres[14273]: [4-1] LOG: recycled transaction log file "000B0082" ... Oct 26 17:31:27 vl-pe6650-003 postgres[14508]: [2-1] LOG: recycled transaction log file "000B0083" Oct 26 17:31:27 vl-pe6650-003 postgres[14508]: [3-1] LOG: recycled transaction log file "000B0084" Oct 26 17:31:27 vl-pe6650-003 postgres[14508]: [4-1] LOG: recycled transaction log file "000B0085" ... I have increased them from default 3 to 15. Haven't altered the frequency though Thanks, Anjan -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 26, 2004 5:53 PM To: Anjan Dave Cc: Rod Taylor; Postgresql Performance Subject: Re: [PERFORM] can't handle large number of INSERT/UPDATEs "Anjan Dave" <[EMAIL PROTECTED]> writes: > None of the locks are in state false actually. In that case you don't have a locking problem. > I don't have iostat on that machine, but vmstat shows a lot of writes to > the drives, and the runnable processes are more than 1: I get the impression that you are just saturating the write bandwidth of your disk :-( It's fairly likely that this happens during checkpoints. Look to see if the postmaster has a child that shows itself as a checkpointer in "ps" when the saturation is occurring. You might be able to improve matters by altering the checkpoint frequency parameters (though beware that either too small or too large will likely make matters even worse). regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Sequential Scan with LIMIT
Jaime Casanova wrote: [...] I'm not sure. They all return the same information. of course, both queries will return the same but that's just because you forced it. LIMIT and DISTINCT are different things so they behave and are plenned different. What's also weird is stuff like: SELECT DISTINCT(NULL) FROM mytable WHERE col = 'myval' LIMIT 1; why do you want to do such a thing? regards, Jaime Casanova I was trying to see if selecting a constant would change things. I could have done SELECT DISTINCT(1) or just SELECT 1 FROM ... The idea of the query is that if 'myval' exists in the table, return something different than if 'myval' does not exist. If you are writing a function, you can use: SELECT something... IF FOUND THEN do a ELSE do b END IF; The whole point of this exercise was just to find what the cheapest query is when you want to test for the existence of a value in a column. The only thing I've found for my column is: SET enable_seq_scan TO off; SELECT col FROM mytable WHERE col = 'myval' LIMIT 1; SET enable_seq_scan TO on; My column is not distributed well (larger numbers occur later in the dataset, but may occur many times.) In total there are something like 500,000 rows, the number 555647 occurs 100,000 times, but not until row 300,000 or so. The analyzer looks at the data and says "1/5th of the time it is 555647, so I can just do a sequential scan as the odds are I don't have to look for very long, then I don't have to load the index". It turns out this is very bad, where with an index you just have to do 2 page loads, instead of reading 300,000 rows. Obviously this isn't a general-case solution. But if you have a situation similar to mine, it might be useful. (That's one thing with DB tuning. It seems to be very situation dependent, and it's hard to plan without a real dataset.) John =:-> signature.asc Description: OpenPGP digital signature
Re: [PERFORM] can't handle large number of INSERT/UPDATEs
"Anjan Dave" <[EMAIL PROTECTED]> writes: > One thing I am not sure is why 'bi' (disk writes) stays at 0 mostly, > it's the 'bo' column that shows high numbers (reads from disk). With so > many INSERT/UPDATEs, I would expect it the other way around... Er ... it *is* the other way around. bi is blocks in (to the CPU), bo is blocks out (from the CPU). regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] can't handle large number of INSERT/UPDATEs
Ok, i was thinking from the disk perspective. Thanks! -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Tue 10/26/2004 6:37 PM To: Anjan Dave Cc: Matt Clark; Rod Taylor; Postgresql Performance Subject: Re: [PERFORM] can't handle large number of INSERT/UPDATEs "Anjan Dave" <[EMAIL PROTECTED]> writes: > One thing I am not sure is why 'bi' (disk writes) stays at 0 mostly, > it's the 'bo' column that shows high numbers (reads from disk). With so > many INSERT/UPDATEs, I would expect it the other way around... Er ... it *is* the other way around. bi is blocks in (to the CPU), bo is blocks out (from the CPU). regards, tom lane ---(end of broadcast)--- TIP 3: 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] can't handle large number of INSERT/UPDATEs
On Tue, 26 Oct 2004, Tom Lane wrote: > "Anjan Dave" <[EMAIL PROTECTED]> writes: > > One thing I am not sure is why 'bi' (disk writes) stays at 0 mostly, > > it's the 'bo' column that shows high numbers (reads from disk). With so > > many INSERT/UPDATEs, I would expect it the other way around... > > Er ... it *is* the other way around. bi is blocks in (to the CPU), > bo is blocks out (from the CPU). > > regards, tom lane Ummm. [EMAIL PROTECTED] T2]$ man vmstat FIELD DESCRIPTIONS IO bi: Blocks sent to a block device (blocks/s). bo: Blocks received from a block device (blocks/s). And on my read-heavy 7.4.2 system (running on rh8 at the moment) (truncated for readability...) [EMAIL PROTECTED] T2]# vmstat 1 procs memoryswap io system r b w swpd free buff cache si sobibo incs us 0 0 0 127592 56832 365496 2013788 0 1 3 64 0 4 2 0 0 127592 56868 365496 2013788 0 0 0 0 363 611 1 1 0 0 127592 57444 365508 2013788 0 0 8 972 1556 3616 11 0 0 1 127592 57408 365512 2013800 0 0 0 448 614 1216 5 0 0 0 127592 56660 365512 2013800 0 0 0 0 666 1150 6 0 3 1 127592 56680 365512 2013816 0 016 180 1280 2050 2 0 0 0 127592 56864 365516 2013852 0 020 728 2111 4360 11 0 0 0 127592 57952 365544 2013824 0 0 0 552 1153 2002 10 0 0 0 127592 57276 365544 2013824 0 0 0 504 718 5 1 0 0 127592 57244 365544 2013824 0 0 0 436 1495 2366 7 0 0 0 127592 57252 365544 2013824 0 0 0 0 618 1380 5 0 0 0 127592 57276 365556 2014192 0 0 360 1240 2418 5056 14 2 0 0 127592 56664 365564 2014176 0 0 0 156 658 1349 5 1 0 0 127592 55864 365568 2014184 0 0 0 1572 1388 3598 9 2 0 0 127592 56160 365572 2014184 0 0 0 536 4860 6621 13 Which seems appropriate for both the database and the man page -Curtis ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] [PATCHES] [HACKERS] ARC Memory Usage analysis
Simon, As a postgres DBA, I find your comments about how not to use effective_cache_size instructive, but I'm still not sure how I should arrive at a target value for it. On most of the machines on which I admin postgres, I generally set shared_buffers to 10,000 (using what seems to have been the recent conventional wisdom of the lesser of 10,000 or 10% of RAM). I haven't really settled on an optimal value for effective_cache_size, and now I'm again confused as to how I might even benchmark it. Here are the documents on which I've based my knowledge: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html#effcache http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html http://www.ca.postgresql.org/docs/momjian/hw_performance/node8.html From Bruce's document, I gather that effective_cache_size would assume that either shared buffers or unused RAM were valid sources of cached pages for the purposes of assessing plans. As a result, I was intending to inflate the value of effective_cache_size to closer to the amount of unused RAM on some of the machines I admin (once I've verified that they all have a unified buffer cache). Is that correct? -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Oct 26, 2004, at 3:49 AM, Simon Riggs wrote: On Mon, 2004-10-25 at 16:34, Jan Wieck wrote: The problem is, with a too small directory ARC cannot guesstimate what might be in the kernel buffers. Nor can it guesstimate what recently was in the kernel buffers and got pushed out from there. That results in a way too small B1 list, and therefore we don't get B1 hits when in fact the data was found in memory. B1 hits is what increases the T1target, and since we are missing them with a too small directory size, our implementation of ARC is propably using a T2 size larger than the working set. That is not optimal. I think I have seen that the T1 list shrinks "too much", but need more tests...with some good test results The effectiveness of ARC relies upon the balance between the often conflicting requirements of "recency" and "frequency". It seems possible, even likely, that pgsql's version of ARC may need some subtle changes to rebalance it - if we are unlikely enough to find cases where it genuinely is out of balance. Many performance tests are required, together with a few ideas on extra parameters to includehence my support of Jan's ideas. That's also why I called the B1+B2 hit ratio "turbulence" because it relates to how much oscillation is happening between T1 and T2. In physical systems, we expect the oscillations to be damped, but there is no guarantee that we have a nearly critically damped oscillator. (Note that the absence of turbulence doesn't imply that T1+T2 is optimally sized, just that is balanced). [...and all though the discussion has wandered away from my original patch...would anybody like to commit, or decline the patch?] If we would replace the dynamic T1 buffers with a max_backends*2 area of shared buffers, use a C value representing the effective cache size and limit the T1target on the lower bound to effective cache size - shared buffers, then we basically moved the T1 cache into the OS buffers. Limiting the minimum size of T1len to be 2* maxbackends sounds like an easy way to prevent overbalancing of T2, but I would like to follow up on ways to have T1 naturally stay larger. I'll do a patch with this idea in, for testing. I'll call this "T1 minimum size" so we can discuss it. Any other patches are welcome... It could be that B1 is too small and so we could use a larger value of C to keep track of more blocks. I think what is being suggested is two GUCs: shared_buffers (as is), plus another one, larger, which would allow us to track what is in shared_buffers and what is in OS cache. I have comments on "effective cache size" below On Mon, 2004-10-25 at 17:03, Tom Lane wrote: Jan Wieck <[EMAIL PROTECTED]> writes: This all only holds water, if the OS is allowed to swap out shared memory. And that was my initial question, how likely is it to find this to be true these days? I think it's more likely that not that the OS will consider shared memory to be potentially swappable. On some platforms there is a shmctl call you can make to lock your shmem in memory, but (a) we don't use it and (b) it may well require privileges we haven't got anyway. Are you saying we shouldn't, or we don't yet? I simply assumed that we did use that function - surely it must be at least an option? RHEL supports this at least It may well be that we don't have those privileges, in which case we turn off the option. Often, we (or I?) will want to install a dedicated server, so we should have all the permissions we need, in which case... This has always been one of the arguments against making shared_buffers really large, of course --- if the buffers aren't all
Re: [PERFORM] [PATCHES] [HACKERS] ARC Memory Usage analysis
Thomas, > As a result, I was intending to inflate the value of > effective_cache_size to closer to the amount of unused RAM on some of > the machines I admin (once I've verified that they all have a unified > buffer cache). Is that correct? Currently, yes. Right now, e_c_s is used just to inform the planner and make index vs. table scan and join order decisions. The problem which Simon is bringing up is part of a discussion about doing *more* with the information supplied by e_c_s.He points out that it's not really related to the *real* probability of any particular table being cached. At least, if I'm reading him right. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] can't handle large number of INSERT/UPDATEs
Anjan, > Oct 26 17:26:25 vl-pe6650-003 postgres[14273]: [4-1] LOG: recycled > transaction > log file "000B0082" > ... > Oct 26 17:31:27 vl-pe6650-003 postgres[14508]: [2-1] LOG: recycled > transaction > log file "000B0083" > Oct 26 17:31:27 vl-pe6650-003 postgres[14508]: [3-1] LOG: recycled > transaction > log file "000B0084" > Oct 26 17:31:27 vl-pe6650-003 postgres[14508]: [4-1] LOG: recycled > transaction > log file "000B0085" Looks like you're running out of disk space for pending transactions. Can you afford more checkpoint_segments? Have you considered checkpoint_siblings? -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] can't handle large number of INSERT/UPDATEs
Josh, I have increased them to 30, will see if that helps. Space is not a concern. slightly longer recovery time could be fine too. Wonder what people use (examples) for this value for high volume databases (except for dump/restore)...? I don't know what is checkpoint_sibling. I'll read about it if there's some info on it somewhere. Thanks, Anjan -Original Message- From: Josh Berkus [mailto:[EMAIL PROTECTED] Sent: Tue 10/26/2004 8:42 PM To: [EMAIL PROTECTED] Cc: Anjan Dave; Tom Lane; Rod Taylor Subject: Re: [PERFORM] can't handle large number of INSERT/UPDATEs Anjan, > Oct 26 17:26:25 vl-pe6650-003 postgres[14273]: [4-1] LOG: recycled > transaction > log file "000B0082" > ... > Oct 26 17:31:27 vl-pe6650-003 postgres[14508]: [2-1] LOG: recycled > transaction > log file "000B0083" > Oct 26 17:31:27 vl-pe6650-003 postgres[14508]: [3-1] LOG: recycled > transaction > log file "000B0084" > Oct 26 17:31:27 vl-pe6650-003 postgres[14508]: [4-1] LOG: recycled > transaction > log file "000B0085" Looks like you're running out of disk space for pending transactions. Can you afford more checkpoint_segments? Have you considered checkpoint_siblings? -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] can't handle large number of INSERT/UPDATEs
Curtis Zinzilieta <[EMAIL PROTECTED]> writes: > On Tue, 26 Oct 2004, Tom Lane wrote: >> Er ... it *is* the other way around. bi is blocks in (to the CPU), >> bo is blocks out (from the CPU). > Ummm. > [EMAIL PROTECTED] T2]$ man vmstat >bi: Blocks sent to a block device (blocks/s). >bo: Blocks received from a block device (blocks/s). You might want to have a word with your OS vendor. My vmstat man page says IO bi: Blocks received from a block device (blocks/s). bo: Blocks sent to a block device (blocks/s). and certainly anyone who's been around a computer more than a week or two knows which direction "in" and "out" are customarily seen from. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] can't handle large number of INSERT/UPDATEs
Tom Lane wrote: Curtis Zinzilieta <[EMAIL PROTECTED]> writes: On Tue, 26 Oct 2004, Tom Lane wrote: Er ... it *is* the other way around. bi is blocks in (to the CPU), bo is blocks out (from the CPU). Ummm. [EMAIL PROTECTED] T2]$ man vmstat bi: Blocks sent to a block device (blocks/s). bo: Blocks received from a block device (blocks/s). You might want to have a word with your OS vendor. My vmstat man page says IO bi: Blocks received from a block device (blocks/s). bo: Blocks sent to a block device (blocks/s). and certainly anyone who's been around a computer more than a week or two knows which direction "in" and "out" are customarily seen from. regards, tom lane Interesting. I checked this on several machines. They actually say different things. Redhat 9- bi: Blocks sent to a block device (blocks/s). Latest Cygwin- bi: Blocks sent to a block device (blocks/s). Redhat 7.x- bi: Blocks sent to a block device (blocks/s). Redhat AS3- bi: blocks sent out to a block device (in blocks/s) I would say that I probably agree, things should be relative to the cpu. However, it doesn't seem to be something that was universally agreed upon. Or maybe the man-pages were all wrong, and only got updated recently. John =:-> signature.asc Description: OpenPGP digital signature
Re: [PERFORM] can't handle large number of INSERT/UPDATEs
Turbo linux 7 sems to be agreeing with Curtis, (B (Bbi: $B%V%m%C%/%G%P%$%9$KAw$i$l$?%V%m%C%/(B (blocks/s)$B!#(B (Bbo: $B%V%m%C%/%G%P%$%9$+$i (B (BSorry it's in Japanese but bi says "blocks sent to block device" and bo is (B"blocks received from block device". (B (BI don't know that much about it but the actual output seems to suggest that (Bthe man page is wrong. I find it just the slightest bit amusing that such (Berrors in the docs should be translated faithfully when translating (Binvariably introduces errors of it's own ;) (B (BRegards (BIain (B (B (B- Original Message - (BFrom: "Tom Lane" <[EMAIL PROTECTED]> (BTo: "Curtis Zinzilieta" <[EMAIL PROTECTED]> (BCc: "Anjan Dave" <[EMAIL PROTECTED]>; "Matt Clark" <[EMAIL PROTECTED]>; "Rod (BTaylor" <[EMAIL PROTECTED]>; "Postgresql Performance" (B<[EMAIL PROTECTED]> (BSent: Wednesday, October 27, 2004 12:21 PM (BSubject: Re: [PERFORM] can't handle large number of INSERT/UPDATEs (B (B (B> Curtis Zinzilieta <[EMAIL PROTECTED]> writes: (B>> On Tue, 26 Oct 2004, Tom Lane wrote: (B>>> Er ... it *is* the other way around. bi is blocks in (to the CPU), (B>>> bo is blocks out (from the CPU). (B> (B>> Ummm. (B>> [EMAIL PROTECTED] T2]$ man vmstat (B>>bi: Blocks sent to a block device (blocks/s). (B>>bo: Blocks received from a block device (blocks/s). (B> (B> You might want to have a word with your OS vendor. My vmstat (B> man page says (B> (B> IO (B> bi: Blocks received from a block device (blocks/s). (B> bo: Blocks sent to a block device (blocks/s). (B> (B> and certainly anyone who's been around a computer more than a week or (B> two knows which direction "in" and "out" are customarily seen from. (B> (B> regards, tom lane (B> (B> ---(end of broadcast)--- (B> TIP 4: Don't 'kill -9' the postmaster (B (B (B---(end of broadcast)--- (BTIP 2: you can get off all lists at once with the unregister command (B(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] can't handle large number of INSERT/UPDATEs
and certainly anyone who's been around a computer more than a week or two knows which direction "in" and "out" are customarily seen from. regards, tom lane Apparently not whoever wrote the man page that everyone copied ;-) Interesting. I checked this on several machines. They actually say different things. Redhat 9- bi: Blocks sent to a block device (blocks/s). Latest Cygwin- bi: Blocks sent to a block device (blocks/s). Redhat 7.x- bi: Blocks sent to a block device (blocks/s). Redhat AS3- bi: blocks sent out to a block device (in blocks/s) I would say that I probably agree, things should be relative to the cpu. However, it doesn't seem to be something that was universally agreed upon. Or maybe the man-pages were all wrong, and only got updated recently. Looks like the man pages are wrong, for RH7.3 at least. It says bi is 'blocks written', but an actual test like 'dd if=/dev/zero of=/tmp/test bs=1024 count=16384' on an otherwise nearly idle RH7.3 box gives: procs memoryswap io system cpu r b w swpd free buff cache si sobibo incs us sy id 0 0 0 75936 474704 230452 953580 0 0 0 0 106 2527 0 0 99 0 0 0 75936 474704 230452 953580 0 0 0 16512 376 2572 0 2 98 0 0 0 75936 474704 230452 953580 0 0 0 0 105 2537 0 0 100 Which is in line with bo being 'blocks written'. M ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
