Concurrent testing PostgreSQL Vs MySQL

[Durai]

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
.....
Regs,
Durai.


---
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

[Shridhar Daithankar]

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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Durai]

Hello Shridhar,

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);
?>
#
The test result is:

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

This is ApacheBench, Version 1.3d <$Revision: 1.67 $> apache-1.3
Copyright (c) 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Copyright (c) 1998-2002 The Apache Software Foundation, http://www.apache.org/

Benchmarking 127.0.0.1 (be patient).....done
Server Software: Apache/1.3.27
Server Hostname: 127.0.0.1
Server Port: 7070

Document Path: /testpgsql.php
Document Length: 0 bytes

Concurrency Level: 5
Time taken for tests: 51.508 seconds
Complete requests: 50
Failed requests: 34
(Connect: 0, Length: 34, Exceptions: 0)
Broken pipe errors: 0
Total transferred: 15000 bytes
HTML transferred: 6750 bytes
Requests per second: 0.97 [#/sec] (mean)
Time per request: 5150.80 [ms] (mean)
Time per request: 1030.16 [ms] (mean, across all concurrent requests)
Transfer rate: 0.29 [Kbytes/sec] received

Connnection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 0.2 0 2
Processing: 443 4973 3067.8 4513 12357
Waiting: 441 4973 3067.9 4513 12357
Total: 443 4973 3067.7 4513 12357

Percentage of the requests served within a certain time (ms)
50% 4513
66% 6278
75% 7308
80% 7712
90% 9422
95% 10723
98% 12357
99% 12357
100% 12357 (last request)
#
----- Original Message -----
From: "Shridhar Daithankar" <sh*****************@myrealbox.com>
To: "Durai" <vi**************@lycos.co.uk>
Cc: <pg***********@postgresql.org>
Sent: Friday, November 21, 2003 3:33 PM
Subject: Re: [GENERAL] 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

---
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

[Marek Lewczuk]

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...

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Shridhar Daithankar]

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 ma*******@postgresql.org

[Durai]

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" <sh*****************@myrealbox.com>
To: "Durai" <vi**************@lycos.co.uk>
Cc: <pg***********@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 ma*******@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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Shridhar Daithankar]

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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Durai]

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" <sh*****************@myrealbox.com>
To: "Shridhar Daithankar" <sh*****************@myrealbox.com>
Cc: "Durai" <vi**************@lycos.co.uk>; <pg***********@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 ma*******@postgresql.org

[Shridhar Daithankar]

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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Alvaro Herrera]

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)

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Tom Lane]

"Durai" <vi**************@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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Jan Wieck]

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" <sh*****************@myrealbox.com>
To: "Shridhar Daithankar" <sh*****************@myrealbox.com>
Cc: "Durai" <vi**************@lycos.co.uk>; <pg***********@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 ma*******@postgresql.org

--
#================================================= =====================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================= = Ja******@Yahoo.com #
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match