MySQL Bugs: #73753: Query runs on statistics 120 seconds since moved to a new MySQL Version 5.1.73

Bug #73753	Query runs on statistics 120 seconds since moved to a new MySQL Version 5.1.73
Submitted:	28 Aug 2014 11:50	Modified:	28 Sep 2014 14:34
Reporter:	Volker Scholz	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1.73	OS:	Linux (CENTOS 6.5)
Assigned to:		CPU Architecture:	Any
Tags:	long running query

Description:
We moved our shop DB to a new server but the performance is really bad and some function don't work anymore.
The query starts some indexing and on the old server it runs within 0.7 seconds on the new between 110 and 120 seconds.

I tried diffrent perfomance optimization on the DB server but without success.
I set innodb-stats-on-metadata to FALSE no improvement.
Below the my.cnf
[mysqld]
bind-address=127.0.0.1
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
innodb_stats_on_metadata=OFF
query_cache_type=1
query_cache_size=16M
query_cache_limit=16M
thread_cache_size=16
key_buffer=256M
table_cache=1024
table_definition_cache=1024
innodb_buffer_pool_size=512M
innodb_thread_concurrency=8
innodb_additional_mem_pool_size=8M
innodb_log_buffer_size=4M
join_buffer_size=2M
max_heap_table_size=16M
tmp_table_size=16M
max_allowed_packet=2M
read_buffer_size=2M
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# tmpdir=/var/tmp

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Query profile:

mysql> SHOW PROFILE FOR QUERY 7;
+----------------------+------------+
| Status               | Duration   |
+----------------------+------------+
| starting             |   0.002962 |
| checking permissions |   0.000020 |
| Opening tables       |   0.000346 |
| System lock          |   0.000013 |
| Table lock           |   0.000031 |
| init                 |   0.001905 |
| optimizing           |   0.000127 |
| statistics           | 117.369500 |
| preparing            |   0.000352 |
| executing            |   0.000006 |
| Sending data         |   0.008086 |
| end                  |   0.000014 |
| query end            |   0.000004 |
| freeing items        |   0.000315 |
| logging slow query   |   0.000009 |
| logging slow query   |   0.000004 |
| cleaning up          |   0.000014 |
+----------------------+------------+
17 rows in set (0.00 sec)

How to repeat:
Mail or blog

Thank you for the bug report. Please check MySQL Product Support EOL for 5.1:

http://www.mysql.com/support/eol-notice.html

You are encouraged to upgrade to MySQL 5.6. 

Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".