Monday, July 31, 2006

For any DBA, broken replication represents a dreaded moment. Replication can break for many factors including when the master server issues a 'Duplicate entry' error. At that point you can either skip the slave counter (recommended) or ignore/skip the errors (slave-skip-errors=1062) if you are sure that ignoring such errors won't cause a nasty effect somewhere.

Depending on when you troubleshoot the situation, when replication resumes, slaves may fall significantly behind and the relay log files may start consuming huge amount of disk space. If you have ample free disk space then that is not an issue however, if you are tight on disk space then the relay-log-space-limit option can really come handy.

The relay-log-space-limit option limits the disk space used by the relay log files. You can specify the value in bytes, megabytes (10000M) or gigabytes (10G) in either my.cnf file or as a start up option.

To specify it in the my.cnf file, backup your current my.cnf file (always recommended), stop slave, stop the MySQL server and place the following option:

# relay log restrictionsrelay-log-space-limit=15G

Then save and quit the file and start MySQL. Unless you configured differently, MySQL will automatically start the slave thread.

Now assuming the relay log files were using more than 15G space, issuing a

I have a table of type MyISAM that is reporting 47 million rows when I do a SELECT COUNT(*). When I convert this table to InnoDB, running a SELECT COUNT(*) returns only 19 million rows. The conversion confirms 19 million rows were inserted and reports no warnings or duplicates.

I have done the conversion to InnoDB using the following ways1. by dumping all the data in a text file and loading it.2. by using ALTER TABLE

Why is the record count so low after conversion to InnoDB? Who should I believe: InnoDB or MyISAM? Any ideas as to what can be done to avoid loss of this many rows?

I will be posting output from my latest conversion attempt in some time.

----------------------------PS: Thank you for all the replies. It turned out that the index was corrupt and after running OPTIMIZE table and then converting to InnoDB everything went fine, i.e. all the records were converted to InnoDB.

Today I noticed something interesting. As I was migrating a table from MyISAM to InnoDB, I saw the number of rows fluctuate up and down as time passed suggesting that SHOW PROCESSLIST wasn't actually reporting the exact number of rows but rather an estimate.

One needs to be very careful before deleting files as once deleted the files are gone for good. A friend of mine today called as MySQL was failing to start on his server. Earlier when "cleaning" his server, he had deleted binary log files without fully understanding their role. When I logged in to his server, I noticed the following errors:

Friday, July 14, 2006

One thing that sucks really bad with MyISAM is table level locking which can cause some serious issues at times when INSERT/UPDATE/DELETE load is very high. If you are experiencing problems in your application associated with locking, try converting your tables to InnoDB.

I was asked today a question as to how one can convert a MyISAM table to InnoDB. I will blog about it in detail later but for right now here's one way:

-ALTER TABLE table_name ENGINE = InnoDB;

and here's another way:

- dump the table to a SQL file- change table type from MyISAM to InnoDB in the dumped file- disable keys (to speed up the process)- SET AUTOCOMMIT to 0 (for speeding up the process)- load the table- SET AUTOCOMMIT to 1- enable keys (the index is rebuilt at this point)

One is preferred over the other based on various factors such as time limit in which the conversion must be done.

Thursday, July 13, 2006

The other day I was talking to Jay about low index selectivity. One of the column in my users table had extremely low index selectivity (less than 0.0000005). However when running certain queries involving COUNT and utilizing that column, it seemed the index was being used as when I ran the same queries with IGNORE INDEX, the query execution time was significantly higher.

There have been posts earlier on PlanetMySQL that noted that in cases of extremely low index selectivity, MySQL's cost based optimizer will never use it.

Jay pointed out that using COUNT(column_name) in a query is a special case where even in case of extremely low selectivity, having an index speeds up the query. For all other queries not involving COUNT, the index will not be used if the selectivity was very low.

Wednesday, July 05, 2006

I have been trying to compile MySQL Administrator on a Solaris 10 box with no success. I followed Ronald's notes (link later) on the compile issues related to mysql-gui-common but continued having problems.

So eventually I filed a bug report and yesterday it was verified. So hopefully it can be resolved soon.