Query optimizer bugs in mysql 3.23.53 (and 4.0.5a?)

In a nutshell, for a complex query (supplied below),

- the query optimizer in 3.23.53 takes 12~24m against ~8m in 4.0.5a;

- if the query has a SELECT INTO, the destination table seems to be
locked while the query optimizer is working; this has been observed with
both ISAM and MyISAM target tables, and can cause process starvation in
busy servers;

- while the query optimizer is working, the mysqld daemon will not
shutdown using the `mysqladmin shutdown` utility;

I think the really serious issue is the table being locked while the
optimizer works. I would expect the table to be locked at a later stage
in the process, preferrably when the SELECT has its output ready.

This is a follow up to a bug I have initially reported using the
mysqlbug script. The original report can be found at
http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:126268 . The original
report states that mysql was crashing, the truth is that we were being
too impatient.

To run these tests we compiled mysql 3.23.53 and 4.0.5a with debug
flags. This tests were performed on an idle debian woody server, which
has a Pentium 3 with 786MB RAM and decently fast UW-SCSI disk. You can
find full compile flags for both mysql daemons at the end of this email.

You will find all the files referenced in this report at
http://cookie.cwa.co.nz/~martin/mysql_playground/ , including datafiles
and SQL queries that reproduce the problem.

How-To-Repeat:

- create 2 empty DBs
`mysql -uroot -e "create database tkidb06_isam; create database
tkidb06_myisam" `

- Import the DB sample data (will take some time!)
`zcat tkidb06.sql.gz | mysql -uroot tkidb06_isam ; zcat
tkidb06.sql.gz | perl -pe 's/TYPE=ISAM/TYPE=MyISAM/' | mysql -uroot
tkidb06_myisam`
get the sample data from
http://cookie.cwa.co.nz/~martin/mysql_playground/

- Optionally, stop the safe_mysqld / mysqld_safe daemon, run (my)isamchk
--analyze on the database tables. In all our tests are on 'analyzed'
tables, but you don't need this step to reproduce the bug. Remember to
restart mysql.

- Optionally, stop the safe_mysqld / mysqld_safe daemon and restart it
with --debug to collect a tracefile. Our trace files did not point to
anything useful.

- Run the provided SQL statement on either database.
`mysql -uroot tkidb06_isam < tki_killer_explain_6.sql`
You can get the statement at


Please note that the statement is an EXPLAIN statement. These timings do
not include time for actual SELECTS.

Here, mysql 3.23.53 took an average of 13 minutes with ISAM tables and
24 minutes with MyISAM. Mysql 4.0.5a took 8 minutes to return.

- Try other SQL statements, all of which will return reasonably fast.



- Now, the bug: on different sessions,
- execute tki_killer_selectinto_6.sql on session A
- wait 1 second
- execute tki_killer_selectinto_4.sql on session B (it usually takes
~ 6 sec.)
-> session B will finish roughly 6 seconds after session A

Workaround:

The only workaround we have identified is to use temporary tables when
using SELECT INTO. Unfortunately, this is sometimes a bit inconvenient.

Thanks a lot for your time, and patience. After all is said and done,
and looking at the nasty SQL we throw at it, mysql does for us a lot
more than we deserve.

kind regards,



martin



Compile settings for 3.23.53
>Release: mysql-3.23.53 (Source distribution)

>Environment:

System: Linux uniform 2.2.20 #1 Sat Apr 20 11:45:28 EST 2002 i686 unknown
Architecture: i686

Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.4/specs
gcc version 2.95.4 20011002 (Debian prerelease)
Compilation info: CC='gcc' CFLAGS='-O2' CXX='gcc' CXXFLAGS='-O2
-felide-constructors -fno-exceptions -fno-rtti' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Oct 22 15:30 /lib/libc.so.6
-> libc-2.2.5.so
-rwxr-xr-x 1 root root 1153784 Sep 18 21:40 /lib/libc-2.2.5.so
-rw-r--r-- 1 root root 2390970 Sep 18 21:41 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Sep 18 21:41 /usr/lib/libc.so
Configure command: ./configure
--prefix=/home/martin/mysql_playground/mysql_3_23_53 --with-debug
--with-extra-charsets=complex CC=gcc CFLAGS=-O2 'CXXFLAGS=-O2 -felide
-constructors -fno-exceptions -fno-rtti' CXX=gcc



Compile settings for 4.0.5
>Release: mysql-4.0.5a-beta (Source distribution)

>Environment:

System: Linux uniform 2.2.20 #1 Sat Apr 20 11:45:28 EST 2002 i686 unknown
Architecture: i686

Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.4/specs
gcc version 2.95.4 20011002 (Debian prerelease)
Compilation info: CC='gcc' CFLAGS='-O2' CXX='gcc' CXXFLAGS='-O2
-felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' ASFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Oct 22 15:30 /lib/libc.so.6
-> libc-2.2.5.so
-rwxr-xr-x 1 root root 1153784 Sep 18 21:40 /lib/libc-2.2.5.so
-rw-r--r-- 1 root root 2390970 Sep 18 21:41 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Sep 18 21:41 /usr/lib/libc.so
Configure command: ./configure
'--prefix=/home/martin/mysql_playground/mysql_4_0_5' '--with-debug'
'--with-extra-charsets=complex' 'CC=gcc' 'CFLAGS=-O2' 'CXXFLAGS=-O2
-felide-constructors -fno-exceptions -fno-rtti' 'CXX=gcc'










------------------------------------------------------------ ---------
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-thread13205@lists.mysql.com
To unsubscribe, e-mail

Re: Query optimizer bugs in mysql 3.23.53 (and 4.0.5a?)

Hello Martin,
On Wed, 2002-12-04 at 07:47, Martin Langhoff wrote:
> In a nutshell, for a complex query (supplied below),
>

Sorry but I don't understand at all why do you post that question to
bugs@

> - the query optimizer in 3.23.53 takes 12~24m against ~8m in 4.0.5a;
>

Hmm.. Do you complain about it? You report that 4.0.5a for some task
works 50-300% faster than 3.23.53. Do you think it is bad?

> - if the query has a SELECT INTO, the destination table seems to be
> locked while the query optimizer is working; this has been observed with
> both ISAM and MyISAM target tables, and can cause process starvation in
> busy servers;
>

Do you mean INSERT ... SELECT ... syntax or SELECT ... INTO OUTFILE "" ?


With MyISAM, ISAM and HEAP storage types only Table Locks are supported
i.e. whole table is locked when necesarry. This is well docummented
feature.

http://www.mysql.com/doc/en/Internal_locking.html
http://www.mysql.com/doc/en/Locking_methods.html

The fact that one session can lock table by executing some query thus
causing other sessions to wait for lock to be released is part of normal
work of MySQL.

> - while the query optimizer is working, the mysqld daemon will not
> shutdown using the `mysqladmin shutdown` utility;
>

Yes. What is wrong with that?

mysqld waits for all queries to finish so it can shutdown gracefully
flushing all buffers and closing all oppened files if needed.

mysqladmin shutdown is NOT 'dirty shutdown' if this is what you have
expected.

> I think the really serious issue is the table being locked while the
> optimizer works. I would expect the table to be locked at a later stage
> in the process, preferrably when the SELECT has its output ready.
>

With 3.23 and 4.0 all queries are executed in single pass. There is no
separate Prepare - Execute stages. This will be supported in 4.1 with
new client-server protocol.

> preferrably when the SELECT has its output ready.

This is the point when Lock can be RELEASED not set. SELECT needs to
lock table so no one updates table while it works then when "output is
ready" table is no more needed so it can be unlocked right?


> This is a follow up to a bug I have initially reported using the
> mysqlbug script. The original report can be found at
> http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:126268 . The original
> report states that mysql was crashing, the truth is that we were being
> too impatient.

Ok. So mysqld didn't crashed? If so why did you reported it as bug?
You just didn't waited enough for EXPLAIN to finish? Is this the case?

Well once I killed query after ~80 hours working, but I am pretty
positive it wasn't 'hanging'. It was just huge and long query and I
expected it to last days. Ofcourse tables were locked all the time...

> To run these tests we compiled mysql 3.23.53 and 4.0.5a with debug
> flags. This tests were performed on an idle debian woody server, which
> has a Pentium 3 with 786MB RAM and decently fast UW-SCSI disk. You can
> find full compile flags for both mysql daemons at the end of this email.
>

I hope you understand that mysqld compiled for debugging is always
slower.

> You will find all the files referenced in this report at
> http://cookie.cwa.co.nz/~martin/mysql_playground/ , including datafiles
> and SQL queries that reproduce the problem.
>
> How-To-Repeat:
>
> - create 2 empty DBs
> `mysql -uroot -e "create database tkidb06_isam; create database
> tkidb06_myisam" `
>
> - Import the DB sample data (will take some time!)
> `zcat tkidb06.sql.gz | mysql -uroot tkidb06_isam ; zcat
> tkidb06.sql.gz | perl -pe 's/TYPE=ISAM/TYPE=MyISAM/' | mysql -uroot
> tkidb06_myisam`
> get the sample data from
> http://cookie.cwa.co.nz/~martin/mysql_playground/
>
> - Optionally, stop the safe_mysqld / mysqld_safe daemon, run (my)isamchk
> --analyze on the database tables. In all our tests are on 'analyzed'
> tables, but you don't need this step to reproduce the bug. Remember to
> restart mysql.
>
> - Optionally, stop the safe_mysqld / mysqld_safe daemon and restart it
> with --debug to collect a tracefile. Our trace files did not point to
> anything useful.
>
> - Run the provided SQL statement on either database.
> `mysql -uroot tkidb06_isam < tki_killer_explain_6.sql`
> You can get the statement at
>
>
> Please note that the statement is an EXPLAIN statement. These timings do
> not include time for actual SELECTS.
>
> Here, mysql 3.23.53 took an average of 13 minutes with ISAM tables and
> 24 minutes with MyISAM. Mysql 4.0.5a took 8 minutes to return.
>
> - Try other SQL statements, all of which will return reasonably fast.
>
>
>
> - Now, the bug: on different sessions,
> - execute tki_killer_selectinto_6.sql on session A
> - wait 1 second
> - execute tki_killer_selectinto_4.sql on session B (it usually takes
> ~ 6 sec.)
> -> session B will finish roughly 6 seconds after session A
>

Sorry, but what exactly do you call BUG in the above scenario?
You run huge query and wait for it to finish for 24 minutes. Ok.

Does mysqld crashes?
Does query provides wrong results?
Does any of your tables gets corrupted?

If answers of all these questions are No - I just don't see any problem.

If you need help with optimizing your query so it works faster then Ok,
but this is absolutely different question and has nothing to do with
Bugs, nor with test case we have to repeat.

Or maybe I don't understand well what you mean? If so don't hesitate to
explain details.

> Workaround:
>
> The only workaround we have identified is to use temporary tables when
> using SELECT INTO. Unfortunately, this is sometimes a bit inconvenient.
>
> Thanks a lot for your time, and patience. After all is said and done,
> and looking at the nasty SQL we throw at it, mysql does for us a lot
> more than we deserve.
>
> kind regards,
>
>
>
> martin


> --
> For technical support contracts, visit https://order.mysql.com/?ref=msal
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
> /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
> <___/ www.mysql.com
>
>

------------------------------------------------------------ ---------
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-thread13210@lists.mysql.com
To unsubscribe, e-mail

Re: Query optimizer bugs in mysql 3.23.53 (and 4.0.5a?)

Alexander,

thanks for your reply. I've tried to be a bit more detailed. Note I have
edited your reply for brevity. See below.

> Sorry but I don't understand at all why do you post that question to
> bugs@

Hmm. You might have a point. I was following up a prior bug report that
was wrong. However, mysqlbug had posted it to the general
discussion list, it seems.

I would also qualify my 'bug report' stance. I don't have an
understanding of the internals, so I am reporting some 'odd behaviour',
and I am open to accept that the problem might be in my own assumtions
and/or code.

Having said that... I believe there are 3 issues, so let's see:

>>- the query optimizer in 3.23.53 takes 12~24m against ~8m in 4.0.5a;
>
> Hmm.. Do you complain about it? You report that 4.0.5a for some task
> works 50-300% faster than 3.23.53. Do you think it is bad?

No. I think it is bad that running "EXPLAIN SELECT ..." takes that
long., even with tables that have had their indexes analyzed.

I accept that the computation needed by query optimizer strategies are
likely to grow exponentially. However, 8m is an extremely long time to
spend ordering a query using bits data you can retrieve with a few disk
seeks and cache in memory (given a fast CPU, fast disk and plenty of RAM).

As I said, my assumtions might be wrong. I am tempted to dig the
optimizer code from the sources and end my ignorance.

>>- if the query has a SELECT INTO, the destination table seems to be
>>locked while the query optimizer is working; this has been observed with
>>both ISAM and MyISAM target tables, and can cause process starvation in
>>busy servers;
>
> Do you mean INSERT ... SELECT ... syntax or SELECT ... INTO OUTFILE "" ?

Sorry! I meant "INSERT INTO tablename SELECT..." the full query can be
found in
http://cookie.cwa.co.nz/~martin/mysql_playground/tki_killer_ selectinto_6.sql

> With MyISAM, ISAM and HEAP storage types only Table Locks are supported
> i.e. whole table is locked when necesarry. This is well docummented
> feature.

Hmm. My issue here is that reading
http://www.mysql.com/doc/en/MyISAM.html I got the impression that (for
MyISAM tables with no free blocks) I could run concurrent inserts. See
the 2nd bullet point.

> The fact that one session can lock table by executing some query thus
> causing other sessions to wait for lock to be released is part of normal
> work of MySQL.

Agreed. I somehow did not expect the table to be locked so early. It
doesn't normally matter, but in this case, it is 8m early ;)

>>- while the query optimizer is working, the mysqld daemon will not
>>shutdown using the `mysqladmin shutdown` utility;

[...]

> mysqladmin shutdown is NOT 'dirty shutdown' if this is what you have
> expected.

Agreed. Dismiss that.

>>I think the really serious issue is the table being locked while the
>>optimizer works. I would expect the table to be locked at a later stage
>>in the process, preferrably when the SELECT has its output ready.
>>
>
>
> With 3.23 and 4.0 all queries are executed in single pass. There is no
> separate Prepare - Execute stages. This will be supported in 4.1 with
> new client-server protocol.

I was not aware of that. I was assuming several stages. Hmmm.


>>This is a follow up to a bug I have initially reported using the
>>mysqlbug script. The original report can be found at
>>http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:126268 . The original
>>report states that mysql was crashing, the truth is that we were being
>>too impatient.
>
>
> Ok. So mysqld didn't crashed? If so why did you reported it as bug?
> You just didn't waited enough for EXPLAIN to finish? Is this the case?

Yes it is. Shame on us for killing mysql after it took over 1hr to run
EXPLAIN. Part of the reason for this email is to supercede that 'bad'
bug report. We were running the tests on a moderately loaded dev server
with IDE disks. When then moved our testing to an idle machine with SCSI
disks.

> Well once I killed query after ~80 hours working, but I am pretty
> positive it wasn't 'hanging'. It was just huge and long query and I
> expected it to last days. Ofcourse tables were locked all the time...

You trust mysql a lot, and I guess we are learning to be more respectful.

(...)

> Sorry, but what exactly do you call BUG in the above scenario?
> You run huge query and wait for it to finish for 24 minutes. Ok.

Ok, my fault, the bug I see is "No concurrency whatsoever on INSERT ...
SELECT on (freshly created) MyISAM tables."

I would also like a hint to understand why it is "normal" that my
queries take 8m (in the best case) to EXPLAIN.

regards,






m



------------------------------------------------------------ ---------
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-thread13212@lists.mysql.com
To unsubscribe, e-mail

Re: Query optimizer bugs in mysql 3.23.53 (and 4.0.5a?)

On Wednesday 04 December 2002 23:46, Martin Langhoff wrote:

Dear Martin,

Of course you experience with Explain does not show quite nice MySQL behavior
and we would like to check if we can do something about it. However I would
ask you to do the following
- Try this query on release version, compiled without debugging information.
I had some experience with EXPLAIN which took about 100 times more time in
case of MySQL was compiled with debugging - there are just a lot of debug
checks on this path.
- If this is repeatable bug report and it still on its place with recent
MySQL 4.0 tree (MySQL 3.23 tree is for critical bugs only) please tar/gzip
your tables queries and description of the isue and upload it to
ftp://support.mysql.com/pub/mysql/secret

Speaking about TableLocks - you're right MyISAM can do concurrent inserts if
table does not have any holes but Innodb is the solution for all other cases
- it uses row level locks and consistent reads.

The same process can be done with "hanging" queries you have, but before you
upload anything we shall make sure this is a bug - sending query with EXPLAIN
output is the good way to check it. We often get hanging queries bug reports
which really turn to be just very slow or malformed queries (lost join where
clause etc).

Good luck with your investigations.


--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Peter Zaitsev
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Moscow, Russia
<___/ www.mysql.com


------------------------------------------------------------ ---------
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-thread13213@lists.mysql.com
To unsubscribe, e-mail

Re: Query optimizer bugs in mysql 3.23.53 (and 4.0.5a?)

Hello Martin,
On Wed, 2002-12-04 at 22:46, Martin Langhoff wrote:
> Alexander,


> Hmm. You might have a point. I was following up a prior bug report that
> was wrong. However, mysqlbug had posted it to the general

Thank you for clarifaing that your first report was wrong so we don't
have to waste time on it.

> No. I think it is bad that running "EXPLAIN SELECT ..." takes that
> long., even with tables that have had their indexes analyzed.

When in doubt about any problem always try to repeat it with our
binaries. We know how exactly they are built and as you know build
environment can change a lot.

Keep in mind also what Peter Zaitsev wrote you about debugging binaries
and EXPLAIN.

> As I said, my assumtions might be wrong. I am tempted to dig the
> optimizer code from the sources and end my ignorance.

That is ok. As you know optimizing oprimizer is endless job so if you
have comments or suggestions on it's code you are welcome. MySQL is Open
Source product isn't it? :)


> Hmm. My issue here is that reading
> http://www.mysql.com/doc/en/MyISAM.html I got the impression that (for
> MyISAM tables with no free blocks) I could run concurrent inserts. See
> the 2nd bullet point.

Well maybe it is not that clear from manual, but "concurrent" here is
about Insert <-> Select concurrency. You cannot have 2 concurrent
INSERTs

You can test it with opening 3 sessions and running show processlist in
third one (or using mysqldadmin processlist)

You will find that you can have 1 SELECT 1 INSERT with same table
running concurrent, but if you try 2 INSERTS with same table you will
see one of them staying in Locked state waiting for other to finish.

All of the above takes place with MyISAM tables (and ISAM, but it is
deprecated format which we can stop supporting in the future)

With InnoDB storage which support row-locks things can be quite
different.



Best regards
>
> --
> For technical support contracts, visit https://order.mysql.com/?ref=msal
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
> /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
> <___/ www.mysql.com
>
>

------------------------------------------------------------ ---------
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-thread13214@lists.mysql.com
To unsubscribe, e-mail