Обсуждение: common area
It seems we need something like that too. I've just run the new perftest program against PostgreSQL and Oracle. It does: 1) insert 1407 tuples into two tables 2) select one attribute from one table 3) select one attribute from the join og both tables 4) updates one attribute in one table. Here are the results: Oracle: I needed 24 seconds and 1937 microseconds for the insert test. I needed 1 seconds and 600641 microseconds for the selection&projection test. I needed 1 seconds and 703673 microseconds for the join test. I needed 2 seconds and 404709 microseconds for the update test. PostgreSQL with -F: I needed 10 seconds and 297716 microseconds for the insert test. I needed 28 seconds and 964208 microseconds for the selection&projection test. I needed 83 seconds and 931762 microseconds for the join test. I needed 0 seconds and 588390 microseconds for the update test. Michael -- Dr. Michael Meskes, Project-Manager | topsystem Systemhaus GmbH meskes@topsystem.de | Europark A2, Adenauerstr. 20 meskes@debian.org | 52146 Wuerselen Go SF49ers! Go Rhein Fire! | Tel: (+49) 2405/4670-44 Use Debian GNU/Linux! | Fax: (+49) 2405/4670-10
Tables, queries ? Also, did you look @ src/test/performance ? It would be nice to add support for Oracle there and new tests. Michael Meskes wrote: > > It seems we need something like that too. I've just run the new perftest > program against PostgreSQL and Oracle. It does: > > 1) insert 1407 tuples into two tables > 2) select one attribute from one table > 3) select one attribute from the join og both tables > 4) updates one attribute in one table. > > Here are the results: > > Oracle: > I needed 24 seconds and 1937 microseconds for the insert test. All inserts in single transaction ? > I needed 1 seconds and 600641 microseconds for the selection&projection test. > I needed 1 seconds and 703673 microseconds for the join test. > I needed 2 seconds and 404709 microseconds for the update test. > > PostgreSQL with -F: > I needed 10 seconds and 297716 microseconds for the insert test. > I needed 28 seconds and 964208 microseconds for the selection&projection test. > I needed 83 seconds and 931762 microseconds for the join test. > I needed 0 seconds and 588390 microseconds for the update test. Vadim
Vadim B. Mikheev writes:
> Tables, queries ?
It's just a very simple test program I use mainly to debug ecpg. I include
it below.
> Also, did you look @ src/test/performance ?
> It would be nice to add support for Oracle there and new tests.
Yes, that's what I'm going to do once I have ecpg completed for 6.3. I'd
like to run some of mysql benchmarks too. In particular I'd like to see a
comparison between as many DB systems as possible using e.g. the Wisconsin
benchmark.
Michael
#include <stdio.h>
#include <sys/time.h>
#include <unistd.h>
exec sql include sqlca;
exec sql whenever sqlerror sqlprint;
exec sql whenever not found sqlprint;
static void
print_result(long sec, long usec, char *text)
{
if (usec < 0)
{
sec--;
usec+=1000000;
}
printf("I needed %ld seconds and %ld microseconds for the %s test.\n", sec, usec, text);
}
int
main ()
{
exec sql begin declare section;
long i;
exec sql end declare section;
struct timeval tvs, tve;
exec sql connect 'mm';
exec sql create table perftest1(number int4, ascii char16);
exec sql create unique index number1 on perftest1(number);
exec sql create table perftest2(number int4, next_number int4);
exec sql create unique index number2 on perftest2(number);
gettimeofday(&tvs, NULL);
for (i = 0;i < 1407; i++)
{
exec sql begin declare section;
char text[16];
exec sql end declare section;
sprintf(text, "%ld", i);
exec sql insert into perftest1(number, ascii) values (:i, :text);
exec sql insert into perftest2(number, next_number) values (:i, :i+1);
exec sql commit;
}
gettimeofday(&tve, NULL);
print_result(tve.tv_sec - tvs.tv_sec, tve.tv_usec - tvs.tv_usec, "insert");
gettimeofday(&tvs, NULL);
for (i = 0;i < 1407; i++)
{
exec sql begin declare section;
char text[16];
exec sql end declare section;
exec sql select ascii into :text from perftest1 where number = :i;
exec sql commit;
}
gettimeofday(&tve, NULL);
print_result(tve.tv_sec - tvs.tv_sec, tve.tv_usec - tvs.tv_usec, "selection&projection");
gettimeofday(&tvs, NULL);
for (i = 0;i < 1407; i++)
{
exec sql begin declare section;
char text[16];
exec sql end declare section;
exec sql select perftest1.ascii into :text from perftest1, perftest2 where perftest1.number = perftest2.number
andperftest2.number = :i;
exec sql commit;
}
gettimeofday(&tve, NULL);
print_result(tve.tv_sec - tvs.tv_sec, tve.tv_usec - tvs.tv_usec, "join");
gettimeofday(&tvs, NULL);
exec sql update perftest2 set next_number = next_number + 1;
exec sql commit;
gettimeofday(&tve, NULL);
print_result(tve.tv_sec - tvs.tv_sec, tve.tv_usec - tvs.tv_usec, "update");
exec sql drop index number2;
exec sql drop table perftest2;
exec sql drop index number1;
exec sql drop table perftest1;
exec sql commit;
return (0);
}
--
Dr. Michael Meskes, Project-Manager | topsystem Systemhaus GmbH
meskes@topsystem.de | Europark A2, Adenauerstr. 20
meskes@debian.org | 52146 Wuerselen
Go SF49ers! Go Rhein Fire! | Tel: (+49) 2405/4670-44
Use Debian GNU/Linux! | Fax: (+49) 2405/4670-10
> > It seems we need something like that too. I've just run the new perftest > program against PostgreSQL and Oracle. It does: > > 1) insert 1407 tuples into two tables > 2) select one attribute from one table > 3) select one attribute from the join og both tables > 4) updates one attribute in one table. > > Here are the results: > > Oracle: > I needed 24 seconds and 1937 microseconds for the insert test. > I needed 1 seconds and 600641 microseconds for the selection&projection test. > I needed 1 seconds and 703673 microseconds for the join test. > I needed 2 seconds and 404709 microseconds for the update test. > > PostgreSQL with -F: > I needed 10 seconds and 297716 microseconds for the insert test. > I needed 28 seconds and 964208 microseconds for the selection&projection test. > I needed 83 seconds and 931762 microseconds for the join test. > I needed 0 seconds and 588390 microseconds for the update test. This seems strange. Is a vacuum being done so the optimizer knows how large each table is? -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
Bruce Momjian writes: > This seems strange. Is a vacuum being done so the optimizer knows how > large each table is? No. The problem is how to send a vacuum from ecpg. I do not know enough about libpq to know the command. And just sending it via PQexec doesn't work. Michael -- Dr. Michael Meskes, Project-Manager | topsystem Systemhaus GmbH meskes@topsystem.de | Europark A2, Adenauerstr. 20 meskes@debian.org | 52146 Wuerselen Go SF49ers! Go Rhein Fire! | Tel: (+49) 2405/4670-44 Use Debian GNU/Linux! | Fax: (+49) 2405/4670-10
Michael Meskes writes: > Bruce Momjian writes: > > This seems strange. Is a vacuum being done so the optimizer knows how > > large each table is? > > No. The problem is how to send a vacuum from ecpg. I do not know enough > about libpq to know the command. And just sending it via PQexec doesn't > work. Oops, it seems that one is a bug in ecpg. I found a way to execute it but that doesn't change much: I needed 9 seconds and 903642 microseconds for the insert test. I needed 26 seconds and 882004 microseconds for the selection&projection test. I needed 75 seconds and 983778 microseconds for the join test. I needed 0 seconds and 550797 microseconds for the update test. Michael -- Dr. Michael Meskes, Project-Manager | topsystem Systemhaus GmbH meskes@topsystem.de | Europark A2, Adenauerstr. 20 meskes@debian.org | 52146 Wuerselen Go SF49ers! Go Rhein Fire! | Tel: (+49) 2405/4670-44 Use Debian GNU/Linux! | Fax: (+49) 2405/4670-10
Bruce Momjian wrote: > > > > > Oracle: > > I needed 24 seconds and 1937 microseconds for the insert test. > > I needed 1 seconds and 600641 microseconds for the selection&projection test. > > I needed 1 seconds and 703673 microseconds for the join test. > > I needed 2 seconds and 404709 microseconds for the update test. > > > > PostgreSQL with -F: > > I needed 10 seconds and 297716 microseconds for the insert test. > > I needed 28 seconds and 964208 microseconds for the selection&projection test. > > I needed 83 seconds and 931762 microseconds for the join test. > > I needed 0 seconds and 588390 microseconds for the update test. > > This seems strange. Is a vacuum being done so the optimizer knows how > large each table is? You're right, Bruce. I don't see VACUUM in Michael' programm. This also affects the second test (selection&projection): during select backend updates data pages to set commit/abort statuses for tuples' xactions. Vadim
Michael Meskes wrote:
>
> Bruce Momjian writes:
> > This seems strange. Is a vacuum being done so the optimizer knows how
> > large each table is?
>
> No. The problem is how to send a vacuum from ecpg. I do not know enough
> about libpq to know the command. And just sending it via PQexec doesn't work.
^^^^^^^^^^^^^^^^^^^
Should work!
Vadim