Description:
Performance down when insert record inside store procedure.
After version 5.0.3-beta, store procedure does not allow locking
tables inside body, but will be auto locking when call out
store procedure.
I testing two versions 5.0.2-alpha and 5.0.3-beta, perfomance
issues without using locking tables inside body.
Version 5.0.2-alpha (Locking tables inside body)
================================
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 5.0.2-alpha
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use mydata
Database changed
mysql> call puttestdata(100000);
Query OK, 0 rows affected (33.51 sec)
mysql> select count(*) from testdata;
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.00 sec)
Version 5.0.3-beta (without locking tables, becuase not allow)
========================================
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.3-beta
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use mydata
Database changed
mysql> call puttestdata(100000);
Query OK, 1 row affected (1 min 16.35 sec)
mysql> select count(*) from testdata;
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.00 sec)
How to repeat:
delimiter //;
drop database if exists mydata//
create database mydata//
use mydata//
drop table if exists testdata//
create table testdata (
id int not null auto_increment,
col1 char(200),
col2 char(200),
col3 char(200),
col4 char(200),
col5 char(200),
primary key(id)
) engine=MYISAM//
drop procedure if exists puttestdata//
create procedure puttestdata(totalinput int)
begin
declare varcount int default 1;
/* lock tables testdata write; */
while varcount <= totalinput do
insert into testdata values(null,
"abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz",
"abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz",
"abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz",
"abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz",
"abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz");
set varcount = varcount + 1;
end while;
/* unlock tables; */
end//
call puttestdata(100000)//
select count(*) from testdata//
----------------------------------------------------------
For version 5.0.2-alpha, please remove remark two lines.
Suggested fix:
don't know. Maybe check already lock tables or not.
Description: Performance down when insert record inside store procedure. After version 5.0.3-beta, store procedure does not allow locking tables inside body, but will be auto locking when call out store procedure. I testing two versions 5.0.2-alpha and 5.0.3-beta, perfomance issues without using locking tables inside body. Version 5.0.2-alpha (Locking tables inside body) ================================ Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 to server version: 5.0.2-alpha Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use mydata Database changed mysql> call puttestdata(100000); Query OK, 0 rows affected (33.51 sec) mysql> select count(*) from testdata; +----------+ | count(*) | +----------+ | 100000 | +----------+ 1 row in set (0.00 sec) Version 5.0.3-beta (without locking tables, becuase not allow) ======================================== Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 to server version: 5.0.3-beta Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use mydata Database changed mysql> call puttestdata(100000); Query OK, 1 row affected (1 min 16.35 sec) mysql> select count(*) from testdata; +----------+ | count(*) | +----------+ | 100000 | +----------+ 1 row in set (0.00 sec) How to repeat: delimiter //; drop database if exists mydata// create database mydata// use mydata// drop table if exists testdata// create table testdata ( id int not null auto_increment, col1 char(200), col2 char(200), col3 char(200), col4 char(200), col5 char(200), primary key(id) ) engine=MYISAM// drop procedure if exists puttestdata// create procedure puttestdata(totalinput int) begin declare varcount int default 1; /* lock tables testdata write; */ while varcount <= totalinput do insert into testdata values(null, "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz", "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz", "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz", "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz", "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"); set varcount = varcount + 1; end while; /* unlock tables; */ end// call puttestdata(100000)// select count(*) from testdata// ---------------------------------------------------------- For version 5.0.2-alpha, please remove remark two lines. Suggested fix: don't know. Maybe check already lock tables or not.