Re: [PERFORM] slow queries, possibly disk io
On Fri, 2005-05-27 at 07:52 -0500, Josh Close wrote: Setting shared buffers above something like 10-30% of memory is counter productive. What is the reason behind it being counter productive? If shared buffers are at 30%, should effective cache size be at 70%? How do those two relate? They don't relate. shared_buffers = 5 is enough. More than that will give bgwriter issues. effective_cache_size changes whether indexes are selected or not. Higher settings favour indexed access. Increasing sort_mem can help with various activities, but increasing it too much can cause you to swap, which kills performance. The caution is that you will likely use at least 1 sort_mem per connection, and can likely use more than one if the query is complicated. I have a max of 100 connections and 2 gigs of mem. Right now the sort mem is a 4 megs. How much higher could I put that? Please post your server hardware config all in one go. You have more than 2 CPUs, yes? Also, mention what bgwriter settings are. You may need to turn those down a bit. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] slow queries, possibly disk io
I didn't see iostat as available to install, but I'm using dstat to see this. The server has constant disk reads averaging around 50M and quite a few in the 60M range. This is when selects are being done, which is almost always. I would think if postgres is grabbing everything from memory that this wouldn't happen. This is why I think there must be some way to allocate more mem to postgres. There is 2 gigs of mem in this server. Here are my current settings. max_connections = 100 shared_buffers = 5 sort_mem = 4096 vacuum_mem = 32768 effective_cache_size = 45 Shared buffers is set to 10% of total mem. Effective cache size is 90% of mem. Is there anything that can be done to have postgres grab more from memory rather than disk? On 5/30/05, Rudi Starcevic [EMAIL PROTECTED] wrote: Hi, I had some disk io issues recently with NFS, I found the command 'iostat -x 5' to be a great help when using Linux. For example here is the output when I do a 10GB file transfer onto hdc Device:rrqm/s wrqm/s r/s w/s rsec/s wsec/srkB/swkB/s avgrq-sz avgqu-sz await svctm %util hdc 0.00 875.95 0.00 29.660.00 7244.89 0.00 3622.44 244.27 3.07 103.52 1.78 5.27 The last field show the disk is 5.27% busy. I have seen this value at 100%, adding more server brought it under 100%. It seems that if you hit 100% problems sort of cascade all over that place. For example Apache connections went right up and hit their max. I am not sure how accurate the % is but it has work pretty well for me. Perhaps use this command in another window with you run your SQL and see what it shows. HTH. Kind regards, Rudi. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- -Josh ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] slow queries, possibly disk io
Josh Close [EMAIL PROTECTED] writes: There is 2 gigs of mem in this server. Here are my current settings. max_connections = 100 shared_buffers = 5 sort_mem = 4096 vacuum_mem = 32768 effective_cache_size = 45 Shared buffers is set to 10% of total mem. Effective cache size is 90% of mem. Uh, shared_buffers and effective_cache_size are both measured in pages, which are 8K apiece unless you built with a nondefault BLCKSZ. So the above calculations are off ... Is there anything that can be done to have postgres grab more from memory rather than disk? It's not so much a matter of what Postgres will do as what the kernel will do. Check to see if there is some limit on how much memory the kernel will set aside for disk buffers. Plain old top will generally tell you what is going on, though interpreting its output sometimes requires some wizardry. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] slow queries, possibly disk io
On 5/31/05, Martin Fandel [EMAIL PROTECTED] wrote: In the documentation of http://www.powerpostgresql.com/Downloads/annotated_conf_80.html is the shared_buffers set to 1/3 of the availble RAM. You're set 5*8/1024=391 MB SHMEM. The effective_cache_size in your configuration is 45*8/1024=3516 MB SHMEM. That's 3907MB of RAM but you have less than 2048MB availble. I wrote that wrong, there is actually 4 gigs of ram available. What value do you have in /proc/sys/kernel/shmmax? I'm really new at using postgres and i have not many experience but maybe you can try to use 1/3 (682MB/87424)for shared_buffers and 2/3 (1365MB/174720) for the effective_cache_size? But i these settings are to high too. best regards Martin ---(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] slow queries, possibly disk io
On 5/31/05, Martin Fandel [EMAIL PROTECTED] wrote: In the documentation of http://www.powerpostgresql.com/Downloads/annotated_conf_80.html is the shared_buffers set to 1/3 of the availble RAM. Well, it says you should never use more than 1/3 of your available RAM which is not quite the same as it is set to 1/3. I'd even say, never set it higher than 1/10 of your available RAM, unless you know what you're doing and why you're doing it. Servus Manfred ---(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] slow queries, possibly disk io
Hi, I had some disk io issues recently with NFS, I found the command 'iostat -x 5' to be a great help when using Linux. For example here is the output when I do a 10GB file transfer onto hdc Device:rrqm/s wrqm/s r/s w/s rsec/s wsec/srkB/swkB/s avgrq-sz avgqu-sz await svctm %util hdc 0.00 875.95 0.00 29.660.00 7244.89 0.00 3622.44 244.27 3.07 103.52 1.78 5.27 The last field show the disk is 5.27% busy. I have seen this value at 100%, adding more server brought it under 100%. It seems that if you hit 100% problems sort of cascade all over that place. For example Apache connections went right up and hit their max. I am not sure how accurate the % is but it has work pretty well for me. Perhaps use this command in another window with you run your SQL and see what it shows. HTH. Kind regards, Rudi. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] slow queries, possibly disk io
On 5/26/05, Christopher Kings-Lynne [EMAIL PROTECTED] wrote: I have some queries that have significan't slowed down in the last couple days. It's gone from 10 seconds to over 2 mins. The cpu has never gone over 35% in the servers lifetime, but the load average is over 8.0 right now. I'm assuming this is probably due to disk io. You sure it's not a severe lack of vacuuming that's the problem? It's vacuumed hourly. If it needs to be more than that I could do it I guess. But from everything I've been told, hourly should be enough. -Josh ---(end of broadcast)--- TIP 8: explain analyze is your friend
[PERFORM] slow queries, possibly disk io
Few mandatory questions: 1. Do you vacuum your db on regular basis? :) It's vacuumed once every hour. The table sizes and data are constantly changing. 2. Perhaps statistics for tables in question are out of date, did you try alter table set statistics? No I haven't. What would that do for me? 3. explain analyze of the slow query? Here is the function that is ran: CREATE OR REPLACE FUNCTION adaption.funmsgspermin() RETURNS int4 AS ' DECLARE this_rServerIds RECORD; this_sQuery TEXT; this_iMsgsPerMin INT; this_rNumSentRECORD; BEGIN this_iMsgsPerMin := 0; FOR this_rServerIds IN SELECT iId FROM adaption.tblServers LOOP this_sQuery := \' SELECT COALESCE( SUM( iNumSent ), 0 ) AS iNumSent FROM adaption.tblBatchHistory_\' || this_rServerIds.iId || \' WHERE tStamp now() - interval \'\'5 mins\'\'; \'; FOR this_rNumSent IN EXECUTE this_sQuery LOOP this_iMsgsPerMin := this_iMsgsPerMin + this_rNumSent.iNumSent; END LOOP; END LOOP; this_iMsgsPerMin := this_iMsgsPerMin / 5; RETURN this_iMsgsPerMin; END; ' LANGUAGE 'plpgsql' VOLATILE; Here is the explain analyze of one loops of the sum: Aggregate (cost=31038.04..31038.04 rows=1 width=4) (actual time=14649.602..14649.604 rows=1 loops=1) - Seq Scan on tblbatchhistory_1 (cost=0.00..30907.03 rows=52401 width=4) (actual time=6339.223..14648.433 rows=919 loops=1) Filter: (tstamp (now() - '00:05:00'::interval)) Total runtime: 14649.709 ms 4. if you for some reason cannot give explain analyze, please try to describe the type of query (what kind of join(s)) and amount of data found in the tables. 2 minutes from 10 seconds is a huge leap, and it may mean that PostgreSQL for some reason is not planning as well as it could. Throwing more RAM at the problem can help, but it would be better to hint the planner to do the right thing. It may be a good time to play with planner variables. :) Is there any documentation on planner vars? And how would I throw more ram at it? It has 2 gigs right now. How do I know if postgres is using that? -Josh ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] slow queries, possibly disk io
Josh Close [EMAIL PROTECTED] writes: this_sQuery := \' SELECT COALESCE( SUM( iNumSent ), 0 ) AS iNumSent FROM adaption.tblBatchHistory_\' || this_rServerIds.iId || \' WHERE tStamp now() - interval \'\'5 mins\'\'; \'; Here is the explain analyze of one loops of the sum: Aggregate (cost=31038.04..31038.04 rows=1 width=4) (actual time=14649.602..14649.604 rows=1 loops=1) - Seq Scan on tblbatchhistory_1 (cost=0.00..30907.03 rows=52401 width=4) (actual time=6339.223..14648.433 rows=919 loops=1) Filter: (tstamp (now() - '00:05:00'::interval)) Total runtime: 14649.709 ms I think you really want that seqscan to be an indexscan, instead. I'm betting this is PG 7.4.something? If so, probably the only way to make it happen is to simplify the now() expression to a constant: SELECT COALESCE( SUM( iNumSent ), 0 ) AS iNumSent FROM adaption.tblBatchHistory_\' || this_rServerIds.iId || \' WHERE tStamp \\\'' || (now() - interval \'5 mins\')::text || \'\\\'\'; because pre-8.0 the planner won't realize that the inequality is selective enough to favor an indexscan, unless it's comparing to a simple constant. (BTW, 8.0's dollar quoting makes this sort of thing a lot less painful) regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] slow queries, possibly disk io
I think you really want that seqscan to be an indexscan, instead. I'm betting this is PG 7.4.something? If so, probably the only way to make it happen is to simplify the now() expression to a constant: SELECT COALESCE( SUM( iNumSent ), 0 ) AS iNumSent FROM adaption.tblBatchHistory_\' || this_rServerIds.iId || \' WHERE tStamp \\\'' || (now() - interval \'5 mins\')::text || \'\\\'\'; The dollar sign thing would be a lot easier. I can't get this to work. I'm using a db manager where I can just use ' instead of \'. How would it look for that? In other words, it doesn't have the create or replace function as ' --stuff ' language 'plpgsql' it just has the actual function. Makes things a little easier. I'm getting an error at or near 5. because pre-8.0 the planner won't realize that the inequality is selective enough to favor an indexscan, unless it's comparing to a simple constant. (BTW, 8.0's dollar quoting makes this sort of thing a lot less painful) regards, tom lane -- -Josh ---(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] slow queries, possibly disk io
Doing the query explain SELECT COALESCE( SUM( iNumSent ), 0 ) AS iNumSent FROM adaption.tblBatchHistory_1 WHERE tStamp ( now() - interval '5 mins' )::text gives me this: Aggregate (cost=32138.33..32138.33 rows=1 width=4) - Seq Scan on tblbatchhistory_1 (cost=0.00..31996.10 rows=56891 width=4) Filter: ((tstamp)::text ((now() - '00:05:00'::interval))::text) Still not an index scan. On 5/27/05, Tom Lane [EMAIL PROTECTED] wrote: Josh Close [EMAIL PROTECTED] writes: this_sQuery := \' SELECT COALESCE( SUM( iNumSent ), 0 ) AS iNumSent FROM adaption.tblBatchHistory_\' || this_rServerIds.iId || \' WHERE tStamp now() - interval \'\'5 mins\'\'; \'; Here is the explain analyze of one loops of the sum: Aggregate (cost=31038.04..31038.04 rows=1 width=4) (actual time=14649.602..14649.604 rows=1 loops=1) - Seq Scan on tblbatchhistory_1 (cost=0.00..30907.03 rows=52401 width=4) (actual time=6339.223..14648.433 rows=919 loops=1) Filter: (tstamp (now() - '00:05:00'::interval)) Total runtime: 14649.709 ms I think you really want that seqscan to be an indexscan, instead. I'm betting this is PG 7.4.something? If so, probably the only way to make it happen is to simplify the now() expression to a constant: SELECT COALESCE( SUM( iNumSent ), 0 ) AS iNumSent FROM adaption.tblBatchHistory_\' || this_rServerIds.iId || \' WHERE tStamp \\\'' || (now() - interval \'5 mins\')::text || \'\\\'\'; because pre-8.0 the planner won't realize that the inequality is selective enough to favor an indexscan, unless it's comparing to a simple constant. (BTW, 8.0's dollar quoting makes this sort of thing a lot less painful) regards, tom lane -- -Josh ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PERFORM] slow queries, possibly disk io
I have some queries that have significan't slowed down in the last couple days. It's gone from 10 seconds to over 2 mins. The cpu has never gone over 35% in the servers lifetime, but the load average is over 8.0 right now. I'm assuming this is probably due to disk io. I need some help setting up postgres so that it doesn't need to go to disk. I think the shared_buffers and effective_cache_size values are the one's I need to look at. Would setting shmmax and smmall to 90% or so of available mem and putting a lot for postgres be helpful? Effective cach size says this: Sets the planner's assumption about the effective size of the disk cache (that is, the portion of the kernel's disk cache that will be used for PostgreSQL data files). Does that mean the total available ram? Or what's left over from shared_buffers? I've tried different things and not much has been working. Is there a good way to ensure that most of the tables accessed in postgres will be cached in mem and not have to go to disk? If I'm joining a lot of tables, should the sort_mem be set high also? Do shared_buffers, effective_cache_size, and sort_mem all use different mem? Or are they seperate? I've looked for information and haven't found any useful pages about this. Any help would be greatly appreciated. Thanks. -Josh ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] slow queries, possibly disk io
Josh Close wrote: I have some queries that have significan't slowed down in the last couple days. It's gone from 10 seconds to over 2 mins. The cpu has never gone over 35% in the servers lifetime, but the load average is over 8.0 right now. I'm assuming this is probably due to disk io. I need some help setting up postgres so that it doesn't need to go to disk. I think the shared_buffers and effective_cache_size values are the one's I need to look at. Would setting shmmax and smmall to 90% or so of available mem and putting a lot for postgres be helpful? Setting shared buffers above something like 10-30% of memory is counter productive. Effective cach size says this: Sets the planner's assumption about the effective size of the disk cache (that is, the portion of the kernel's disk cache that will be used for PostgreSQL data files). Does that mean the total available ram? Or what's left over from shared_buffers? I've tried different things and not much has been working. Is there a good way to ensure that most of the tables accessed in postgres will be cached in mem and not have to go to disk? If I'm joining a lot of tables, should the sort_mem be set high also? Do shared_buffers, effective_cache_size, and sort_mem all use different mem? Or are they seperate? Increasing sort_mem can help with various activities, but increasing it too much can cause you to swap, which kills performance. The caution is that you will likely use at least 1 sort_mem per connection, and can likely use more than one if the query is complicated. effective_cache_size changes how Postgres plans queries, but given the same query plan, it doesn't change performance at all. I've looked for information and haven't found any useful pages about this. Any help would be greatly appreciated. Thanks. -Josh John =:- signature.asc Description: OpenPGP digital signature
Re: [PERFORM] slow queries, possibly disk io
On 5/26/05, Josh Close [EMAIL PROTECTED] wrote: I have some queries that have significan't slowed down in the last couple days. It's gone from 10 seconds to over 2 mins. The cpu has never gone over 35% in the servers lifetime, but the load average is over 8.0 right now. I'm assuming this is probably due to disk io. I need some help setting up postgres so that it doesn't need to go to disk. I think the shared_buffers and effective_cache_size values are the one's I need to look at. Few mandatory questions: 1. Do you vacuum your db on regular basis? :) 2. Perhaps statistics for tables in question are out of date, did you try alter table set statistics? 3. explain analyze of the slow query? 4. if you for some reason cannot give explain analyze, please try to describe the type of query (what kind of join(s)) and amount of data found in the tables. 2 minutes from 10 seconds is a huge leap, and it may mean that PostgreSQL for some reason is not planning as well as it could. Throwing more RAM at the problem can help, but it would be better to hint the planner to do the right thing. It may be a good time to play with planner variables. :) Regards, Dawid ---(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
