perl/mysql/innodb

I have a perl script which fork()'s many children and each
child is updating a table, and each child is inserting/updating
DIFFERENT rows - I split up the work so that no two children
try to update the same row so that no child has to wait for any
locks to be released. The problem is, I am getting lock wait
timeout's still, its not consistant - sometimes it happens
sometimes it does not. It might be due to next key locking.
Anyhow, has anyone tried to do something similar before ? How
can I have all the children update/insert without running into
lock problems.

I am using the latest production version of MySQL and all the
tables are InnoDB.

Any input is welcome.
thank you.

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: perl/mysql/innodb

Jochen Wiedmann wrote:

> mayuran wrote:
>
>> I have a perl script which fork()'s many children and each
>> child is updating a table, and each child is inserting/updating
>> DIFFERENT rows - I split up the work so that no two children
>> try to update the same row so that no child has to wait for any
>> locks to be released. The problem is, I am getting lock wait
>> timeout's still, its not consistant - sometimes it happens
>> sometimes it does not.
>
>
> Is it possible that you are attempting to share handles or do similar
> stuff? If so, try to avoid it.
>
>
> Jochen
>
Jochen, there is a global database handler $Dbh and after the parent
fork()'s its children, each
child calls sub reconnectDB() to re-establish the connection for that
child. However, each child
has its own copy of the variable, so I dont think this is sharing the
handle.

Mayuran

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: perl/mysql/innodb

Jochen Wiedmann wrote:

> mayuran wrote:
>
>> I have a perl script which fork()'s many children and each
>> child is updating a table, and each child is inserting/updating
>> DIFFERENT rows - I split up the work so that no two children
>> try to update the same row so that no child has to wait for any
>> locks to be released. The problem is, I am getting lock wait
>> timeout's still, its not consistant - sometimes it happens
>> sometimes it does not.
>
>
> Is it possible that you are attempting to share handles or do similar
> stuff? If so, try to avoid it.
>
>
> Jochen
>
Jochen, there is a global database handler $Dbh and after the parent
fork()'s its children, each
child calls sub reconnectDB() to re-establish the connection for that
child. However, each child
has its own copy of the variable, so I dont think this is sharing the
handle.

Mayuran

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: perl/mysql/innodb

mayuran wrote:

> I have a perl script which fork()'s many children and each
> child is updating a table, and each child is inserting/updating
> DIFFERENT rows - I split up the work so that no two children
> try to update the same row so that no child has to wait for any
> locks to be released. The problem is, I am getting lock wait
> timeout's still, its not consistant - sometimes it happens
> sometimes it does not.

Is it possible that you are attempting to share handles or do similar stuff?
If so, try to avoid it.


Jochen

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: perl/mysql/innodb

mayuran wrote:

> I have a perl script which fork()'s many children and each
> child is updating a table, and each child is inserting/updating
> DIFFERENT rows - I split up the work so that no two children
> try to update the same row so that no child has to wait for any
> locks to be released. The problem is, I am getting lock wait
> timeout's still, its not consistant - sometimes it happens
> sometimes it does not.

Is it possible that you are attempting to share handles or do similar stuff?
If so, try to avoid it.


Jochen

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: perl/mysql/innodb

mayuran wrote:

> Jochen, there is a global database handler $Dbh and after the parent
> fork()'s its children, each
> child calls sub reconnectDB() to re-establish the connection for that
> child. However, each child
> has its own copy of the variable, so I dont think this is sharing the
> handle.

Not sure, whether this is responsible for your problems, but it sounds
dangerous. Create a new connection for each forked child. And make sure to
set the InactiveDestroy flag on the parents handle, so that no child closes
the connection.

If that might cause performance problems, consider using a thread model with
a connection pool.


Jochen


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: perl/mysql/innodb

mayuran wrote:

> Jochen, there is a global database handler $Dbh and after the parent
> fork()'s its children, each
> child calls sub reconnectDB() to re-establish the connection for that
> child. However, each child
> has its own copy of the variable, so I dont think this is sharing the
> handle.

Not sure, whether this is responsible for your problems, but it sounds
dangerous. Create a new connection for each forked child. And make sure to
set the InactiveDestroy flag on the parents handle, so that no child closes
the connection.

If that might cause performance problems, consider using a thread model with
a connection pool.


Jochen


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: perl/mysql/innodb

Jochen Wiedmann wrote:

> mayuran wrote:
>
>> Jochen, there is a global database handler $Dbh and after the parent
>> fork()'s its children, each
>> child calls sub reconnectDB() to re-establish the connection for that
>> child. However, each child
>> has its own copy of the variable, so I dont think this is sharing the
>> handle.
>
>
> Not sure, whether this is responsible for your problems, but it sounds
> dangerous.


> Create a new connection for each forked child.

Isnt that what I am doing tho? Although the database handler is $Dbh for
each child, they each get
a copy from the parent. Then each child re-establishes their connection
using reconnectDB which
reactives $Dbh as the database handle for each child.

> And make sure to set the InactiveDestroy flag on the parents handle,
> so that no child closes the connection.
>
I set the InactiveDestroy flag for each child, not the parent. I saw
Rudy Lippan suggest this in some
article, thats why I did it this way. Is this incorrect ?

> If that might cause performance problems, consider using a thread
> model with a connection pool.
>
Unfortunately my boss is convinced that Perl isnt thread safe, so we are
not allowed to use threads for anything.

>
> Jochen
>

Thanks for you feedback!


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: perl/mysql/innodb

Jochen Wiedmann wrote:

> mayuran wrote:
>
>> Jochen, there is a global database handler $Dbh and after the parent
>> fork()'s its children, each
>> child calls sub reconnectDB() to re-establish the connection for that
>> child. However, each child
>> has its own copy of the variable, so I dont think this is sharing the
>> handle.
>
>
> Not sure, whether this is responsible for your problems, but it sounds
> dangerous.


> Create a new connection for each forked child.

Isnt that what I am doing tho? Although the database handler is $Dbh for
each child, they each get
a copy from the parent. Then each child re-establishes their connection
using reconnectDB which
reactives $Dbh as the database handle for each child.

> And make sure to set the InactiveDestroy flag on the parents handle,
> so that no child closes the connection.
>
I set the InactiveDestroy flag for each child, not the parent. I saw
Rudy Lippan suggest this in some
article, thats why I did it this way. Is this incorrect ?

> If that might cause performance problems, consider using a thread
> model with a connection pool.
>
Unfortunately my boss is convinced that Perl isnt thread safe, so we are
not allowed to use threads for anything.

>
> Jochen
>

Thanks for you feedback!


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: perl/mysql/innodb

On Wed, 12 May 2004, mayuran wrote:

You would probably be better served asking this question on the mysql general
list as this is not a perl specific problem, and you might find more people that
that might have done things like this before.

> I have a perl script which fork()'s many children and each
> child is updating a table, and each child is inserting/updating
> DIFFERENT rows - I split up the work so that no two children

Look at the code here:
http://lists.mysql.com/perl/2915

The update does not even touch one row but will cause a lock wait timeout.

> try to update the same row so that no child has to wait for any
> locks to be released. The problem is, I am getting lock wait
> timeout's still, its not consistant - sometimes it happens
> sometimes it does not. It might be due to next key locking.
> Anyhow, has anyone tried to do something similar before ? How
> can I have all the children update/insert without running into
> lock problems.

AutoCommit would go a long way to helping with the lock wait tiemout.. And you
can always replay the transactions.

Another thing that you can do is use explicit locks on the tables so that your
children control who locks what and when (and don't forget to commit the tx).


Rudy


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: perl/mysql/innodb

On Wed, 12 May 2004, mayuran wrote:

You would probably be better served asking this question on the mysql general
list as this is not a perl specific problem, and you might find more people that
that might have done things like this before.

> I have a perl script which fork()'s many children and each
> child is updating a table, and each child is inserting/updating
> DIFFERENT rows - I split up the work so that no two children

Look at the code here:
http://lists.mysql.com/perl/2915

The update does not even touch one row but will cause a lock wait timeout.

> try to update the same row so that no child has to wait for any
> locks to be released. The problem is, I am getting lock wait
> timeout's still, its not consistant - sometimes it happens
> sometimes it does not. It might be due to next key locking.
> Anyhow, has anyone tried to do something similar before ? How
> can I have all the children update/insert without running into
> lock problems.

AutoCommit would go a long way to helping with the lock wait tiemout.. And you
can always replay the transactions.

Another thing that you can do is use explicit locks on the tables so that your
children control who locks what and when (and don't forget to commit the tx).


Rudy


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: perl/mysql/innodb

Rudy Lippan wrote:

>On Wed, 12 May 2004, mayuran wrote:
>
>You would probably be better served asking this question on the mysql general
>list as this is not a perl specific problem, and you might find more people that
>that might have done things like this before.
>
>
>
>>I have a perl script which fork()'s many children and each
>>child is updating a table, and each child is inserting/updating
>>DIFFERENT rows - I split up the work so that no two children
>>
>>
>
>Look at the code here:
>http://lists.mysql.com/perl/2915
>
>The update does not even touch one row but will cause a lock wait timeout.
>
>
>
>>try to update the same row so that no child has to wait for any
>>locks to be released. The problem is, I am getting lock wait
>>timeout's still, its not consistant - sometimes it happens
>>sometimes it does not. It might be due to next key locking.
>>Anyhow, has anyone tried to do something similar before ? How
>>can I have all the children update/insert without running into
>>lock problems.
>>
>>
>
>AutoCommit would go a long way to helping with the lock wait tiemout.. And you
>can always replay the transactions.
>
>
>
If I use AutoCommit, then all the inserts/updates for a child isnt treated
as 1 transaction (or am i wrong?). We wanted the data to be consistant.
If I use AutoCommit and something goes wrong partway through and I have to
redo that child's work, then whatever was inserted from the first time
(before
the error) would be updated the second time which is not good - this will
make our numbers incorrect.

> Another thing that you can do is use explicit locks on the tables so that your
>children control who locks what and when (and don't forget to commit the tx).
>
>
>Rudy
>
>
Thanks for your help.

>
>
>


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: perl/mysql/innodb

Rudy Lippan wrote:

>On Wed, 12 May 2004, mayuran wrote:
>
>You would probably be better served asking this question on the mysql general
>list as this is not a perl specific problem, and you might find more people that
>that might have done things like this before.
>
>
>
>>I have a perl script which fork()'s many children and each
>>child is updating a table, and each child is inserting/updating
>>DIFFERENT rows - I split up the work so that no two children
>>
>>
>
>Look at the code here:
>http://lists.mysql.com/perl/2915
>
>The update does not even touch one row but will cause a lock wait timeout.
>
>
>
>>try to update the same row so that no child has to wait for any
>>locks to be released. The problem is, I am getting lock wait
>>timeout's still, its not consistant - sometimes it happens
>>sometimes it does not. It might be due to next key locking.
>>Anyhow, has anyone tried to do something similar before ? How
>>can I have all the children update/insert without running into
>>lock problems.
>>
>>
>
>AutoCommit would go a long way to helping with the lock wait tiemout.. And you
>can always replay the transactions.
>
>
>
If I use AutoCommit, then all the inserts/updates for a child isnt treated
as 1 transaction (or am i wrong?). We wanted the data to be consistant.
If I use AutoCommit and something goes wrong partway through and I have to
redo that child's work, then whatever was inserted from the first time
(before
the error) would be updated the second time which is not good - this will
make our numbers incorrect.

> Another thing that you can do is use explicit locks on the tables so that your
>children control who locks what and when (and don't forget to commit the tx).
>
>
>Rudy
>
>
Thanks for your help.

>
>
>


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org