MySQL Bugs: #67064: SQL Query Execution from Batch files is now really slow, since 5.5.24 and new.

Bug #67064	SQL Query Execution from Batch files is now really slow, since 5.5.24 and new.
Submitted:	3 Oct 2012 3:36	Modified:	13 Feb 2018 22:39
Reporter:	MELERIX ...	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S5 (Performance)
Version:	since 5.5.24 and new versions	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Slow SQL Query Execution

Description:
I hope it describe the issue properly.

in MySQL 5.5.23 SQL query execution from batch files is fast, but in MySQL 5.5.24 something was changed (probably the fix for IPV4/IPV6) and now SQL query execution is really slow, and could take minutes.

How to repeat:
Working Fine Scenario
---------------------

1 - Download MySQL 5.5.23.
2 - Download L2J DataPack from here: http://www.l2jserver.com/nightly/index.php?dir=beta%2F&download=L2J_DataPack_BETA.zip
3 - Extract it in a folder.
4 - Open /tools/ folder, execute "database_installer.bat" or "database_installer.sh" if you are in Linux, and follow the steps in the screen to install the DB.
5 - You will notice SQL query execution is fast, it take a short time to import/update/maintain the DB files.

Working Slow Scenario
---------------------

1 - Download MySQL 5.5.24 or any new version for example 5.5.28.
2 - Download L2J DataPack from here: http://www.l2jserver.com/nightly/index.php?dir=beta%2F&download=L2J_DataPack_BETA.zip
3 - Extract it in a folder.
4 - Open /tools/ folder, execute "database_installer.bat" or "database_installer.sh" if you are in Linux, and follow the steps in the screen to install the DB.
5 - You will notice SQL query execution is really slow, it take several minutes to import/update/maintain the DB files.

Thank you for the bug report. Verified on Windows 7 64-bits 8GB RAM.

Both scripts ran in a couple of seconds on 5.5.23 and 5.5.28 on my machine.
So, no real difference for me.

Does adding "skip-name-resolve" help ?   Can you run a test on 5.5.28 using options to mysqld server, then upload the slow query log for us to check if any particular query is slow ?

--skip-name-resolve --long-query-time=0 --slow-query-log ?

if you think ipv6 could be a problem, please test it as so.
start server with --bind-address=127.0.0.1  and tell installation to use 127.0.0.1 .  does it make any difference?

@Shane Bester

if I enable "skip-name-resolve" or "--bind-address=127.0.0.1", then I can't connect anymore to MySQL server, it throw the following error:

"1130 - Host '127.0.0.1' is not allowed to connect to this MySQL server"

adding "--bind-address=127.0.0.1" doesn't change anything, SQL queries from batch files are still slow.

does this connection test run in same speed on both servers ?

Attachment: test_connection.bat (application/octet-stream, text), 46.83 KiB.

with your "test_connection.bat" there is not noticeable difference , is 1 or 2 seconds, due is just basic SQL query.

you should try to test with the batch file script from the links I've reported, and only then you will notice the big difference.

I did run the installer batch file. it ran in same time on both 5.5.23 and 5.5.28 (some seconds).

We need to analyze what exactly took longer and why.  I assume you had identical my.ini on both instances, and clean datadir to start with...

Can you pinpoint which queries takes longer?  We need a simple set of sql queries that when executed will take longer on newer version.

in my test the following SQL queries are affected: import/update/delete/optimize, a table.

the difference is really big.

is not hard to test, you just need to download and check with the batch scripts I've reported above.

alter table queries are affected too.

This looks related:
http://bugs.mysql.com/bug.php?id=35918

Posted by developer:
 
Closing as "Could not reproduce" since this has been idle for a long time.
Please reopen if problem persists.