Re: checking progress of alter table on an InnoDB table (Was: Re: checking progress of alter table on a MyISAM table)

[Larry Martell]

On Thu, Sep 27, 2012 at 3:02 PM, Akshay Suryavanshi
akshay.suryavansh...@gmail.com wrote:
 Hi,

 22 indexes are simply too many, assuming they are not composite, which means
 you already have a very large table. Secondly the most important bottleneck
 is shown by the inserts/sec, only 405 inserts is very very slow. This could
 take ages to complete. And the 405 inserts/sec are averages calculated over
 some small period of time mostly under a minute, not from the uptime, you
 can see that at the top (initial lines) of the Show engine innodb status \G
 output.

 Depending upon the machine footprint, inserts/sec should atleast be more
 than 1 inserts/sec even on a busy server.

 Indexes are slowing this down. your calculation of 79 hours should be
 correct, only if there are no unique indexes, otherwise this will slow down
 more as the data increases.

It finally finished after 55 hours.


 On Fri, Sep 28, 2012 at 2:22 AM, Larry Martell larry.mart...@gmail.com
 wrote:

 On Thu, Sep 27, 2012 at 2:37 PM, Akshay Suryavanshi
 akshay.suryavansh...@gmail.com wrote:
  Hi,
 
  The alter taking such a long time, could be due to composite indexes on
  the
  table.

 There are 22 indexes on the table, but none are composites.

  we understand the table is big but not so big to take such a long
  time. Also we can get a hold of the process looking at the disk space
  consumed. Usually a tmp table created in data directory would also give
  a
  good understanding of the process, remember sizes need not be exact
  since
  there might be some defragmentation at file level.

 Ok, I will look for a temp file.

  Next you can check inserts/sec in Show engine innodb status \G and
  calculate
  the time it should take for the number of rows in the table.

 The ROW OPERATIONS section has this:

 1 queries inside InnoDB, 0 queries in queue
 1 read views open inside InnoDB
 Main thread process no. 7913, id 14020684432, state: sleeping
 Number of rows inserted 75910241, updated 15602, deleted 70, read
 9130481311
 405.80 inserts/s, 0.00 updates/s, 0.00 deletes/s, 405.80 reads/s

 This appears to be statistics over the life of the invocation of the
 server, correct? But using 405.80 inserts/s give that the alter will
 take almost 79 hours.

 
  Usually, you carry this operation by adding the secondary indexes after
  the
  data import or such alters are complete.
 
  Regards,
  Akshay Suryavanshi
 
 
  On Fri, Sep 28, 2012 at 1:56 AM, Rick James rja...@yahoo-inc.com
  wrote:
 
  Isn't ALTER a DDL, not DML?  So I don't think you would find anything
  in
  undo logs.
 
   -Original Message-
   From: Larry Martell [mailto:larry.mart...@gmail.com]
   Sent: Thursday, September 27, 2012 1:20 PM
   To: Rick James
   Cc: mysql mailing list
   Subject: checking progress of alter table on an InnoDB table (Was:
   Re:
   checking progress of alter table on a MyISAM table)
  
   So we changed the table from MyISAM to InnoDB. I read that the 'undo
   log entries' shown in 'show engine innodb status' would correspond to
   the number of rows that have been operated on throughout the process
   of
   the ALTER. The table we're altering has 115,096,205 rows, and the
   alter's been running for 28 hours, and the undo log entries is 9309.
   Also that number seems to go up and down. So clearly, it's not what I
   think.
  
   So anyone know a way to monitor the status of the alter now that it's
   an InnoDB table?
  
  
   On Wed, Sep 26, 2012 at 10:31 AM, Rick James rja...@yahoo-inc.com
   wrote:
Not really.
You could look at the .TYD and .TYI file sizes and compare to the
   .MYD and .MYI, but that can be deceptive.  If the table is really
   big,
   and has lots of indexes, the generation of the indexes might go
   slower
   and slower -- hence any math on the sizes would be optimistic.
   
-Original Message-
From: Larry Martell [mailto:larry.mart...@gmail.com]
Sent: Wednesday, September 26, 2012 8:52 AM
To: mysql mailing list
Subject: checking progress of alter table on a MyISAM table
   
Is there any way to check on the progress of a long running alter
table on a MyISAM table? I know it can be done with an InnoDB
table,
but I haven't found a way to do it on with a MyISAM table.
  
   --
   MySQL General Mailing List
   For list archives: http://lists.mysql.com/mysql
   To unsubscribe:http://lists.mysql.com/mysql
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql
 
 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: Need Help Converting Character Sets

[Rick James]

Thanks for that link!  That's another subtle issue I had not noted.

There are so many combinations, that it is hard to say do this:
* Incoming bytes are latin1 / utf8 / Microsquish control characters.
* You do/don't have SET NAMES (or equivalent)
* The database/table/column is declared latin1/utf8/other.
* The problem is on ingestion / on retrieval.

The thing mentioned involved 2 steps:
ALTER TABLE ... MODIFY COLUMN  BINARY (or BLOB);  -- to forget any charset 
knowledge
ALTER TABLE ... MODIFY COLUMN  CHARACTER SET ...;  -- coming from BINARY, this 
does not check the encoding.
(sorry, don't have the link handy)

 -Original Message-
 From: h...@tbbs.net [mailto:h...@tbbs.net]
 Sent: Thursday, September 27, 2012 2:24 PM
 To: Mark Phillips
 Cc: Mysql List
 Subject: Re: Need Help Converting Character Sets
 
  2012/09/24 16:28 -0700, Mark Phillips 
 I have a table, Articles, of news articles (in English) with three text
 columns for the intro, body, and caption. The data came from a web
 page, and the content was cut and pasted from other sources. I am
 finding that there are some non utf-8 characters in these three text
 columns. I would like to (1) convert these text fields to be strict
 utf-8 and then (2) fix the input page to keep all new submissions utf-
 8.
 
 91) For the first step, fixing the current database, I tried:
 
 update Articles set body = CONVERT(body USING ASCII);
 
 However, when I checked one of the articles I found an apostrophe had
 been converted into a question mark. (FWIW, the apostrophe was one of
 those offending non utf-8 characters):
 
 Before conversion: I stepped into the observatory?s control room ...
 
 After conversion: I stepped into the observatory?s control room...
 
 Is there a better way to accomplish my first goal, without reading each
 article and manually making the changes?
 
 I do not remember where on the MySQL website this is, but there was an
 article about converting from character sets in version 4 to those in
 version 5, when UTF-8 first was supported. It sounds to me that maybe
 the tricks shown there would be useful to you, since, in effect,
 through MySQL MySQL was fooled into accepting for UTF-8 that which was
 not. Conversion to binary string was mentioned.
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql