Re: libpq pipelineing
Here is a short example: https://gist.github.com/ioquatix/2f08f78699418f65971035785c80cf18 It makes 10 queries in one "PQsendQuery" and sets single row mode. But all the results come back at once as shown by the timestamps. Next I'm planning to investigate streaming large recordsets to see if it works better/incrementally.
Re: error messages (autovaccum canceled and syntax errors) while loading a DUMP
El día Donnerstag, Juni 25, 2020 a las 04:25:00 -0400, Tom Lane escribió: > Matthias Apitz writes: > > On Thursday, 25 June 2020 21:41:54 CEST, Tom Lane > > wrote: > >> regardless of the exact details, it seems like the most likely theory > >> about what is happening is that the dump file is corrupt and the > >> corruption is causing the de-gzipped output to be missing or > >> duplicating chunks of text. > > > I think, that a > > gzip -dc foo > > will work, regardless of the file names extensiom not beeing .gz > > Agreed, that gzip command won't care. I was concerned about what > might've happened in between. > > ... I restarted the server, created a new database with: $ /usr/local/sisis-pap/pgsql/bin/createdb -U sisis -T template0 testdump and loaded the DUMP while no other processes were connected to the server with: $ gzip -dc newanna_export.dmp | /usr/local/sisis-pap/pgsql/bin/psql -U sisis -dtestdump all went fine (table creations, COPYs into them, creating indexs) without any error message in the terminal. In the serverlog only these messages came out: 2020-06-27 10:16:38.778 CEST [18094] LOG: database system is ready to accept connections 2020-06-27 10:21:26.062 CEST [18096] LOG: checkpoints are occurring too frequently (25 seconds apart) 2020-06-27 10:21:26.062 CEST [18096] HINT: Consider increasing the configuration parameter "max_wal_size". 2020-06-27 10:21:52.436 CEST [18096] LOG: checkpoints are occurring too frequently (26 seconds apart) 2020-06-27 10:21:52.436 CEST [18096] HINT: Consider increasing the configuration parameter "max_wal_size". 2020-06-27 10:22:07.424 CEST [18302] ERROR: canceling autovacuum task 2020-06-27 10:22:07.424 CEST [18302] CONTEXT: automatic analyze of table "testdump.public.d01buch" 2020-06-27 10:22:14.545 CEST [18302] ERROR: canceling autovacuum task 2020-06-27 10:22:14.545 CEST [18302] CONTEXT: automatic analyze of table "testdump.public.d02zus" 2020-06-27 10:22:16.051 CEST [18249] ERROR: canceling autovacuum task 2020-06-27 10:22:16.051 CEST [18249] CONTEXT: automatic analyze of table "testdump.public.d03geb" 2020-06-27 10:22:18.331 CEST [18249] ERROR: canceling autovacuum task 2020-06-27 10:22:18.331 CEST [18249] CONTEXT: automatic analyze of table "testdump.public.d11loesch" 2020-06-27 10:22:19.718 CEST [18302] ERROR: canceling autovacuum task 2020-06-27 10:22:19.718 CEST [18302] CONTEXT: automatic analyze of table "testdump.public.d11rueck" 2020-06-27 10:22:23.034 CEST [18249] ERROR: canceling autovacuum task 2020-06-27 10:22:23.034 CEST [18249] CONTEXT: automatic analyze of table "testdump.public.d35buchnotiz" 2020-06-27 10:22:38.902 CEST [18302] ERROR: canceling autovacuum task 2020-06-27 10:22:38.902 CEST [18302] CONTEXT: automatic analyze of table "testdump.public.idm_statistic" 2020-06-27 10:22:59.332 CEST [18302] ERROR: canceling autovacuum task 2020-06-27 10:22:59.332 CEST [18302] CONTEXT: automatic analyze of table "testdump.public.idm_targetlog" 2020-06-27 10:23:01.493 CEST [18302] ERROR: canceling autovacuum task 2020-06-27 10:23:01.493 CEST [18302] CONTEXT: automatic analyze of table "testdump.public.idm_targettab" 2020-06-27 10:23:02.631 CEST [18302] ERROR: canceling autovacuum task 2020-06-27 10:23:02.631 CEST [18302] CONTEXT: automatic analyze of table "testdump.public.idm_tasktab" The messages about 'ERROR: canceling autovacuum task' appeared while the indexes were created. The database has around 400 tables and the uncompressed dump is around 3.6 GByte: $ gzip -dc newanna_export.dmp | wc -c 3643850597 So, the syntax error messages were caused by some other process (and we have already an idea which cron job it was). What must be done re/ the messages about 'ERROR: canceling autovacuum task', or can we just ignore them? Thanks matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!
Re: error messages (autovaccum canceled and syntax errors) while loading a DUMP
On 6/27/20 1:37 AM, Matthias Apitz wrote: El día Donnerstag, Juni 25, 2020 a las 04:25:00 -0400, Tom Lane escribió: Matthias Apitz writes: On Thursday, 25 June 2020 21:41:54 CEST, Tom Lane wrote: regardless of the exact details, it seems like the most likely theory about what is happening is that the dump file is corrupt and the corruption is causing the de-gzipped output to be missing or duplicating chunks of text. I think, that a gzip -dc foo will work, regardless of the file names extensiom not beeing .gz Agreed, that gzip command won't care. I was concerned about what might've happened in between. ... I restarted the server, created a new database with: $ /usr/local/sisis-pap/pgsql/bin/createdb -U sisis -T template0 testdump and loaded the DUMP while no other processes were connected to the server with: $ gzip -dc newanna_export.dmp | /usr/local/sisis-pap/pgsql/bin/psql -U sisis -dtestdump all went fine (table creations, COPYs into them, creating indexs) without any error message in the terminal. In the serverlog only these messages came out: 2020-06-27 10:16:38.778 CEST [18094] LOG: database system is ready to accept connections 2020-06-27 10:21:26.062 CEST [18096] LOG: checkpoints are occurring too frequently (25 seconds apart) 2020-06-27 10:21:26.062 CEST [18096] HINT: Consider increasing the configuration parameter "max_wal_size". 2020-06-27 10:21:52.436 CEST [18096] LOG: checkpoints are occurring too frequently (26 seconds apart) 2020-06-27 10:21:52.436 CEST [18096] HINT: Consider increasing the configuration parameter "max_wal_size". 2020-06-27 10:22:07.424 CEST [18302] ERROR: canceling autovacuum task 2020-06-27 10:22:07.424 CEST [18302] CONTEXT: automatic analyze of table "testdump.public.d01buch" 2020-06-27 10:22:14.545 CEST [18302] ERROR: canceling autovacuum task 2020-06-27 10:22:14.545 CEST [18302] CONTEXT: automatic analyze of table "testdump.public.d02zus" 2020-06-27 10:22:16.051 CEST [18249] ERROR: canceling autovacuum task 2020-06-27 10:22:16.051 CEST [18249] CONTEXT: automatic analyze of table "testdump.public.d03geb" 2020-06-27 10:22:18.331 CEST [18249] ERROR: canceling autovacuum task 2020-06-27 10:22:18.331 CEST [18249] CONTEXT: automatic analyze of table "testdump.public.d11loesch" 2020-06-27 10:22:19.718 CEST [18302] ERROR: canceling autovacuum task 2020-06-27 10:22:19.718 CEST [18302] CONTEXT: automatic analyze of table "testdump.public.d11rueck" 2020-06-27 10:22:23.034 CEST [18249] ERROR: canceling autovacuum task 2020-06-27 10:22:23.034 CEST [18249] CONTEXT: automatic analyze of table "testdump.public.d35buchnotiz" 2020-06-27 10:22:38.902 CEST [18302] ERROR: canceling autovacuum task 2020-06-27 10:22:38.902 CEST [18302] CONTEXT: automatic analyze of table "testdump.public.idm_statistic" 2020-06-27 10:22:59.332 CEST [18302] ERROR: canceling autovacuum task 2020-06-27 10:22:59.332 CEST [18302] CONTEXT: automatic analyze of table "testdump.public.idm_targetlog" 2020-06-27 10:23:01.493 CEST [18302] ERROR: canceling autovacuum task 2020-06-27 10:23:01.493 CEST [18302] CONTEXT: automatic analyze of table "testdump.public.idm_targettab" 2020-06-27 10:23:02.631 CEST [18302] ERROR: canceling autovacuum task 2020-06-27 10:23:02.631 CEST [18302] CONTEXT: automatic analyze of table "testdump.public.idm_tasktab" The messages about 'ERROR: canceling autovacuum task' appeared while the indexes were created. The database has around 400 tables and the uncompressed dump is around 3.6 GByte: $ gzip -dc newanna_export.dmp | wc -c 3643850597 So, the syntax error messages were caused by some other process (and we have already an idea which cron job it was). What must be done re/ the messages about 'ERROR: canceling autovacuum task', or can we just ignore them? From what I understand they are occurring because the machine is to busy doing the restore to get to the autovacuum task in a timely manner. So I would say ignore and check back later to see that the autovacuum is working. Given that it is ANALYZE that is being cancelled I would run a manual ANALYZE after the restore is done to update the database statistics. Thanks matthias -- Adrian Klaver adrian.kla...@aklaver.com
Re: libpq pipelineing
Samuel Williams writes: > Here is a short example: > https://gist.github.com/ioquatix/2f08f78699418f65971035785c80cf18 > It makes 10 queries in one "PQsendQuery" and sets single row mode. But > all the results come back at once as shown by the timestamps. That looks to be less about what libpq will do than what the Ruby interface code will do. The volume of return data may also be an issue. I don't think the backend will flush data out to the client except when it (a) reaches an idle state or (b) fills the output buffer. Ten occurrences of a short query result aren't gonna be enough for (b) --- from memory, that buffer is probably 8KB. regards, tom lane
Re: error messages (autovaccum canceled and syntax errors) while loading a DUMP
El día Samstag, Juni 27, 2020 a las 07:21:21 -0700, Adrian Klaver escribió: > > 2020-06-27 10:23:02.631 CEST [18302] ERROR: canceling autovacuum task > > 2020-06-27 10:23:02.631 CEST [18302] CONTEXT: automatic analyze of table > > "testdump.public.idm_tasktab" > > > From what I understand they are occurring because the machine is to busy > doing the restore to get to the autovacuum task in a timely manner. So I > would say ignore and check back later to see that the autovacuum is working. > Given that it is ANALYZE that is being cancelled I would run a manual > ANALYZE after the restore is done to update the database statistics. The machine is a development server and no one was working on it (today is Saturday) apart of me. It has 4 modern and fast CPU, Running ANALYZE VERBOSE does not give any unusual output. Only for each table lines like: ... INFO: analyzing "public.z39t_term" INFO: "z39t_term": scanned 2 of 2 pages, containing 135 live rows and 0 dead rows; 135 rows in sample, 135 estimated total rows INFO: analyzing "public.z39t_trunc" INFO: "z39t_trunc": scanned 1 of 1 pages, containing 135 live rows and 0 dead rows; 135 rows in sample, 135 estimated total rows ... How could I check that the autovacuum is working? Thanks matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!
Re: error messages (autovaccum canceled and syntax errors) while loading a DUMP
Matthias Apitz writes: > El día Samstag, Juni 27, 2020 a las 07:21:21 -0700, Adrian Klaver escribió: >>> 2020-06-27 10:23:02.631 CEST [18302] ERROR: canceling autovacuum task >>> 2020-06-27 10:23:02.631 CEST [18302] CONTEXT: automatic analyze of table >>> "testdump.public.idm_tasktab" >> From what I understand they are occurring because the machine is to busy >> doing the restore to get to the autovacuum task in a timely manner. So I >> would say ignore and check back later to see that the autovacuum is working. >> Given that it is ANALYZE that is being cancelled I would run a manual >> ANALYZE after the restore is done to update the database statistics. > The machine is a development server and no one was working on it (today > is Saturday) apart of me. It has 4 modern and fast CPU, I think more likely is this: 1. The restore run loads a lot of data into a table. 2. The autovacuum daemon notices all the inserts and starts an auto-analyze run. 3. The restore run gets to the part where it tries to create indexes on the table. This requires a ShareLock if memory serves, which conflicts with the ShareUpdateExclusive lock that ANALYZE is holding. Rather than making a foreground process wait for autovacuum, we kick autovacuum off its lock by aborting the auto-analyze. Hence the message(s). 4. The un-analyzed inserts are still there, so some future autovacuum cycle will try again. In short, yeah, you should just ignore these log messages. regards, tom lane
Re: n_distinct off by a factor of 1000
On the topic of n_distinct. I am not sure whether I am misinterpreting something, or if it is a bug (probably former) however, when using partitions, are not n_distinct_inherited supposed to propagate to the child partitions? It does not seem to do so. (Yes, I run Analyze after setting the variable) I had to set the n_distinct separately on all partitions to get the desired planer behavior, but I thought that setting n_distinct_inherited was supposed to prevent manually setting the partitions. K
Re: n_distinct off by a factor of 1000
> I am not sure whether I am misinterpreting something, or if it is a bug > (probably former) however, when using partitions, are not > n_distinct_inherited supposed to propagate to the child partitions? It does > not seem to do so. (Yes, I run Analyze after setting the variable) I had to > set the n_distinct separately on all partitions to get the desired planer > behavior, but I thought that setting n_distinct_inherited was supposed to > prevent manually setting the partitions. I follow up with a dbfiddle for this problem: https://www.db-fiddle.com/f/f5a2kPaq71G4yH8EAuXQV6/1 best regards K
Re: error messages (autovaccum canceled and syntax errors) while loading a DUMP
On 6/27/20 10:52 AM, Matthias Apitz wrote: El día Samstag, Juni 27, 2020 a las 07:21:21 -0700, Adrian Klaver escribió: 2020-06-27 10:23:02.631 CEST [18302] ERROR: canceling autovacuum task 2020-06-27 10:23:02.631 CEST [18302] CONTEXT: automatic analyze of table "testdump.public.idm_tasktab" From what I understand they are occurring because the machine is to busy doing the restore to get to the autovacuum task in a timely manner. So I would say ignore and check back later to see that the autovacuum is working. Given that it is ANALYZE that is being cancelled I would run a manual ANALYZE after the restore is done to update the database statistics. The machine is a development server and no one was working on it (today is Saturday) apart of me. It has 4 modern and fast CPU, Running ANALYZE VERBOSE does not give any unusual output. Only for each table lines like: I would not expect that it would. The purpose for running it was to make sure the statistics for the tables in the database where up to date. ... INFO: analyzing "public.z39t_term" INFO: "z39t_term": scanned 2 of 2 pages, containing 135 live rows and 0 dead rows; 135 rows in sample, 135 estimated total rows INFO: analyzing "public.z39t_trunc" INFO: "z39t_trunc": scanned 1 of 1 pages, containing 135 live rows and 0 dead rows; 135 rows in sample, 135 estimated total rows ... How could I check that the autovacuum is working? Two ways: 1) Look in the logs for autovacuum lines. 2) Query the pg_stat_all_tables view. https://www.postgresql.org/docs/12/monitoring-stats.html#PG-STAT-ALL-TABLES-VIEW Thanks matthias -- Adrian Klaver adrian.kla...@aklaver.com
