Re: [GENERAL] Postgres INSERTs much slower than MySQL?

Thanks.

It's now a lot faster. Now only about 5 or so times slower. Cool.

But it wasn't unexpected that I got the following after a while ;).

NOTICE:  BufferAlloc: cannot write block 990 for joblist/central

NOTICE:  BufferAlloc: cannot write block 991 for joblist/central
DBD::Pg::st execute failed: NOTICE:  BufferAlloc: cannot write block 991
for joblist/central
Error executing insert!NOTICE:  BufferAlloc: cannot write block 991 for
joblist/central
Database handle destroyed without explicit disconnect.

I don't mind that. I was actually waiting to see what would happen and
my jaw would have dropped if MVCC could handle Multi Versions with
10,000,000 records!

But the trouble is postgres seemed to behave strangely after that error.
The select count(*) from central took so long that I gave up. I tried drop
table central, and so far it hasn't dropped yet. Single record selects
still work tho.

Well next time I'll commit after a few thousand inserts. But still things
shouldn't lock up like that right? It's only inserted a few more thousand
records to the 50000 to 60000 records stage, so it's not a big table I'm
dealing with.

I cancelled the drop, killed postmaster (nicely), restarted it and tried
vacuuming. Vacuuming found some errors, but now it has got stuck too:
NOTICE:  Index central_counter_key: pointer to EmptyPage (blk 988 off 52) -
fixing
NOTICE:  Index central_counter_key: pointer to EmptyPage (blk 988 off 53) -
fixing
Then nothing for the past 5 minutes.


Looks like I may have to manually clean things up with good ol rm. <sigh>.
Not an urgent problem since this shouldn't happen in production.

By the way, the 999,999th record has been inserted into MySQL already. It's
pretty good at the rather limited stuff it does.

But Postgres' MVCC thing sounds real cool. Not as cool as a 10MegaRecord
MVCC would be tho <grin>.

Must try screwing up Oracle one of these days. I'm pretty good at messing
things up ;).

Cheerio,

Link.

At 02:56 AM 20-10-1999 -0300, Charles Tassell wrote:
>Try turning off Autocommit: MySQL doesn't support transactions, so that
>might be what's causing the speed boost.   Just change the connect line from:
>$pg_con=DBI->connect("DBI:Pg:....
>to
>$pg_con=DBI->connect("DBI:Pg(AutoCommit=>0):....
>
>and add
>
>$pg_con->commit
>
>before you disconnect.  I may have the syntax wrong, so double check the
>docs for the DBI and PG modules (perldoc DBD::Pg and perldoc DBI)
>
>At 01:25 AM 10/20/99, Lincoln Yeoh wrote:
>>Hi everyone,
>>
>>Should inserts be so slow?
>>
>>I've written a perl script to insert 10 million records for testing
>>purposes and it looks like it's going to take a LONG time with postgres.
>>MySQL is about 150 times faster! I don't have any indexes on either. I am
>>using the DBI and relevant DBD for both.
>>
>>For Postgres 6.5.2 it's slow with either of the following table structures.
>>create table central ( counter serial, number varchar (12), name text,
>>address text );
>>create table central ( counter serial, number varchar (12), name
>>varchar(80), address varchar(80));
>>
>>For MySQL I used:
>>create table central (counter int not null auto_increment primary key,
>>number varchar(12), name varchar(80), address varchar(80));
>>
>>The relevant perl portion is (same for both):
>>        $SQL=<<"EOT";
>>insert into central (number,name,address) values (?,?,?)
>>EOT
>>        $cursor=$dbh->prepare($SQL);
>>
>>    while ($c<10000000) {
>>        $number=$c;
>>        $name="John Doe the number ".$c;
>>        $address="$c, Jalan SS$c/$c, Petaling Jaya";
>>        $rv=$cursor->execute($number,$name,$address) or die("Error executing
>>insert!",$DBI::errstr);
>>        if ($rv==0) {
>>            die("Error inserting a record with database!",$DBI::errstr);
>>        };
>>        $c++;
>>        $d++;
>>        if ($d>1000) {
>>            print "$c\n";
>>            $d=1;
>>        }
>>    }
>>
>>
>>
>>************
>>
>
>
>