Thread: Concurrent testing PostgreSQL Vs MySQL

Durai wrote:

> Hi All,
>
>             I tested "concurrent testing" in MySQL. It works fine. But I
> couldn't do in PostgreSQL 7.3.4 on HPUX IPF. I got deadlock problem.  I
> used the PHP script to update table( one script increment the column &
> another one decrement the column). Is the postgres support the
> concurrent access to update database?
>
> I got the following errors:
>
> test=# ERROR:  deadlock detected
> ERROR:  deadlock detected
> ERROR:  deadlock detected

Did you commited the transaction anywhere? Can we see the php code?

  Shridhar

Użytkownik Durai napisał:

 > Hello Shridhar,
 >
 >                     The script content is:
 > ** *# cat te**stpgsql.php*
 > *
 > *<?php
 > $dbconn = pg_connect("host=172.16.1.158 port=5432 dbname=test
user=postgres");
 > $result = pg_exec ($dbconn, "update table2 set C2=C2+1;");
 > $result = pg_exec ($dbconn, "update table2 set C2=C2-1;");
 > pg_close($dbconn);


You have to read about transactions in PostgreSQL or you should turn on
autocommit in conf file...

Durai wrote:

> Hello Shridhar,
>
>                     The script content is:
> **
> *# cat te**stpgsql.php*
> *
> *<?php
> $dbconn = pg_connect("host=172.16.1.158 port=5432 dbname=test
> user=postgres");
> $result = pg_exec ($dbconn, "update table2 set C2=C2+1;");
> $result = pg_exec ($dbconn, "update table2 set C2=C2-1;");

$result = pg_exec ($dbconn, "commit;");

> pg_close($dbconn);
> ?>
> #

Try it now. It should work without problem.

  shridhar

Hello Sridhar,

No. Still Its not working.

I used the following command.

# bin/ab -c 5 -n 50 http://127.0.0.1:7070/testpgsql.php

I got the following error:

test=# ERROR:  deadlock detected
ERROR:  deadlock detected
WARNING:  COMMIT: no transaction in progress
WARNING:  COMMIT: no transaction in progress
WARNING:  COMMIT: no transaction in progress
WARNING:  COMMIT: no transaction in progress
WARNING:  COMMIT: no transaction in progress
WARNING:  COMMIT: no transaction in progress
ERROR:  deadlock detected
ERROR:  deadlock detected

The script works only it has to be like:

<?php
$dbconn = pg_connect("host=172.16.1.158 port=5432 dbname=test
user=postgres");
$result = pg_exec ($dbconn, "update table2 set C2=C2+1;");
pg_close($dbconn);
$dbconn = pg_connect("host=172.16.1.158 port=5432 dbname=test
user=postgres");
$result = pg_exec ($dbconn, "update table2 set C2=C2-1;");
pg_close($dbconn);
?>

Regs,
Durai.


----- Original Message -----
From: "Shridhar Daithankar" <shridhar_daithankar@myrealbox.com>
To: "Durai" <visolve_postgres@lycos.co.uk>
Cc: <pgsql-general@postgresql.org>
Sent: Friday, November 21, 2003 4:09 PM
Subject: Re: [GENERAL] Concurrent testing PostgreSQL Vs MySQL


> Durai wrote:
>
> > Hello Shridhar,
> >
> >                     The script content is:
> > **
> > *# cat te**stpgsql.php*
> > *
> > *<?php
> > $dbconn = pg_connect("host=172.16.1.158 port=5432 dbname=test
> > user=postgres");
> > $result = pg_exec ($dbconn, "update table2 set C2=C2+1;");
> > $result = pg_exec ($dbconn, "update table2 set C2=C2-1;");
>
> $result = pg_exec ($dbconn, "commit;");
>
> > pg_close($dbconn);
> > ?>
> > #
>
> Try it now. It should work without problem.
>
>   shridhar
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.538 / Virus Database: 333 - Release Date: 11/11/2003

Damn.. I forgot to begin the transaction..

Shridhar Daithankar wrote:
> Durai wrote:
>> Hello Shridhar,
>>                     The script content is:
>> ** *# cat te**stpgsql.php*
>> *
>> *<?php
>> $dbconn = pg_connect("host=172.16.1.158 port=5432 dbname=test
>> user=postgres");

$result = pg_exec ($dbconn, "begin;");

>> $result = pg_exec ($dbconn, "update table2 set C2=C2+1;");
>> $result = pg_exec ($dbconn, "update table2 set C2=C2-1;");
> $result = pg_exec ($dbconn, "commit;");
>> pg_close($dbconn);
>> ?>
>> #

Check this..

  Shridhar

Hello Sridhar,

                    Now, It works. Thanks lot.

But Still I have doubt:

1. How MySQL works in this case? It has any default "autocommit"?

2. Is there any other method to test for "concurrent testing"?

Regs,
Durai.
----- Original Message -----
From: "Shridhar Daithankar" <shridhar_daithankar@myrealbox.com>
To: "Shridhar Daithankar" <shridhar_daithankar@myrealbox.com>
Cc: "Durai" <visolve_postgres@lycos.co.uk>; <pgsql-general@postgresql.org>
Sent: Friday, November 21, 2003 5:28 PM
Subject: Re: [GENERAL] Concurrent testing PostgreSQL Vs MySQL


> Damn.. I forgot to begin the transaction..
>
> Shridhar Daithankar wrote:
> > Durai wrote:
> >> Hello Shridhar,
> >>                     The script content is:
> >> ** *# cat te**stpgsql.php*
> >> *
> >> *<?php
> >> $dbconn = pg_connect("host=172.16.1.158 port=5432 dbname=test
> >> user=postgres");
>
> $result = pg_exec ($dbconn, "begin;");
>
> >> $result = pg_exec ($dbconn, "update table2 set C2=C2+1;");
> >> $result = pg_exec ($dbconn, "update table2 set C2=C2-1;");
> > $result = pg_exec ($dbconn, "commit;");
> >> pg_close($dbconn);
> >> ?>
> >> #
>
> Check this..
>
>   Shridhar
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.538 / Virus Database: 333 - Release Date: 11/11/2003

Durai wrote:

> Hello Sridhar,
>
>                     Now, It works. Thanks lot.
>
> But Still I have doubt:
>
> 1. How MySQL works in this case? It has any default "autocommit"?

Depends. What version of mysql you are using? If you are using mysql 3.x and/or
non-innodb tables, you don't have transactions in first place. Mysql does not
need autocommit in earlier version because it does not have transactions in
first place.

> 2. Is there any other method to test for "concurrent testing"?

Against mysql? I doubt. You can try scaling number of concurrent connections and
  number of rows in database. Furthermore don't do bulk update but do selective
random updates. And verify the results in your php section.


  Shridhar

On Fri, Nov 21, 2003 at 03:39:42PM +0530, Durai wrote:

>                     The script content is:
>
> # cat testpgsql.php
>
> <?php
> $dbconn = pg_connect("host=172.16.1.158 port=5432 dbname=test user=postgres");
> $result = pg_exec ($dbconn, "update table2 set C2=C2+1;");
> $result = pg_exec ($dbconn, "update table2 set C2=C2-1;");
> pg_close($dbconn);
> ?>

Is this the exact equivalent test of the MySQL "concurrent" test?
(Ignoring the transaction handling difference)

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"La tristeza es un muro entre dos jardines" (Khalil Gibran)

"Durai" <visolve_postgres@lycos.co.uk> writes:
> # cat testpgsql.php

> <?php
> $dbconn =3D pg_connect("host=3D172.16.1.158 port=3D5432 dbname=3Dtest user=
> =3Dpostgres");
> $result =3D pg_exec ($dbconn, "update table2 set C2=3DC2+1;");
> $result =3D pg_exec ($dbconn, "update table2 set C2=3DC2-1;");
> pg_close($dbconn);
> ?>
> #

It doesn't surprise me that concurrent execution of that script would
yield deadlocks in Postgres but not in MySQL.  The reason is that there
*isn't* any concurrent execution of that script going on in MySQL.
Each UPDATE command will (if I understand their behavior correctly) take
a table-level lock until it's done, thereby preventing any other UPDATE
from proceeding concurrently.  Postgres tries to do the locking at the
row level, and so can easily get into a state where transaction A has
updated row 1 and now wants to update row 2, whereas transaction B has
updated row 2 and now wants to update row 1 ... ie, deadlock.

You could "fix" this by taking a table-level lock ("LOCK TABLE table2")
before starting the updates, thereby dumbing Postgres down to MySQL's
level.  I don't see the point though, as this benchmark is completely
irrelevant to most real-world uses.  In the real world you more commonly
have different transactions independently updating different rows of a
table.  In that sort of scenario, MySQL loses badly because it cannot
process such updates concurrently, due to table-level locking.  Unless
your real application mostly does whole-table updates, you should
rewrite your benchmark to be more representative of what you really need
to do.

BTW, I think that when you use InnoDB tables, MySQL does use row-level
locks for updates, and so would likely show the same deadlock risk as
Postgres.

            regards, tom lane

Durai wrote:

> Hello Sridhar,
>
>                     Now, It works. Thanks lot.
>
> But Still I have doubt:
>
> 1. How MySQL works in this case? It has any default "autocommit"?

I am more worried what this test is testing at all and what MySQL
finally returns as a supposed to be correct result.

In particular, does the row after that test was running for some time
report the correct value? Looking at the test it should be the same
value the test started from.

Also, just as a side note, this sort of isolated, single table, single
row, as primitive a query as possible tests is usually the stuff where
MySQL beats PostgreSQL, so I wouldn't be surprised if one more "from
another world" test shows MySQL's strength over PostgreSQL.


Jan

>
> 2. Is there any other method to test for "concurrent testing"?
>
> Regs,
> Durai.
> ----- Original Message -----
> From: "Shridhar Daithankar" <shridhar_daithankar@myrealbox.com>
> To: "Shridhar Daithankar" <shridhar_daithankar@myrealbox.com>
> Cc: "Durai" <visolve_postgres@lycos.co.uk>; <pgsql-general@postgresql.org>
> Sent: Friday, November 21, 2003 5:28 PM
> Subject: Re: [GENERAL] Concurrent testing PostgreSQL Vs MySQL
>
>
>> Damn.. I forgot to begin the transaction..
>>
>> Shridhar Daithankar wrote:
>> > Durai wrote:
>> >> Hello Shridhar,
>> >>                     The script content is:
>> >> ** *# cat te**stpgsql.php*
>> >> *
>> >> *<?php
>> >> $dbconn = pg_connect("host=172.16.1.158 port=5432 dbname=test
>> >> user=postgres");
>>
>> $result = pg_exec ($dbconn, "begin;");
>>
>> >> $result = pg_exec ($dbconn, "update table2 set C2=C2+1;");
>> >> $result = pg_exec ($dbconn, "update table2 set C2=C2-1;");
>> > $result = pg_exec ($dbconn, "commit;");
>> >> pg_close($dbconn);
>> >> ?>
>> >> #
>>
>> Check this..
>>
>>   Shridhar
>>
>>
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.538 / Virus Database: 333 - Release Date: 11/11/2003
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org


--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #