Lets talk Technical!

innodb_file_per_table

2010-02-10T14:59:00.000-08:00

Recently I attempted to use this parameter in one of our InnoDB table and had an experience to make a note of.

So this is what actually happened.

As we know InnoDB writes all the table information into one tablespace file ibdata1.
Obviously this lead to a disk space issue, since the ibdata1 file grew to 90G+ and it was not possible to defragment tablespace using the Alter method (Infact is is hardly possible to do in a production environment, since it leads to a huge downtime.)

So I decided to go for innodb_file_per_table on our other production database server which was being configured from scratch.

Now this database had around 15 tables and it had very high inserts/deletes/updates. For few days, things went ok. But in few days, I started getting complains from development team regarding performance on the tables and queue being backed up. Apparently whenever a delete / update was made on a table, all the other tables slow down and queries started taking more than 4 to 5 secs to complete.

Dug around, ran some more optimisations , flush tables but not much difference. I was able to get 660 to 661 qps tops.

On digging further and discussing with developers, we figured out that innodb_file_per_table is the culprit.

InnoDB uses fsync() instead of fdatasync() to flush both data and log files on non-Windows system. fdatasync() is like fsync(), except it flushes only files data, not its metadata (last modified time etc.). Therefor fsync() can cause more IO.

innodb_file_per_table causes each file to be fsync()ed separately which means writes to multiple tables can't be combined into a single IO operation. This requires InnoDB to perform higher total number of fsync() operations, leading to a IO contention in my case.

Finally we had to migrate the database to another machine without innodb_file_per_table since it is also not possible to undo the existing tables for which files are created per table.

As quoted in MySQL documentation :

The --innodb_file_per_table option affects only table creation, not access to existing tables. If you start the server with this option, new tables are created using .ibd files, but you can still access tables that exist in the shared tablespace. If you start the server without this option, new tables are created in the shared tablespace, but you can still access any tables that were created using multiple tablespaces.

Once we migrated the performance started going up , almost upto 1500 qps.

So moral of the story : Better test the parameter innodb_file_per_table in a test environment, with the load higher than expected on production. I have read many blogs and many people are benefited by the use of this parametere, but as a general practice I would not favor its use. Sure it can be useful to reclaim some disk space, but I dont think it would be worth at the cost of performance.

Purging binary logs.

2010-01-12T13:38:00.000-08:00

Being a MySQL DBA , one faces a common issue in replication environment -> Disk space issue on master, since the number of binary logs have increased.

Now, one of the solution to this would be using expire_logs_days parameter in your mysql config file.

But what if, the slave is lagging by few hours or if the slave is broken since few days and the binary logs are removed due to the parameter set. Whenever the salve comes up, it will go bonkers, knowing that the binary log where it last stopped no more exists.

I faced this issue a couple of times until I decided to automate it using a script. Herewith I am attaching the URL to my python script which can run regularly in cron.

Features :

Checks the slaves connected to the master (I have limit it to 3 for now.)
Checks the last binary log file which is being used by the slave.
All the binary logs until the last bin log used by slave are purged.
If a slave is not connected, purging is aborted, so that important bin logs are not purged.

You can download this script from here .

Tips and Warnings to use this script

Test a couple of times, different test cases, before using this script on critical or production databases.
It is advisable to take backup of data before running this script.
You can send the output of this script to syslog or to different mail addresses.
You can embed into you alerting system, so that whenever there is a disk space warning on the machine, this scrip is fired.

MySQL Cluster : Errors faced while importing data

2009-10-21T13:08:00.000-07:00

Here is the list of errors I have faced while importing a 25G .sql file into my cluster setup.

This is a test setup and to get maximum RAM availability, I have 3 data nodes and 1 Management node with replication factor of 1. The solutions provided are the ones which helped me.

1. ERROR 1297 (HY000) at line 1872: Got temporary error 410 'REDO log files overloaded, consult online manual (decrease TimeBetweenLocalCheckpoints, and|or incre' from NDBCLUSTER

Solution : Modify / Add parameter TimeBetweenLocalCheckpoints in your config.ini.
Default value = 20
Changed it to TimeBetweenLocalCheckpoints =6

Setting TimeBetweenLocalCheckpoints to 6 or less means that local checkpoints will be executed continuously without pause, independent of the cluster's workload.

2. ERROR 1297 (HY000) at line 1443: Got temporary error 1220 'REDO log files overloaded, consult online manual (increase FragmentLogFileSize)' from NDBCLUSTER

Solution: Modify / Add parameter FragmentLogFileSize in your config.ini.

Default value = 16M

Changed it to FragmentLogFileSize=256M

Setting this parameter allows you to control directly the size of the redo log files. This can be useful in situations when MySQL Cluster is operating under a high load and it is unable to close fragment log files quickly enough before attempting to open new ones. Increasing the size of the fragment log files gives the cluster more time before having to open each new fragment log file.

3. ERROR 1297 (HY000) at line 1826: Got temporary error 1221 'REDO buffers overloaded, consult online manual (increase RedoBuffer)' from NDBCLUSTER

Solution: Modify / Add parameter RedoBuffer in your config.ini.

Default value = 8M

Changed5 it to RedoBuffer=128M

All update activities also need to be logged. The REDO log makes it possible to replay these updates whenever the system is restarted. RedoBuffer sets the size of the buffer in which REDO log is written.

4. ERROR 1297 (HY000) at line 7064: Got temporary error 233 'Out of operation records in transaction coordinator (increase MaxNoOfConcurrentOperations)' from NDBCLUSTER

Solution: Modify / Add parameter MaxNoOfConcurrentOperations in your config.ini

Default value=32K

Changed it to MaxNoOfConcurrentOperations=1M

It is a good idea to adjust the value of this parameter according to the size and number of transactions.

5. ERROR 1114 (HY000) at line 8820: The table 'table_1' is full

Solution: Modify the parameter DataMemory in your config.ini

Default value: 80M

Changed it to DataMemory=14G

This parameter defines the amount of space (in bytes) available for storing database records. The entire amount specified by this value is allocated in memory., so it is extremely important that the machine has sufficient physical memory to accommodate it.

The memory allocated by DataMemory is used to store both the actual records and indexes. There is a 16-byte overhead on each record; an additional amount for each record is incurred because it is stored in a 32KB page with 128 byte page overhead. There is also a small amount of wastage per page due to the fact that each record is stored in only one page.

6. ERROR 1205 (HY000) at line 10208: Lock wait timeout exceeded; try restarting transaction

Solution: Add / modify the parameter TransactionDeadlockDetectionTimeout in config.ini

Default value = 1200 milliseconds

Changed the value to TransactionDeadlockDetectionTimeout=15000

The timeout parameter states how long the transaction coordinator waits for the query execution by another node before aborting the transaction and is important for both node failure handling and deadlock detection.

7. ERROR 1118 (42000) at line 661: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8052. You have to change some columns to TEXT or BLOBs

Solution: This error is generally due to the know limitation in MySQL Cluster 5.1.

"The maximum permitted size of any one row is 8KB." .

The work around for this is modify the table schema so that large varchar values are changed to text

8. ERROR 1005 (HY000): Can't create table 'DB1.Table_5' (errno: 136)

Solution: Errno: 136 refers to index file being full. Parameter that can be modified here is MaxNoOfOrderedIndexes.

Default Value=128. Change it to something higher based on the size mentioned by running ndb_size.pl. In my case it is 263.

References:

Mysql Cluster Document

Replacing a RAID 5 disk with an old already RAID(any level) configured disk.

2009-04-02T17:05:00.000-07:00

In this use case.
We have a RAID 5 configuration and c1 p5 has been Degraded

//umang> maint remove c1 p5

Exporting port /c1/p5.... Done.

After replacing the disk with a new disk (It was an already used disk with RAID 1 configured on it) following was found

//umang> info c1

Unit     UnitType  Status         %Cmpl  Stripe  Size(GB)  Cache  AVerify  IgnECC
------------------------------------------------------------------------------
u0    RAID-5    OK             -      64K     1117.56   ON     OFF      OFF
u1    RAID-5    DEGRADED       -      64K     1117.56   OFF    OFF      OFF
u2    RAID-1    DEGRADED       -      -       372.519   OFF    OFF      OFF

Port   Status           Unit   Size        Blocks        Serial
---------------------------------------------------------------
p0     OK               u0     372.61 GB   781422768     WD-WMAMY1329898
p1     OK               u0     372.61 GB   781422768     WD-WMAMY1329877
p2     OK               u0     372.61 GB   781422768     WD-WMAMY1370633
p3     OK               u0     372.61 GB   781422768     WD-WMAMY1329586
p4     OK               u1     372.61 GB   781422768     WD-WMAMY1367924
p5     OK               u2     372.61 GB   781422768     WD-WMAMY1211759
p6     OK               u1     372.61 GB   781422768     WD-WMAMY1465371
p7     OK               u1     372.61 GB   781422768     WD-WMAMY1369106

Name  OnlineState  BBUReady  Status    Volt     Temp     Hours  LastCapTest
---------------------------------------------------------------------------
bbu   On           Yes       OK        OK       OK       0      xx-xxx-xxxx

*** Notice that c1 p5 has a new unit u2 and instead of Unit u0 and u1 , now you can see three units u0, u1, u2 (Raid 1) out of which u1 and u2 are degraded.
This is because the new replaced disk already has RAID 1 configured on it before.

So we need to delete this RAID on c1 p5 (new disk)

//umang> /c1/u2 del
Deleting /c1/u2 will cause the data on the unit permanently loss.
Do you want to continue ? Y|N [N]: y
Deleting unit c1/u2 ...Done.

And now see

//umang> info c1

Unit  UnitType  Status         %Cmpl  Stripe  Size(GB)  Cache  AVerify  IgnECC
------------------------------------------------------------------------------
u0    RAID-5    OK             -      64K     1117.56   ON     OFF      OFF
u1    RAID-5    DEGRADED       -      64K     1117.56   OFF    OFF      OFF

Port   Status           Unit   Size        Blocks        Serial
---------------------------------------------------------------
p0     OK               u0     372.61 GB   781422768     WD-WMAMY1329898
p1     OK               u0     372.61 GB   781422768     WD-WMAMY1329877
p2     OK               u0     372.61 GB   781422768     WD-WMAMY1370633
p3     OK               u0     372.61 GB   781422768     WD-WMAMY1329586
p4     OK               u1     372.61 GB   781422768     WD-WMAMY1367924
p5     OK               -      372.61 GB   781422768     WD-WMAMY1211759
p6     OK               u1     372.61 GB   781422768     WD-WMAMY1465371
p7     OK               u1     372.61 GB   781422768     WD-WMAMY1369106

Name  OnlineState  BBUReady  Status    Volt     Temp     Hours  LastCapTest
---------------------------------------------------------------------------
bbu   On           Yes       OK        OK       OK       0      xx-xxx-xxxx


//umang> /c1 rescan
Rescanning controller /c1 for units and drives ...Done.
Found the following unit(s): [none].
Found the following drive(s): [/c1/p5].

//umang> info c1

Unit  UnitType  Status         %Cmpl  Stripe  Size(GB)  Cache  AVerify  IgnECC
------------------------------------------------------------------------------
u0    RAID-5    OK             -      64K     1117.56   ON     OFF      OFF
u1    RAID-5    DEGRADED       -      64K     1117.56   OFF    OFF      OFF

Port   Status           Unit   Size        Blocks        Serial
---------------------------------------------------------------
p0     OK               u0     372.61 GB   781422768     WD-WMAMY1329898
p1     OK               u0     372.61 GB   781422768     WD-WMAMY1329877
p2     OK               u0     372.61 GB   781422768     WD-WMAMY1370633
p3     OK               u0     372.61 GB   781422768     WD-WMAMY1329586
p4     OK               u1     372.61 GB   781422768     WD-WMAMY1367924
p5     OK               -      372.61 GB   781422768     WD-WMAMY1211759
p6     OK               u1     372.61 GB   781422768     WD-WMAMY1465371
p7     OK               u1     372.61 GB   781422768     WD-WMAMY1369106

Name  OnlineState  BBUReady  Status    Volt     Temp     Hours  LastCapTest
---------------------------------------------------------------------------
bbu   On           Yes       OK        OK       OK       0      xx-xxx-xxxx

After the rescan it will start rebuilding on its own.

//umang> info c1

Unit  UnitType  Status         %Cmpl  Stripe  Size(GB)  Cache  AVerify  IgnECC
------------------------------------------------------------------------------
u0    RAID-5    OK             -      64K     1117.56   ON     OFF      OFF
u1    RAID-5    REBUILDING     1      64K     1117.56   OFF    OFF      OFF

Port   Status           Unit   Size        Blocks        Serial
---------------------------------------------------------------
p0     OK               u0     372.61 GB   781422768     WD-WMAMY1329898
p1     OK               u0     372.61 GB   781422768     WD-WMAMY1329877
p2     OK               u0     372.61 GB   781422768     WD-WMAMY1370633
p3     OK               u0     372.61 GB   781422768     WD-WMAMY1329586
p4     OK               u1     372.61 GB   781422768     WD-WMAMY1367924
p5     DEGRADED         u1     372.61 GB   781422768     WD-WMAMY1211759
p6     OK               u1     372.61 GB   781422768     WD-WMAMY1465371
p7     OK               u1     372.61 GB   781422768     WD-WMAMY1369106

Name  OnlineState  BBUReady  Status    Volt     Temp     Hours  LastCapTest
---------------------------------------------------------------------------
bbu   On           Yes       OK        OK       OK       0      xx-xxx-xxxx