RE: Innobase UPDATE ... LIMIT x
RE: Innobase UPDATE ... LIMIT x
am 27.08.2002 07:16:28 von Nicholas Gaugler
Heikki,
I decided to load up MySQL-Max 3.23.52 on a machine and give her a try, and
I still see this issue. Here are my details,
I downloaded mysql-max-3.23.52-pc-linux-gnu-i686, binary version. I
ungziped, untared, created my user databases, etc. Setup the following
settings in my.cnf:
innodb_data_home_dir = /usr/local/mysql/data/
innodb_data_file_path = ibdata/ibdata1:1000M
innodb_log_group_home_dir = /usr/local/mysql/data/iblogs/
innodb_log_arch_dir = /usr/local/mysql/data/iblogs/
set-variable = innodb_mirrored_log_groups=1
set-variable = innodb_log_files_in_group=3
set-variable = innodb_log_file_size=50M
set-variable = innodb_log_buffer_size=8M
#set-variable = innodb_log_buffer_size=38M
innodb_flush_log_at_trx_commit=1
innodb_log_archive=0
set-variable = innodb_buffer_pool_size=64M
#set-variable = innodb_buffer_pool_size=256M
set-variable = innodb_additional_mem_pool_size=10M
set-variable = innodb_file_io_threads=4
set-variable = innodb_lock_wait_timeout=50
Started her up, she created the tables. I then ran the Perl script you
supplied below to create my million rows..then I ran the following after
waiting 5 minutes after she was done:
mysql> select count(*), AssignedTo from queryqueue GROUP BY AssignedTo;
+----------+------------+
| count(*) | AssignedTo |
+----------+------------+
| 999000 | 252 |
| 1000 | 253 |
+----------+------------+
2 rows in set (2.53 sec)
mysql> select * from queryqueue where AssignedTo = 252 LIMIT 5;
+----------+------------+
| RootNSID | AssignedTo |
+----------+------------+
| 1 | 252 |
| 2 | 252 |
| 3 | 252 |
| 4 | 252 |
| 5 | 252 |
+----------+------------+
5 rows in set (0.04 sec)
mysql>
mysql> update queryqueue set AssignedTo = 1 where AssignedTo = 252 LIMIT 5;
Query OK, 5 rows affected (23.87 sec)
Rows matched: 5 Changed: 5 Warnings: 0
mysql>
mysql> update queryqueue set AssignedTo = 1 where AssignedTo = 252 LIMIT 5;
Query OK, 5 rows affected (16.79 sec)
Rows matched: 5 Changed: 5 Warnings: 0
mysql> select * from queryqueue where AssignedTo = 252 LIMIT 5;
+----------+------------+
| RootNSID | AssignedTo |
+----------+------------+
| 11 | 252 |
| 12 | 252 |
| 13 | 252 |
| 14 | 252 |
| 15 | 252 |
+----------+------------+
5 rows in set (0.00 sec)
mysql> update queryqueue set AssignedTo = 1 where RootNSID = 11;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update queryqueue set AssignedTo = 1 where RootNSID = 12;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update queryqueue set AssignedTo = 1 where RootNSID = 13;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update queryqueue set AssignedTo = 1 where RootNSID = 14;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update queryqueue set AssignedTo = 1 where RootNSID = 15;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
There is 0 other load on this server, it is completely a development server.
The kernel is as follows:
Linux linuxstaging 2.4.18-5 #1 Mon Jun 10 15:38:12 EDT 2002 i686 unknown
show table status like 'queryqueue';
+------------+--------+------------+--------+--------------- -+-------------+
-----------------+--------------+-----------+--------------- -+-------------+
-------------+------------+----------------+---------------- --------+
| Name | Type | Row_format | Rows | Avg_row_length | Data_length |
Max_data_length | Index_length | Data_free | Auto_increment | Create_time |
Update_time | Check_time | Create_options | Comment |
+------------+--------+------------+--------+--------------- -+-------------+
-----------------+--------------+-----------+--------------- -+-------------+
-------------+------------+----------------+---------------- --------+
| queryqueue | InnoDB | Fixed | 998851 | 31 | 30998528 |
NULL | 14172160 | 0 | NULL | NULL | NULL
| NULL | | InnoDB free: 919552 kB |
+------------+--------+------------+--------+--------------- -+-------------+
-----------------+--------------+-----------+--------------- -+-------------+
-------------+------------+----------------+---------------- --------+
1 row in set (0.03 sec)
There is absolutely nothing going on on this box, there are no other
databases on this server. There shouldn't be any dangling deletions, since
nothing was deleted. Please let me know of any details I can get that would
assist you.
-----Original Message-----
From: Heikki Tuuri [mailto:Heikki.Tuuri@innodb.com]
Sent: Monday, April 15, 2002 7:54 PM
To: bugs@lists.mysql.com
Subject: RE: Innobase UPDATE ... LIMIT x
Nick,
"
Sinisa,
Hmm, I didn't even try my example, when the c2 value is set to 5, it is
fast. However, when the c2 value is set to say, 252 it is slow:
mysql> select count(*), AssignedTo from queryqueue GROUP BY AssignedTo;
+----------+------------+
| count(*) | AssignedTo |
+----------+------------+
| 998568 | 252 |
| 1432 | 253 |
+----------+------------+
2 rows in set (1.11 sec)
mysql> select * from queryqueue where AssignedTo = 252 LIMIT 5;
+----------+------------+
| RootNSID | AssignedTo |
+----------+------------+
| 1433 | 252 |
| 1434 | 252 |
| 1435 | 252 |
| 1436 | 252 |
| 1437 | 252 |
+----------+------------+
5 rows in set (0.00 sec)
mysql> update queryqueue set AssignedTo = 1 where AssignedTo = 252 LIMIT
5;
Query OK, 5 rows affected (16.25 sec)
Rows matched: 5 Changed: 5 Warnings: 0
I can understand if there are a large number of rows for MySQL to sort
through until it reaches the values being 252, but there is nothing
between
0 and 252, so the first rows in the index should be the ones equaling 252.
nickg
How-To-Repeat:
CREATE TABLE `queryqueue` (
`RootNSID` int(11) unsigned NOT NULL default '0',
`AssignedTo` tinyint(3) unsigned NOT NULL default '0',
PRIMARY KEY (`RootNSID`),
KEY `AssignedTo` (`AssignedTo`)
) TYPE=InnoDB
"
I tested this and it ran fast:
.....
mysql> select count(*), AssignedTo from queryqueue GROUP BY AssignedTo;
+----------+------------+
| count(*) | AssignedTo |
+----------+------------+
| 998001 | 252 |
| 1999 | 253 |
+----------+------------+
2 rows in set (1.67 sec)
mysql> select * from queryqueue where AssignedTo = 252 LIMIT 5;
+----------+------------+
| RootNSID | AssignedTo |
+----------+------------+
| 1001 | 252 |
| 1002 | 252 |
| 1003 | 252 |
| 1004 | 252 |
| 1005 | 252 |
+----------+------------+
5 rows in set (0.00 sec)
mysql> update queryqueue set AssignedTo = 1 where AssignedTo = 252 LIMIT
5;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5 Changed: 5 Warnings: 0
mysql> update queryqueue set AssignedTo = 1 where AssignedTo = 252 LIMIT
5;
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5 Changed: 5 Warnings: 0
mysql> update queryqueue set AssignedTo = 1 where AssignedTo = 252 LIMIT
5;
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5 Changed: 5 Warnings: 0
mysql> update queryqueue set AssignedTo = 1 where AssignedTo = 252 LIMIT
5;
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5 Changed: 5 Warnings: 0
mysql>
.....
Are you sure you do not have uncommitted transactions dangling in the
database? Then there might be a lot of delete marked records which purge
has
not been able to remove, and which might slow down searches.
Is there any other load on the database? What is the operating system?
What does
SHOW TABLE STATUS FROM ... LIKE 'queryqueue';
say?
Can you make a repeatable test case as a Perl program, for example, like
below?
......
use DBI;
use Benchmark;
chomp($pwd = `pwd`);
$pwd = "." if ($pwd eq '');
require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n";
$dbh = $server->connect();
$dbh->do("set autocommit = 1");
$s = 0;
for ($j = 0; $j < 1000000; $j = $j + 1) {
if ($j % 1000 == 0) {
$dbh->do("insert into queryqueue values ($j, 253)")
|| print $dbh->errstr;
} else {
$dbh->do("insert into queryqueue values ($j, 252)")
|| print $dbh->errstr;
}
}
$dbh->disconnect;
.....
Best regards,
Heikki Tuuri
Innobase Oy
---
Order technical MySQL/InnoDB support at https://order.mysql.com/
See http://www.innodb.com for the online manual and latest news on InnoDB
------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail bugs-thread11933@lists.mysql.com
To unsubscribe, e-mail
------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail bugs-thread12413@lists.mysql.com
To unsubscribe, e-mail
Re: Innobase UPDATE ... LIMIT x
am 27.08.2002 10:03:29 von Heikki Tuuri
Nicholas,
thank you for testing this. I was able to repeat the slowness with both
MyISAM and InnoDB type tables, with both 3.23.52 and 4.0.2.
The reason is that the optimizer chooses the key AssignedTo as the access
path to the table. Since you update the indexed column, to be sure that we
do not update some row twice, MySQL reads the row pointers of the whole
result set and stores them to a temporary file.
Thus we read 999 000 rows and that takes time. I guess that in my earlier
test the optimizer chose a table scan, and consequently only 5 rows were
read which explains why it ran so fast.
We could add an optimization to sql_select.cc, about line 153, so that we
would use the LIMIT to limit the number of rows we read.
A workaround is to use a transaction like this:
BEGIN
SELECT RootNSID FROM ... WHERE AssignedTo = 252 LIMIT 1 FOR UPDATE;
UPDATE ... SET AssignedTo = 1 WHERE RootNSID = ...
COMMIT
Above we force the optimizer to use the primary key as the access path in
the update.
Best regards,
Heikki
Innobase Oy
----- Original Message -----
From: "Nicholas Gaugler"
To: "Heikki Tuuri" ;
Sent: Tuesday, August 27, 2002 8:16 AM
Subject: RE: Innobase UPDATE ... LIMIT x
> Heikki,
>
> I decided to load up MySQL-Max 3.23.52 on a machine and give her a try,
and
> I still see this issue. Here are my details,
>
> I downloaded mysql-max-3.23.52-pc-linux-gnu-i686, binary version. I
> ungziped, untared, created my user databases, etc. Setup the following
> settings in my.cnf:
>
>
> innodb_data_home_dir = /usr/local/mysql/data/
> innodb_data_file_path = ibdata/ibdata1:1000M
> innodb_log_group_home_dir = /usr/local/mysql/data/iblogs/
> innodb_log_arch_dir = /usr/local/mysql/data/iblogs/
> set-variable = innodb_mirrored_log_groups=1
> set-variable = innodb_log_files_in_group=3
> set-variable = innodb_log_file_size=50M
> set-variable = innodb_log_buffer_size=8M
> #set-variable = innodb_log_buffer_size=38M
> innodb_flush_log_at_trx_commit=1
> innodb_log_archive=0
> set-variable = innodb_buffer_pool_size=64M
> #set-variable = innodb_buffer_pool_size=256M
> set-variable = innodb_additional_mem_pool_size=10M
> set-variable = innodb_file_io_threads=4
> set-variable = innodb_lock_wait_timeout=50
>
> Started her up, she created the tables. I then ran the Perl script you
> supplied below to create my million rows..then I ran the following after
> waiting 5 minutes after she was done:
>
> mysql> select count(*), AssignedTo from queryqueue GROUP BY AssignedTo;
> +----------+------------+
> | count(*) | AssignedTo |
> +----------+------------+
> | 999000 | 252 |
> | 1000 | 253 |
> +----------+------------+
> 2 rows in set (2.53 sec)
>
> mysql> select * from queryqueue where AssignedTo = 252 LIMIT 5;
> +----------+------------+
> | RootNSID | AssignedTo |
> +----------+------------+
> | 1 | 252 |
> | 2 | 252 |
> | 3 | 252 |
> | 4 | 252 |
> | 5 | 252 |
> +----------+------------+
> 5 rows in set (0.04 sec)
>
> mysql>
> mysql> update queryqueue set AssignedTo = 1 where AssignedTo = 252 LIMIT
5;
>
> Query OK, 5 rows affected (23.87 sec)
> Rows matched: 5 Changed: 5 Warnings: 0
>
> mysql>
> mysql> update queryqueue set AssignedTo = 1 where AssignedTo = 252 LIMIT
5;
> Query OK, 5 rows affected (16.79 sec)
> Rows matched: 5 Changed: 5 Warnings: 0
>
> mysql> select * from queryqueue where AssignedTo = 252 LIMIT 5;
> +----------+------------+
> | RootNSID | AssignedTo |
> +----------+------------+
> | 11 | 252 |
> | 12 | 252 |
> | 13 | 252 |
> | 14 | 252 |
> | 15 | 252 |
> +----------+------------+
> 5 rows in set (0.00 sec)
>
> mysql> update queryqueue set AssignedTo = 1 where RootNSID = 11;
> Query OK, 1 row affected (0.00 sec)
> Rows matched: 1 Changed: 1 Warnings: 0
>
> mysql> update queryqueue set AssignedTo = 1 where RootNSID = 12;
> Query OK, 1 row affected (0.01 sec)
> Rows matched: 1 Changed: 1 Warnings: 0
>
> mysql> update queryqueue set AssignedTo = 1 where RootNSID = 13;
> Query OK, 1 row affected (0.00 sec)
> Rows matched: 1 Changed: 1 Warnings: 0
>
> mysql> update queryqueue set AssignedTo = 1 where RootNSID = 14;
> Query OK, 1 row affected (0.00 sec)
> Rows matched: 1 Changed: 1 Warnings: 0
>
> mysql> update queryqueue set AssignedTo = 1 where RootNSID = 15;
> Query OK, 1 row affected (0.00 sec)
> Rows matched: 1 Changed: 1 Warnings: 0
>
> mysql>
>
>
> There is 0 other load on this server, it is completely a development
server.
> The kernel is as follows:
>
> Linux linuxstaging 2.4.18-5 #1 Mon Jun 10 15:38:12 EDT 2002 i686 unknown
>
>
>
> show table status like 'queryqueue';
>
+------------+--------+------------+--------+--------------- -+-------------+
> -----------------+--------------+-----------+--------------- -+------------
-+
> -------------+------------+----------------+---------------- --------+
> | Name | Type | Row_format | Rows | Avg_row_length | Data_length
|
> Max_data_length | Index_length | Data_free | Auto_increment | Create_time
|
> Update_time | Check_time | Create_options | Comment |
>
+------------+--------+------------+--------+--------------- -+-------------+
> -----------------+--------------+-----------+--------------- -+------------
-+
> -------------+------------+----------------+---------------- --------+
> | queryqueue | InnoDB | Fixed | 998851 | 31 | 30998528
|
> NULL | 14172160 | 0 | NULL | NULL | NULL
> | NULL | | InnoDB free: 919552 kB |
>
+------------+--------+------------+--------+--------------- -+-------------+
> -----------------+--------------+-----------+--------------- -+------------
-+
> -------------+------------+----------------+---------------- --------+
> 1 row in set (0.03 sec)
>
>
> There is absolutely nothing going on on this box, there are no other
> databases on this server. There shouldn't be any dangling deletions,
since
> nothing was deleted. Please let me know of any details I can get that
would
> assist you.
>
>
>
> -----Original Message-----
> From: Heikki Tuuri [mailto:Heikki.Tuuri@innodb.com]
> Sent: Monday, April 15, 2002 7:54 PM
> To: bugs@lists.mysql.com
> Subject: RE: Innobase UPDATE ... LIMIT x
>
>
> Nick,
>
> "
> Sinisa,
>
> Hmm, I didn't even try my example, when the c2 value is set to 5, it is
> fast. However, when the c2 value is set to say, 252 it is slow:
>
> mysql> select count(*), AssignedTo from queryqueue GROUP BY AssignedTo;
> +----------+------------+
> | count(*) | AssignedTo |
> +----------+------------+
> | 998568 | 252 |
> | 1432 | 253 |
> +----------+------------+
> 2 rows in set (1.11 sec)
>
> mysql> select * from queryqueue where AssignedTo = 252 LIMIT 5;
> +----------+------------+
> | RootNSID | AssignedTo |
> +----------+------------+
> | 1433 | 252 |
> | 1434 | 252 |
> | 1435 | 252 |
> | 1436 | 252 |
> | 1437 | 252 |
> +----------+------------+
> 5 rows in set (0.00 sec)
>
> mysql> update queryqueue set AssignedTo = 1 where AssignedTo = 252 LIMIT
> 5;
> Query OK, 5 rows affected (16.25 sec)
> Rows matched: 5 Changed: 5 Warnings: 0
>
> I can understand if there are a large number of rows for MySQL to sort
> through until it reaches the values being 252, but there is nothing
> between
> 0 and 252, so the first rows in the index should be the ones equaling 252.
>
> nickg
>
> How-To-Repeat:
> CREATE TABLE `queryqueue` (
> `RootNSID` int(11) unsigned NOT NULL default '0',
> `AssignedTo` tinyint(3) unsigned NOT NULL default '0',
> PRIMARY KEY (`RootNSID`),
> KEY `AssignedTo` (`AssignedTo`)
> ) TYPE=InnoDB
> "
>
> I tested this and it ran fast:
>
> ....
> mysql> select count(*), AssignedTo from queryqueue GROUP BY AssignedTo;
> +----------+------------+
> | count(*) | AssignedTo |
> +----------+------------+
> | 998001 | 252 |
> | 1999 | 253 |
> +----------+------------+
> 2 rows in set (1.67 sec)
>
> mysql> select * from queryqueue where AssignedTo = 252 LIMIT 5;
> +----------+------------+
> | RootNSID | AssignedTo |
> +----------+------------+
> | 1001 | 252 |
> | 1002 | 252 |
> | 1003 | 252 |
> | 1004 | 252 |
> | 1005 | 252 |
> +----------+------------+
> 5 rows in set (0.00 sec)
>
> mysql> update queryqueue set AssignedTo = 1 where AssignedTo = 252 LIMIT
> 5;
> Query OK, 5 rows affected (0.00 sec)
> Rows matched: 5 Changed: 5 Warnings: 0
>
> mysql> update queryqueue set AssignedTo = 1 where AssignedTo = 252 LIMIT
> 5;
> Query OK, 5 rows affected (0.01 sec)
> Rows matched: 5 Changed: 5 Warnings: 0
>
> mysql> update queryqueue set AssignedTo = 1 where AssignedTo = 252 LIMIT
> 5;
> Query OK, 5 rows affected (0.01 sec)
> Rows matched: 5 Changed: 5 Warnings: 0
>
> mysql> update queryqueue set AssignedTo = 1 where AssignedTo = 252 LIMIT
> 5;
> Query OK, 5 rows affected (0.01 sec)
> Rows matched: 5 Changed: 5 Warnings: 0
>
> mysql>
> ....
>
> Are you sure you do not have uncommitted transactions dangling in the
> database? Then there might be a lot of delete marked records which purge
> has
> not been able to remove, and which might slow down searches.
>
> Is there any other load on the database? What is the operating system?
>
> What does
>
> SHOW TABLE STATUS FROM ... LIKE 'queryqueue';
>
> say?
>
> Can you make a repeatable test case as a Perl program, for example, like
> below?
>
> .....
> use DBI;
> use Benchmark;
> chomp($pwd = `pwd`);
> $pwd = "." if ($pwd eq '');
> require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n";
>
> $dbh = $server->connect();
> $dbh->do("set autocommit = 1");
>
> $s = 0;
>
> for ($j = 0; $j < 1000000; $j = $j + 1) {
> if ($j % 1000 == 0) {
> $dbh->do("insert into queryqueue values ($j, 253)")
> || print $dbh->errstr;
> } else {
> $dbh->do("insert into queryqueue values ($j, 252)")
> || print $dbh->errstr;
> }
> }
>
> $dbh->disconnect;
> ....
>
> Best regards,
>
> Heikki Tuuri
> Innobase Oy
> ---
> Order technical MySQL/InnoDB support at https://order.mysql.com/
> See http://www.innodb.com for the online manual and latest news on InnoDB
>
>
>
>
> ------------------------------------------------------------ ---------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail bugs-thread11933@lists.mysql.com
> To unsubscribe, e-mail
>
>
>
------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail bugs-thread12415@lists.mysql.com
To unsubscribe, e-mail
RE: Innobase UPDATE ... LIMIT x
am 28.08.2002 02:44:17 von Nicholas Gaugler
Heikki,
Thanks for looking into the situation again. For now, I think I am just
going to stick with my current situation. I'd like to keep queries off
the server, whatever I can put into one query, is ideal. As of the last
48 hours, I have 550 queries per second average. Ranging from
SELECT/INSERT/UPDATE/DELETEs over 5 tables. It's a sticky situation
with only dual 500Mhz and 140 other Perl processes trying to connect
back to MySQL, all on the same box using the same CPU resources. If I
get some extra time I might play around with it, but I will keep up with
the Changes Log as well. I'm not in any rush to migrate to Innobase,
some AB guys just recommended I try it and saw how it compared.
nickg
24 million rows, Gemini, SELECT/INSERT/UPDATE
53 million rows, Gemini, SELECT/INSERT/UPDATE/DELETE
24 million rows, Gemini, SELECT/UPDATE
24 million rows, MyISAM, SELECT
53 million rows, MyISAM, SELECT
Uptime: 234600 Threads: 147 Questions: 128736070 Slow queries: 174332
Opens: 68828 Flush tables: 1 Open tables: 512 Queries per second avg:
548.747
-----Original Message-----
From: Heikki Tuuri [mailto:Heikki.Tuuri@innodb.com]
Sent: Tuesday, August 27, 2002 3:03 AM
To: Nicholas Gaugler; bugs@lists.mysql.com
Subject: Re: Innobase UPDATE ... LIMIT x
Nicholas,
thank you for testing this. I was able to repeat the slowness with both
MyISAM and InnoDB type tables, with both 3.23.52 and 4.0.2.
The reason is that the optimizer chooses the key AssignedTo as the
access
path to the table. Since you update the indexed column, to be sure that
we
do not update some row twice, MySQL reads the row pointers of the whole
result set and stores them to a temporary file.
Thus we read 999 000 rows and that takes time. I guess that in my
earlier
test the optimizer chose a table scan, and consequently only 5 rows were
read which explains why it ran so fast.
We could add an optimization to sql_select.cc, about line 153, so that
we
would use the LIMIT to limit the number of rows we read.
A workaround is to use a transaction like this:
BEGIN
SELECT RootNSID FROM ... WHERE AssignedTo = 252 LIMIT 1 FOR UPDATE;
UPDATE ... SET AssignedTo = 1 WHERE RootNSID = ...
COMMIT
Above we force the optimizer to use the primary key as the access path
in
the update.
Best regards,
Heikki
Innobase Oy
------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail bugs-thread12420@lists.mysql.com
To unsubscribe, e-mail