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 'XXXXXX' \G;" on both the
instances, and following are the outputs (here "XXXXXX" 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 XXXXXX::
db1>show table status like 'XXXXXX' \G;
*************************** 1. row ***************************
Name: XXXXXX
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 XXXXXX ::
db2>show table status like 'XXXXXX' \G;
*************************** 1. row ***************************
Name: XXXXXX
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
> MG>How do you extract the metrics to determine what the second instance is
> handling the same load as first instance?
> MG>vmstat?
> MG>iostat?
> MG>SHOW 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
