| Bug #24169 | Dramatic speed loss when going from 5.1.11 to 5.1.12 (or to 5.0.26) | ||
|---|---|---|---|
| Submitted: | 10 Nov 2006 7:41 | Modified: | 28 Feb 2007 7:26 |
| Reporter: | Martin Boer | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 5.1.12, 5.1.14 | OS: | Linux (Linux (Debian/Sarge)) |
| Assigned to: | Valeriy Kravchuk | CPU Architecture: | Any |
| Tags: | 5.1.11, regression | ||
[10 Nov 2006 7:41]
Martin Boer
[10 Nov 2006 8:29]
Valeriy Kravchuk
Thank you for a problem report. Please, upload your database and test query to: ftp://ftp.mysql.com/pub/mysql/upload/ Use bug number (24169) in file names. Add comment to this report when finished.
[10 Nov 2006 9:40]
Martin Boer
Hi, thanks for your fast response. At this moment I'm uploading the files, but the database tgz file takes quite long. As I use the unix ftp client I can't see if it still is doing something. Can you tell if the file is still increasing in size ? Using another connection told me I dont have read-permission in the upload directory (which makes sense). It is probably my upload speed which is restricted. When this upload is finished I still need to upload our my.cnf
[10 Nov 2006 10:09]
Martin Boer
Okay... if everything is correct you now have A perlscipt, a perl module, 2 databases and a my.cnf. I used the following for the compilation of MySQL. #!/bin/bash cd mysql-5.1.12-beta CFLAGS="-O3" CXX=gcc CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti" export CFLAGS export CXX export CXXFLAGS ./configure --enable-assembler --with-mysqld-ldflags=-all-static --prefix=/afs1/mysql/5.1.12 --localstatedir=/afs2/mysql/active/data --with-unix-socket-path=/afs2/mysql/active/data/mysql.sock --with-mysqld-user=mysql
[10 Nov 2006 10:47]
Valeriy Kravchuk
Please, confirm that file sizes are OK: mysqldev@production:~> ls -l /supportftp/pub/mysql/upload/*24169* --w-rw---- 1 ftpuploads myftp 2927 Nov 10 09:47 /supportftp/pub/mysql/upload /beat-24169.pm --w-rw---- 1 ftpuploads myftp 1259 Nov 10 09:47 /supportftp/pub/mysql/upload /checkdb-24169.pl --w-rw---- 1 ftpuploads myftp 7322643 Nov 10 10:53 /supportftp/pub/mysql/upload /db-nagios-24169.tgz --w-rw---- 1 ftpuploads myftp 50533 Nov 10 10:53 /supportftp/pub/mysql/upload /db-userobjects-24169.tgz --w-rw---- 1 ftpuploads myftp 762 Nov 10 11:03 /supportftp/pub/mysql/upload /my-24169.cnf
[22 Dec 2006 12:06]
Valeriy Kravchuk
Please, try to repeat with a newer version, 5.1.14, and inform about the results.
[28 Dec 2006 9:39]
Martin Boer
I just compiled/installed 5.1.14-beta using the same variables as before but just like 5.1.12 the test query takes around 7 seconds: # ./checkdb.pl Start query .... timetaken@1167298496: 7.47562 Start query .... timetaken@1167298505: 7.633205 Start query .... timetaken@1167298514: 7.499059 I hope this helps (but I got this feeling it doesn't).
[12 Feb 2007 14:01]
Valeriy Kravchuk
Sorry for a delay with htis report. I had dowloaded and checked your files. First of all, you sent me tables from 4.1.x (note Version 9):
mysql> show table status like 'config_services'\G
*************************** 1. row ***************************
Name: config_services
Engine: MyISAM
Version: 9
Row_format: Dynamic
Rows: 14864
Avg_row_length: 280
Data_length: 4193524
Max_data_length: 4294967295
Index_length: 215040
Data_free: 30276
Auto_increment: 19434
Create_time: 2006-10-19 11:23:57
Update_time: 2006-11-10 10:36:19
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)
Have you dumped and restored them in 5.1.x before using? If not, please, do. Please, send also the results of EXPLAIN for the query you had used in your *.pl code from both 5.1.11 ("fast") and 5.1.12 (or 5.1.14), "slow".
[20 Feb 2007 8:24]
Valeriy Kravchuk
Still need EXPLAIN results from "fast" and "slow" versions. We may have new optimizer bug/changed behaviour here because of some other bug fix. Please, after getting these EXPLAIN results, run ANALIZE on table(s) involved (on "slow" version) and check if runtimes and EXPLAIN results will change.
[28 Feb 2007 7:26]
Valeriy Kravchuk
Problem fixed by reporter.
