0

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?

Improve this question
2
  • 2
    Did you compare the explains? Commented Sep 29, 2009 at 17:20
  • I could try that. although it may bring my site to a halt. Commented Sep 29, 2009 at 17:25

3 Answers 3

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

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

Improve this answer

Comments

0

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.