Re: Query on some MySQL-internals
Hi Martin. Thanks for the reply. As I had mentioned, we are running both the instances since last 6 years or so, and the records are inserted/deleted on both the instances. So, we did a show table status like 'XX' \G; on both the instances, and following are the outputs (here XX is the table upon which the OPTIMIZE command was run). Also note that the outputs are after the OPTIMIZE command had been run on the respective instance-tables :: 1) Instance 1, which showed massive improvement in INSERT query completion times after OPTIMIZE command was run on table XX:: db1show table status like 'XX' \G; *** 1. row *** Name: XX Engine: InnoDB Version: 10 Row_format: Compact Rows: 12380147 Avg_row_length: 473 Data_length: 5865701376 Max_data_length: 0 Index_length: 522043392 Data_free: 91226112 Auto_increment: NULL Create_time: NULL Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: partitioned Comment: 1 row in set (0.08 sec) 2) Instance 2, which showed no improvement in INSERT query completion times, after running OPTIMIZE command on table XX :: db2show table status like 'XX' \G; *** 1. row *** Name: XX Engine: InnoDB Version: 10 Row_format: Compact Rows: 13189570 Avg_row_length: 407 Data_length: 5376540672 Max_data_length: 0 Index_length: 518553600 Data_free: 36700160 Auto_increment: NULL Create_time: NULL Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: partitioned Comment: 1 row in set (0.24 sec) Thanks and Regards, Ajay On Sat, Sep 6, 2014 at 8:06 PM, Martin Gainty mgai...@hotmail.com wrote: Date: Sat, 6 Sep 2014 14:26:22 +0530 Subject: Query on some MySQL-internals From: ajaygargn...@gmail.com To: mysql@lists.mysql.com Hi all. We are facing a very strange scenario. We have two mysql-instances running on the same machine, and they had been running functionally fine since about 6 years or so (catering to millions of records per day). However, since last few days, we were experiencing some elongated slowness on both the instances. So, we decided to OPTIMIZE TABLE slow_table on both the instances. We first ran the command on one instance. That speeded up things massively (select count(*) that was earlier taking 45 minutes was now running in less than 3 minutes). We then ran the command on the second instance. However, that seemed to have no effect. We ran the command again (on the same instance); again it had no effect. What could be the reason of this strange behavior? Both the instances run under fairly the same load MGHow do you extract the metrics to determine what the second instance is handling the same load as first instance? MGvmstat? MGiostat? MGSHOW GLOBAL STATUS ? and both instances are mounted on the same partition (obviously, all the directories are different). Hoping for some light on this strange issue. Thanks and Regards, Ajay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- Regards, Ajay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Query on some MySQL-internals
Good Afternoon Ajay Im not seeing any giant deltas between the two metrics except i did notice the elapsed time to run the metric on second instance was 3 times slower Any chance we can do pathping from your present location: 1)pathping SQLServerInstance1 2)pathping SQLServerInstance2 to determine if there is an intervening router that is slowing down the second instance? Does anyone have advice to get Ajay to track down why his second instance is non-preformant ? Martin __ Date: Sun, 7 Sep 2014 23:06:09 +0530 Subject: Re: Query on some MySQL-internals From: ajaygargn...@gmail.com To: mgai...@hotmail.com CC: mysql@lists.mysql.com Hi Martin. Thanks for the reply. As I had mentioned, we are running both the instances since last 6 years or so, and the records are inserted/deleted on both the instances. So, we did a show table status like 'XX' \G; on both the instances, and following are the outputs (here XX is the table upon which the OPTIMIZE command was run). Also note that the outputs are after the OPTIMIZE command had been run on the respective instance-tables :: 1) Instance 1, which showed massive improvement in INSERT query completion times after OPTIMIZE command was run on table XX:: db1show table status like 'XX' \G; *** 1. row *** Name: XX Engine: InnoDB Version: 10 Row_format: Compact Rows: 12380147 Avg_row_length: 473 Data_length: 5865701376 Max_data_length: 0 Index_length: 522043392 Data_free: 91226112 Auto_increment: NULL Create_time: NULL Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: partitioned Comment: 1 row in set (0.08 sec) 2) Instance 2, which showed no improvement in INSERT query completion times, after running OPTIMIZE command on table XX :: db2show table status like 'XX' \G; *** 1. row *** Name: XX Engine: InnoDB Version: 10 Row_format: Compact Rows: 13189570 Avg_row_length: 407 Data_length: 5376540672 Max_data_length: 0 Index_length: 518553600 Data_free: 36700160 Auto_increment: NULL Create_time: NULL Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: partitioned Comment: 1 row in set (0.24 sec) Thanks and Regards, Ajay On Sat, Sep 6, 2014 at 8:06 PM, Martin Gainty mgai...@hotmail.com wrote: Date: Sat, 6 Sep 2014 14:26:22 +0530 Subject: Query on some MySQL-internals From: ajaygargn...@gmail.com To: mysql@lists.mysql.com Hi all. We are facing a very strange scenario. We have two mysql-instances running on the same machine, and they had been running functionally fine since about 6 years or so (catering to millions of records per day). However, since last few days, we were experiencing some elongated slowness on both the instances. So, we decided to OPTIMIZE TABLE slow_table on both the instances. We first ran the command on one instance. That speeded up things massively (select count(*) that was earlier taking 45 minutes was now running in less than 3 minutes). We then ran the command on the second instance. However, that seemed to have no effect. We ran the command again (on the same instance); again it had no effect. What could be the reason of this strange behavior? Both the instances run under fairly the same load MGHow do you extract the metrics to determine what the second instance is handling the same load as first instance? MGvmstat? MGiostat? MGSHOW GLOBAL STATUS ? and both instances are mounted on the same partition (obviously, all the directories are different). Hoping for some light on this strange issue. Thanks and Regards, Ajay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- Regards, Ajay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
RE: Query on some MySQL-internals
Date: Sat, 6 Sep 2014 14:26:22 +0530 Subject: Query on some MySQL-internals From: ajaygargn...@gmail.com To: mysql@lists.mysql.com Hi all. We are facing a very strange scenario. We have two mysql-instances running on the same machine, and they had been running functionally fine since about 6 years or so (catering to millions of records per day). However, since last few days, we were experiencing some elongated slowness on both the instances. So, we decided to OPTIMIZE TABLE slow_table on both the instances. We first ran the command on one instance. That speeded up things massively (select count(*) that was earlier taking 45 minutes was now running in less than 3 minutes). We then ran the command on the second instance. However, that seemed to have no effect. We ran the command again (on the same instance); again it had no effect. What could be the reason of this strange behavior? Both the instances run under fairly the same loadMGHow do you extract the metrics to determine what the second instance is handling the same load as first instance? MGvmstat?MGiostat? MGSHOW GLOBAL STATUS ? and both instances are mounted on the same partition (obviously, all the directories are different). Hoping for some light on this strange issue. Thanks and Regards, Ajay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
