Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 5 down vote favorite

I have two mysql 5.1 instances (say A, B) hosting the same database schema. If I run (with mysql workbench) the same query on both instances I don't understand why I get very different response times with subsequent requests.

On instance A, first query execution takes 0.688s and second query execution takes 0.683s
On instance B, first query execution takes 0.688s and second query execution takes 0.027s

It looks like there's a cache configuration difference between the two instances but I can't find it.

Comparing the SHOW VARIABLES result on both instances gives only few distinct values (which I don't see how they may impact query execution time) : 

general_log_file :  
/path/to/file2.log    VS    /path/to/file1.log  

hostname :  
mysql2    VS    mysql1  

pid_file :  
/var/lib/mysql/mysql2.pid    VS    /var/lib/mysql/mysql1.pid  

slave_max_allowed_packet :  
1073741824    VS    (empty)  

slow_query_log_file :  
/var/lib/mysql/mysql2-slow.log   VS   /var/lib/mysql/mysql1-slow.log  

system_time_zone :  
CET   VS  CEST  

timestamp :  
1352219171   VS   1352219229  

version :  
5.1.66-0ubuntu0.10.04.1   VS   5.1.62-0ubuntu0.10.04.1

Just to mention, instance A is our test environment and instance B is our production environment

Edit : (recommended by @Rick James)

The following variables are strictly identical on both environments

SHOW VARIABLES LIKE '%buffer%'

bulk_insert_buffer_size 8388608
innodb_buffer_pool_size 8388608
innodb_log_buffer_size  1048576
join_buffer_size    131072
key_buffer_size 16777216
myisam_sort_buffer_size 8388608
net_buffer_length   16384
preload_buffer_size 32768
read_buffer_size    131072
read_rnd_buffer_size    262144
sort_buffer_size    2097144
sql_buffer_result   OFF


SHOW VARIABLES LIKE 'innodb%'

innodb_adaptive_hash_index  ON
innodb_additional_mem_pool_size 1048576
innodb_autoextend_increment 8
innodb_autoinc_lock_mode    1
innodb_buffer_pool_size 8388608
innodb_checksums    ON
innodb_commit_concurrency   0
innodb_concurrency_tickets  500
innodb_data_file_path   ibdata1:10M:autoextend
innodb_data_home_dir    
innodb_doublewrite  ON
innodb_fast_shutdown    1
innodb_file_io_threads  4
innodb_file_per_table   OFF
innodb_flush_log_at_trx_commit  1
innodb_flush_method 
innodb_force_recovery   0
innodb_lock_wait_timeout    50
innodb_locks_unsafe_for_binlog  OFF
innodb_log_buffer_size  1048576
innodb_log_file_size    5242880
innodb_log_files_in_group   2
innodb_log_group_home_dir   ./
innodb_max_dirty_pages_pct  90
innodb_max_purge_lag    0
innodb_mirrored_log_groups  1
innodb_open_files   300
innodb_rollback_on_timeout  OFF
innodb_stats_method nulls_equal
innodb_stats_on_metadata    ON
innodb_support_xa   ON
innodb_sync_spin_loops  20
innodb_table_locks  ON
innodb_thread_concurrency   8
innodb_thread_sleep_delay   10000
innodb_use_legacy_cardinality_algorithm ON

The actual SELECT : 

select
    post0_.id as id83_0_,
    thread1_.id as id81_1_,
    post0_.createDate as createDate83_0_,
    post0_.editCount as editCount83_0_,
    post0_.editDate as editDate83_0_,
    post0_.editor_id as editor7_83_0_,
    post0_.postType as postType83_0_,
    post0_.poster_id as poster8_83_0_,
    post0_.repliedTo_id as repliedTo9_83_0_,
    post0_.text as text83_0_,
    post0_.thread_id as thread10_83_0_,
    thread1_.created as created81_1_,
    thread1_.debate_id as debate16_81_1_,
    thread1_.description as descript4_81_1_,
    thread1_.lastPostDate as lastPost5_81_1_,
    thread1_.poster_id as poster17_81_1_,
    thread1_.status as status81_1_,
    thread1_.threadType as threadType81_1_,
    thread1_.title as title81_1_,
    thread1_.updated as updated81_1_,
    thread1_.viewCount as viewCount81_1_,
    thread1_.imageUrl as imageUrl81_1_,
    thread1_.mediaTypeValue as mediaTy11_81_1_,
    thread1_.mediaUrl as mediaUrl81_1_,
    thread1_.modoWeight as modoWeight81_1_,
    thread1_.shortName as shortName81_1_,
    thread1_.why as why81_1_,
    thread1_.theme_id as theme18_81_1_,
    thread1_.answer_debaterId as answer19_81_1_,
    thread1_.answer_pollId as answer20_81_1_,
    (SELECT
        COUNT(*) 
    FROM
        Post p 
    WHERE
        p.thread_id = thread1_.id) as formula9_1_ 
from
    Post post0_ 
inner join
    Thread thread1_ 
        on post0_.thread_id=thread1_.id 
where
    post0_.postType in (
        'P', 'F'
    ) 
    and thread1_.debate_id=69 
    and thread1_.threadType<>'B' 
    and length(post0_.text)>0 
order by
    createDate desc limit 5

The EXPLAIN SELECT (sam on both environments) :

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY thread1_    ref PRIMARY,FK9545FA2AEBB74893  FK9545FA2AEBB74893  5   const   3690    Using where; Using temporary; Using filesort
1   PRIMARY post0_  ref FK260CC0D74BC5F3    FK260CC0D74BC5F3    5   debate-atest.thread1_.id    2   Using where
2   DEPENDENT SUBQUERY  p   ref FK260CC0D74BC5F3    FK260CC0D74BC5F3    5   debate-atest.thread1_.id    2   Using where; Using index

The CREATE TABLE STATEMENT (exact same on both except the constraint names) : 

delimiter $$

CREATE TABLE `Post` (
  `postType` varchar(1) NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `createDate` datetime DEFAULT NULL,
  `editCount` int(11) NOT NULL,
  `editDate` datetime DEFAULT NULL,
  `text` longtext,
  `editor_id` int(11) DEFAULT NULL,
  `poster_id` int(11) DEFAULT NULL,
  `repliedTo_id` int(11) DEFAULT NULL,
  `thread_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `createDateIx` (`createDate`),
  KEY `FK260CC0D74BC5F3` (`thread_id`),
  KEY `FK260CC07C1A4F95` (`poster_id`),
  KEY `FK260CC0960B3775` (`editor_id`),
  KEY `FK260CC0D7C95B5F` (`repliedTo_id`),
  CONSTRAINT `FK260CC0D7C95B5F` FOREIGN KEY (`repliedTo_id`) REFERENCES `Post` (`id`),
  CONSTRAINT `FK260CC07C1A4F95` FOREIGN KEY (`poster_id`) REFERENCES `Debater` (`id`),
  CONSTRAINT `FK260CC0960B3775` FOREIGN KEY (`editor_id`) REFERENCES `Debater` (`id`),
  CONSTRAINT `FK260CC0D74BC5F3` FOREIGN KEY (`thread_id`) REFERENCES `Thread` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=148523 DEFAULT CHARSET=utf8$$

and

delimiter $$

CREATE TABLE `Thread` (
  `threadType` varchar(1) NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created` datetime DEFAULT NULL,
  `description` varchar(700) DEFAULT NULL,
  `lastPostDate` datetime DEFAULT NULL,
  `status` int(11) NOT NULL,
  `title` varchar(100) DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  `viewCount` int(11) NOT NULL,
  `imageUrl` varchar(255) DEFAULT NULL,
  `mediaTypeValue` int(11) DEFAULT NULL,
  `mediaUrl` varchar(1000) DEFAULT NULL,
  `modoWeight` int(11) DEFAULT NULL,
  `shortName` varchar(15) DEFAULT NULL,
  `why` longtext,
  `debate_id` int(11) DEFAULT NULL,
  `poster_id` int(11) DEFAULT NULL,
  `theme_id` int(11) DEFAULT NULL,
  `answer_debaterId` int(11) DEFAULT NULL,
  `answer_pollId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `answerPollIx` (`answer_pollId`),
  KEY `FK9545FA2AAF24B581` (`theme_id`),
  KEY `FK9545FA2A7C1A4F95` (`poster_id`),
  KEY `FK9545FA2AEBB74893` (`debate_id`),
  KEY `FK9545FA2A82957DB8` (`answer_debaterId`,`answer_pollId`),
  CONSTRAINT `FK9545FA2A82957DB8` FOREIGN KEY (`answer_debaterId`, `answer_pollId`) REFERENCES `Answer` (`debaterId`, `pollId`),
  CONSTRAINT `FK9545FA2A7C1A4F95` FOREIGN KEY (`poster_id`) REFERENCES `Debater` (`id`),
  CONSTRAINT `FK9545FA2AAF24B581` FOREIGN KEY (`theme_id`) REFERENCES `Thread` (`id`),
  CONSTRAINT `FK9545FA2AEBB74893` FOREIGN KEY (`debate_id`) REFERENCES `Debate` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=49829 DEFAULT CHARSET=utf8$$
share|improve this question
Are the 2 environments identical? (RAM size for example) – ypercube Nov 6 '12 at 18:15
Are the tables identical (including indexing) and with same data (number of rows)? – ypercube Nov 6 '12 at 18:17
Are the two machines at the same distance (e.g. ping time) from you? – ypercube Nov 6 '12 at 18:45
Yes, the 2 environments are identical : they are openvz VM's on the same machnine with exactly the same hardware configuration : memory 1GB (swap 1GB). – koyaga Nov 6 '12 at 19:09
And this happens only for one query? And only once or in a consistent way for that query? What about other queries? – ypercube Nov 6 '12 at 19:12
show 6 more comments

closed as too localized by Mark Storey-Smith, Max Vernon, dezso, RolandoMySQLDBA, StanleyJohns Apr 29 at 17:41

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, see the FAQ.

2 Answers

up vote 0 down vote

SHOW VARIABLES LIKE '%buffer%'; -- Don't trust the my.cnf

SHOW VARIABLES LIKE 'innodb%';

Let's see the actual SELECT -- there are probably clues there.

EXPLAIN SELECT -- on each machine.

SHOW CREATE TABLE

Then diff those between the two machines. If that does not tell you what the issue is, show us the outputs, highlighting the diffs.

share|improve this answer
Thanks for your answer. I re-checked the variables as you stated and there NO diffs between the two instances. I edited the question with the info you mentioned – koyaga Nov 7 '12 at 10:17
up vote 0 down vote

If the 2 machines are identical, my eyes turn to the virtual machine host. I'm not familiar with openvz, but those hosts usually apply some sort of static/dynamic assignment of ram and disk space/IO. Maybe the other virtual client has more load and thus never gives up or frees RAM back to the host while the other has to ask for it when you run simple queries every now and then.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.