Re: [PERFORM] 10x faster sort performance on Skylake CPU vs Ivy Bridge
> On Aug 25, 2017, at 17:07, Tom Lane wrote: > > =?utf-8?Q?Felix_Geisend=C3=B6rfer?= writes: >> I recently came across a performance difference between two machines that >> surprised me: >> ... >> As you can see, Machine A spends 5889ms on the Sort Node vs 609ms on Machine >> B when looking at the "Exclusive" time with explain.depesz.com [3][4]. I.e. >> Machine B is ~10x faster at sorting than Machine B (for this particular >> query). > > I doubt this is a hardware issue, it's more likely that you're comparing > apples and oranges. The first theory that springs to mind is that the > sort keys are strings and you're using C locale on the faster machine but > some non-C locale on the slower. strcoll() is pretty darn expensive > compared to strcmp() :-( You're right, that seems to be it. Machine A was using strcoll() (lc_collate=en_US.UTF-8) Machine B was using strcmp() (lc_collate=C) After switching Machine A to use lc_collate=C, I get: CTE Scan on zulu (cost=40673.620..40742.300 rows=3434 width=56) (actual time=1368.610..1368.698 rows=58 loops=1) CTE zulu -> HashAggregate (cost=40639.280..40673.620 rows=3434 width=56) (actual time=1368.607..1368.659 rows=58 loops=1) Group Key: mike.two, ((mike.golf)::text) -> Unique (cost=37656.690..40038.310 rows=34341 width=104) (actual time=958.493..1168.128 rows=298104 loops=1) -> Sort (cost=37656.690..38450.560 rows=317549 width=104) (actual time=958.491..1055.635 rows=316982 loops=1) Sort Key: mike.two, ((mike.lima)::text) COLLATE "papa", mike.echo DESC, mike.quebec Sort Method: quicksort Memory: 56834kB -> Seq Scan on mike (cost=0.000..8638.080 rows=317549 width=104) (actual time=0.043..172.496 rows=316982 loops=1) Filter: (golf five NOT NULL) Rows Removed by Filter: 26426 So Machine A needs 883ms [1] for the sort vs 609ms [2] for Machine B. That's ~1.4x faster which seems reasonable :). Sorry for the delayed response, I didn't have access to machine B to confirm this right away. > regards, tom lane This is my first post to a PostgreSQL mailing list, but I've been lurking for a while. Thank you for taking the time for replying to e-mails such as mine and all the work you've put into PostgreSQL over the years. I'm deeply grateful. > On Aug 25, 2017, at 17:43, Peter Geoghegan wrote: > > On Fri, Aug 25, 2017 at 8:07 AM, Tom Lane wrote: >> I doubt this is a hardware issue, it's more likely that you're comparing >> apples and oranges. The first theory that springs to mind is that the >> sort keys are strings and you're using C locale on the faster machine but >> some non-C locale on the slower. strcoll() is pretty darn expensive >> compared to strcmp() :-( > > strcoll() is very noticeably slower on macOS, too. > Thanks. This immediately explains what I saw when testing this query on a Linux machine that was also using lc_collate=en_US.UTF-8 but not being slowed down by it as much as the macOS machine. [1] https://explain.depesz.com/s/LOqa [2] https://explain.depesz.com/s/zVe -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] performance problem on big tables
Hi, yes indeed I'm using laurenz`s oracle_fdw extension. I tried to run it but I'm getting error dbch=# ALTER FOREIGN TABLE tc_sub_rate_ver_prod OPTIONS ( SET prefetch 10240 ); ERROR: syntax error at or near "10240" LINE 1: ...N TABLE tc_sub_rate_ver_prod OPTIONS ( SET prefetch 10240 ); dbch=# alter foreign table tc_sub_rate_ver_prod OPTIONS (SET prefetch '10240'); ERROR: option "prefetch" not found 2017-08-24 19:14 GMT+03:00 Claudio Freire : > On Thu, Aug 24, 2017 at 4:51 AM, Mariel Cherkassky > wrote: > > Hi Claudio, how can I do that ? Can you explain me what is this option ? > > > > 2017-08-24 2:15 GMT+03:00 Claudio Freire : > >> > >> On Mon, Aug 21, 2017 at 5:00 AM, Mariel Cherkassky > >> wrote: > >> > To summarize, I still have performance problems. My current situation > : > >> > > >> > I'm trying to copy the data of many tables in the oracle database into > >> > my > >> > postgresql tables. I'm doing so by running insert into > >> > local_postgresql_temp > >> > select * from remote_oracle_table. The performance of this operation > are > >> > very slow and I tried to check the reason for that and mybe choose a > >> > different alternative. > >> > > >> > 1)First method - Insert into local_postgresql_table select * from > >> > remote_oracle_table this generated total disk write of 7 M/s and > actual > >> > disk > >> > write of 4 M/s(iotop). For 32G table it took me 2 hours and 30 > minutes. > >> > > >> > 2)second method - copy (select * from oracle_remote_table) to > /tmp/dump > >> > generates total disk write of 4 M/s and actuval disk write of 100 K/s. > >> > The > >> > copy utility suppose to be very fast but it seems very slow. > >> > >> Have you tried increasing the prefetch option in the remote table? > >> > >> If you left it in its default, latency could be hurting your ability > >> to saturate the network. > > > > > > Please don't top-post. > > I'm assuming you're using this: http://laurenz.github.io/oracle_fdw/ > > If you check the docs, you'll see this: > https://github.com/laurenz/oracle_fdw#foreign-table-options > > So I'm guessing you could: > > ALTER FOREIGN TABLE remote_table OPTIONS ( SET prefetch 10240 ); >
Re: [PERFORM] performance problem on big tables
On Sun, Aug 27, 2017 at 1:34 PM, Mariel Cherkassky wrote: > Hi, yes indeed I'm using laurenz`s oracle_fdw extension. I tried to run it > but I'm getting error > > dbch=# ALTER FOREIGN TABLE tc_sub_rate_ver_prod OPTIONS ( SET prefetch 10240 > ); > ERROR: syntax error at or near "10240" > LINE 1: ...N TABLE tc_sub_rate_ver_prod OPTIONS ( SET prefetch 10240 ); Yeah, might need to put the 10240 in quotes. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] performance problem on big tables
On Sun, Aug 27, 2017 at 1:34 PM, Mariel Cherkassky wrote: > Hi, yes indeed I'm using laurenz`s oracle_fdw extension. I tried to run it > but I'm getting error > > dbch=# ALTER FOREIGN TABLE tc_sub_rate_ver_prod OPTIONS ( SET prefetch 10240 > ); > ERROR: syntax error at or near "10240" > LINE 1: ...N TABLE tc_sub_rate_ver_prod OPTIONS ( SET prefetch 10240 ); > > > dbch=# alter foreign table tc_sub_rate_ver_prod OPTIONS (SET prefetch > '10240'); > ERROR: option "prefetch" not found Oh, sorry, I hadn't seen this until I hit send. Unless the documentation is inaccurate or you're using a really old version (from the changelog that option is from 2016), that should work. I don't have enough experience with oracle_fdw to help there, most of my dealings have been with postgres_fdw. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] performance problem on big tables
I have the newest version : select oracle_diag(); oracle_diag - oracle_fdw 1.5.0, PostgreSQL 9.6.3, Oracle client 11.2.0.4.0, ORACLE_HOME=/PostgreSQL/9.6/tools/instantclient_11_2/ (1 row) Is there a prefetch also for local tables ? I mean If I run with a cursor over results of a select query, mybe setting the prefetch for a local table might also improve performance ? 2017-08-28 8:51 GMT+03:00 Claudio Freire : > On Sun, Aug 27, 2017 at 1:34 PM, Mariel Cherkassky > wrote: > > Hi, yes indeed I'm using laurenz`s oracle_fdw extension. I tried to run > it > > but I'm getting error > > > > dbch=# ALTER FOREIGN TABLE tc_sub_rate_ver_prod OPTIONS ( SET prefetch > 10240 > > ); > > ERROR: syntax error at or near "10240" > > LINE 1: ...N TABLE tc_sub_rate_ver_prod OPTIONS ( SET prefetch 10240 ); > > > > > > dbch=# alter foreign table tc_sub_rate_ver_prod OPTIONS (SET prefetch > > '10240'); > > ERROR: option "prefetch" not found > > Oh, sorry, I hadn't seen this until I hit send. > > Unless the documentation is inaccurate or you're using a really old > version (from the changelog that option is from 2016), that should > work. > > I don't have enough experience with oracle_fdw to help there, most of > my dealings have been with postgres_fdw. >
