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 '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
>
