Re: Error creating postgres extensions
On Tue, May 4, 2021 at 10:04 PM Ian Lawrence Barwick wrote: > 2021年5月5日(水) 10:43 Tiffany Thang : > > > > Hi, > > > > I'm having trouble finding in the documentation the steps to install the > postgres extensions such as pg_stat_statements and pg_trgm on > PG13.1/CentOS7. Can someone please assist? > > > > > > postgres=# create extension pg_stat_statements; > > > > ERROR: could not open extension control file > "/usr/pgsql-13/share/extension/pg_stat_statements.control": No such file or > directory > > > > postgres=# create extension pg_trgm; > > > > ERROR: could not open extension control file > "/usr/pgsql-13/share/extension/pg_trgm.control": No such file or directory > > > > > > > > I installed PG13 via the YUM repository > https://download.postgresql.org/pub/repos/yum/13/redhat/rhel-7-x86_64 and > then > > > > I ran "yum install postgresql13 postgresql13-server" > > You need the "postgresql13-contrib" package as well. > > > Also, what are the steps to update these extensions in the future when > newer releases are made available. For example, updating > > > > pg_stat_statements from 1.7 to 1.8? > > Normally new extension versions (at least for the core contrib > modules) are provided with > each major version release, so that will be take care of when > performing a major version upgrade. > > If you do ever need to update an extension, the process is: > - install new binary > - execute "ALTER EXTENSION UPDATE" > - if the extension provides a shared library, PostgreSQL will need to > be restarted to activate the new library > > Regards > > Ian Barwick > > > -- > EnterpriseDB: https://www.enterprisedb.com Thanks Ian for your help! I was able to create the extensions.
Re: Huge performance penalty with parallel queries in Windows x64 v. Linux x64
On Wed, May 5, 2021 at 2:12 PM Thomas Munro wrote: > It might be interesting to know how that 40ms time scales as you add > more workers. ... Another thought: I'd also try tests like that in large databases (ie large virtual memory) vs small ones, and with and without huge/locked memory pages configured[1], to see how much of the overhead is due to virtual memory work (though I know nothing about Windows VM, it's just an idea). [1] https://www.postgresql.org/docs/13/runtime-config-resource.html#GUC-HUGE-PAGES
Re: Huge performance penalty with parallel queries in Windows x64 v. Linux x64
On Wed, May 5, 2021 at 3:50 AM Hans Buschmann wrote: > (BTW: Is this cost multiplied by the real count of workers choosen > (max_parallel_workers_per_gather) or only a value independent of the number > of workers?. This would matter in windows-high-parallel scenarios) It's not multiplied: https://github.com/postgres/postgres/blob/50e17ad281b8d1c1b410c9833955bc80fbad4078/src/backend/optimizer/path/costsize.c#L398 It might be interesting to know how that 40ms time scales as you add more workers. For what it's worth, I see that the following query takes around about ~6ms + ~1.5ms per worker on my FreeBSD machine, and on Linux it's harder to pin down, varying a lot, usually a bit slower (sorry I didn't have time to do proper statistics). create table t (); alter table t set (parallel_workers=8); set min_parallel_table_scan_size = 0; set parallel_setup_cost = 0; set parallel_tuple_cost = 0; set max_parallel_workers_per_gather = 1; explain analyze select count(*) from t; ... set max_parallel_workers_per_gather = 7; explain analyze select count(*) from t;
Re: Error creating postgres extensions
2021年5月5日(水) 10:43 Tiffany Thang : > > Hi, > > I'm having trouble finding in the documentation the steps to install the > postgres extensions such as pg_stat_statements and pg_trgm on PG13.1/CentOS7. > Can someone please assist? > > > postgres=# create extension pg_stat_statements; > > ERROR: could not open extension control file > "/usr/pgsql-13/share/extension/pg_stat_statements.control": No such file or > directory > > postgres=# create extension pg_trgm; > > ERROR: could not open extension control file > "/usr/pgsql-13/share/extension/pg_trgm.control": No such file or directory > > > > I installed PG13 via the YUM repository > https://download.postgresql.org/pub/repos/yum/13/redhat/rhel-7-x86_64 and then > > I ran "yum install postgresql13 postgresql13-server" You need the "postgresql13-contrib" package as well. > Also, what are the steps to update these extensions in the future when newer > releases are made available. For example, updating > > pg_stat_statements from 1.7 to 1.8? Normally new extension versions (at least for the core contrib modules) are provided with each major version release, so that will be take care of when performing a major version upgrade. If you do ever need to update an extension, the process is: - install new binary - execute "ALTER EXTENSION UPDATE" - if the extension provides a shared library, PostgreSQL will need to be restarted to activate the new library Regards Ian Barwick -- EnterpriseDB: https://www.enterprisedb.com
Error creating postgres extensions
Hi, I'm having trouble finding in the documentation the steps to install the postgres extensions such as pg_stat_statements and pg_trgm on PG13.1/CentOS7. Can someone please assist? postgres=# create extension pg_stat_statements; ERROR: could not open extension control file "/usr/pgsql-13/share/extension/pg_stat_statements.control": No such file or directory postgres=# create extension pg_trgm; ERROR: could not open extension control file "/usr/pgsql-13/share/extension/pg_trgm.control": No such file or directory I installed PG13 via the YUM repository https://download.postgresql.org/pub/repos/yum/13/redhat/rhel-7-x86_64 and then I ran "yum install postgresql13 postgresql13-server" Also, what are the steps to update these extensions in the future when newer releases are made available. For example, updating pg_stat_statements from 1.7 to 1.8? Thanks. Tiff
Re: Postgres upgrade 12 - issues with OIDs
On Wed, May 5, 2021 at 07:49:29AM +1000, Venkata B Nagothi wrote:
> Hi There,
>
> We are attempting to upgrade our Postgres databases from 9.5 to 12.5 using
> pg_upgrade link mode and are facing issues with OIDs.
>
> ALTER TABLE... SET WITHOUT OIDs on the larger tables is taking very long and
> is
> locking up the table as well. We do have tables of more than 1 TB of size.
> Is there any way to make this complete faster ? Any suggestions would be
> great.
Uh, I see this on our code:
pg_fatal("Your installation contains tables declared WITH OIDS, which
is not\n"
"supported anymore. Consider removing the oid column using\n"
"ALTER TABLE ... SET WITHOUT OIDS;\n"
"A list of tables with the problem is in the file:\n"
"%s\n\n", output_path);
Uh, I don't know of any way to speed that up, though it might be faster
if it was done while no one else was accessing the table. I see this
comment in our PG 11 code:
/*
* If we dropped the OID column, must adjust pg_class.relhasoids and tell
* Phase 3 to physically get rid of the column. We formerly left the
* column in place physically, but this caused subtle problems. See
* http://archives.postgresql.org/pgsql-hackers/2009-02/msg00363.php
*/
--
Bruce Momjian https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
Postgres upgrade 12 - issues with OIDs
Hi There, We are attempting to upgrade our Postgres databases from 9.5 to 12.5 using pg_upgrade link mode and are facing issues with OIDs. ALTER TABLE... SET WITHOUT OIDs on the larger tables is taking very long and is locking up the table as well. We do have tables of more than 1 TB of size. Is there any way to make this complete faster ? Any suggestions would be great. Regards, Venkata B N Database Consultant
Re: Extended stats - value not in MCV list
Hi, this topic would probably be a better fit for pgsql-hackers, as it's about the internal implementation, but let's continue the discussion here for now. On 5/2/21 6:52 AM, Pedro Luis Guzmán Hernández wrote: Hi there, I've just started using extended stats cause the planner was giving me terrible estimates for a certain table. MCV extended stats solved my problem when values are in the extended MCV list, but estimates are still terrible when they are not in the MCV list. Limited as my knowledge of database internals is, I dug into the source code and found an important difference on how these not-MCV values are handled in the single-column and multi-column cases. Hmm, so which which PostgreSQL version are you testing this on? I'm not sure which code branch you're looking at. Can you share a reproducer, i.e. a simple example demonstrating the issue? That'd be very helpful. For single columns, the estimate is calculated as follows: selectivity = (1 - sum(MCV_frequencies)) / (distinct_values - count(MCV)) Which seems to assume a uniform distribution of non-MCV values and looks like a sensible guess, at least to my amateur eyes. For multi-column statistics it seems to me that the estimate is calculated instead as: selectivity = 1 - sum(MCV_frequencies) Which instead seems to assume that the value could potentially be present in all the rows not covered by the MCV. This seems like an adequate upper bound, but is also quite conservative compared to the single-column estimate. In my specific case this yields a selectivity even higher than some of the least frequent MCV values, which is a condition that is actually checked for and compensated in the single-column estimate as an additional check. I have MCV and distinct extended stats, so I know the distinct_values stats is available. It shouldn't behave like that, and some quick experiments suggest it does not (at least on master). I can't rule out a bug, of course. A reproducer would be helpful. So I hope my question is clear from the above. How come the estimates are calculated with such different approaches? I insist I have no experience with database/query planner development, so excuse me if I am overlooking some obvious conceptual difference between single-column and multi-column stats. The single-column estimate is actually described in the documentation, but the multi-column estimate is not. If there is indeed a good reason for this difference, I think it should be documented. As for the ndistinct estimates and multi-column MCV lists, it's not all that simple - there may be conditions on only some of the columns, in which case we don't know how many groups we actually matched, etc. TBH I'm not sure how much of those implementation details we want to put into the user docs - it may be a bit too much, and we may need to change some of it. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: PostgreSQL upgrade from 10 to 12 fails with "ERROR: column r.relhasoids does not exist at character 1616"
On Tue, May 4, 2021 at 12:43:36PM -0400, Tom Lane wrote:
> Arne Henrik Segtnan writes:
> > When trying to upgrade from PostgreSQL 10 to 12, the installation fails as
> > shown below.
>
> > 2021-05-04 13:06:05.614 CEST [12890] postgres@zabbix_db ERROR: column
> > r.relhasoids does not exist at character 1616
> > 2021-05-04 13:06:05.614 CEST [12890] postgres@zabbix_db STATEMENT:
> > -- For binary upgrade, must preserve pg_type oid
>
> > SELECT
> > pg_catalog.binary_upgrade_set_next_pg_type_oid('16'::pg_catalog.oid);
> > -- For binary upgrade, must preserve pg_type array oid
> > SELECT
> > pg_catalog.binary_upgrade_set_next_array_pg_type_oid('162221'::pg_catalog.oid);
>
>
> > -- For binary upgrade, must preserve pg_class oids
> > SELECT
> > pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('162220'::pg_catalog.oid);
>
>
> > CREATE VIEW "repack"."tables" AS
> > SELECT ("r"."oid")::"regclass" AS "relname",
> > "r"."oid" AS "relid",
> > "r"."reltoastrelid",
> > CASE
> > .
> > .
> > .
>
> You didn't show us the rest of the command, but it looks like
> this view contains a reference to pg_class.relhasoids. As
> Bruce noted, that column no longer exists, so you're not going
> to be able to transparently upgrade this view.
>
> I don't know what this view is from, though the schema name
> "repack" is suggestive. Possibly you could drop whatever it's
> a part of, and then reinstall an updated version after upgrading?
Yeah, Tom, I think you are on to something. I couldn't figure out where
the r.relhasoids was referenced in the rest of the log output, but if it
is on the lines that are part of CREATE VIEW, it certainly could be the
case that the view references a pre-PG-12 column of pg_class. To fix
it, Tom is right that removing the view then recreating it with PG-12
pg_class column assumptions is the right fix. I actually rarely see
this kind of failure.
--
Bruce Momjian https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
Re: AW: Huge performance penalty with parallel queries in Windows x64 v. Linux x64
Hans Buschmann writes: > In my opinion, even for Linux the default for parallel_setup_cost is set too > low (1000). It should reflect the sequential access of 1000 pages, which > normally is faster from buffer cache on modern hardware. I tend to agree with you that the default costs for parallel queries are far too optimistic --- we've seen enough complaints about parallelism making things slower to make that clear. However, proposing some new numbers without gathering some hard evidence to support them is not helpful. regards, tom lane
Re: PostgreSQL upgrade from 10 to 12 fails with "ERROR: column r.relhasoids does not exist at character 1616"
Arne Henrik Segtnan writes:
> When trying to upgrade from PostgreSQL 10 to 12, the installation fails as
> shown below.
> 2021-05-04 13:06:05.614 CEST [12890] postgres@zabbix_db ERROR: column
> r.relhasoids does not exist at character 1616
> 2021-05-04 13:06:05.614 CEST [12890] postgres@zabbix_db STATEMENT:
> -- For binary upgrade, must preserve pg_type oid
> SELECT
> pg_catalog.binary_upgrade_set_next_pg_type_oid('16'::pg_catalog.oid);
> -- For binary upgrade, must preserve pg_type array oid
> SELECT
> pg_catalog.binary_upgrade_set_next_array_pg_type_oid('162221'::pg_catalog.oid);
> -- For binary upgrade, must preserve pg_class oids
> SELECT
> pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('162220'::pg_catalog.oid);
> CREATE VIEW "repack"."tables" AS
>SELECT ("r"."oid")::"regclass" AS "relname",
> "r"."oid" AS "relid",
> "r"."reltoastrelid",
> CASE
> .
> .
> .
You didn't show us the rest of the command, but it looks like
this view contains a reference to pg_class.relhasoids. As
Bruce noted, that column no longer exists, so you're not going
to be able to transparently upgrade this view.
I don't know what this view is from, though the schema name
"repack" is suggestive. Possibly you could drop whatever it's
a part of, and then reinstall an updated version after upgrading?
regards, tom lane
Re: PostgreSQL upgrade from 10 to 12 fails with "ERROR: column r.relhasoids does not exist at character 1616"
On Tue, May 4, 2021 at 6:38 PM Bruce Momjian wrote: > > On Tue, May 4, 2021 at 06:29:17PM +0200, Arne Henrik Segtnan wrote: > > Hi, > > > > When trying to upgrade from PostgreSQL 10 to 12, the installation fails as > > shown below. > > > > # pg_upgradecluster --link --method=upgrade 10 main /dbdata/zabbix > > > > > > From PosgreSQL log: > > 2021-05-04 13:06:04.780 CEST [12861] LOG: database system is ready to > > accept > > connections > > done > > server started > > . > > . > > 2021-05-04 13:06:05.614 CEST [12890] postgres@zabbix_db ERROR: column > > r.relhasoids does not exist at character 1616 > > pg_class.relhasoids was removed in PG 12. > > > - Other upgrade methods (clone/dump) fails with the same errors. > > OK, so the problem is really pg_dump. > > > Anyone seen similar issue? > > Nope, something very odd going on here. Do you by any chance have more than one version of PostgreSQL running at the same time for different clusters? It kind of sounds like it's picking up the wrong version at some point. -- Magnus Hagander Me: https://www.hagander.net/ Work: https://www.redpill-linpro.com/
Re: PostgreSQL upgrade from 10 to 12 fails with "ERROR: column r.relhasoids does not exist at character 1616"
On Tue, May 4, 2021 at 06:29:17PM +0200, Arne Henrik Segtnan wrote: > Hi, > > When trying to upgrade from PostgreSQL 10 to 12, the installation fails as > shown below. > > # pg_upgradecluster --link --method=upgrade 10 main /dbdata/zabbix > > > From PosgreSQL log: > 2021-05-04 13:06:04.780 CEST [12861] LOG: database system is ready to accept > connections > done > server started > . > . > 2021-05-04 13:06:05.614 CEST [12890] postgres@zabbix_db ERROR: column > r.relhasoids does not exist at character 1616 pg_class.relhasoids was removed in PG 12. > - Other upgrade methods (clone/dump) fails with the same errors. OK, so the problem is really pg_dump. > Anyone seen similar issue? Nope, something very odd going on here. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
AW: Huge performance penalty with parallel queries in Windows x64 v. Linux x64
Thank you Thomas for pointing me to this GUC which I haven't realized before. >From the documentation I take that a cost of 1.0 is set for a sequential page >fetch. In my opinion, even for Linux the default for parallel_setup_cost is set too low (1000). It should reflect the sequential access of 1000 pages, which normally is faster from buffer cache on modern hardware. For Windows, these costs are much higher, so I would propose to set the default to at least 1, perhaps 25000 to reflect the real parallel overhead. (BTW: Is this cost multiplied by the real count of workers choosen (max_parallel_workers_per_gather) or only a value independent of the number of workers?. This would matter in windows-high-parallel scenarios) The inadequate default gives more and more slower-then-necessary plans when people are moving to newer PG versions with good parallel support. For them it's like for me a little surprise, which most won't even notice or remedy nor full understand. For bigger installations the knowledge of query tuning is more probable and people can react on their real situation. Perhaps someone with more knowledge with parallel queries can make some profiling / performance tests to justify my proposals (e.g. what is the sequential page access equivalent of 40 ms on selected platforms): New defaults proposal: -- Linux and comparable architectures with fast process creation: parallel_setup_cost = 2500 -- Windows parallel_setup_cost = 25000 Thanks Hans Buschmann
PostgreSQL upgrade from 10 to 12 fails with "ERROR: column r.relhasoids does not exist at character 1616"
Hi,
When trying to upgrade from PostgreSQL 10 to 12, the installation fails as
shown below.
# pg_upgradecluster --link --method=upgrade 10 main /dbdata/zabbix
From PosgreSQL log:
2021-05-04 13:06:04.780 CEST [12861] LOG: database system is ready to accept
connections
done
server started
.
.
2021-05-04 13:06:05.614 CEST [12890] postgres@zabbix_db ERROR: column
r.relhasoids does not exist at character 1616
2021-05-04 13:06:05.614 CEST [12890] postgres@zabbix_db STATEMENT:
-- For binary upgrade, must preserve pg_type oid
SELECT
pg_catalog.binary_upgrade_set_next_pg_type_oid('16'::pg_catalog.oid);
-- For binary upgrade, must preserve pg_type array oid
SELECT
pg_catalog.binary_upgrade_set_next_array_pg_type_oid('162221'::pg_catalog.oid);
-- For binary upgrade, must preserve pg_class oids
SELECT
pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('162220'::pg_catalog.oid);
CREATE VIEW "repack"."tables" AS
SELECT ("r"."oid")::"regclass" AS "relname",
"r"."oid" AS "relid",
"r"."reltoastrelid",
CASE
.
.
.
command: "/usr/lib/postgresql/12/bin/pg_ctl" -w -D "/etc/postgresql/12/main" -o
"" -m fast stop >> "pg_upgrade_server.log" 2>&1
2021-05-04 13:06:05.641 CEST [12861] LOG: received fast shutdown request
2021-05-04 13:06:05.641 CEST [12861] LOG: aborting any active transactions
waiting for server to shut down2021-05-04 13:06:05.644 CEST [12861] LOG:
background worker "logical replication launcher" (PID 12867) exited with exit
code 1
2021-05-04 13:06:05.645 CEST [12863] LOG: shutting down
2021-05-04 13:06:05.657 CEST [12861] LOG: database system is shut down
done
server stopped
- Other upgrade methods (clone/dump) fails with the same errors.
OS info:
NAME="Ubuntu"
VERSION="20.04.2 LTS (Focal Fossa)"
Linux 5.4.0-72-generic #80-Ubuntu SMP x86_64 x86_64 x86_64 GNU/Linux
Anyone seen similar issue?
Please advise.
Best regards,
Arne Segtnan
How to Host Multiple Mail Domains (Email Hosting) in iRedMail Full Featured Linux Mail Server
Subject: How to Host Multiple Mail Domains (Email Hosting) in iRedMail Full Featured Linux Mail Server Author: Mr. Turritopsis Dohrnii Teo En Ming (TARGETED INDIVIDUAL) Country: Singapore Date: 3rd May 2021 Monday Type of Publication: PDF Manual Document Version: 20210503.01 ***IMPORTANT NOTICE*** Please note that Turritopsis Dohrnii Teo En Ming’s guide is based on Xiao Guoan’s guide at linuxbabe.com. Reference Guide Used by Teo En Ming: How to Host Multiple Mail Domains in iRedMail with Nginx Link: https://www.linuxbabe.com/mail-server/set-up-iredmail-multiple-domains-nginx Original Author: Xiao Guoan The following is a list of open-source software that will be automatically installed and configured by iRedMail. • Postfix SMTP server • Dovecot IMAP server • Nginx web server to serve the admin panel and webmail • OpenLDAP, MySQL/MariaDB, or PostgreSQL for storing user information • Amavised-new for DKIM signing and verification • SpamAssassin for anti-spam • ClamAV for anti-virus • Roundcube webmail • SOGo groupware, providing webmail, calendar (CalDAV), contacts (CardDAV), tasks and ActiveSync services. • Fail2ban for protecting SSH • mlmmj mailing list manager • Netdata server monitoring • iRedAPD Postfix policy server for greylisting In addition, you need to add MX, A and TXT records to your ISC BIND DNS domain name server. Redundant download links for Turritopsis Dohrnii Teo En Ming's PDF manual: [1] https://www.mediafire.com/file/q6txmx0rc1cwfzw/Additional+Mail+Domains+1st+Release.pdf/file [2] https://www.docdroid.net/Q9TRL5D/additional-mail-domains-1st-release-pdf [3] https://www.scribd.com/document/506193434/Additional-Mail-Domains-1st-Release [4] https://drive.google.com/file/d/10AYGt4-omBeC3vXJfzswQq0M7E09gXpG/view?usp=sharing [5] https://drive.google.com/file/d/113dy0AKmBYGugBueK1vS_dJwI35J6XmJ/view?usp=sharing [6] https://drive.google.com/file/d/1dhpYIZp31ug5hoiVNbjh6uh99s_PEx8q/view?usp=sharing Mr. Turritopsis Dohrnii Teo En Ming, 43 years old as of 4th May 2021, is a TARGETED INDIVIDUAL living in Singapore. He is an IT Consultant with a System Integrator (SI)/computer firm in Singapore. He is an IT enthusiast. -BEGIN EMAIL SIGNATURE- The Gospel for all Targeted Individuals (TIs): [The New York Times] Microwave Weapons Are Prime Suspect in Ills of U.S. Embassy Workers Link: https://www.nytimes.com/2018/09/01/science/sonic-attack-cuba-microwave.html Singaporean Targeted Individual Mr. Turritopsis Dohrnii Teo En Ming's Academic Qualifications as at 14 Feb 2019 and refugee seeking attempts at the United Nations Refugee Agency Bangkok (21 Mar 2017), in Taiwan (5 Aug 2019) and Australia (25 Dec 2019 to 9 Jan 2020): [1] https://tdtemcerts.wordpress.com/ [2] https://tdtemcerts.blogspot.sg/ [3] https://www.scribd.com/user/270125049/Teo-En-Ming -END EMAIL SIGNATURE-
Re: Huge performance penalty with parallel queries in Windows x64 v. Linux x64
On Tue, May 4, 2021 at 7:40 PM Hans Buschmann wrote: > The problem seems that this (probably inherent) performance disadvantage of > windows is not reflected in the cost model. https://www.postgresql.org/docs/13/runtime-config-query.html#GUC-PARALLEL-SETUP-COST is for that. It might be interesting to do some profiling to see exactly what is slow. Presumably CreateProcess(), but I wonder why exactly. It'd be nice if we had a way to reuse parallel workers, but alas we do not, yet. Or threads.
Re: Is this the future of I/O for the RDBMS?
Hi again Peter (and thanks again for your input) > > https://pingcap.com/blog/tikv-and-spdk-pushing-the-limits-of-storage-performance > > It talks about the Storage Performance Development Kit (SPDK) (spdk.io). > It looks somewhat similar to Oracle's "raw device tablespaces" Run far and run fast... Never worked with it but have vague memories of senior colleagues who did... some are still in therapy :-) > Maybe with NVME SSDs > and persistent memory like Intel Optane it is time to revisit that idea. Plus ça change... just goes to show that there's rarely anything truly new in ICT... > There are less intrusive possibilities, though: Linux has recently > (kernel 5.1 - oh, that is already 2 years old) aquired a new async I/O > API named io_uring, I found this https://thenewstack.io/how-io_uring-and-ebpf-will-revolutionize-programming-in-linux/ and a few other bits and pieces - really interesting stuff! I had read the term io_uring but hadn't appreciated what it was about - it does add another layer of complexity to my future study of this area - Linux I/O and db I/O (esp. PG) and how to tie it all together. > More important for PostgreSQL is whether something like this can be > incorporated without changing the overall architecture: The one major architectural criticism that I regularly read about PG is that is uses a process per connection rather than threads: https://rbranson.medium.com/10-things-i-hate-about-postgresql-20dbab8c2791 #5: Process-Per-Connection = Pain at Scale I appreciate that the architecture can't be changed for every shiny new toy that comes along - However, it's frequently interesting though to look at underlying assumptions and check to see if they're still valid. MfG & nochmal Dank. Pól... > hp
Re: PostgreSQL, Asynchronous I/O, Buffered I/O and why did fsync-gate not affect Oracle or MySQL?
Hi Peter, and thanks for your input. > > Now, I'm not quite sure that I completely comprehend matters: Is there > > a difference between Asynchronous I/O and Buffered I/O? > Buffered I/O... So, with buffered I/O, I might write something to the db (with a commit) and would get a "yes, written" response (almost instantanous), even though the data hadn't actually touched the disk? And any subsequent (independent - separate user) read request will see that data as having been "written" even though it hasn't actually reached the disk yet? It would do this, because read requests would first look through buffers before actually checking the disk? > Asynchronous I/O refers to a different programming model: Any read or > write request only initiates the data transfer and returns immediately. > The application will later be notified when the request is finished. > This is very different from the traditional (in Unix) synchronous > programming model where a read would block until the data was actually > available and a write would block until the data was safely transferred > to the OS's buffer cache (and can be overwritten by the application). So, any separate read would see what? What does PostgreSQL do? Or is this configurable? Also, how does the WAL fit into all of this? As I responded to others who have taken the trouble to reply to me - if you (or anyone) would have some detailed references covering these issues (either PG specific or generally), I'd be very grateful. MfG und vielen Dank. Pól... > hp
Re: PostgreSQL, Asynchronous I/O, Buffered I/O and why did fsync-gate not affect Oracle or MySQL?
Hi Ron, and thanks for your input. > > Now, I'm not quite sure that I completely comprehend matters: Is there > > a difference between Asynchronous I/O and Buffered I/O? > * Asynchronous (a-syn-chron-ous) is an adjective which means "not together > with time". > * Buffered means "read more than you need at the moment, and then do > processing to/from a cache". > Their antonyms are > * Synchronous (syn-chron-ous): together with time. > * Direct, where you read/write only what you need at the moment, directly > to/from the IO device. > Thus, async IO is where you tell the IO subsystem that you need something, > and then go off and do something else; the IO system interrupts you when the > data has been delivered. > Synchronous IO is where you request IO and then wait for the data. Grand - that's the conclusion I was coming to myself... If you (or anyone) would have any good detailed technical references which explain these issues, I'd be very grateful. Rgs, Pól...
Re: PostgreSQL, Asynchronous I/O, Buffered I/O and why did fsync-gate not affect Oracle or MySQL?
Hi Vijay, and thanks for your input. > This wiki page. > It has PR references for mysql and mongo for the fsycnc issue. > Fsync Errors - PostgreSQL wiki OK - so that shows that it did affect MySQL and MongoDB... I'm surprised that there wasn't more of a brouhaha over it so - what with MySQL being so prevalent - there appears to have been more coverage of the issue on the PostgreSQL side - even given that it was first spotted by PG developers. > I'd leave the more intellectual brainstorming to the experts. Indeed - some of those dev list conversations can be very involved... Rgs, Pól... > Vijay
AW: Huge performance penalty with parallel queries in Windows x64 v. Linux x64
>No Windows here, but could it be super slow at launching workers? How >does a trivial parallel query compare, something like? >SET force_parallel_mode = on; >EXPLAIN ANALYZE SELECT 42; indeed this query takes about 40ms in windows and 7ms on Linux (lowest values). Due to remoting the machine the reported times vary quite a bit. The problem seems that this (probably inherent) performance disadvantage of windows is not reflected in the cost model. This causes little to middle complex queries to prioritize parallel execution on windows which is certainly not the best option in these cases. The starting of processes should have an adequate cost penalty to guide the planner in the right direction. Generally disabling parallel queries seems not a viable option with mixed loads. Here are the query plans: QUERY PLAN Windows Gather (cost=1000.00..1000.11 rows=1 width=4) (actual time=34.995..38.207 rows=1 loops=1) Workers Planned: 1 Workers Launched: 1 Single Copy: true -> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=1) Planning Time: 0.016 ms Execution Time: 39.136 ms (7 Zeilen) QUERY PLAN Linux -- Gather (cost=1000.00..1000.11 rows=1 width=4) (actual time=6.864..7.764 rows=1 loops=1) Workers Planned: 1 Workers Launched: 1 Single Copy: true -> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=1) Planning Time: 0.026 ms Execution Time: 7.812 ms (7 rows) Hans Buschmann
Identified cause of potential pg_upgrade failure on Windows due to OS update adding partial block list of ports 50xxxx (could not connect to server)
Hello, I'm a PostgreSQL user on Windows and encountered trouble running pg_upgrade with the following error [1], ultimately due to blocked ports in Windows added via OS updates. A workaround is to run pg_upgrade with lower range ports for the old/new servers via the -p/-P parameters. ## > pg_upgrade Performing Consistency Checks - Checking cluster versions ok *failure* Consult the last few lines of "pg_upgrade_server_start.log" or "pg_upgrade_server.log" for the probable cause of the failure. connection to database failed: could not connect to server: Connection refused (0x274D/10061) Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 50432? could not connect to server: Connection refused (0x274D/10061) Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 50432? could not connect to source postmaster started with the command: "C:/program files/postgresql/12/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "C:/program files/postgresql/12/data" -o "-p 50432 -b " start ## The root cause is that pg_upgrade wants to start a pg server instance on port 50432, which is in the Windows port block list as of (I believe) KB4074588 (see https://support.microsoft.com/en-us/topic/february-13-2018-kb4074588-os-build-16299-248-b4e2ca66-dd7a-6fd5-a8f3-dc6683d4922b and https://superuser.com/a/1504618/74693) The block list as of writing for me is: ## > netsh interface ipv4 show excludedportrange protocol=tcp Protocol tcp Port Exclusion Ranges Start PortEnd Port -- 53575357 44376 44376 49683 49782 49783 49882 49883 49982 5 50059 * 50265 50364 50370 50469 50849 50948 51049 51148 51149 51248 51349 51448 51449 51548 51549 51648 51649 51748 56959 57058 57059 57158 62662 62662 * - Administered port exclusions. ## I've seen previous people report trouble with this problem without an obvious resolution (see https://www.postgresql-archive.org/Upgrade-9-4-to-12-on-windows-system-td6163643.html and https://community.microfocus.com/cyberres/idm/f/idm_discussion/301027/pg_upgrade-fails-on-windows-2012r2-when-updating-to-idm-4-6 ). A silly problem, but perhaps pg_upgrade could look to use different ports not in the Windows block list to prevent this issue. Kind regards, Andrew
