Rebuilding an 4.0 data into 4.1 data

In 4.0 the default character set is latin1. When converting to 4.1 this relationship is preserved. In many cases as in the case at flickr.com we store utf8 data in latin1 columns. So, think of the situation as latin1 being the container for utf8. This screws up a lot of thing especially when Java is used. We have to hack the driver to tell it even though the server is returning the data is latin1 it's really UTF8.


So, what is a good way to convert to true utf8 character set with a unicode collation.

Rebuild the data! This is the way that I'm doing it to also shrink the innodb datafile. There is a lot of free space.

Now remember we have utf8 data in a latin1 container column, so we need to make sure that we get a dump of the raw data. MySQL will double pad the data with default options converting the latin1 charset to utf8, which is garbage.

mysqldump -hHOST -uroot -p DB TABLE --no-create-info --compatible=mysql323

This statement will dump the raw data, the option that tells mysqldump to do so is mysql323 compatible.


My personal preference is to

mysqldump -hHOST -uroot -p DB TABLE -T /tmp/

What this does is SELECT * INTO OUTFILE '/tmp/TABLE.txt'

This to will get raw data.

Since all of my tables are INNODB, dumping it out sorted by the PRIMARY index is good for re-import via LOAD DATA.

Next we dump the table structure and remove all occurances of latin1 on each of the column definitions.

Finally reimport the data

Start the slave.


So here is a good procedure.

1. Stop the Slave


2. Stop MySQL


3. Put in your my.cnf skip-slave-start


4. Start mySQL (we do the above steps to make sure everything is flushed to disk just to be sure)


5. Next check that the dump point has enough space


6. Dump the data via the method described above for each table


7. Dump the table definition


8. Stop mySQL


9. Destroy the INNODB datafile and log files rm /var/lib/mysql/ib*


10. Remove Database directory


11. Edit my.cnf and define default startup for the server
    

    
    
[mysqld]
character-set-server=utf8
collation-server=utf8_unicode_ci
character-set-server=utf8
collation-server=utf8_unicode_ci
#
# on every connection we tell the server that transfer data as utf8 so it doesn't translate data comming in for us. SET NAMES is equivlent to
# SET character_set_client = utf8; SET character_set_connection = utf8;SET character_set_results = utf8
#
init-connect="SET NAMES = utf8"

    

    
    
    


12. Start mySQL


13. CREATE DATABASE DATABASENAME DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci


14. Remove all references to latin1 from the column definition of each table


15. At the end of each table definition add ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


16. Add the tables to the database


17. IMPORT THE DATA

Let me know if you have any questions.

Putting a Unique index on columns that are NOT NULL


CREATE TABLE `test_unique` (
`id` int(10) unsigned NOT NULL auto_increment,
`col` varchar(5) default NULL,
`col2` varchar(50) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `col` (`col`)
);


SELECT * FROM test_unique;
+----+------+------+
| id | col | col2 |
+----+------+------+
| 1 | | blah |
| 2 | | blah |
| 3 | | blah |
| 4 | blah | NULL |
+----+------+------+

UPDATE test_unique SET col=\N WHERE col='';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0

SELECT * FROM test_unique;
+----+------+------+
| id | col | col2 |
+----+------+------+
| 1 | NULL | blah |
| 2 | NULL | blah |
| 3 | NULL | blah |
| 4 | blah | NULL |
+----+------+------+
4 rows in set (0.00 sec)

ALTER TABLE test_unique modify col varchar(5) DEFAULT NULL, ADD UNIQUE INDEX (col);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0


This is how it's done.

SHOW INNODB STATUS: ROW OPERATIONS


--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread process no. 25100, id 114696, state: sleeping
Number of rows inserted 200059954, updated 1278003724, deleted 130588363, read 47473646817
37.99 inserts/s, 1718.43 updates/s, 0.33 deletes/s, 5470.51 reads/s


This section shows innodb thread queue status and the state of the main thread.

SHOW INNODB STATUS: BUFFER POOL AND MEMORY


----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 13505821346; in additional pool allocated 17936896
Buffer pool size 768000
Free buffers 5
Database pages 728656
Modified db pages 108067
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 31203802, created 3930786, written 131784156
4.50 reads/s, 0.00 creates/s, 131.22 writes/s
Buffer pool hit rate 1000 / 1000


Pending reads and writes are pending requests on buffer pool.
Different types of IO are used by Innodb, think of it like a IO scheduler for INNODB itself.

LRU pages - dirty pages which were not accessed long time
flush list - old pages which need to be flushed by checkpointing process
single page - independent page writes.

SHOW INNODB STATUS: LOG


---
LOG
---
Log sequence number 1039 3161691821
Log flushed up to 1039 3161691821
Last checkpoint at 1039 2401802667
0 pending log writes, 0 pending chkp writes
222872631 log i/o's done, 56.99 log i/o's/second


• Log sequence number is the number of bytes written to the buffer pool.


• Log flushed up to is the number of bytes that has been flushed or unflush (sequence number - log flushed up to)
  
• Last checkpoint is a fuzzy line of what was flushed to logs ie recovery line for a crash (from my understanding).

Then there is some cool stats on log writes which should remain low.

Tuning

If you see more than 30% of log buffer size being unflushed (Log flushed up to) increase it if possible.

SHOW INNODB STATUS: ADAPTIVE HASH INDEX


-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 16812, free list len 20200, seg size 37013, is not empty
Ibuf for space 0: size 16812, free list len 20200, seg size 37013,
89630355 inserts, 89285599 merged recs, 5642191 merges
Hash table size 24902177, used cells 16368903, node heap has 39339 buffer(s)
5523.49 hash searches/s, 1663.78 non-hash searches/s


1st 2 lines show segement size, and if their are any records in the innodb_buffer_pool
Next
How many inserts where done to the insert buffer,
Then
How many records where merged
Finally
How many merges it took to merge the records. (BTREE internals)

Adaptive hash index is hash index Innodb builds for some pages to speed up row lookup replacing btree search with hash search.

These are stats explaining how many hash searches / size / and non hash searches done a second. (Hash lookups are fast)

SHOW INNODB STATUS: FILE IO


--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 1
29333024 OS file reads, 299924198 OS file writes, 226649540 OS fsyncs
5.33 reads/s, 17408 avg bytes/read, 195.60 writes/s, 77.31 fsyncs/s


Innodb support greater then 4 helper threads (the 4 above) in windows and with a small change the the source it can be increased for unix.

Each Helper thread shows the pending status of how many jobs are queued for executing, with just 4 helper threads on one of my most heavy hit dbs, jobs are not queued, IO is cool in linux, that's why INNODB nativly doesn't allow for more IO threads.


Also Pending flushes is shown since innodb doesn't rely on the os to say the data is commited but the disk itself. When a crash happens data in the OS cache can be lost corrupting your database, thus the need for a flush!

OS file * section is great for monitoring, for innodb use of IO. (I think this is better then I/O Stat)

SHOW INNODB STATUS: TRANSACTIONS


------------
TRANSACTIONS
------------
Trx id counter 4 4177811381
Purge done for trx's n:o < 4 4177810974 undo n:o < 0 0
History list length 68
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
MySQL thread id 6, query id 12052143 Has read all relay log; waiting for the slave I/O thread to update it
---TRANSACTION 4 4177811379, ACTIVE 0 sec, process no 1537, OS thread id 418595012843 committing


• Purge done for trx's n:o - number of transactions to which a purge is done. Means gets rid of old transactions that are not needed anymore. *Look Tuning secion


• History list length 68 - number of transaction in the undo space


• Total number of lock structs in row lock hash table - not locked rows but structures which one row may have many of

Tuning

If you see a lot of "waiting in InnoDB queue" then tune innodb_thread_conccurency raise it higher - for my typical workloads, which a fast single statement transactions I allow the innodb kernel to have 32 threads at any given time

If you see a lot of "sleeping before joining InnoDB queue" then tune
innodb_thread_sleep_delay variable. Value is specified in microseconds. Innodb sleeps a thread before it starts to wait for entry into the queue. This is to avoid many threads going into the thread queue to make the most of innodb_thread_conccurency

SHOW INNODB STATUS: DEAD LOCKS

In the deadlock section, innodb shows:


what the last deadlock was

what the deadlocks state was

what the deadlock lock was holding

and what they were waiting for


060718 8:00:18
*** (1) TRANSACTION:
TRANSACTION 4 4095301271, ACTIVE 0 sec, process no 19925, OS thread id 387040248038 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 368
MySQL thread id 2287022118, query id 5904669207 [HOST] [user] Searching rows for update
/* /PHPFILE: PHP FUNCTION() */ UPDATE Latest SET `date_create`='?', `id`='?' WHERE user_id=?
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 6669281 n bits 608 index `user_id` of table `DB/Latest` trx id 4 4095301271 lock_mode X waiting
Record lock, heap no 38 PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits 32
0: len 8; hex 000000000036a53b; asc 6 ;;; 1: len 4; hex 44bc9512; asc D ;; 2: len 8; hex 000000000b786a9f; asc xj ;;


SHOW INNODB STATUS: SEMAPHORES

A summary from the mySQL Performace Blog

Innodb takes averages over time and displays its numbers which are hard to figure out what a true average is from some sort of polling method.


----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 28655958, signal count 25483315
Mutex spin waits 1468249294, rounds 5838311115, OS waits 20658052
RW-shared spins 4676021, OS waits 1740480; RW-excl spins 13056306, OS waits 2135090


• reservation count - counter for the sync array when slots are added to it


• signal count - counter for the sync array for when threads are signal for use


• OS Waits - used for exclusive and shared locks, and relates to context switching on the OS side.


• spin waits / rounds - internal innodb lock that is faster then an OS wait but wastes CPU cycles
  

Tuning

To help with context switching a new variable was added in the 5.0 tree called innodb_sync_spin_loops playing with this might reduce unneed context switches since it's used to ballance between CPU and spins.