Mysql Query hang on production database server but works fine locally

Question

I'm running into an issue that I can't seem to solve on my own. I've got an amazon EC2 server (large instance) running mysql 5.1.37 and a rails application. The server has recently been having issues with database process hangs and high cpu use, and when I go into the mysql console and do a show full processlist I see it hangs with sending data on a query like this:

| 315 | root | 10.249.61.118:47812 | roomorama | Query   |  193 | Sending data | SELECT COUNT(*) FROM (SELECT `rooms`.*, IFNULL(MIN(av.host_daily_price), rooms.host_daily_price) AS 'price' FROM `rooms` LEFT JOIN availabilities AS av ON (av.room_id = rooms.id AND av.date BETWEEN '2011-01-01' AND '2011-07-01' AND av.inquiry_id IS NULL AND (av.status IS NULL OR av.status = 0)) WHERE (rooms.deleted_at IS NULL) AND (`rooms`.`city_id` = 1 AND `rooms`.`hidden` = 0) AND (`rooms`.`created_at` <= '2011-01-01 16:54:43') GROUP BY rooms.id) as search_results |

This seems to not return at all or take a ridiculously long time to complete. Running this query on my dev machine (same mysql version) locally returns the results quickly and without an issue though. I have no idea where to start looking, is this a mysql bug? a very badly configured mysql server? Tables are innodb and here is my full mysql config file on the server

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp

character-set-server            = utf8
default-character-set           = utf8

default-storage-engine=innodb
skip-external-locking
skip-name-resolve
skip-federated

bind-address            = 0.0.0.0

#
# * Fine Tuning
#

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover          = BACKUP

#thread_concurrency     = 10
max_allowed_packet      = 32M
thread_stack            = 192K
thread_cache_size       = 8
key_buffer              = 250M
max_connections         = 1024
wait_timeout            = 180
net_read_timeout        = 30
net_write_timeout       = 30
back_log                = 128
table_cache             = 1024
max_heap_table_size     = 512M                                                                                                                                              

#
# * InnoDB Tuning
#

innodb_buffer_pool_size         = 5000M
innodb_additional_mem_pool_size = 20M
innodb_flush_log_at_trx_commit  = 2
innodb_lock_wait_timeout        = 50
#innodb_log_file_size=250M
#innodb_log_buffer_size=8M


#
# * Query Cache Configuration
#

# Query Cache
query_cache_limit      = 4M
query_cache_size       = 256M
query_cache_type       = 1


#
# * Slow Query Logging
#
log_slow_queries       = /var/log/mysql/mysql-slow.log
long_query_time = 1
log-queries-not-using-indexes

# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id              = 1
#log_bin                        = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name



[mysqldump]
quick
quote-names

max_allowed_packet      = 32M
default-character-set   = utf8

[mysql]
default-character-set   = utf8

[isamchk]
key_buffer              = 16M

Answer 1

You should look into the indexes of your tables. I just now had the same problem and found that I was missing an index on the local table.

Answer 2

Is it possible that your dev server has a different set of data on it? Remember that the time the query takes to complete is dependent on the number and contents of the rows in the database.