Re: [ADMIN] [PERFORM] autovacuum on a -mostly- r/o table
In response to Edoardo Ceccarelli <[EMAIL PROTECTED]>: > Hello, > > we are running a 7.3 postgres db with only a big table (avg > 500.000records) and 7 indexes for a search engine. > we have 2 of this databases and we can switch from one to another. > Last week we decided to give a try to 8.1 on one of them and everything > went fine, db is faster (about 2 or 3 times in our case) and the server > load is higher - which should mean that faster response time is achieved > by taking a better use of the server. > > We also activated the autovacuum feature to give it a try and that's > were our problems started. > I left the standard autovacuum configuration just to wait and see, pg > decided to start a vacuum on the table just midday when users were > launching search queries on the table and server load reached a very > high value so that in a couple of minutes the db was unusable > > With pg7.3 we use to vacuum the db night time, mostly because the insert > and updates in this table is made in a batch way: a single task that > puts 100.000 records in the db in 10/20minutes, so the best time to > actually vacuum the db would be after this batch. > > I have read that autovacuum cannot check to see pg load before launching > vacuum but is there any patch about it? that would sort out the problem > in a good and simple way. > Otherwise, which kind of set of parameters I should put in autovacuum > configuration? I am stuck because in our case the table gets mostly read > and if I set up things as to vacuum the table after a specific amount of > insert/updates, I cannot foresee whether this could happen during > daytime when server is under high load. > How can I configure the vacuum to run after the daily batch insert/update? It doesn't sound as if your setup is a good match for autovacuum. You might be better off going back to the cron vacuums. That's the beauty of Postgres -- it gives you the choice. If you want to continue with autovac, you may want to experiment with vacuum_cost_delay and associated parameters, which can lessen the impact of vacuuming. -- Bill Moran Collaborative Fusion Inc. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] [ADMIN] Increasing the shared memory
In response to "Sorin N. Ciolofan" <[EMAIL PROTECTED]>: > > I've a value of 1000 set for shared_buffers, does this means > that I use 8kbX1000=8Mb of Shared Mem? > > > > The definition from the manual is quite confusing: > > > > shared_buffers (integer) > > Sets the amount of memory the database server uses for shared memory > buffers. The default is typically 32 megabytes (32MB), but may be less if > your kernel settings will not support it (as determined during initdb). This > setting must be at least 128 kilobytes and at least 16 kilobytes times > max_connections > <http://www.postgresql.org/docs/current/static/runtime-config-connection.htm > l#GUC-MAX-CONNECTIONS> . > > > > What does the integer number represent? Number of shared buffers? If yes, > what size does each shared buffer have? > > "The default is typically 32 megabytes" suggests that this integer could > also represent the number of megabytes?!? > > In the postgresql.conf file is an ambiguous comment that could induce the > idea that each shared buffer has 8 kb. > > So, which is the meaning of this integer? Older versions of PostgreSQL, the number was the _number_ of shared buffers. Each buffer is typically 8K, but this can be altered at compile time (I believe) and the PGDG has no way to know for sure if whatever packaged version you installed might have done so. However, on more recent versions of Postgres (although I don't know exactly what version first included this) you can specify this value as "M" or "G" to specify a number of megabytes or gigabytes. Internally, this is still converted to a number of 8K buffers, but it makes the config file easier to read and understand. -- Bill Moran http://www.potentialtech.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [ADMIN] Increasing the shared memory
In response to "Sorin N. Ciolofan" <[EMAIL PROTECTED]>: > I've tried first to increase the number of shared buffers, I > doubled it, from 1000 to 2000 (16Mb) > > Unfortunately this had no effect. The difference between 8M and and 16M of shared buffers is pretty minor. Try bumping it up to 250M or so and see if that helps. You could install the pg_buffercache addon and monitor your buffer usage to see how much is actually being used. However, if the problem is write performance (which I'm inferring from your message that it is) then increasing shared_buffers isn't liable to make a significant improvement, unless the inserts are doing a lot of querying as well. With inserts, the speed is going to (most likely) be limited by the speed of your disks. I may have missed this information in earlier posts, did you provide details of you hardware configuration? Have you done tests to find out what speed your disks are running? Have you monitored IO during your inserts to see if the IO subsystem is maxed out? Also, the original problem you were trying to solve has been trimmed from this thread, which makes me wonder if any of my advice is relevant. > > Then I increased the number of max_locks_per_transaction > from 64 to 128 (these shoul assure about 12 800 lock slots) considering > max_connections=100 and max_prepared_transaction=5 (Quote from the manual - > The shared lock table is created to track locks on max_locks_per_transaction > * (max_connections > <http://www.postgresql.org/docs/8.2/interactive/runtime-config-connection.ht > ml#GUC-MAX-CONNECTIONS> + max_prepared_transactions > <http://www.postgresql.org/docs/8.2/interactive/runtime-config-resource.html > #GUC-MAX-PREPARED-TRANSACTIONS> ) objects (e.g. tables);) > > I've also restarted > > This had also no effect. Because I can't see any difference > between the maximum input accepted for our application with the old > configuration and the maximum input accepted now, with the new > configuration. It looks like nothing happened. > > > > Thanks > > Sorin > > _ > > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Shoaib Mir > Sent: Monday, April 02, 2007 6:02 PM > To: Sorin N. Ciolofan > Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: Re: [ADMIN] Increasing the shared memory > > > > An extract from --> http://www.powerpostgresql.com/PerfList/ might help > you > > shared_buffers: > > As a reminder: This figure is NOT the total memory PostgreSQL has to work > with. It is the block of dedicated memory PostgreSQL uses for active > operations, and should be a minority of your total RAM on the machine, since > PostgreSQL uses the OS disk cache as well. Unfortunately, the exact amount > of shared buffers required is a complex calculation of total RAM, database > size, number of connections, and query complexity. Thus it's better to go > with some rules of thumb in allocating, and monitor the server (particuarly > pg_statio views) to determine adjustments. > On dedicated servers, useful values seem to be between between 8MB and 400MB > (between 1000 and 50,000 for 8K page size). Factors which raise the desired > shared buffers are larger active portions of the database, large complex > queries, large numbers of simultaneous queries, long-running procedures or > transactions, more available RAM, and faster/more CPUs. And, of course, > other applications on the machine. Contrary to some expectations, allocating > much too much shared_buffers can actually lower peformance, due time > required for scanning. Here's some examples based on anecdotes and TPC tests > on Linux machines: > > * Laptop, Celeron processor, 384MB RAM, 25MB database: 12MB/1500 > * Athlon server, 1GB RAM, 10GB decision-support database: 120MB/15000 > * Quad PIII server, 4GB RAM, 40GB, 150-connection heavy transaction > processing database: 240MB/3 > * Quad Xeon server, 8GB RAM, 200GB, 300-connection heavy transaction > processing database: 400MB/5 > > Please note that increasing shared_buffers, and a few other memory > parameters, will require you to modify your operating system's System V > memory parameters. See the main PostgreSQL documentation for instructions on > this. > > -- > Shoaib Mir > EnterpriseDB (www.enterprisedb.com) > > -- Bill Moran http://www.potentialtech.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [ADMIN] Increasing the shared memory
In response to "Sorin N. Ciolofan" <[EMAIL PROTECTED]>: > > Dear all, > > Thanks for your advices. I'd like to ask you where can I download the > pg_buffercache add-on and also where can I find some documentation about how > can I install it? It's part of the contrib directory that ships with the source tarball. Depending on your OS and associated packaging system (which you don't bother to mention) it's probably available via RPMs or DEBs or whatever. For example, under FreeBSD it's in /usr/ports/databases/postgresql-contrib -- Bill Moran http://www.potentialtech.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] [GENERAL] pg_buffercache view
In response to "Sorin N. Ciolofan" <[EMAIL PROTECTED]>: > > Dear all, > > About the pg_buffercache view: > I couldn't find the description for this view in the manual at > http://www.postgresql.org/docs/8.2/interactive/catalogs.html > However I found the readme file provided in the /contrib./pg_buffercache of > the source code for version 8.2.3 Since pg_buffercache is contributed software, it's not documented in the official PostgreSQL docs. > Here it's written the following description: > >Column | references | Description > > +--+ >bufferid | | Id, 1..shared_buffers. >relfilenode| pg_class.relfilenode | Refilenode of the relation. >reltablespace | pg_tablespace.oid| Tablespace oid of the relation. >reldatabase| pg_database.oid | Database for the relation. >relblocknumber | | Offset of the page in the > relation. >isdirty| | Is the page dirty? > > I've 2 questions: > 1) > I was not able to find the field "oid" from pg_database view. Could you > please tell me what is the actual name of the column for which reldatabase > is reffering to? At the end of the README is an example query that I think answers your question: SELECT c.relname, count(*) AS buffers FROM pg_class c, pg_buffercache b WHERE b.relfilenode = c.relfilenode GROUP BY c.relname ORDER BY 2 DESC LIMIT 10; > 2) > In readme file is also written: > "Unused buffers are shown with all fields null except buffered". > A "used" buffer means that is used 100% or could it be filled only > partially? Yes. The buffer is either "used" or "not used", but pg_buffercache doesn't know what percentage of it is used. >0% is used. 0% is not used. > Is there any way to know at a certain moment with precision how much shared > memory expressed in Mb is used? The precision is +/- 1 buffer. I expect that trying to get more precision out of the system will result in considerable performance degradation as the data is collected and/or tracked. -- Bill Moran http://www.potentialtech.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] [GENERAL] pg_buffercache view
In response to "Sorin N. Ciolofan" <[EMAIL PROTECTED]>: > > Hello! > > Do you know which could be the reasons that could conduce an application to > not release the shared buffers, even after the application was shut down? > I noticed that only if a pg_ctl restart command is issued some of the > buffers are set free. The reason would be "by design." If the server flushes its cache every time the application restarts, the cache isn't going to be very effective. If PostgreSQL is using more shared buffers than you're comfortable with, reduce the shared_buffers setting in the config. That will allow the OS to decide how to use the memory instead. -- Bill Moran http://www.potentialtech.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] [GENERAL] pg_buffercache view
In response to "Sorin N. Ciolofan" <[EMAIL PROTECTED]>: > > I don't know the algorithm on which Postgre uses the shared buffers but I'd > like to find the principles behind it. Let's assume the following scenario: > I've set shared_buffers=3000 > At the starting of Postgres there are 115 buffers used by database A > After the execution of some processing caused by a java methodA1() > invocation, 2850 buffers are used by A. > What happens next if these 2850 buffers remains used even if the methodA1() > finished its execution? They'll be reused the next time a query needs the same data, or discarded when the buffer space is needed for something else. > Suppose that now a methodA2() invocation occurs and this method works with > database A, too. Will be the 2850 buffers reused yes > or will postgre throw an > "out of shared memory" exception? no > What happens if a methodB() invocation occurs, assuming that this method > tries to work with database B? Buffers will be allocated/reallocate/reused as best fits the usage pattern of the server. > How Postgres decides the allocation of shared_buffers? They're "buffers". They follow the design of just about any other type of buffer: http://foldoc.org/index.cgi?query=buffer&action=Search "Buffers are used to decouple processes so that the reader and writer may operate at different speeds or on different sized blocks of data." > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Bill Moran > Sent: Thursday, April 26, 2007 3:32 PM > To: Sorin N. Ciolofan > Cc: [EMAIL PROTECTED]; pgsql-admin@postgresql.org > Subject: Re: [ADMIN] [GENERAL] pg_buffercache view > > In response to "Sorin N. Ciolofan" <[EMAIL PROTECTED]>: > > > > > Hello! > > > > Do you know which could be the reasons that could conduce an application > to > > not release the shared buffers, even after the application was shut down? > > I noticed that only if a pg_ctl restart command is issued some of the > > buffers are set free. > > The reason would be "by design." > > If the server flushes its cache every time the application restarts, the > cache isn't going to be very effective. > > If PostgreSQL is using more shared buffers than you're comfortable with, > reduce the shared_buffers setting in the config. That will allow the OS > to decide how to use the memory instead. > > -- > Bill Moran > http://www.potentialtech.com > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org/ > > -- Bill Moran http://www.potentialtech.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [ADMIN] [GENERAL] Inserts hang in DB and error messages in log
In response to "Moshe Ben-Shoham" : > > We're working with version 8.3.5-1. > > Lately we started seeing insert statements hang in the DB. The > statements come from two different clients. > > When it happens, I see the following messages in the log every second or > so: > > 2009-05-21 08:56:49 IDT ERROR: permission denied to set parameter > "log_statement" > > 2009-05-21 08:56:49 IDT STATEMENT: SET log_statement='none'; [snip the same errors over an over ...] > Any help (or guidelines for additional required information) is > appreciated. It's kind of hard to tell from what you've posted, but I'll take a guess. First, the inability to turn log_statement off isn't going to cause the server to hang or pause or anything. This error is _not_ the problem, although it's probably related, since it's happening in conjunction with the problem. Based on that, my guess is that you're running a commercial application that is trying to hide its SQL from you, thus it refuses to run any queries unless it can turn log_statement to "none". However, log_statement can only be changed by a superuser, and I'm betting you did the _right_ thing and didn't make the application user a superuser. If my guesses are right, the following is true: * The application is probably garbage. You'll probably have other problems with it if you continue to use it. You should contact the vendor and chew them out for their crappy design. Either that or they're so brilliant that they've managed to write SQL statements that are copyrighted or something. * If you give the application user superuser privs, the application will probably start working. * You _may_ be able to get the application to start working without granting superuser privs, simply by setting the value of log_statement to "none" in the postgresql.conf. This is assuming the application is smart enough to check the value and only change it if it's not already "none". As I said, the advice is all based on guessing, so good luck with it. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [GENERAL] [ADMIN] Query is stuck
In response to Szymon Guz : > 2010/4/13 Satish Burnwal (sburnwal) > > > I have a query which is not giving me the result even after 30 minutes. I > > want to know how to detect what is going and what’s wrong ? > > > > > > > > EXPLAIN query - gives me the following: > > > > controlsmartdb=# explain select report_id, dm_ip, dm_mac, dm_user, dm_os, > > report_time, sys_name, sys_user, sys_user_domain, ss_key, login_time, > > role_id, new_vlan_id from repcopy as a where report_time = (select > > max(report_time) from repcopy as b where a.dm_ip = b.dm_ip and > > a.dm_user=b.dm_user and b.ss_key != '') and report_status = 0 and dm_user = > > 'u1'; > > > > QUERY PLAN > > > > > > > > > > Seq Scan on repcopy a (cost=0.00..1630178118.35 rows=35 width=133) > > > >Filter: ((report_status = 0) AND ((dm_user)::text = 'u1'::text) AND > > (report_time = (subplan))) > > > >SubPlan > > > > -> Aggregate (cost=8151.65..8151.66 rows=1 width=8) > > > >-> Seq Scan on repcopy b (cost=0.00..8151.65 rows=1 width=8) > > > > Filter: ((($0)::text = (dm_ip)::text) AND (($1)::text = > > (dm_user)::text) AND ((ss_key)::text <> ''::text)) > > > > (6 rows) > > > > > > > > But EXPLAIN ANALYSE query hangs (is not giving me any output even after 30 > > minutes). > > > > > > > > Pg_stat_activity shows this - SELECT procpid, usename, current_query, > > query_start from pg_stat_activity: > > > > 2942 | postgres | select report_id, dm_ip, dm_mac, dm_user, dm_os, > > report_time, sys_name, sys_user, sys_user_domain, ss_key, login_time, > > role_id, new_vlan_id from repcopy as a where report_time = (select > > max(report_time) from repcopy as b where a.dm_ip = b.dm_ip and > > a.dm_user=b.dm_user and b.ss_key != '') and report_status = 0 and dm_user = > > 'u1'; | 2010-04-13 18:20:02.828623+05:30 > > > > > > > > > > > > In such a case what can I do ? > > > > > > First things that came to my mind: > > 1. Check if the query waits on some lock: add the column `waiting` to the > above query from pg_stat_activity. > 2. Run vacuum analyze on the table repcopy In addition to that, indexes on report_time, report_status, and dm_user might help. And your query is not "hung", it's just taking a LONG time. Based on the explain, it could take several hours to complete. How many rows are in repcopy? What is your vacuum schedule? Do a vacuum verbose, if the number of dead rows is very high on that table, you may benefit from doing a VACUUM FULL + REINDEX or CLUSTER on the table. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Problems tuning shared_buffers value
Tom Lane wrote: Bill Moran <[EMAIL PROTECTED]> writes: curious about some things, so I set shared_buffers to 16 (which the config file claims is the lowest allowable value) The result was that Postgre refused to start. I upped it to 32 and the result was the same. I looked in both /var/log/messages and in /usr/local/pgsql/logfile and found nothing about _why_ it wouldn't start, There's something broken about your logging setup then, because the postmaster will most definitely tell you why it's not starting: $ postmaster -B 16 postmaster: The number of buffers (-B) must be at least twice the number of allowed connections (-N) and at least 16. $ postmaster -B 32 postmaster: The number of buffers (-B) must be at least twice the number of allowed connections (-N) and at least 16. $ Without having looked at the code, I think that these very-early-startup messages may only get sent to stderr and not syslog; so take another look at what you're doing with the postmaster's stderr. A ... The startup script that the FreeBSD ports install redirects it to /var/log/pgsql, which I hadn't noticed. And I see the exact error messages you describe. Thanks, Tom. -- Bill Moran Potential Technologies http://www.potentialtech.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[ADMIN] Problems tuning shared_buffers value
I'm new to this list. This morning I was experimenting with some tunings for PostgreSQL. (I currently have 7.3.1 installed ... it's behind a firewall so I've been using that as an excuse not to worry about 7.3.2 yet) I was curious about some things, so I set shared_buffers to 16 (which the config file claims is the lowest allowable value) The result was that Postgre refused to start. I upped it to 32 and the result was the same. I looked in both /var/log/messages and in /usr/local/pgsql/logfile and found nothing about _why_ it wouldn't start, so I became bored and commented out shared_buffers (returning it to its default value, I assume) and the postmaster started just fine. I know it's a little weird to be setting shared_buffers that low, but this is an old (almost anchient) machine used for testing and I was wondering how memory-efficient I could make Postgre, even at the expense of speed. I guess the real question is: what is the true minimal value for shared_buffers. Also, shouldn't there be somewhere I can get a more descriptive error message than "postmaster did not start" (in case I had changed multiple values and wasn't sure which one was wrong) TIA for answers. -- Bill Moran Potential Technologies http://www.potentialtech.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
