pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedFirebird 1.5.3 X Postgresql 8.1.3 (linux and windows)
10+ messages / 5 participants
[nested] [flat]
* Firebird 1.5.3 X Postgresql 8.1.3 (linux and windows)
@ 2006-04-25 16:38 andremachado <andremachado@techforce.com.br>
0 siblings, 1 reply; 10+ messages in thread
From: andremachado @ 2006-04-25 16:38 UTC (permalink / raw)
To: pgsql-performance
Hello,
The performance comparison saga of the last month continues (see list archive).
After some time experimenting on windows, the conclusion is clear:
windows is likely crap for databases other than MS-SQL.
I guess that MS-SQL uses lot of undocumented api calls, may run in kernel
mode, ring 0 and a lot of dirty tricks to get some reasonable performance.
Then, I asked my coleague to send a new FB dump and a Pg dump to try at my
desktop machine.
This time, the database is somewhat bigger. Around 20 million records.
The timings are attached. Tried to follow the same query sequence on both files.
Both databases are much more faster on linux than on windows, and the desktop
machine is not dedicated and tuned. (no scsi, no raid, many services enabled,
ext3 fs, etc).
At many of the queries, postgresql is faster, sometimes way MUCH faster.
But Firebird have very good defaults out of the box and a few of the queries
are really a pain in Postgresql.
Please, see the abismal timing differences at the last 2 queries, for example.
They used 100% cpu, almost no disk activity, no twait cpu, for loooong time to
complete.
Maybe these queries bring into the light some instructions weaknesses, or bad
tuning.
Do you have some suggestions?
Regards.
Andre Felipe Machado
http://www.techforce.com.br
linux blog
Attachments:
queries_timing_firebird.txt.tar.gz (application/x-gzip, 1.7K) download
queries_timing_postgresql.txt.tar.gz (application/x-gzip, 9.8K) download
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Firebird 1.5.3 X Postgresql 8.1.3 (linux and windows)
@ 2006-04-26 02:06 Tom Lane <tgl@sss.pgh.pa.us>
parent: andremachado <andremachado@techforce.com.br>
0 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2006-04-26 02:06 UTC (permalink / raw)
To: andremachado <andremachado@techforce.com.br>; +Cc: pgsql-performance
"andremachado" <andremachado@techforce.com.br> writes:
> After some time experimenting on windows, the conclusion is clear:
> windows is likely crap for databases other than MS-SQL.
Maybe. One thing that comes to mind is that you really should do some
performance tuning experiments. In particular it'd be a good idea to
increase checkpoint_segments and try other settings for wal_sync_method.
Your fifth query,
bddnf=# explain analyze update NOTA_FISCAL set VA_TOTAL_ITENSDNF = (select sum(ITEM_NOTA.VA_TOTAL) from ITEM_NOTA where ITEM_NOTA.ID_NF = NOTA_FISCAL.ID_NF) where ID_NF in (select NF2.ID_NF from DECLARACAO DE inner join CADASTRO CAD on (CAD.ID_DECLARACAO=DE.ID_DECLARACAO) inner join NOTA_FISCAL NF2 on (NF2.ID_CADASTRO=CAD.ID_CADASTRO) where DE.ID_ARQUIVO in (1) );
shows runtime of the plan proper as 158 seconds but total runtime as 746
seconds --- the discrepancy has to be associated with writing out the
updated rows, which there are a lot of (719746) in this query, but still
we should be able to do it faster than that. So I surmise a bottleneck
in pushing WAL updates to disk.
The last two queries are interesting. Does Firebird have any equivalent
of EXPLAIN, ie a way to see what sort of query plan they are using?
I suspect they are being more aggressive about optimizing the max()
functions in the sub-selects than we are. In particular, the 8.1 code
for optimizing min/max just punts if it sees any sub-selects in the
WHERE clause, which prevents us from doing anything with these examples.
/*
* Also reject cases with subplans or volatile functions in WHERE. This
* may be overly paranoid, but it's not entirely clear if the
* transformation is safe then.
*/
if (contain_subplans(parse->jointree->quals) ||
contain_volatile_functions(parse->jointree->quals))
return NULL;
This is something I'd wanted to go back and look at more carefully, but
never got around to.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Firebird 1.5.3 X Postgresql 8.1.3 (linux Firebird 1.5.3 X Postgresql 8.1.3 (linux and and windows)]
@ 2006-04-27 12:32 andremachado <andremachado@techforce.com.br>
0 siblings, 1 reply; 10+ messages in thread
From: andremachado @ 2006-04-27 12:32 UTC (permalink / raw)
To: pgsql-performance
Hello,
Many thanks for your suggestions.
I will try them.
The last two queries almost did not use disk, but used 100% cpu.
The differences of performance are big.
Firebird has something similiar to EXPLAIN. Please look below.
Is there something really wrong with the postgresql configuration (at my
previous msg) that is causing this poor performance at these 2 queries?
I tweaked until almost no disk was used, but now it is using 100% cpu and took
too much time to complete.
Thanks.
Andre Felipe Machado
http://www.techforce.com.br
SQL> set plan on;
SQL> set stats on;
SQL> update CADASTRO set IN_CADASTRO_MAIS_ATUAL = case when
CADASTRO.ID_CADASTRO= (select max(CAD2.ID_CADASTRO) from CADASTRO CAD2 inner
join DECLARACAO DECL on (DECL.ID_DECLARACAO=CAD2.ID_DECLARACAO) where
CAD2.ID_EMPRESA=CADASTRO.ID_EMPRESA and DECL.AM_REFERENCIA = (select
max(DEC2.AM_REFERENCIA) from DECLARACAO DEC2 where DEC2.IN_FOI_RETIFICADA=0
and 1 in (select CAD3.ID_CADASTRO from CADASTRO CAD3 where
CAD3.ID_DECLARACAO=DEC2.ID_DECLARACAO and CAD3.ID_EMPRESA=CADASTRO.ID_EMPRESA
) )and DECL.IN_FOI_RETIFICADA=0 )then 1 else 0 end;
PLAN (CAD3 INDEX (PK_CADASTRO_DESC))
PLAN (DEC2 NATURAL)
PLAN JOIN (DECL INDEX (IDX_DT_REFERENCIA),CAD2 INDEX (RDB$FOREIGN18))
PLAN (CADASTRO NATURAL)
Current memory = 911072
Delta memory = 355620
Max memory = 911072
Elapsed time= 1.89 sec
Cpu = 0.00 sec
Buffers = 2048
Reads = 1210
Writes = 14
Fetches = 310384
SQL>
SQL> update CADASTRO set IN_CADASTRO_MAIS_ATUAL = case when
CADASTRO.ID_CADASTRO= (select max(CAD2.ID_CADASTRO) from CADASTRO CAD2 inner
join DECLARACAO DECL on (DECL.ID_DECLARACAO=CAD2.ID_DECLARACAO) where
CAD2.ID_EMPRESA=CADASTRO.ID_EMPRESA and DECL.AM_REFERENCIA = (select
max(DEC2.AM_REFERENCIA) from DECLARACAO DEC2 where DEC2.IN_FOI_RETIFICADA=0
and exists (select CAD3.ID_CADASTRO from CADASTRO CAD3 where
CAD3.ID_DECLARACAO=DEC2.ID_DECLARACAO and CAD3.ID_EMPRESA=CADASTRO.ID_EMPRESA
) )and DECL.IN_FOI_RETIFICADA=0 )then 1 else 0 end;
PLAN (CAD3 INDEX (RDB$FOREIGN18))
PLAN (DEC2 NATURAL)
PLAN JOIN (DECL INDEX (IDX_DT_REFERENCIA),CAD2 INDEX (RDB$FOREIGN18))
PLAN (CADASTRO NATURAL)
Current memory = 938968
Delta memory = 8756
Max memory = 15418996
Elapsed time= 1.09 sec
Cpu = 0.00 sec
Buffers = 2048
Reads = 0
Writes = 0
Fetches = 301007
SQL>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Firebird 1.5.3 X Postgresql 8.1.3 (linux Firebird 1.5.3 X Postgresql 8.1.3 (linux and and windows)]
@ 2006-04-27 16:13 Tom Lane <tgl@sss.pgh.pa.us>
parent: andremachado <andremachado@techforce.com.br>
0 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2006-04-27 16:13 UTC (permalink / raw)
To: andremachado <andremachado@techforce.com.br>; +Cc: pgsql-performance
"andremachado" <andremachado@techforce.com.br> writes:
> Firebird has something similiar to EXPLAIN. Please look below.
Hm, maybe I just don't know how to read their output, but it's not
obvious to me where they are doing the min/max aggregates.
> Is there something really wrong with the postgresql configuration (at my
> previous msg) that is causing this poor performance at these 2 queries?
I don't think it's a configuration issue, it's a quality-of-plan issue.
Could you put together a self-contained test case for this problem? I
don't have the time or interest to try to reverse-engineer tables and
test data for these queries --- but I would be interested in finding out
where the time is going, if I could run the queries.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Firebird 1.5.3 X Postgresql 8.1.3 (linux and windows)
@ 2006-05-12 14:48 andremachado <andremachado@techforce.com.br>
0 siblings, 3 replies; 10+ messages in thread
From: andremachado @ 2006-05-12 14:48 UTC (permalink / raw)
To: pgsql-performance
Hello,
continuing the saga,
http://archives.postgresql.org/pgsql-performance/2006-04/msg00558.php ,
my coleague created a test database with fake data (see below).
The above archived message contains the the timings of firebird and postgresql.
The weird problem are the 2 queries that firebird executes in less than 2
seconds and postgresql took almost half hour to complete at 100% cpu.
you could download the test database at the address below. It is a 128 kpbs
adsl connection.
74 MB
http://www.eicomm.no-ip.com/download/BackDNF_Cript.zip
Many thanks.
Andre Felipe Machado
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Firebird 1.5.3 X Postgresql 8.1.3 (linux and windows)
@ 2006-05-12 19:14 Jim C. Nasby <jnasby@pervasive.com>
parent: andremachado <andremachado@techforce.com.br>
2 siblings, 2 replies; 10+ messages in thread
From: Jim C. Nasby @ 2006-05-12 19:14 UTC (permalink / raw)
To: andremachado <andremachado@techforce.com.br>; +Cc: pgsql-performance
On Fri, May 12, 2006 at 12:48:52PM -0200, andremachado wrote:
> Hello,
> continuing the saga,
> http://archives.postgresql.org/pgsql-performance/2006-04/msg00558.php ,
> my coleague created a test database with fake data (see below).
>
> The above archived message contains the the timings of firebird and postgresql.
> The weird problem are the 2 queries that firebird executes in less than 2
> seconds and postgresql took almost half hour to complete at 100% cpu.
How about posting EXPLAIN ANALYZE for those two queries, as well as the
queries themselves?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Firebird 1.5.3 X Postgresql 8.1.3 (linux and windows)
@ 2006-05-12 19:54 Joshua D. Drake <jd@commandprompt.com>
parent: Jim C. Nasby <jnasby@pervasive.com>
1 sibling, 0 replies; 10+ messages in thread
From: Joshua D. Drake @ 2006-05-12 19:54 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: andremachado <andremachado@techforce.com.br>; pgsql-performance
Jim C. Nasby wrote:
> On Fri, May 12, 2006 at 12:48:52PM -0200, andremachado wrote:
>> Hello,
>> continuing the saga,
>> http://archives.postgresql.org/pgsql-performance/2006-04/msg00558.php ,
>> my coleague created a test database with fake data (see below).
>>
>> The above archived message contains the the timings of firebird and postgresql.
>> The weird problem are the 2 queries that firebird executes in less than 2
>> seconds and postgresql took almost half hour to complete at 100% cpu.
>
> How about posting EXPLAIN ANALYZE for those two queries, as well as the
> queries themselves?
I have this database downloaded if anyone wants a copy off a faster link.
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Firebird 1.5.3 X Postgresql 8.1.3 (linux and windows)
@ 2006-05-12 22:19 Tom Lane <tgl@sss.pgh.pa.us>
parent: andremachado <andremachado@techforce.com.br>
2 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2006-05-12 22:19 UTC (permalink / raw)
To: andremachado <andremachado@techforce.com.br>; +Cc: pgsql-performance
"andremachado" <andremachado@techforce.com.br> writes:
> continuing the saga,
> http://archives.postgresql.org/pgsql-performance/2006-04/msg00558.php ,
> my coleague created a test database with fake data (see below).
Thanks. I played around with this a bit, and got results like these:
original query, 8.1 branch from a couple weeks back: 945 sec
original query, 8.1 branch tip: 184 sec
modified query, 8.1 branch tip: 15 sec
The first differential is because of this patch:
http://archives.postgresql.org/pgsql-committers/2006-04/msg00355.php
viz
Remove the restriction originally coded into
optimize_minmax_aggregates() that MIN/MAX not be converted to
use an index if the query WHERE clause contains any volatile
functions or subplans.
Allowing the max(DEC2.AM_REFERENCIA) subquery to be converted to an
indexscan makes for about a 5X reduction in the number of times the
EXISTS sub-subquery is executed. But the real problem is that Postgres
isn't excessively smart about EXISTS subqueries. I manually changed it
into an IN to get the 15-second runtime: instead of
(select max(DEC2.AM_REFERENCIA) from DECLARACAO DEC2
where DEC2.IN_FOI_RETIFICADA=0 and
exists (select CAD3.ID_CADASTRO from CADASTRO CAD3 where
CAD3.ID_DECLARACAO=DEC2.ID_DECLARACAO and
CAD3.ID_EMPRESA=CADASTRO.ID_EMPRESA ) )
write
(select max(DEC2.AM_REFERENCIA) from DECLARACAO DEC2
where DEC2.IN_FOI_RETIFICADA=0 and DEC2.ID_DECLARACAO in
(select CAD3.ID_DECLARACAO from CADASTRO CAD3 where
CAD3.ID_EMPRESA=CADASTRO.ID_EMPRESA ) )
I'm not clear on how Firebird is managing to do this query in under
a second --- I can believe that they know how to do EXISTS as a join
but it still seems like the subqueries need to be done many thousand
times. I thought maybe they were caching the results of the overall
subquery for specific values of CADASTRO.ID_EMPRESA, but now that I
see your test data, there are several thousand distinct values of
that, so there's not a lot of traction to be gained that way.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Firebird 1.5.3 X Postgresql 8.1.3 (linux and windows)
@ 2006-05-13 09:58 André Felipe Machado <andremachado@techforce.com.br>
parent: Jim C. Nasby <jnasby@pervasive.com>
1 sibling, 0 replies; 10+ messages in thread
From: André Felipe Machado @ 2006-05-13 09:58 UTC (permalink / raw)
To: pgsql-performance; +Cc: Jim C. Nasby <jnasby@pervasive.com>
Hello, Jim
I did not want to clutter mailboxes of those who are not interested at
this weird problem.
So, i pointed to the archived message that contains 2 tar.gz files
(around 50 KB) with the two sets of queries (firebird and postgresql
respectively), its results, explain analyze, pg configurations, firebird
plan outputs, etc.
Please, open the cited link and scroll to the end of message. You will
find the 2 tar.gz files.
http://archives.postgresql.org/pgsql-performance/2006-04/msg00558.php
If you have some difficulty, I could send a private email containing the
2 files in order to not send the big email to the all list again.
Many thanks.
Andre Felipe Machado
Em Sex, 2006-05-12 às 14:14 -0500, Jim C. Nasby escreveu:
> On Fri, May 12, 2006 at 12:48:52PM -0200, andremachado wrote:
> > Hello,
> > continuing the saga,
> > http://archives.postgresql.org/pgsql-performance/2006-04/msg00558.php ,
> > my coleague created a test database with fake data (see below).
> >
> > The above archived message contains the the timings of firebird and postgresql.
> > The weird problem are the 2 queries that firebird executes in less than 2
> > seconds and postgresql took almost half hour to complete at 100% cpu.
>
> How about posting EXPLAIN ANALYZE for those two queries, as well as the
> queries themselves?
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Firebird 1.5.3 X Postgresql 8.1.3 (linux and windows)
@ 2006-05-13 21:26 Tom Lane <tgl@sss.pgh.pa.us>
parent: andremachado <andremachado@techforce.com.br>
2 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2006-05-13 21:26 UTC (permalink / raw)
To: andremachado <andremachado@techforce.com.br>; +Cc: pgsql-performance
"andremachado" <andremachado@techforce.com.br> writes:
> continuing the saga,
> http://archives.postgresql.org/pgsql-performance/2006-04/msg00558.php ,
> my coleague created a test database with fake data (see below).
I tried to use this data to replicate your results, and could not.
I grabbed a copy of what I think is the latest Firebird release,
firebird-1.5.3.4870, built it on a Fedora Core 4 machine (32-bit,
couldn't get it to build cleanly on my newer 64-bit machine :-()
and compared to Postgres 8.1 branch tip on the same machine.
On the interesting sub-sub-EXISTS query, I see these results:
Firebird:
SQL> set stats on;
SQL> set plan on;
SQL> update CADASTRO set IN_CADASTRO_MAIS_ATUAL = case when CADASTRO.ID_CADASTRO= (select max(CAD2.ID_CADASTRO) from CADASTRO CAD2 inner join DECLARACAO DECL on (DECL.ID_DECLARACAO=CAD2.ID_DECLARACAO) where CAD2.ID_EMPRESA=CADASTRO.ID_EMPRESA and DECL.AM_REFERENCIA = (select max(DEC2.AM_REFERENCIA) from DECLARACAO DEC2 where DEC2.IN_FOI_RETIFICADA=0 and exists (select CAD3.ID_CADASTRO from CADASTRO CAD3 where CAD3.ID_DECLARACAO=DEC2.ID_DECLARACAO and CAD3.ID_EMPRESA=CADASTRO.ID_EMPRESA ) )and DECL.IN_FOI_RETIFICADA=0 )then 1 else 0 end ;
PLAN (CAD3 INDEX (RDB$FOREIGN1))
PLAN (DEC2 NATURAL)
PLAN JOIN (DECL INDEX (IDX_DT_REFERENCIA),CAD2 INDEX (RDB$FOREIGN1))
PLAN (CADASTRO NATURAL)
Current memory = 786704
Delta memory = 309056
Max memory = 786704
Elapsed time= 344.19 sec
Cpu = 0.03 sec
Buffers = 75
Reads = 2081702
Writes = 16173
Fetches = 21713743
The cpu = 0.03 sec bit is bogus; in reality the CPU is maxed out
and the isql process accumulates very nearly 344 seconds runtime.
Postgres:
bc=# \timing
Timing is on.
bc=# update CADASTRO set IN_CADASTRO_MAIS_ATUAL = case when CADASTRO.ID_CADASTRO= (select max(CAD2.ID_CADASTRO) from CADASTRO CAD2 inner join DECLARACAO DECL on (DECL.ID_DECLARACAO=CAD2.ID_DECLARACAO) where CAD2.ID_EMPRESA=CADASTRO.ID_EMPRESA and DECL.AM_REFERENCIA = (select max(DEC2.AM_REFERENCIA) from DECLARACAO DEC2 where DEC2.IN_FOI_RETIFICADA=0 and exists (select CAD3.ID_CADASTRO from CADASTRO CAD3 where CAD3.ID_DECLARACAO=DEC2.ID_DECLARACAO and CAD3.ID_EMPRESA=CADASTRO.ID_EMPRESA ) )and DECL.IN_FOI_RETIFICADA=0 )then 1 else 0 end ;
UPDATE 15490
Time: 420350.628 ms
Now I know nothing about Firebird and it's quite possible that I missed
some essential tuning step, but I'm sure not in the same ballpark as
your report of 0.72 sec to run this query.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2006-05-13 21:26 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-25 16:38 Firebird 1.5.3 X Postgresql 8.1.3 (linux and windows) andremachado <andremachado@techforce.com.br>
2006-04-26 02:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-04-27 12:32 Firebird 1.5.3 X Postgresql 8.1.3 (linux Firebird 1.5.3 X Postgresql 8.1.3 (linux and and windows)] andremachado <andremachado@techforce.com.br>
2006-04-27 16:13 ` Re: Firebird 1.5.3 X Postgresql 8.1.3 (linux Firebird 1.5.3 X Postgresql 8.1.3 (linux and and windows)] Tom Lane <tgl@sss.pgh.pa.us>
2006-05-12 14:48 Firebird 1.5.3 X Postgresql 8.1.3 (linux and windows) andremachado <andremachado@techforce.com.br>
2006-05-12 19:14 ` Jim C. Nasby <jnasby@pervasive.com>
2006-05-12 19:54 ` Joshua D. Drake <jd@commandprompt.com>
2006-05-13 09:58 ` André Felipe Machado <andremachado@techforce.com.br>
2006-05-12 22:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-05-13 21:26 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox