Re: Query performance in 9.6.24 vs 14.10

[Bob Jolliffe]

Thanks for the update.

On Mon, Jan 29, 2024, 16:53 Ron Johnson  wrote:

> According to my tests, sometimes JIT is a little faster, and sometimes
> it's a little slower. Mostly within the realm of statistical noise
> (especially with each query having a sample size of only 13, on a VM that
> lives on a probably-busy host).
>
> On Mon, Jan 29, 2024 at 9:18 AM Ron Johnson 
> wrote:
>
>> Yes, jit=on.
>>
>> I'll test them with jit=off, to see the difference.  (The application is
>> 3rd party, so will change it at the system level.)
>>
>> On Mon, Jan 29, 2024 at 7:09 AM Bob Jolliffe 
>> wrote:
>>
>>> Out of curiosity, is the pg14 running with the default jit=on setting?
>>>
>>> This is obviously entirely due to the nature of the particular queries
>>> themselves, but we found that for our workloads that pg versions
>>> greater than 11 were exacting a huge cost due to the jit compiler.  Once we
>>> explicitly turned jit=off we started to see improvements.
>>>
>>

Re: Query performance in 9.6.24 vs 14.10

[Bob Jolliffe]

Out of curiosity, is the pg14 running with the default jit=on setting?

This is obviously entirely due to the nature of the particular queries
themselves, but we found that for our workloads that pg versions
greater than 11 were exacting a huge cost due to the jit compiler.  Once we
explicitly turned jit=off we started to see improvements.

On Mon, 29 Jan 2024 at 07:55, Ron Johnson  wrote:

> On Sun, Jan 28, 2024 at 10:44 PM David Rowley 
> wrote:
>
>> On Mon, 29 Jan 2024 at 07:37, Ron Johnson 
>> wrote:
>>
>>> 08 9.6.24 1,142.164 1,160.801 1,103.716 1,249.852 1,191.081
>>> 14.10 159.354 155.111 155.111 162.797 158.157 86.72%
>>>
>>
>> Your speedup per cent calculation undersells PG14 by quite a bit.  I'd
>> call that an increase of ~639% rather than 86.72%.
>>
>> I think you've done "1 - sum( <14.10 numbers> ) / sum( <9.6.24 numbers>)"
>> whereas I think you should have done "sum( <9.6.24 numbers>) / sum( <14.10
>> numbers> ) - 1"
>>
>> Nonetheless, thanks for testing this out.  I assume this is just a report
>> giving good feedback about progress in this area...?
>>
>
> The spreadsheet function, using the Median cells, is (PG9.6 - PG14) /
> PG9.6).  That's essentially the same as what you wrote.
>
> 158.157 / 1191.081 = 0.13278
>
> 1191.081 / 158.157 = 7.53, so 9.6.24 on that query is 7.53x slower.
>
>

Re: Best Open Source OS for Postgresql

[Bob Jolliffe]

I have generally favoured ubuntu LTS editions over the years, more out of
familiarity than any particular good technical reason.  In the past,
postgresql on FreeBSD would have been my first goto, but it's harder to get
freebsd skills out on the market than ubuntu/debian linux.

I do have one gripe about the packaged postgis packages : the list of
dependencies seem quite enormous.  For example, the previous poster
mentions 234MB.  I find the list of dependencies a little baffling and
disconcerting.

Regards
Bob

On Wed, 1 Feb 2023 at 16:25, Adrian Klaver 
wrote:

> On 2/1/23 07:54, Marc Millas wrote:
> > If I remember  well, I did try all repo I ever heard off, and basic
> > Google search
> > Till I ask someone thru a postgis mailing list.
> > If you find something, like how to install a postgres 12 with a postgis
> > 3.0 on any debian based distro.. Pls tell, I will read with interest.
> > Also, pls, not recompiling the whole thing with all associated libs ...
> > Thanks
> > Which was my customer requirelent
>
> Using:
>
> lsb_release -a
> No LSB modules are available.
> Distributor ID: Ubuntu
> Description:Ubuntu 20.04.5 LTS
> Release:20.04
> Codename:   focal
>
>
> and community repos:
>
> deb http://apt.postgresql.org/pub/repos/apt focal-pgdg main
> deb https://apt-archive.postgresql.org/pub/repos/apt focal-pgdg-archive
> main
>
>
> sudo apt install postgresql-12
>
>
> Success. You can now start the database server using:
>
>  pg_ctlcluster 12 main start
>
>
> Then:
>
> sudo apt list postgis -a
> Listing... Done
> postgis/focal-pgdg,focal-pgdg-archive 3.3.2+dfsg-1.pgdg20.04+1 amd64
> postgis/focal-pgdg-archive 3.3.1+dfsg-2.pgdg20.04+1 amd64
> postgis/focal-pgdg-archive 3.3.1+dfsg-1.pgdg20.04+1 amd64
> postgis/focal-pgdg-archive 3.3.0+dfsg-1.pgdg20.04+1 amd64
> postgis/focal-pgdg-archive 3.2.3+dfsg-1.pgdg20.04+1 amd64
> postgis/focal-pgdg-archive 3.2.2+dfsg-1.pgdg20.04+1 amd64
> postgis/focal-pgdg-archive 3.2.1+dfsg-1.pgdg20.04+1 amd64
> postgis/focal-pgdg-archive 3.2.0+dfsg-1.pgdg20.04+1 amd64
> postgis/focal-pgdg-archive 3.1.4+dfsg-3.pgdg20.04+1 amd64
> postgis/focal-pgdg-archive 3.1.4+dfsg-1.pgdg20.04+1 amd64
> postgis/focal-pgdg-archive 3.1.3+dfsg-1~exp1.pgdg20.04+1+b1 amd64
> postgis/focal-pgdg-archive 3.1.2+dfsg-1~exp2.pgdg20.04+1 amd64
> postgis/focal-pgdg-archive 3.1.1+dfsg-1.pgdg20.04+1 amd64
> postgis/focal-pgdg-archive 3.1.0+dfsg-1.pgdg20.04+1 amd64
> postgis/focal-pgdg-archive
> 3.1.0~alpha1+dfsg-1~exp2~232.gitc62f703.pgdg20.04+1 amd64
> postgis/focal-pgdg-archive 3.0.3+dfsg-2.pgdg20.04+1 amd64
> postgis/focal-pgdg-archive 3.0.2+dfsg-4.pgdg20.04+1 amd64
> postgis/focal-pgdg-archive 3.0.2+dfsg-2.pgdg20.04+1 amd64
> postgis/focal-pgdg-archive 3.0.1+dfsg-4.pgdg20.04+1 amd64
> postgis/focal-pgdg-archive 3.0.1+dfsg-2.pgdg20.04+1 amd64
> postgis/focal 3.0.0+dfsg-6ubuntu4 amd64
>
>
> Followed by:
>
> sudo apt install postgis=3.0.3+dfsg-2.pgdg20.04+1
>
> ...
>
> After this operation, 234 MB of additional disk space will be used.
> Do you want to continue? [Y/n] n
>
>
> I did not actually install as I don't use PostGIS, so I would not know
> how to verify a clean install.
>
>
> This:
>
> deb https://apt-archive.postgresql.org/pub/repos/apt focal-pgdg-archive
> main
>
> comes from here:
>
> https://www.df7cb.de/blog/2020/apt-archive.postgresql.org.html
>
> The community packagers for deb packages have set up an archive repo for
> older versions.
>
>
> >
> > Le mar. 31 janv. 2023 à 17:38, Adrian Klaver  > > a écrit :
> >
> > On 1/31/23 05:09, Marc Millas wrote:
> >  > Sorry for inappropriate "reply".
> >  >
> >  > if you do check the debian postgis repo, you ll find that its NOT
> >  > possible to choose a postgis version.
> >
> > Are you talking about the repo hosted by Debian or the Postgres
> > community repo's here:
> >
> > https://www.postgresql.org/download/linux/debian/
> > 
> >
> >  > its possible for postgis 2.4 and 2.5, then ALL 3.x versions are
> >  > inaccessible but one, that did change from time to time.
> >  > (you MUST ask for postgis 3 without being able to ask for 3.0 or
> 3.1
> >  > or...  its like asking for postgres 9 without .5 or .6)
> >  > 2 of my customers reverse to a RH family linux because they have
> > been
> >  > unable to install the requested postgres/postgis version on
> debian.
> >  > when I did ask the team, the reply was: we cannot package for all
> > cross
> >  > possibilities (ie. 5 postgres x 6 postgis, less some
> impossibilities
> >  > according to postgis matrix)
> >  >
> >  > so...
> >  >
> >  >
> >  > Marc MILLAS
> >  > Senior Architect
> >  > +33607850334
> >  > www.mokadb.com   > >
> >  >
> >  >
> >
> > --
> > Adrian Klaver
> > 

Re: ZFS filesystem - supported ?

[Bob Jolliffe]

We have some users of our software who have had a good experience with
postgresql on zfs/zol.  Two features which have proved useful are the
native encryption (less fiddly than luks) and compression.  Interestingly,
many of our users are stuck with quite old and slow disks.  Using
compression (even together with encryption) on the slow disks gives quite a
significant performance boost.  Trading cpu for disk bandwidth.  Also they
often dont have infinite access to more disk, so the storage efficiency is
welcomed.

We are interested in the snapshots but a little wary of potential data
integrity issues.

We have a disturbance in our database structure (usually nightly) where
large tables are dropped and recreated.  snapshots of a gradually
increasing size database probably work very well.  I think these massive
deletions probably make the snapshots quite heavy.  Also creating a
challenge for incremental backups, replication etc but that is another (not
quite unrelated) issue.

Regards
Bob

On Tue, 26 Oct 2021 at 01:18, Benedict Holland 
wrote:

> In my opinion, ext4 will solve any and all problems without a very deep
> understanding of file system architecture. In short, i would stick with
> ext4 unless you have a good reason not to. Maybe there is one. I have done
> this a long time and never thought twice about which file system should
> support my servers.
>
> On Mon, Oct 25, 2021, 6:01 PM Robert L Mathews 
> wrote:
>
>> On 10/25/21 1:40 PM, Mladen Gogala wrote:
>> > This is probably not the place
>> > to discuss the inner workings of snapshots, but it is worth knowing
>> that
>> > snapshots drastically increase the IO rate on the file system - for
>> > every snapshot. That's where the slowness comes from.
>>
>> I have recent anecdotal experience of this. I experiment with using
>> Btrfs for a 32 TB backup system that has five 8 TB spinning disks.
>> There's an average of 8 MBps of writes scattered around the disks, which
>> isn't super high, obviously.
>>
>> The results were vaguely acceptable until I created a snapshot of it, at
>> which point it became completely unusable. Even having one snapshot
>> present caused hundreds of btrfs-related kernel threads to thrash in the
>> "D" state almost constantly, and it never stopped doing that even when
>> left for many hours.
>>
>> I then experimented with adding a bcache layer on top of Btrfs to see if
>> it would help. I added a 2 TB SSD using bcache, partitioned as 1900 GB
>> read cache and 100 GB write cache. It made very little difference and
>> was still unusable as soon as a snapshot was taken.
>>
>> I did play with the various btrfs and bcache tuning knobs quite a bit
>> and couldn't improve it.
>>
>> Since that test was a failure, I then decided to try the same setup with
>> OpenZFS on a lark, with the same set of disks in a "raidz" array, with
>> the 2 TB SSD as an l2arc read cache (no write cache). It easily handles
>> the same load, even with 72 hourly snapshots present, with the default
>> settings. I'm actually quite impressed with it.
>>
>> I'm sure that the RAID, snapshots and copy-on-write reduce the maximum
>> performance considerably, compared to ext4. But on the other hand, it
>> did provide the performance I expected to be possible given the setup.
>> Btrfs *definitely* didn't; I was surprised at how badly it performed.
>>
>> --
>> Robert L Mathews, Tiger Technologies, http://www.tigertech.net/
>>
>>
>>

casting Bangla characters to NUMERIC

[Bob Jolliffe]

Hi

I have an interesting problem.  I have a string field in a table which
(sometimes) is expected to contain numeric values  ie. a cast of the
field to INTEGER is supposed to succeed.

My issue is that the application is running in Bangladesh, and
sometimes the users have entered values using Bangla characters.  eg.
"৮" rather than "8".  (Note that this shouldnt have been possible to
enter in the first place, but we can ignore that for now.  They are
there, I have to somehow deal with it.)

Is there a way with LOCALE settings to get this cast to work
correctly?  Currently I get:
postgres=# select cast('৮' as INTEGER);
ERROR:  invalid input syntax for integer: "৮"
LINE 1: select cast('৮' as INTEGER);

Regards
Bob

Re: Allowing client access

[Bob Jolliffe]

Hi Timmy

You need to use CIDR form in your pg_hba.conf.  So:

host all testuser  111.222.333.444/32  md5

Most likely you would probably want to ensure ssl connection if coming
over untrusted network.  So, at minimum, this is better:

hostssl all testuser  111.222.333.444/32  md5

This is better still:

hostssl testdb testuser  111.222.333.444/32  md5

Better still (IMHO) is to keep it local and use ssh tunnel, but I
understand that might be difficult and not necessarily desirable,
depending on the context.

Regards
Bob

On Wed, 9 Oct 2019 at 13:34, Timmy Siu  wrote:
>
> Dear All Users,
>
> How do I allow an external client IP address access to my pgsql server?
>
> According to my own experience and test, if I set the external client IP 
> address to, for example, 111.222.333.444 in the file 
> /etc/postgresql/11/main/pg_hba.conf, it will not be able to connect to the 
> server:
> #TYPE?? DATABASE?? ?? USER?? ?? ?? ADDRESS?? ?? 
> ?? METHOD
> host?? all?? ?? ?? testuser  ?? 111.222.333.444?? 
> md5
>
> I must set "address" to "any" as in the following:
> #TYPE?? DATABASE?? ?? USER?? ?? ?? ADDRESS?? ?? 
> METHOD
> host?? all?? ?? ?? testuser  ?? 0.0.0.0/0?? ?? md5
>
>
> Can I limit access to my pgsql server through TCP Wrapper? What is the 
> related executable?
>
>
> Regards,
> Timmy
>
> ??

Re: Swap on postgres master server

[Bob Jolliffe]

I guess you can run swapoff (followed by swapon).  That will free up
whatever is currently swapped.  Beware if the system is actively
swapping then swapoff can take some time.  But it seems not in your
case.
On Tue, 16 Oct 2018 at 10:48, Nicola Contu  wrote:
>
> No it is not probably used, because I can't find it in any way as I said.
>
> I run your commands :
>
> [root@usnyh-cmd1 ~]# vmstat 1
> procs ---memory-- ---swap-- -io -system-- 
> --cpu-
>  r  b   swpd   free   buff  cache   si   sobibo   in   cs us sy id wa 
> st
>  3  2 7505332 14545468  13692 2414367840047   11500 11  2 
> 84  2  0
>  3  2 7505332 14490408  13692 24143692800 0   248 6153 2013  3  1 
> 93  3  0
>  1  2 7505332 14474672  13692 24143680000 0  1124 4905 1454  3  1 
> 93  3  0
>  4  2 7505332 14381156  13692 24143683200 096 5322 1782  2  1 
> 94  3  0
> ^C
> [root@usnyh-cmd1 ~]# ps -A --sort -rss -o comm,pmem | awk '
> >   NR == 1 { print; next }
> >   { a[$1] += $2 }
> >   END {
> > for (i in a) {
> >   printf "%-15s\t%s\n", i, a[i];
> > }
> >   }
> > '
> COMMAND %MEM
> kworker/42:20
> kworker/60:6H  0
> kworker/60:2H  0
> kdmwork-253:2  0
> ksoftirqd/600
> postmaster  15.2
> kworker/9:0H0
>
> So I'm just asking why it is still there with free -m and if there is any way 
> to free that up if it is not used.
>
> Thanks
>
> Il giorno mar 16 ott 2018 alle ore 11:18 Hans Schou  ha 
> scritto:
>>
>> Are you sure that swap is used actively? Maybe it had just been used during 
>> backup or something.
>>
>> Look after SwapIn/SwapOut (si/so) it should be '0'
>> $ vmstat 1
>> procs ---memory-- ---swap-- -io -system-- 
>> --cpu-
>>  r  b   swpd   free   buff  cache   si   sobibo   in   cs us sy id 
>> wa st
>>  1  0 12  89344  46608 5863840012 8   30   86  0  0 99  
>> 0  0
>>
>> If you want to see the amount of ram used by each program with childs run 
>> this:
>> ps -A --sort -rss -o comm,pmem | awk '
>>   NR == 1 { print; next }
>>   { a[$1] += $2 }
>>   END {
>> for (i in a) {
>>   printf "%-15s\t%s\n", i, a[i];
>> }
>>   }
>> '
>>
>>
>> On Tue, Oct 16, 2018 at 11:04 AM Nicola Contu  wrote:
>>>
>>> Hello,
>>> we are running Postgres 10.5 with master slave replication.
>>>
>>> These are our custom params
>>>
>>> archive_command = 'pgbackrest --stanza=cmdprod archive-push %p' # command 
>>> to use to archive a logfile segment
>>> archive_mode = on # enables archiving; off, on, or always
>>> checkpoint_completion_target = 0.7 # checkpoint target duration, 0.0 - 1.0
>>> default_text_search_config = 'pg_catalog.english' #
>>> datestyle = 'iso, mdy' #
>>> effective_cache_size = 120GB #
>>> hot_standby = on# "on" allows queries during 
>>> recovery
>>> lc_messages = 'en_US.UTF-8'  # locale for system error 
>>> message
>>> lc_monetary = 'en_US.UTF-8'  # locale for monetary 
>>> formatting
>>> lc_numeric = 'en_US.UTF-8'  # locale for number 
>>> formatting
>>> lc_time = 'en_US.UTF-8'  # locale for time 
>>> formatting
>>> listen_addresses = '*' # defaults to 'localhost', '*' = all
>>> log_autovacuum_min_duration = 1000ms# -1 disables, 0 logs all 
>>> actions and
>>> log_checkpoints = on #
>>> log_line_prefix = '%t [%r] [%p]: [%l-1] db=%d,user=%u ' #
>>> log_lock_waits = on  # log lock waits >= 
>>> deadlock_timeout
>>> log_min_duration_statement = 1000ms# -1 is disabled, 0 logs all 
>>> statements
>>> log_statement = 'ddl'  # none, ddl, mod, all
>>> log_temp_files = 1024kB  # log temporary files equal or 
>>> larger
>>> maintenance_work_mem = 2GB #
>>> max_connections = 220 #
>>> max_parallel_workers_per_gather = 8# taken from max_worker_processes
>>> max_wal_size = 2GB #
>>> min_wal_size = 1GB #
>>> pg_stat_statements.max = 1 #
>>> pg_stat_statements.track = all #
>>> port = 5432# port number which Postgres listen
>>> shared_buffers = 10GB #
>>> shared_preload_libraries = 'pg_stat_statements'  # (change requires 
>>> restart)
>>> synchronous_standby_names = '1 ( "usnyh2" )' # comment out during upgrade
>>> track_activity_query_size = 16384# (change requires restart)
>>> track_io_timing = on #
>>> wal_buffers = 16MB #
>>> wal_keep_segments = 100 #
>>> wal_level = replica#  minimal, replica, or logical
>>> work_mem = 600MB #
>>>
>>> This server is on Centos 7 and the strange thing is that we see a lot of 
>>> swap usage :
>>>
>>> [root@usnyh-cmd1 ~]# free -m
>>>   totalusedfree  shared  buff/cache   
>>> available
>>> Mem: 25765275555559   12804  244536  
>>> 236036
>>> Swap: 1638373269057
>>>
>>> 7GB