Re: PostgreSQL Read IOPS limit per connection
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 Read IOPS limit per connection
@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. Best regards, Haroldo Kerry On Wed, Jan 9, 2019 at 5:14 PM Merlin Moncure wrote: > On Thu, Dec 27, 2018 at 7:29 PM Justin Pryzby > wrote: > > > > On Thu, Dec 27, 2018 at 08:20:23PM -0500, Jeff Janes wrote: > > > Also, you would want to use the newest version of PostgreSQL, as 9.6 > > > doesn't have parallel query, which is much more generally applicable > than > > > effective_io_concurrency is. > > effective_io_concurrency only applies to certain queries. When it > does apply it can work wonders. See: > > https://www.postgresql.org/message-id/CAHyXU0yiVvfQAnR9cyH=HWh1WbLRsioe=mzRJTHwtr=2azs...@mail.gmail.com > for an example of how it can benefit. > > parallel query is not going to help single threaded pg_bench results. > you are going to be entirely latency bound (network from bebench to > postgres, then postgres to storage). On my dell crapbox I was getting > 2200tps so you have some point of slowness relative to me, probably > not the disk itself. > > Geetting faster performance is an age-old problem; you need to > aggregate specific requests into more general ones, move the > controlling logic into the database itself, or use various other > strategies. Lowering latency is a hardware problem and can force > trade-offs (like, don't use a SAN) and has specific boundaries that > are not easy to bust through. > > merlin > > -- Haroldo Kerry CTO/COO Rua do Rócio, 220, 7° andar, conjunto 72 São Paulo – SP / CEP 04552-000 hke...@callix.com.br www.callix.com.br
Re: select query does not pick up the right index
Oups wrong copy and paste. I did run your query with equal instead of in but it resulted in the same plan From: David Rowley Sent: 09 January 2019 17:41:24 To: Abadie Lana Cc: Justin Pryzby; pgsql-performance@lists.postgresql.org Subject: Re: select query does not pick up the right index On Thu, 10 Jan 2019 at 01:55, Abadie Lana wrote: > The other query suggested by D.Rowley has the same issue : still swap > activity is higher. > explain analyze select 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW', > c.smpl_time,c.nanosecs,c.float_val,c.num_val,c.str_val,c.datatype,c.array_val > from sample c where c.channel_id in (select channel_id from channel where > name ='BUIL-B36-VA-RT-RT1:CL0001-2-ABW') order by c.smpl_time desc limit 5; This is not the query I suggested. I mentioned if channel.name had a unique index, you'd be able to do WHERE c.channel_id = (select channel_id from channel where name = '...'). That's pretty different to what you have above. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: select query does not pick up the right index
On Thu, 10 Jan 2019 at 01:55, Abadie Lana wrote: > The other query suggested by D.Rowley has the same issue : still swap > activity is higher. > explain analyze select 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW', > c.smpl_time,c.nanosecs,c.float_val,c.num_val,c.str_val,c.datatype,c.array_val > from sample c where c.channel_id in (select channel_id from channel where > name ='BUIL-B36-VA-RT-RT1:CL0001-2-ABW') order by c.smpl_time desc limit 5; This is not the query I suggested. I mentioned if channel.name had a unique index, you'd be able to do WHERE c.channel_id = (select channel_id from channel where name = '...'). That's pretty different to what you have above. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: postgresql unix socket connections
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 ? בתאריך יום ד׳, 9 בינו׳ 2019 ב-17:13 מאת Jeff Janes < jeff.ja...@gmail.com>: > On Wed, Jan 9, 2019 at 10:09 AM Mariel Cherkassky < > mariel.cherkas...@gmail.com> 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) >> > > It is not a version issue, but a packaging issue. Different systems have > different conventions on where sockets should go, and the packager imposes > their opinion on the things they package. > > Cheers, > > Jeff >
Re: postgresql unix socket connections
On Wed, Jan 9, 2019 at 10:09 AM Mariel Cherkassky < mariel.cherkas...@gmail.com> 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) > It is not a version issue, but a packaging issue. Different systems have different conventions on where sockets should go, and the packager imposes their opinion on the things they package. Cheers, Jeff
Re: postgresql unix socket connections
On Wed, Jan 9, 2019 at 3:35 AM Mariel Cherkassky < mariel.cherkas...@gmail.com> wrote: > > Now, In machine 1 when I run psql I get the prompt password but in machine > 2 I keep getting the next error : > > psql: could not connect to server: No such file or directory > Is the server running locally and accepting > connections on Unix domain socket > "/var/run/postgresql/.s.PGSQL.5432"? > > One important thing that I didnt mention, is that I installed in machine 2 > package postgresql-libs.x86_64 0:8.4.20-8.el6_9 from the postgres > repository (in order to upgrade it to 9.6). > The front end and the backend have compiled-in defaults for the socket directory. If you installed them from different sources, they may have different compiled-in defaults. Which means they may not be able to rendezvous using the default settings for both of them. You can override the default using unix_socket_directory on the server (as you discovered). On the client you can override it by using -h (or PGHOST or host= or whatever mechanism), with an argument that looks like a directory, starting with a '/'. Cheers, Jeff
Re: postgresql unix socket connections
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) בתאריך יום ד׳, 9 בינו׳ 2019 ב-16:55 מאת Tom Lane <t...@sss.pgh.pa.us >: > Mariel Cherkassky writes: > > I'm trying to understand some issues that I'm having with the unix_socket > > settings and pgsql. > > I have 2 machines with pg v9.2.5 with the same next settings : > > #listen_addresses = 'localhost' > > #unix_socket_directory = '' > > This will result in the server creating the socket in whatever it thinks > is the default socket directory. Traditionally PG uses /tmp as the > default socket directory, and your netstat result is consistent with that: > > > unix 2 [ ACC ] STREAM LISTENING 51587140 3729/postgres > >/tmp/.s.PGSQL.5432 > > However, this: > > > psql: could not connect to server: No such file or directory > > Is the server running locally and accepting > > connections on Unix domain socket > > "/var/run/postgresql/.s.PGSQL.5432"? > > shows that your psql is using a libpq that thinks the default socket > directory is /var/run/postgresql. That's a build-time option, and > I recall that Red Hat builds their postgresql package that way. > I'm not 100% sure which way the PGDG RPMs do it. > > You could override libpq's default, for instance via "psql -h /tmp". > But probably you'd be better off removing any packages that provide > libpq versions that don't match your server. > > Alternatively, you could configure the server to create socket > files in both places. > > regards, tom lane >
Re: postgresql unix socket connections
Mariel Cherkassky writes: > I'm trying to understand some issues that I'm having with the unix_socket > settings and pgsql. > I have 2 machines with pg v9.2.5 with the same next settings : > #listen_addresses = 'localhost' > #unix_socket_directory = '' This will result in the server creating the socket in whatever it thinks is the default socket directory. Traditionally PG uses /tmp as the default socket directory, and your netstat result is consistent with that: > unix 2 [ ACC ] STREAM LISTENING 51587140 3729/postgres >/tmp/.s.PGSQL.5432 However, this: > psql: could not connect to server: No such file or directory > Is the server running locally and accepting > connections on Unix domain socket > "/var/run/postgresql/.s.PGSQL.5432"? shows that your psql is using a libpq that thinks the default socket directory is /var/run/postgresql. That's a build-time option, and I recall that Red Hat builds their postgresql package that way. I'm not 100% sure which way the PGDG RPMs do it. You could override libpq's default, for instance via "psql -h /tmp". But probably you'd be better off removing any packages that provide libpq versions that don't match your server. Alternatively, you could configure the server to create socket files in both places. regards, tom lane
RE: select query does not pick up the right index
-Original Message- From: Justin Pryzby Sent: 08 January 2019 09:15 To: Abadie Lana Cc: David Rowley ; pgsql-performance@lists.postgresql.org Subject: Re: select query does not pick up the right index On Mon, Jan 07, 2019 at 04:09:50PM +, Abadie Lana wrote: > "channel_pkey" PRIMARY KEY, btree (channel_id) > "unique_chname" UNIQUE CONSTRAINT, btree (name) > "channel_name_channel_id_idx" btree (name, channel_id) Note, the third index is more or less redundant. > I would say that when you have a partitioned table, running analyse on the > parent table (which includes the children) does not give the same result as > running analyse on each individual child table. I don't know if it is an > expected behaviour? Right, for relkind='r' inheritence, ANALYZE parent gathers 1) stats for the parent ONLY (stored with pg_stats inherited='f'); and, 2) stats for the parent and its children (stored in pg_stats with inherited='t'). It *doesn't* update statistics for each of the children themselves. Note however that for partitions of relkind='p' tables (available since postgres 10) ANALYZE parent *ALSO* updates stats for the children. > But not the other query...still time-consuming because still using the > wrong index in case of sample_buil_year (but curiously not the BRIN > index) explain (analyze, buffers) select > 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW',c.smpl_time,c.nanosecs,c.float_val,c.num_val,c.str_val,c.datatype,c.array_val > from sample c WHERE c.channel_id = (SELECT channel_id FROM channel WHERE > name='BUIL-B36-VA-RT-RT1:CL0001-2-ABW') order by c.smpl_time desc limit 5; > Limit (cost=13.40..30.54 rows=5 width=112) (actual time=63411.725..63411.744 > rows=3 loops=1) >Buffers: shared hit=38 read=193865 >InitPlan 1 (returns $0) > -> Index Scan using unique_chname on channel (cost=0.41..8.43 rows=1 > width=8) (actual time=0.039..0.040 rows=1 loops =1) >Index Cond: ((name)::text = > 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW'::text) >Buffers: shared hit=4 >-> Result (cost=4.96..5294364.58 rows=1544048 width=112) (actual > time=63411.723..63411.740 rows=3 loops=1) > Buffers: shared hit=38 read=193865 > -> Merge Append (cost=4.96..5278924.10 rows=1544048 width=80) > (actual time=63411.719..63411.735 rows=3 loops=1) >Sort Key: c.smpl_time DESC >Buffers: shared hit=38 read=193865 >-> Index Scan Backward using sample_time_all_idx on sample c > (cost=0.12..8.14 rows=1 width=326) (actual time=0.048..0.048 rows=0 loops=1) > Index Cond: (channel_id = $0) > Buffers: shared hit=5 >-> Index Scan Backward using sample_time_b_idx on sample_buil > c_1 (cost=0.42..7775.26 rows=2096 width=320) (actual time=0.008..0.009 > rows=0 loops=1) > Index Cond: (channel_id = $0) > Buffers: shared hit=3 >-> Index Scan Backward using sample_time_c_idx on sample_ctrl > c_2 (cost=0.42..77785.57 rows=22441 width=320) (actual time=0.006..0.006 > rows=0 loops=1) > Index Cond: (channel_id = $0) > Buffers: shared hit=3 >-> Index Scan Backward using sample_time_u_idx on sample_util > c_3 (cost=0.43..14922.72 rows=3830 width=320) (actual time=0.008..0.008 > rows=0 loops=1) > Index Cond: (channel_id = $0) > Buffers: shared hit=3 >-> Index Scan Backward using sample_time_bm_idx on > sample_buil_month c_4 (cost=0.56..2967.10 rows=740 width=74) (actual > time=0.011..0.025 rows=3 loops=1) > Index Cond: (channel_id = $0) > Buffers: shared hit=8 >-> Index Scan Backward using sample_time_yb1_idx on > sample_buil_year c_5 (cost=0.56..2186210.68 rows=665761 width=75) (actual > time=63411.573..63411.574 rows=0 loops=1) > Index Cond: (channel_id = $0) > Buffers: shared read=193865 I think I see the issue.. Note, this is different than before. Initially the query was slow due to reading the indices for the entire heirarchy, then sorting them, then joining: | -> Index Scan Backward using smpl_time_bx2_idx on sample_buil_year c_5(cost=0.56..1897430.89 rows=50597832 width=328) (actual time=0.068..139840.439 rows=50597834 loops=1) | -> Index Scan Backward using smpl_time_cmx1_idx on sample_ctrl_month c_6 (cost=0.44..55253292.21 rows=18277124 width=85) (actual time=0.061..14610.389 rows=18277123 loops=1) | -> Index Scan Backward using smpl_time_cmx2_idx on sample_ctrl_year c_7 (cost=0.57..2987358.31 rows=79579072 width=76) (actual time=0.067..286316.865 rows=79579075 loops=1) | -> Index Scan Backward using smpl_time_ux1_idx on sample_util_month c_8 (cost=0.57..98830163.45 rows=70980976 width=82) (actual time=0.071..60766.643
Re: postgresql unix socket connections
> > I installed on machine 2 the next packages and not what I mentioned on my > last comment : > ---> Package postgresql96.x86_64 0:9.6.10-1PGDG.rhel6 will be installed ---> Package postgresql96-contrib.x86_64 0:9.6.10-1PGDG.rhel6 will be installed ---> Package postgresql96-libs.x86_64 0:9.6.10-1PGDG.rhel6 will be installed ---> Package postgresql96-server.x86_64 0:9.6.10-1PGDG.rhel6 will be installed
postgresql unix socket connections
Hi, I'm trying to understand some issues that I'm having with the unix_socket settings and pgsql. I have 2 machines with pg v9.2.5 with the same next settings : #listen_addresses = 'localhost' #unix_socket_directory = '' in both of the machines I run netstat to check on what socket the postgres listens for connections and I got the same output : machine 1 netstat -nlp | grep postgres tcp0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 2049/postgres unix 2 [ ACC ] STREAM LISTENING 12086 2049/postgres /tmp/.s.PGSQL.5432 machine 2 tcp0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 3729/postgres unix 2 [ ACC ] STREAM LISTENING 51587140 3729/postgres /tmp/.s.PGSQL.5432 In both of the machines I tried to check if there are some PG environment variables but nothing was set : env | grep PG The pg_hba in both cases is the default pg_hba. Now, In machine 1 when I run psql I get the prompt password but in machine 2 I keep getting the next error : psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"? One important thing that I didnt mention, is that I installed in machine 2 package postgresql-libs.x86_64 0:8.4.20-8.el6_9 from the postgres repository (in order to upgrade it to 9.6). I solved it in machine 2 by setting the unix_socket_directory to /var/run/postgresql/.s.PGSQL.5432 and restarting the database. My questions are : 1)Why in machine 1, where I dont have a soft link /var/run/postgresql/.s.PGSQL.5432 that directs to the temp dir I can connect succesfully ? (env|grep PG didnt show anything). 2)What might explain the issue on machine 2? Or maybe machine2 works normally but machine1 has an issue ?
