MySQL Performance inquiry

Question

I have moved my drupal site from one mysql server to another one.

Old Machine has 1 cpu, 1GB Ram New Machine has 4 cpu, 4GB Ram.

I have a huge negative difference in perfomance on this query ( 2 mins vs 2 secs )

    select distinct c.client
    from client_table c
    LEFT JOIN reps r on ( c.client = r.client )
    where r.user_id is NULL
    AND c.client not in ( select distinct client from billing where first_purchase = 1 ) 


NEW       OLD
| connect_timeout           10                    |connect_timeout            5
| have_federated_engine     DISABLED              | have_federated_engine      YES
| max_connections           100                   | max_connections            400
| max_seeks_for_key         18446744073709551615  | max_seeks_for_key          4294967295
| max_write_lock_count      18446744073709551615  | max_write_lock_count       4294967295
| myisam_max_sort_file_size 9223372036853727232   | myisam_max_sort_file_size  2147483647
| max_binlog_cache_size     18446744073709547520  | max_binlog_cache_size      4294967295
| myisam_recover_options    BACKUP                | myisam_recover_options     OFF
| range_alloc_block_size    4096                  | range_alloc_block_size     2048
| table_cache               457                   | table_cache                307
| version                   5.0.67-0ubuntu6-log   | version                    5.0.51a-3ubuntu5.4-log
| version_compile_machine   x86_64                | version_compile_machine    i486


ONLY on NEW | relay_log                       |
ONLY on NEW | relay_log_index                 |               
ONLY on NEW | relay_log_info_file             | relay-log.info
ONLY on NEW innodb_adaptive_hash_index        | ON

Any ideas on how to identify what is causing the problem or how to fix it?

Answer 1

Try using the MySQL Query Profiler.

I would profile in both environments.

So how do you go about analyzing database performance? There are three forms of performance analysis that are used to troubleshoot and tune database systems:

Bottleneck analysis - focuses on answering the questions: What is my database server waiting on; what is a user connection waiting on; what is a piece of SQL code waiting on?

Workload analysis - examines the server and who is logged on to determine the resource usage and activity of each.

Ratio-based analysis - utilizes a number of rule-of-thumb ratios to gauge performance of a database, user connection, or piece of code.

Answer 2

You might need to rebuild your indexes on the new instance.

Answer 3

Make triple-sure you've rebuilt your indicies, they don't really carry over.