does dml operations load the blocks to the shared buffers ?

2019-01-10 Thread Mariel Cherkassky 
Hey,
It is clear that when we query some data, if that data isnt in the shared
buffers pg will go bring the relevant blocks from the disk to the shared
buffers. I wanted to ask if the same logic works with
dml(insert/update/delete). I'm familiar with the writing logic, that the
checkpointer is the process that writing the data changes into the data
files during every checkpoint and that the commit write the changes from
the wal buffers to to the wal files. I wanted to ask about a situation
where we run dmls and that data isnt available in the shared buffers.

Re: postgresql unix socket connections

2019-01-10 Thread Mariel Cherkassky 
In machine 2 :
I found 4 libpq.so files :
[root@~]# locate libpq.so
/usr/lib64/libpq.so.5
/usr/lib64/libpq.so.5.2
/usr/pgsql-9.6/lib/libpq.so.5
/usr/pgsql-9.6/lib/libpq.so.5.9

 cat /etc/ld.so.conf
include ld.so.conf.d/*.conf
 ld.so.conf.d]# cat /etc/ld.so.conf.d/postgresql-pgdg-libs.conf
/usr/pgsql-9.6/lib/


In machine 1 :
locate libpq.so
/usr/lib64/libpq.so.5
/usr/lib64/libpq.so.5.2
/usr/pgsql-9.2/lib/libpq.so.5
/usr/pgsql-9.2/lib/libpq.so.5.5



I checked with rpm -ql the packge postgresql96-libs.x86_64
0:9.6.10-1PGDG.rhel6 and it seems that it indeed put the new libpq.so in
the system. My question is, is it possible that it also deleted the 9.2
libpq file ?


‫בתאריך יום ד׳, 9 בינו׳ 2019 ב-18:11 מאת ‪Tom Lane‬‏ <‪[email protected]
‬‏>:‬

> Mariel Cherkassky  writes:
> > But in both of the machines I have the same os and I used the same
> > repository - postgresql rpm repository. The only difference is that in
> > machine 2 I also installed all pg 9.6 packages. Even When I try to use
> > /usr/pgsql-9.2/bin/psql the psql still tries to access the
> > /var/run/run/postgresql dir as the socket dir. Does those packages
> include
> > a different libpq ?  What postgres package change the libpq ?
>
> "rpm -ql" would tell you about which packages supply what.
>
> Assuming there's more than one libpq.so on your machine, which it sounds
> like there is, which one gets used depends on the dynamic linker's
> configuration -- see /etc/ld.so.conf and "man ldconfig".
>
> regards, tom lane
>

Re: does dml operations load the blocks to the shared buffers ?

2019-01-10 Thread Guillaume Lelarge 
Le jeu. 10 janv. 2019 à 09:07, Mariel Cherkassky <
[email protected]> a écrit :

> Hey,
> It is clear that when we query some data, if that data isnt in the shared
> buffers pg will go bring the relevant blocks from the disk to the shared
> buffers. I wanted to ask if the same logic works with
> dml(insert/update/delete). I'm familiar with the writing logic, that the
> checkpointer is the process that writing the data changes into the data
> files during every checkpoint and that the commit write the changes from
> the wal buffers to to the wal files. I wanted to ask about a situation
> where we run dmls and that data isnt available in the shared buffers.
>
>
It works the same.


-- 
Guillaume.

Re: PostgreSQL Read IOPS limit per connection

2019-01-10 Thread Mark Hogg 
Hello,

I am happy to hear that you have received all the help.

Please feel free to contact us for professional assistance any time you may
need in the future.

Most Welcome!


Regards,


Mark Avinash Hogg

Director of Business Development

2ndQuadrant

+1(647) 770 9821 Cell

www.2ndquadrant.com

[email protected]


On Wed, 9 Jan 2019 at 19:20, Merlin Moncure (via Accelo) 
wrote:

> On Wed, Jan 9, 2019 at 3:52 PM Haroldo Kerry  wrote:
>
>> @Justin  @Merlin @ Jeff,
>> Thanks so much for your time and insights, they improved our
>> understanding of the underpinnings of PostgreSQL and allowed us to deal the
>> issues we were facing.
>> Using parallel query on our PG 9.6 improved a lot the query performance -
>> it turns out that a lot of our real world queries could benefit of parallel
>> query, we saw about 4x improvements after turning it on, and now we see
>> much higher storage IOPS thanks to the multiple workers.
>> On our tests effective_io_concurrency did not show such a large effect as
>> the link you sent, I'll have a new look at it, maybe we are doing something
>> wrong or the fact that the SSDs are on the SAN and not local affects the
>> results.
>> On the process we also learned that changing the default Linux I/O
>> scheduler from CFQ to Deadline worked wonders for our Dell SC2020 SAN
>> Storage setup, we used to see latency peaks of 6,000 milliseconds on busy
>> periods (yes, 6 seconds), we now see 80 milliseconds, an almost 100 fold
>> improvement.
>>
>
> The links sent was using a contrived query to force a type of scan that
> benefits from that kind of query; it's a very situational benefit.  It
> would be interesting if you couldn't reproduce using the same mechanic.
>
> merlin
>
>>

Re: postgresql unix socket connections

2019-01-10 Thread Ken Tanzer 
On Wed, Jan 9, 2019 at 7:09 AM Mariel Cherkassky <
[email protected]> wrote:

> Hey Tom,
> I'm aware of how I can solve it. I wanted to understand why after
> installing the pg 9.6 packages suddenly psql tries to access the socket on
> /var/run/postgresql. Does the libpq default unix socket is changed between
> those two versions ? (9.6,9.2)
>
> I hit this kind of problem too.  Per Devrim in this thread, the default
socket location changed in v. 9.4.

https://www.postgresql.org/message-id/flat/CAD3a31XLfN0hgEVJPzfKj9JzVqEOpLrn6eE06PGNMq5JsFngPA%40mail.gmail.com

Cheers,
Ken


-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
[email protected]
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: postgresql unix socket connections

2019-01-10 Thread Mariel Cherkassky 
Thanks Ken. I just wanted to make sure that it happened because of 9.6
packages installation and not because of any other reason.

‫בתאריך יום ה׳, 10 בינו׳ 2019 ב-11:42 מאת ‪Ken Tanzer‬‏ <‪
[email protected]‬‏>:‬

> On Wed, Jan 9, 2019 at 7:09 AM Mariel Cherkassky <
> [email protected]> wrote:
>
>> Hey Tom,
>> I'm aware of how I can solve it. I wanted to understand why after
>> installing the pg 9.6 packages suddenly psql tries to access the socket on
>> /var/run/postgresql. Does the libpq default unix socket is changed between
>> those two versions ? (9.6,9.2)
>>
>> I hit this kind of problem too.  Per Devrim in this thread, the default
> socket location changed in v. 9.4.
>
>
> https://www.postgresql.org/message-id/flat/CAD3a31XLfN0hgEVJPzfKj9JzVqEOpLrn6eE06PGNMq5JsFngPA%40mail.gmail.com
>
> Cheers,
> Ken
>
>
> --
> AGENCY Software
> A Free Software data system
> By and for non-profits
> *http://agency-software.org/ *
> *https://demo.agency-software.org/client
> *
> [email protected]
> (253) 245-3801
>
> Subscribe to the mailing list
>  to
> learn more about AGENCY or
> follow the discussion.
>

Re: does dml operations load the blocks to the shared buffers ?

2019-01-10 Thread Mariel Cherkassky 
. Lets assume the amount of data I insert is bigger than the
shared_buffers. I didnt commit the transaction yet, the data will be saved
on temp files until I commit ?
What happens if I have in my transaction,I did a lot of changes and I
filled the wal_buffers / shared buffers but I still didnt commit. How the
database will handle it ?

‫בתאריך יום ה׳, 10 בינו׳ 2019 ב-10:55 מאת ‪Guillaume Lelarge‬‏ <‪
[email protected]‬‏>:‬

> Le jeu. 10 janv. 2019 à 09:07, Mariel Cherkassky <
> [email protected]> a écrit :
>
>> Hey,
>> It is clear that when we query some data, if that data isnt in the shared
>> buffers pg will go bring the relevant blocks from the disk to the shared
>> buffers. I wanted to ask if the same logic works with
>> dml(insert/update/delete). I'm familiar with the writing logic, that the
>> checkpointer is the process that writing the data changes into the data
>> files during every checkpoint and that the commit write the changes from
>> the wal buffers to to the wal files. I wanted to ask about a situation
>> where we run dmls and that data isnt available in the shared buffers.
>>
>>
> It works the same.
>
>
> --
> Guillaume.
>

Detect missing combined indexes (automatically)

2019-01-10 Thread Thomas Güttler 

Is there a way to detect missing combined indexes automatically

I am managing a lot of databases and I think a lot of performance
could get gained.

But I don't want to do this manually.

My focus is on missing combined indexes, since for missing
single indexes there are already tools available.

Regards,
  Thomas Güttler


--
Thomas Guettler http://www.thomas-guettler.de/
I am looking for feedback: https://github.com/guettli/programming-guidelines

Re: does dml operations load the blocks to the shared buffers ?

2019-01-10 Thread Guillaume Lelarge 
Le jeu. 10 janv. 2019 à 11:40, Mariel Cherkassky <
[email protected]> a écrit :

> . Lets assume the amount of data I insert is bigger than the
> shared_buffers. I didnt commit the transaction yet, the data will be saved
> on temp files until I commit ?
> What happens if I have in my transaction,I did a lot of changes and I
> filled the wal_buffers / shared buffers but I still didnt commit. How the
> database will handle it ?
>

Please, don't top-post. It makes it hard to follow the thread.

Whatever happens, it will get to disk in the data files, and it doesn't
actually matter. The database has system informations on the datafiles (on
each tuple actually) that will allow to make the distinction between
commited tuples, rollbacked tuples and not-yet-committed-or-rollbacked
tuples.


> ‫בתאריך יום ה׳, 10 בינו׳ 2019 ב-10:55 מאת ‪Guillaume Lelarge‬‏ <‪
> [email protected]‬‏>:‬
>
>> Le jeu. 10 janv. 2019 à 09:07, Mariel Cherkassky <
>> [email protected]> a écrit :
>>
>>> Hey,
>>> It is clear that when we query some data, if that data isnt in the
>>> shared buffers pg will go bring the relevant blocks from the disk to the
>>> shared buffers. I wanted to ask if the same logic works with
>>> dml(insert/update/delete). I'm familiar with the writing logic, that the
>>> checkpointer is the process that writing the data changes into the data
>>> files during every checkpoint and that the commit write the changes from
>>> the wal buffers to to the wal files. I wanted to ask about a situation
>>> where we run dmls and that data isnt available in the shared buffers.
>>>
>>>
>> It works the same.
>>
>>
>> --
>> Guillaume.
>>
>

-- 
Guillaume.