RE: MySQL 5.5.33 History list not purging?
Hi Brad, -Original Message- From: Brad Heller [mailto:b...@cloudability.com] Sent: Sunday, 7 September 2014 03:07 To: MySQL General List Subject: MySQL 5.5.33 History list not purging? For some reason, the history list isn't purging on one of my masters. This is causing all kinds of weird issues/behavior with reads. Here's the last 8 or so hours of history list length: http://i.imgur.com/Q4DEeVi.png I would start looking for an old transaction. You can use SHOW ENGINE INNODB STATUS or the information_schema.INNODB_TRX table to look for that. Best regards, Jesper Krogh MySQL Support -- 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
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
table comments
The TABLES table in MySQL's information_schema has a TABLE_COMMENT column. Could one use that for ad hoc and manual annotation of that table? And if so, could one change its length? Or are there better ways of producing table notes that are kept with the database, as opposed to Evernote or some notebook where you never find it again? Martin Mueller Professor emeritus of English and Classics Northwestern University
Re: table comments
Hello Martin, On 9/7/2014 7:42 PM, Martin Mueller wrote: The TABLES table in MySQL's information_schema has a TABLE_COMMENT column. Could one use that for ad hoc and manual annotation of that table? And if so, could one change its length? Or are there better ways of producing table notes that are kept with the database, as opposed to Evernote or some notebook where you never find it again? Martin Mueller Professor emeritus of English and Classics Northwestern University Every table, every database, and every column all have places in their definitions to place a comment. No, the sizes of the columns cannot be changed. Sorry. To see how to set a comment, check out the appropriate ALTER or CREATE command for the object you want to annotate. http://dev.mysql.com/doc/refman/5.6/en/sql-syntax-data-definition.html Yours, -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
