Re: Question about buffers_alloc in pg_stat_bgwriter view for monitoring
Am 28.03.2018 um 23:38 schrieb Alvar Freude: > Hi all, > > Can someone tell me, what the value of buffers_alloc in the pg_stat_bgwriter > view > (https://www.postgresql.org/docs/current/static/monitoring-stats.html#PG-STAT-BGWRITER-VIEW) > is exactly? Is this the amount of shared buffers used by the bgwriter? As I had to research that anyway, there's no reason not to write this down here as well... (probably simplified, but I'm quite confident the information is correct ;-): Whenever a buffer is allocated, a global counter is incremented (see "StrategyGetBuffer" in storage/buffer/freelist.c. That counter is used by the BGWriter to determine its own wakeup/hibernate strategy, and on-the-fly written to the global stats. Thus, buffer_alloc is the global count of buffers allocated in the cluster. That it appears in the bgwriter statistics is more or less coincidental. Best regards, -- Gunnar "Nick" Bluth RHCE/SCLA Mobil +49 172 8853339 Email: gunnar.bl...@pro-open.de _ In 1984 mainstream users were choosing VMS over UNIX. Ten years later they are choosing Windows over UNIX. What part of that message aren't you getting? - Tom Payne signature.asc Description: OpenPGP digital signature
Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade
Not to come in too late, but have you tried the non-ubuntu: sudo su su -m postgres pg_ctl -D /var/lib/postgresql/9.3/main start cheers Ben On 29 March 2018 at 12:20, Adrian Klaver wrote: > On 03/28/2018 07:29 PM, Ken Beck wrote: > > Comments in line. > > The current log is 0 length, and indeed empty. >> >> I just tried 'sudo pg_ctlcluster 9.3 main start' and got the following >> error: >> >> Error: Config owner (kcb:1000) and data owner (postgres:116) do not >> match, and config owner is not root >> > > So why is your config owned by you and the data owned by postgres? > > Your configuration files should be under: > > /etc/postgresql/9.3/main/ > > and on my Ubuntu setups anyway is owned by postgres. > > >> Changed to user postgres, tried again and go > >> Warning: the cluster will not be running as a systemd service. Consider >> using systemctl: >>sudo systemctl start postgresql@9.3-main >> Removed stale pid file. >> >> Next tried the suggestion: >> >> sudo systemctl start postgresql@9.3-main, but the system failed to >> accept the password for user postgres. That is NOT something I changed >> > > My experience with Ubuntu is that the postgres system user does not have a > password. So where you using the database postgres user password? For more > detail see here: > > https://www.digitalocean.com/community/tutorials/how-to-inst > all-and-use-postgresql-on-ubuntu-16-04 > > during the Ubuntu upgrade process, so that has been a puzzle as well. My >> main login, user kcb is OK, password still works. >> >> So I change back to user kcb and try the suggestion again: >> >> kcb: jobs(0)$ sudo systemctl start postgresql@9.3-main >> Job for postgresql@9.3-main.service failed because the control process >> exited with error code. See "systemctl status >> postgresql@9.3-main.service" and "journalctl -xe" for details. >> kcb: jobs(0)$ systemctl status postgresql@9.3-main.service >> ● postgresql@9.3-main.service - PostgreSQL Cluster 9.3-main >> Loaded: loaded (/lib/systemd/system/postgresql@.service; disabled; >> vendor preset: enabled) >> Active: failed (Result: exit-code) since Wed 2018-03-28 19:51:50 MDT; >> 47s ago >>Process: 2960 ExecStart=postgresql@%i --skip-systemctl-redirect %i >> start (code=exited, status= >> >> Mar 28 19:51:50 kcb-VirtualBox systemd[1]: Starting PostgreSQL Cluster >> 9.3-main... >> Mar 28 19:51:50 kcb-VirtualBox postgresql@9.3-main[2960]: Error: Config >> owner (kcb:1000) and dat >> > > Again the error with the config owner being you. Seems it would help to > track down what copies of the conf files are installed on your machine. > > > Mar 28 19:51:50 kcb-VirtualBox systemd[1]: postgresql@9.3-main.service: >> Control process exited, >> Mar 28 19:51:50 kcb-VirtualBox systemd[1]: Failed to start PostgreSQL >> Cluster 9.3-main. >> Mar 28 19:51:50 kcb-VirtualBox systemd[1]: postgresql@9.3-main.service: >> Unit entered failed stat >> Mar 28 19:51:50 kcb-VirtualBox systemd[1]: postgresql@9.3-main.service: >> Failed with result 'exit >> >> Is it possible a big problem is that the password for user postgres not >> longer works for some reason, not sure why. It appears the password can >> not be rest or changed without knowing the original, and what I thought >> was the original no longer works. >> > > Remember there are two postgres users in play here, the system user and > the database one. Which one are you referring to? > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > -- Dr Ben Madin Managing Director m : +61 448 887 220 e : b...@ausvet.com.au 5 Shuffrey Street, Fremantle Western Australia on the web: www.ausvet.com.au This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this email are the opinion of the writer only and are not endorsed by Ausvet unless expressly stated otherwise. Although Ausvet uses virus scanning software we do not accept liability for viruses or similar in any attachments.
Re: Question about buffers_alloc in pg_stat_bgwriter view for monitoring
Hi,
> Am 29.03.2018 um 10:30 schrieb Gunnar Nick Bluth :
>
> Thus, buffer_alloc is the global count of buffers allocated in the
> cluster. That it appears in the bgwriter statistics is more or less
> coincidental.
So it is the number of shared_buffers used?
This isn’t possible:
postgres=# SELECT
buffers_alloc*current_setting('block_size')::numeric/1024/1024/1024,
current_setting('shared_buffers') FROM pg_stat_bgwriter;
?column?| current_setting
---+-
1219.7707748413085938 | 64450MB
(1 row)
About 64 GB shared buffers and 1219 used? ;-)
Or other machine:
?column? | current_setting
--+-
126.4642944335937500 | 64450MB
(1 row)
My Private:
?column? | current_setting
+-
3.3014221191406250 | 6GB
(1 Zeile)
Ciao
Alvar
--
Alvar C.H. Freude | http://alvar.a-blast.org
https://blog.alvar-freude.de/
https://www.wen-waehlen.de/
Re: Bad Query Plans on 10.3 vs 9.6
David Rowley writes: > On 29 March 2018 at 18:26, Cory Tucker wrote: >> The plan on 9.6 v 10.3 are effectively identical except in 9.6 the planner >> decides to use an index only scan on the primary key and in 10.3 it does a >> sequential scan. The problem is the sequential scan is for a table of 75M >> rows and 25 columns so its quiet a lot of pages it has to traverse. > How certain are you that all the indexes match on each instance? Another possibility is that 10.3 sees the index-only scan as too expensive because it thinks most of the table isn't all-visible. Comparing pg_class.relallvisible values might be informative. regards, tom lane
Re: Bad Query Plans on 10.3 vs 9.6
> > Another possibility is that 10.3 sees the index-only scan as too expensive > because it thinks most of the table isn't all-visible. Comparing > pg_class.relallvisible values might be informative. > > regards, tom lane > I'm happy to try to dig into this one more, however, I'm not familiar with this value. What should I be looking for here?
Re: Bad Query Plans on 10.3 vs 9.6
'On 30 March 2018 at 03:21, Cory Tucker wrote: >> Another possibility is that 10.3 sees the index-only scan as too expensive >> because it thinks most of the table isn't all-visible. Comparing >> pg_class.relallvisible values might be informative. > I'm happy to try to dig into this one more, however, I'm not familiar with > this value. What should I be looking for here? Each table in your database has an entry in the pg_class table. Something like: SELECT relallvisible from pg_class where oid = 'build.household'::regclass; would show you the value, however, I think a problem here is unlikely since that would just control the likelihood of an index-only-scan vs an index-scan. You're getting a Seq-scan, which I imagine is going to be quite a bit more expensive than even an index scan. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: Bad Query Plans on 10.3 vs 9.6
> > Each table in your database has an entry in the pg_class table. Something > like: > > SELECT relallvisible from pg_class where oid = 'build.household'::regclass; > > would show you the value, however, I think a problem here is unlikely > since that would just control the likelihood of an index-only-scan vs > an index-scan. You're getting a Seq-scan, which I imagine is going to > be quite a bit more expensive than even an index scan. > relallvisible has a value of 0 for that table on both databases.
Question about AWS Calculator
I am using http://calculator.s3.amazonaws.com/index.html to calculate the cost of RDS vs EC2. Assuming that I am going for only a 2 node setup (master and slave), few questions: 1. In EC2 how do I indicate that I need two nodes so that I can set up streaming replication between them for HA. Do I mention 2 as number of DB Instance or just add two rows. 2. I understand that in EC2 I can also pick the two nodes in different availability zones and pay for replication data transfer between those zones. Am I right ? 3. In RDS it seems HA is built in without the need to mention nodes, be is same zone or MZ. So how does user pick number of read replicas. thanks.
Re: Bad Query Plans on 10.3 vs 9.6
Cory Tucker writes: > relallvisible has a value of 0 for that table on both databases. That would result in IOS being estimated at the same cost as a regular indexscan, I believe, or very close to that anyway. Is the 10.3 plan parallelized at all? It's possible that the planner thinks a parallel seqscan is faster than a nonparallel indexscan (AFAIR, v10 doesn't have parallel indexscan). The other likely explanation is simply that indexscanning a partitioned table is not considered, or not correctly costed. I'm not very sure what the state of that code is, but certainly all the v10 partitioning logic is still pretty wet behind the ears. regards, tom lane
Re: Question about buffers_alloc in pg_stat_bgwriter view for monitoring
Hi,
> Am 29.03.2018 um 10:30 schrieb Gunnar Nick Bluth :
>
> Thus, buffer_alloc is the global count of buffers allocated in the
> cluster. That it appears in the bgwriter statistics is more or less
> coincidental.
But it seems not to be the total shared_buffers used, but the total number of
allocated and re-allocated buffers. So it increments every time a buffer is
allocated. Maybe I’m the only one who misunderstands it – or someone with
better english then me should update the docs. ;-)
postgres=# SELECT
buffers_alloc*current_setting('block_size')::numeric/1024/1024/1024,
current_setting('shared_buffers') FROM pg_stat_bgwriter;
?column?| current_setting
---+-
1219.7707748413085938 | 64450MB
(1 row)
Or other machine:
?column? | current_setting
--+-
126.4642944335937500 | 64450MB
(1 row)
Small one:
?column? | current_setting
+-
3.3014221191406250 | 6GB
(1 Zeile)
Ciao
Alvar
--
Alvar C.H. Freude | http://alvar.a-blast.org
https://blog.alvar-freude.de/
https://www.wen-waehlen.de/
signature.asc
Description: Message signed with OpenPGP
Re: Bad Query Plans on 10.3 vs 9.6
> > Is the 10.3 plan parallelized at all? It's possible that the planner > thinks a parallel seqscan is faster than a nonparallel indexscan > (AFAIR, v10 doesn't have parallel indexscan). > Its not because its a delete operation, which doesn't support parallel anything. Interestingly, the plain select version of the same tries to do a parallel sequential scan on household instead of using the index. Also, If I change the query to be just regular INNER JOIN with "match" as the driving table, it still does a parallel sequential scan. However, if I change the INNER JOIN to have "household" as the driving table then it will do an index only scan of household. That unfortunately does not help me because I am doing a NOT EXISTS clause and I cannot think of a way to change the query to support that. > > The other likely explanation is simply that indexscanning a partitioned > table is not considered, or not correctly costed. I'm not very sure what > the state of that code is, but certainly all the v10 partitioning logic is > still pretty wet behind the ears. > > regards, tom lane > Could be the partitioning code path, however, I am actually querying the child partition tables directly (which have no further child partitions), so I'd assume it would be the normal code flow. To test, I created another table from the results of the select of the child partitioned table and the query plan looks exactly the same.
dblink: could not send query: another command is already in progress
Hi all I try to use dblink to create a asynchronous logging facility. I have the following code -- open the dblink if it does not yet exist V_DBLINK_CONNECTION_NAME := GET_PROPERTY_VALUE_STRING( I_PROPERTY_NAME => 'DBLINK_CONNECTION_NAME' ); select dblink_get_connections() into V_DBLINK_CONNECTION_NAMES; if ( V_DBLINK_CONNECTION_NAMES is null or V_DBLINK_CONNECTION_NAME != any(V_DBLINK_CONNECTION_NAMES) ) then V_DBLINK_CONNECT_STRING := GET_PROPERTY_VALUE_STRING( I_PROPERTY_NAME => 'DBLINK_CONNECT_STRING' ); -- better to use dblink_connect_u with password file? perform dblink_connect( V_DBLINK_CONNECTION_NAME, V_DBLINK_CONNECT_STRING ); end if; -- send query asynchronously -- Use literal (%L) as it returns the value null as the unquoted -- string NULL. V_QUERY := format( $s$select true $s$ || C_LB || $s$ from %I( $s$ || C_LB || $s$ I_FUNCTION => %L, $s$ || C_LB || $s$ I_MESSAGE => %L, $s$ || C_LB || $s$ I_LEVEL => %L, $s$ || C_LB || $s$ I_PRESENT_USER => %L, $s$ || C_LB || $s$ I_SESSION_USER => %L, $s$ || C_LB || $s$ I_TRANSACTION_TIMESTAMP => $s$ || $s$ %L::timestamp, $s$ || C_LB || $s$ I_TRANSACTION_ID => $s$ || $s$ %L::bigint, $s$ || C_LB || $s$ I_SERVER_PID => $s$ || $s$ %L::bigint, $s$ || C_LB || $s$ I_REMOTE_ADDRESS => $s$ || $s$ %L::inet, $s$ || C_LB || $s$ I_REMOTE_PORT => $s$ || $s$ %L::bigint $s$ || C_LB || $s$ ); $s$ || C_LB || $s$commit $s$, 'WRITE_MESSAGE_TO_TABLE', C_CALLER_FUNCTION, I_MESSAGE, I_LEVEL, C_PRESENT_USER, C_SESSION_USER, C_TRANSACTION_TIMESTAMP, C_TRANSACTION_ID, C_SERVER_PID, C_REMOTE_ADDRESS, C_REMOTE_PORT ); -- send query when connection is ready V_WAIT_FOR := GET_PROPERTY_VALUE_INTERVAL( I_PROPERTY_NAME => 'BUSY_WAIT_INTERVAL' ); -- to avoid continuous re-querying, already queried here -- surprisingly, dblink_is_busy does not return boolean, but 0 for -- false while dblink_is_busy(V_DBLINK_CONNECTION_NAME) != 0 loop perform pg_sleep_for(V_WAIT_FOR); end loop; perform dblink_send_query( V_DBLINK_CONNECTION_NAME, V_QUERY ); raise notice 'Connection busy: %', dblink_is_busy(V_DBLINK_CONNECTION_NAME); raise notice 'Last error: %', dblink_error_message(V_DBLINK_CONNECTION_NAME); raise notice 'Cancel query: %', dblink_cancel_query(V_DBLINK_CONNECTION_NAME); -- ??? commit needed? raise notice 'Connection busy: %', dblink_is_busy(V_DBLINK_CONNECTION_NAME); while dblink_is_busy(V_DBLINK_CONNECTION_NAME) != 0 loop perform pg_sleep_for(V_WAIT_FOR); raise notice 'Waited for commit for % seconds', V_WAIT_FOR; raise notice 'Connection busy: %', dblink_is_busy(V_DBLINK_CONNECTION_NAME); end loop; perform dblink_send_query( V_DBLINK_CONNECTION_NAME, 'commit' ); I get the following output. psql:testing/test.pg_sql:41: NOTICE: Connection busy: 1 psql:testing/test.pg_sql:41: NOTICE: Last error: OK psql:testing/test.pg_sql:41: NOTICE: Cancel query: OK psql:testing/test.pg_sql:41: NOTICE: Connection busy: 0 psql:testing/test.pg_sql:41: NOTICE: could not send query: another command is already in progress I did all the raise notice and dblink querying and cancelling to get some information on what is going on but I am no wiser than before as without that the connection was not busy either. But it was still blocking I had the second call even though the commit did not seem to work and I was trying to send it for good. Btw, there is no entry in the logging table which is being done when the same function is called without using dblink. Maybe I am wrong but I tried the solution with dblink_connect dblink_send_query instead of simply dblink believing that dblink function would open and close a connection at every call. I wante
Asynchronous Trigger?
Is it possible to have the execution of a trigger (or any function) not block the completion of the statement they are associated with? A pattern I had hoped to implement was to do a quick update of rows that signaled they needed attention, and then an async per-row trigger would come and do the maintenance (in this case, make an expensive materialized view). Any suggestions welcome. thanks! --Cory
Autovacuum behavior with rapid insert/delete 9.6
Using mostly default parameters with 9.3 we are seeing reasonable performance with our heavy transaction-based application. However, when we are upgraded to 9.6 we are seeing horrible performance with the same loads using the same hardware. Gathering the configuration details, but I think I've spotted something that might point to the problem. Our application has a repeated pattern of inserting a customer invoice and details sending a message to another application which reads the transaction, inserts it on another database server, and the deletes the original. In looking at pg_stat_activity I notice under the same load, autovacuum seems to be running way more often under 9.6 and hanging around longer in the activity queue. Also, I notice that IO run queue is like 10x deeper and write latency balloons from < 10ms to around 300/400msec. Clearly something is slamming the IO channel. Granted I see there are many new autovacuum variables that likely have to be tuned, Also, we've really got to redesign the application logic, it seems moronic to have rapid insert/deletes in a handful of tables that is likely to overwhelm autovacuum for no good reason. What I am really asking to confirm is after describing the situation is it reasonable to focus on (in the short term) tuning autovacuum to increase performance or does this not make sense given the workload and I should look elsewhere? Thanks.
Re: Autovacuum behavior with rapid insert/delete 9.6
On Thu, Mar 29, 2018 at 4:01 PM, Ted Filmore wrote: > What I am really asking to confirm is after describing the situation is it > reasonable to focus on (in the short term) tuning autovacuum to increase > performance or does this not make sense given the workload and I should look > elsewhere? I would look into this suspected 9.5 regression, if that's possible: https://postgr.es/m/CAH2-Wz=sfakvmv1x9jh19ej8am8tzn9f-yecips9hrrrqss...@mail.gmail.com -- Peter Geoghegan
Re: dblink: could not send query: another command is already in progress
Thiemo Kellner wrote: > I try to use dblink to create a asynchronous logging facility. I have > the following code > > [...] > perform dblink_send_query( > V_DBLINK_CONNECTION_NAME, > V_QUERY > ); > raise notice 'Connection busy: %', dblink_is_busy(V_DBLINK_CONNECTION_NAME); > raise notice 'Last error: %', > dblink_error_message(V_DBLINK_CONNECTION_NAME); > raise notice 'Cancel query: %', > dblink_cancel_query(V_DBLINK_CONNECTION_NAME); > -- ??? commit needed? > raise notice 'Connection busy: %', dblink_is_busy(V_DBLINK_CONNECTION_NAME); > while dblink_is_busy(V_DBLINK_CONNECTION_NAME) != 0 loop > perform pg_sleep_for(V_WAIT_FOR); > raise notice 'Waited for commit for % seconds', V_WAIT_FOR; > raise notice 'Connection busy: %', dblink_is_busy(V_DBLINK_CONNECTION_NAME); > end loop; > perform dblink_send_query( > V_DBLINK_CONNECTION_NAME, > 'commit' > ); > > I get the following output. > psql:testing/test.pg_sql:41: NOTICE: Connection busy: 1 > > psql:testing/test.pg_sql:41: NOTICE: Last error: OK > > psql:testing/test.pg_sql:41: NOTICE: Cancel query: OK > > psql:testing/test.pg_sql:41: NOTICE: Connection busy: 0 > > psql:testing/test.pg_sql:41: NOTICE: could not send query: another > command is already in progress > Has anyone an idea? The cause of the error message is clear; as the documentation says: dblink_cancel_query attempts to cancel any query that is in progress on the named connection. Note that this is not certain to succeed (since, for example, the remote query might already have finished). A cancel request simply improves the odds that the query will fail soon. You must still complete the normal query protocol, for example by calling dblink_get_result. Not sure if that answers all your questions. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Asynchronous Trigger?
Cory Tucker wrote: > Is it possible to have the execution of a trigger (or any function) not block > the > completion of the statement they are associated with? Constraint triggers can be deferred to the end of the transaction, but I am certain that is not what you are looking for. > A pattern I had hoped to implement was to do a quick update of rows that > signaled > they needed attention, and then an async per-row trigger would come and do the > maintenance (in this case, make an expensive materialized view). > > Any suggestions welcome. This seems like a really bad idea - even if it happens "in the background", would you want an expensive materialized view to be updated whenever a row is modified? If you modify three rows, that would happen three times in a row! One solution would be to set a flag somewhere that marks the materialized view dirty, and a regular cron-job that checks the flag and rebuilds the view. With the asynchronous solution you had in mind, the materialized view would not be up to date anyway. Other than that, if your materialized view definition is simple enough, you might be able to use a regular table and a trigger that updates the table to reflect the modifications caused by the insert, update or delete. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
