sorted by: recent | see :
popular
Content Tagged with of + Tip
Tip of the Day ? max_connections
This is a little simpler, but iti s good to cover this system variable for the administrators who are just starting out. In your my.cnf (or my.ini on Windows) file you need to specify the maximum number of connections (clients) that are allowed to connect to your MySQL server at one time. You will want to set it at a reasonable level for your system load. Don’t over-inflate as it does use an (admittedly fairly small) amount of memory.
As an example, if you want to allow a maximum of 400 connections to your server the following would be in your [mysqld] section:
max_connections = 400
Tip of the Day ? Repairing Tables ? Part I
Sometimes things go bump in the night and break. There are various ways to fix tables when this happens. From the mysql command line you can do the following:
> check table table_name;
This does a check of the table structure and contents. It works on both MyISAM and InnoDB tables.
> repair table table_name;
This will correct corrupted tables. It only works for MyISAM tables.
> analyze table table_name;
This updates information that the server stores about the tables. This information is used by the server optimizer when determining the best choice for query execution. This will work for both InnoDB and MyISAM.
> optimize table table_name;
The optimize table command will compact and optimize a MyISAM table by creating a new copy of the table on the filesystem. While it is doing this it updates index statistics, and sorts the index pages themselves if necessary. optimize table will work for InnoDB but simply maps to an alter table command which performs the same essential operations.
Tip of the Day ? Disabling Binary Logging Temporarily
Recently I came across something new (for me anyways). There are times when it can be useful to temporarily turn off binary logging. You can disable it in the my.cnf file, but that requires a daemon restart. Coming from the Reference Manual:
“A client that has the SUPER privilege can disable binary logging of its own statements by using a SET SQL_LOG_BIN=0 statement”
As a brief example, if I am loading a large table it could be good to disable logging before beginning the import.
mysql> SET SQL_LOG_BIN=0;
mysql> LOAD DATA INFILE ‘honking_big_file’ INTO BIG_TABLE;
I don’t know about you, but more than once I have started a large import and had /var fill up because of logging. Just remember, if you need the table replicated to a slave server this will defeat replication.
Hope that is useful.
Tip of the Day ? Managing Your Query Cache
When it comes to benchmarking it is useful to turn your query cache off sometimes. This can be done on both client connection level and the global/server level.
Client level:
to turn the cache off
SET SESSION query_cache_type = OFF;
and then to turn it back on
SET SESSION query_cache_type = ON;
Server level:
to turn off the cache
SET GLOBAL query_cache_type = OFF;
and to turn it back on
SET GLOBAL query_cache_type = ON;
Tip of the Day ? Sorting ENUMs
Been a bit behind on tip of the days if you haven’t noticed. Sorry about that . It’s getting crazy trying to get out the next issue of the magazine, training our new jr dba (who suggested this tip..thanks David!!) and trying to get things in line for both a short vacation and then the Users Conference next month.
Excuses over, here is the tip. Today’s tip is dealing with the ENUM (enumeration) data type. ENUM is very similar to the SET data type. It can hold a list of allowable values. The values in the list are called members. An ENUM list can hold up to 65,535 members. Try typing that many in!! As an example of a very simple table:
CREATE TABLE enums (
userid INT NOT NULL AUTO_INCREMENT
value ENUM( ‘FALSE’, ‘TRUE’ )
PRIMARY KEY (userid)
);
Here is what is important to understand. Internally to the MySQL database enums are stored as integers beginning with a value of one and going to N where N is the number of members in the list. In our example above ‘FALSE’ would be represented internally as a ‘1′ and ‘TRUE’ is represented as a ‘2′.
There can be subtle “problems” (errors) when you don’t take into account the string nature of what you are inserting into a ENUM type and its internal integer representation.
If, instead of the above table example, I used the following table:
CREATE TABLE enums (
userid INT NOT NULL AUTO_INCREMENT
value ENUM(’0′,’1′)
PRIMARY KEY (userid)
);
Here I stored the values as ‘0′ for false and ‘1′ for true which is a fairly common programming practice. I would argue that you shouldn’t, but let us assume you did. Internally these strings of ‘0′ and ‘1′ are stored as integers of ‘1′ and ‘2′. Confused yet?
Do you see the problem?
What if I do this:
INSERT INTO enums (value) VALUES (’1′);
I get the expected value returned when I SELECT:
userid = 1
value = 1
ie we just set userid of one to “true”.
Now we insert this way:
INSERT INTO enums (value) VALUES (1);
meaning to insert a value of TRUE into the value. However, that isn’t what happened. The 1 is an integer, so MySQL uses the first value of the list which is a ‘0′ .. ie false
While the example is a bit contrived, it goes to show you that you have to be careful how you insert when using enums.
Tip of the Day ? Table Spaces
This might not be quite what you think. One of the sys admins here at work ran into a small problem when moving some databases from one server to another. When using mysqlimport to bring in the databases on the new server it (mysqlimport) choked on a table that had a space in the name. He couldn’t get it to work until he used load data infile and put quotes around the table name: “table name”. I just checked the man pages for mysqlimport and didn’t see any options that would help.
Now I would recommend that you never use spaces in your table names, but if you do and try to restore a backup keep this in mind!!
Thanks for the tip Justin!!
Tip of the Day ? MySQL Roadmap
This is the best “set” of information in one place about the future plans for MySQL server. It looks like it is from a presentation give by Robin Schumacher. I don’t remember where I first saw this so I apologize for not linking to the source of the news.
Tip of the Day ? NULL != Empty String
Thanks to David, our Jr. DBA, we get the tip of the day. Did you realize that NULL (the topic of another post here) is not equal to the empty string (’ ‘). It is true. If you compare a NULL and an empty string you can see this:
mysql> select ‘ ‘ is NULL;
+————-+
| ‘ ‘ is null |
+————-+
| 0 |
+————-+
1 row in set (0.05 sec)
mysql> select ‘ ‘ is not NULL;
+—————–+
| ‘ ‘ is not NULL |
+—————–+
| 1 |
+—————–+
1 row in set (0.00 sec)
The zero returned means false, the one means true. This has implications to your code. For example, since an empty string is not NULL it can be inserted into a column that is set to NOT NULL.
For a more in depth discussion take a look here: http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html
Tip of the Day ? A Gentle Introduction to MySQL
If you are a newbie here is one for you…
Go to amazon.com and have them send this out: MySQL Tutorial
It is an excellent introduction to MySQL, SQL and even some database administration thrown in for good measure.
The book is a little dated because it was released in December 2003, but is still a great way to get involved with the MySQL world. At around 250 pages it isn’t so big that it overwhelms you and the exercises at the end of each chapter are a very helpful way to insure that you understand the material.
Tip of the Day ? Syncing a New Slave
This tip is a follow-up to yesterday’s post about setting up a new slave server with a new master (one that has no current data on it). Many times you need to add a slave to a master server that already has data on it. This is a little trickier, but it is possible.
There are other ways of doing this. For me, this is typically the fastest way to do it and the one I use most often.
Assumptions I am making: your operating system is Linux and you are using what is called LVM (logical volume manager). The lvm programs are used to manage the partitions and filesystems on your server. You can use it to, among other things, resize partitions and most importantly, in this case, take “snapshots” of your partitions. WARNING: before performing this on production systems you NEED to research LVM and test it out on test servers.
step one - set up your slave server with the operating systems and MySQL. Your master server MUST have LVM on it (and I would recommend you set up LVM on the slave also).
step two - on the master you need to add the replication user:
GRANT REPLICATION SLAVE ON *.* TO ?repl_user?@?ip_of_new_slave_server?
IDENTIFIED BY ?repl_user_password?;
step three - open up two terminal screens on the master server.
step four - Issue a ‘FLUSH TABLES WITH READ LOCK’ on the master server’s mysql client. This will block all database activity on the master server.
step five - As soon as the ‘FLUSH TABLES’ command returns issue a ‘SHOW MASTER STATUS’ command. You need to save the information returned as you will need it in step eleven.
step six - In the second terminal screen you need to create the lvm snapshot. This is done by running the following command:
lvcreate -s -n snap-data -L 10G /dev/vg0/data
The exact format will vary somewhat depending on your setup. In this case it creates a snapshot of the data partition that is 10 gigabytes in size. This means that up to 10 gigabytes of changes can be recorded before the snapshot fills up.
step seven - Now back in the original terminal screen (the one that you issued the ‘FLUSH TABLES’ and ‘SHOW MASTER STATUS’ commands) you need to execute an ‘UNLOCK TABLES’ command. This will return your database to normal activity. You need to execute steps four through seven as quickly as possible.
step eight - Now you need to mount the snapshot partition. Do something like this (as root):
mount /dev/vg0/snap-data /mnt
step nine - Now you rsync the files to the slave server:
rsync -Wav --delete --progress /mnt/mysql slave:/data/mysql
step ten - Start the mysqld daemon on the slave server. It might spit and sputter a bit as it is starting up, but only once have I had the daemon not start up (and that appeared to be a completely unrelated issue). In the last year I have probably done this procedure fifty times so it is well tested!
step eleven - Now it is time to tell the slave server about the master. You will also need the IP address of the master server. On Linux run the ifconfig command to determine the IP address. To put in the information on the slave server:
RESET SLAVE;
CHANGE MASTER TO master_log_file=?master_log?,
master_log_pos=master_log_position, master_user=?repl_user?,
master_password=?repl_user_password?, master_host=?ip_of_master?;
SHOW SLAVE STATUS;
step twelve - Cleanup!! Now you need to remove the snapshot partition you made on the master:
umount /mnt
lvremove /dev/vg0/snap-data
That’s it. A little more complicated. But worth it!!
Tip of the Day ? Slave Setup
I set up a new server pair today. You know people complain sometimes that replication isn’t durable enough (and when it breaks at 3:00 AM I don’t think it is durable enough 
), but no one complains that it is too complex to set up.
step one - set up your servers with their operating systems and MySQL
step two - on the master you need to add the replication user:
GRANT REPLICATION SLAVE ON *.* TO ‘repl_user’@‘ip_of_new_slave_server’
IDENTIFIED BY ‘repl_user_password’;
step three - you need to get your starting information about the master (we assume that the master isn’t in production yet)
SHOW MASTER STATUS;
This will display the binary log file currently being written to and the log position in the file.
step four - starting the slave. Plug in the information you determined above. You will also need the IP address of the master server. On Linux run the ifconfig command, on Winodows it would be ipconfig.
RESET SLAVE;
CHANGE MASTER TO master_log_file=‘master_log’,
master_log_pos=master_log_position, master_user=‘repl_user’,
master_password=‘repl_user_password’, master_host=‘ip_of_master’;
START SLAVE;
SHOW SLAVE STATUS;
That’s it. You should be up and running.
So set up a slave server just for fun!!
Tip of the Day ? ACID Compliance
When working with databases that allow for transactions you will often see the term ACID compliant. ACID is an acronym for:
Atomic - all (SQL) code is successfully executed or is canceled as a unit
Consistent -a database in a consistent state when a transaction begins is left in a consistent state by the transaction
Isolated - one transaction does not affect another
Durable - all changes that finish successfully are recorded properly in the database. Changes are not lost.
Transactions are groups of one or more SQL statements that have a beginning (denoted by BEGIN or START TRANSACTION) and an ending (denoted by COMMIT). As Roland points out in the comments transactional support can also be achieved by using autocommit mode. This is important when dealing with things such as financial information. MySQL provides transaction support on multiple storage engines and ACID compliance along with it.
A simple transactional example might be beneficial.
Suppose two people wanted to move $4,000 from one account to another for the purchase of a car. Also assume that the bank database had a simple account table structure like the following:
create table accounts ( account_id int not null auto_increment primary key, balance float ) type = InnoDB;
Now to move the money from the account of customer one to the account of customer two we might do something like this in my very bad pseudo-code:
select balance from accounts where account_id = id_of_customer_one;
make sure that balance is greater than 4,000;
update accounts set balance = balance - 4000 where account_id = account_id_of_customer_one;
update accounts set balance = balance + 4000 where account_id = account_id_of_customer_two;
The problem with this is that if this is an interruption between the two update statements there is a problem. Suppose the server crashes or the power goes off. Now there is a problem. One account has 4,000 less dollars, but the other account didn’t receive it. The solution is a transaction. Adding a transaction makes our code look like this:
select balance from accounts where account_id = id_of_customer_one;
make sure that balance is greater than 4,000;
begin;
update accounts set balance = balance - 4000 where account_id = account_id_of_customer_one;
update accounts set balance = balance + 4000 where account_id = account_id_of_customer_two;
commit;
Now with the addition of the transaction statements the two updates will either commit or fail (and do what is called rollback..going back to the original state). If the power fails or the server crashes in between the updates the rollback will be performed when the server comes back up and everything will be as it was when the transaction began.
It is important that the created table is of the type that supports transactions. Innodb (like this example table) does support transactions. MyISAM does not support transactions and would just ignore the BEGIN and COMMIT statements.
Tip of the Day ? Checking Your Tables ? Part I
Most production environments have a master/slave setup of servers. This provides the ability to easily perform backups, have warm fail-over if you have a hardware failure on the master, etc. Replication between the master and slave is fairly easy to set up, however as Baron points out here, it is possible (and probably quite common) for for a master and slave to “drift” out of sync.
Fortunately, not only did he point out the problem, he provides a fix for the problem. Included in the maatkit toolkit is the mk-table-checksum tool (and the related tool mk-table-sync which we won’t be discussing this time). It can tell you which tables on the master and and slave are out of sync and even tell you within a fair degree what rows are out of sync. Here is an example run:
mk-table-checksum –chunksize 500000 –replicate=test.checksum localhost
What are we doing here? Well, the chunksize option specifies that if tables are larger than 500,000 rows (in this case) that they should be chunked — broken apart — when processing. This will mean that a table doesn’t get blocked for too long a period of time. The replicate option specifies that you are wanting the checksum tool to use the test.checksum table to store results in. This can be very helpful in providing an accurate picture of your results. The create command for this table should be:
CREATE TABLE `checksum` (
`db` char(64) NOT NULL,
`tbl` char(64) NOT NULL,
`chunk` int(11) NOT NULL,
`boundaries` char(64) NOT NULL,
`this_crc` char(40) NOT NULL,
`this_cnt` int(11) NOT NULL,
`master_crc` char(40) default NULL,
`master_cnt` int(11) default NULL,
`ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`db`,`tbl`,`chunk`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
It doesn’t have to be in the test database by the way. You can put it wherever you want. Your option. Just be sure to specify the right database when running the mk-table-sync command.
The final option is really easy. It just specifies the location of the master server. In this case, I ran the command on the master server itself so localhost was specified.
It will take a while for this command to run your databases are of any size. Once it is done executing and your slave has caught up (if it lagged behind the master) than you just run the following:
mk-table-checksum –replicate=test.checksum –replcheck 2 localhost
This will give you the results you want. Some sample output:
mk-table-checksum --replicate=test.checksum –replcheck 2 localhost
Differences on h=server.slave
DB TBL CHUNK CNT_DIFF CRC_DIFF BOUNDARIES database_1 table_1 0 0 1 `clientid` < 184723 database_1 table_2 0 0 1 `clientid` < 184721 mysql db 0 -19 1 1=1 mysql user 0 -1 1 1=1
This shows that there were some differences on the slave. In the mysql database, on the slave, there are 19 fewer rows in the db table and one less row in the user database. This is because I have different permissions on my master and slave servers.
On database_1 I have two tables that are out of sync. The two table_1 tables (on the master and slave) have the same row count (shown by the cnt_diff column). However, the crc_diff column shows that there are differences in the contents of the columns on the two tables. The boundaries column show you which chunk of the table that the differences are located in. You can use this to figure out the exact problem yourself..or you can use the mk-table-sync tool to do this for you. I will do another “Tip of the Day” on that tool.
Tomorrow I will wrap up the talk on the mk-table-sync tool by showing how to use the UDF function that Baron programmed. Using it speeds up the run time of mk-table-sync quite significantly!!
Tip of the Day ? Checking Your Tables ? Part II
To finish the discussion on the mk-table-checksum tool I wanted to let you know about the custom udf (user defined function) that Baron programmed for use in the maatkit toolkit. Essentially the udf provides a replacement for the md5 hash function. This function provides an impressive speedup. On two servers I ran the mk-table-checksum tool with the default setup and then installed the udf and re-ran the checksum tool. Here are the results:
| server | checksum w/md5 | checksum w/fvn |
| db3 | 17m10.220s | 5m0.090s |
| db6 | 112m21.719s | 53m58.368s |
Clearly it is a great benefit to use this udf if you are running table checksums. So how do you set it up? The source code is included in the tar.gz file that is distributed from the maatkit website. Just download and unzip/untar. In the included files there is a udf directory with one file. To compile “just”:
gcc -fPIC -Wall -I/usr/include/mysql -shared -o fnv_udf.so fnv_udf.cc
The instructions are included in the source code. Once you have it compiled, just copy the resultant file (fnv_udf.so) to the /lib directory and then run the command:
mysql mysql -e “CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME ‘fnv_udf.so’”
This adds the function to your MySQL server setup. To test, log into mysql and make this call:
mysql> SELECT FNV_64(’hello’, ‘world’);
This should returns some results if everything is done properly. Once your MySQL server “sees” the new udf maatkit will begin using it automatically.
Baron has an excellent background post on the fvn udf here: xaprb post
Thanks for the great tools Baron!!
Tip of the Day ? Checking Your Tables
Most production environments have a master/slave setup of servers. This provides the ability to easily perform backups, have warm fail-over if you have a hardware failure on the master, etc. Replication between the master and slave is fairly easy to set up, however as Baron points out here, it is possible (and probably quite common) for for a master and slave to “drift” out of sync.
Fortunately, not only did he point out the problem, he provides a fix for the problem. Included in the maatkit toolkit is the mk-table-checksum tool (and the related tool mk-table-sync which we won’t be discussing this time). It can tell you which tables on the master and and slave are out of sync and even tell you within a fair degree what rows are out of sync. Here is an example run:
mk-table-checksum –chunksize 500000 –replicate=test.checksum localhost
What are we doing here? Well, the chunksize option specifies that if tables are larger than 500,000 rows (in this case) that they should be chunked — broken apart — when processing. This will mean that a table doesn’t get blocked for too long a period of time. The replicate option specifies that you are wanting the checksum tool to use the test.checksum table to store results in. This can be very helpful in providing an accurate picture of your results. The create command for this table should be:
CREATE TABLE `checksum` (
`db` char(64) NOT NULL,
`tbl` char(64) NOT NULL,
`chunk` int(11) NOT NULL,
`boundaries` char(64) NOT NULL,
`this_crc` char(40) NOT NULL,
`this_cnt` int(11) NOT NULL,
`master_crc` char(40) default NULL,
`master_cnt` int(11) default NULL,
`ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`db`,`tbl`,`chunk`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
It doesn’t have to be in the test database by the way. You can put it wherever you want. Your option. Just be sure to specify the right database when running the mk-table-sync command.
The final option is really easy. It just specifies the location of the master server. In this case, I ran the command on the master server itself so localhost was specified.
It will take a while for this command to run your databases are of any size. Once it is done executing and your slave has caught up (if it lagged behind the master) than you just run the following:
mk-table-checksum –replicate=test.checksum –replcheck 2 localhost
This will give you the results you want. Some sample output:
mk-table-checksum --replicate=test.checksum –replcheck 2 localhost
Differences on h=server.slave
DB TBL CHUNK CNT_DIFF CRC_DIFF BOUNDARIES database_1 table_1 0 0 1 `clientid` < 184723 database_1 table_2 0 0 1 `clientid` < 184721 mysql db 0 -19 1 1=1 mysql user 0 -1 1 1=1
This shows that there were some differences on the slave. In the mysql database, on the slave, there are 19 fewer rows in the db table and one less row in the user database. This is because I have different permissions on my master and slave servers.
On database_1 I have two tables that are out of sync. The two table_1 tables (on the master and slave) have the same row count (shown by the cnt_diff column). However, the crc_diff column shows that there are differences in the contents of the columns on the two tables. The boundaries column show you which chunk of the table that the differences are located in. You can use this to figure out the exact problem yourself..or you can use the mk-table-sync tool to do this for you. I will do another “Tip of the Day” on that tool.
Tomorrow I will wrap up the talk on the mk-table-sync tool by showing how to use the UDF function that Baron programmed. Using it speeds up the run time of mk-table-sync quite significantly!!
Tip of the Day ? What MySQL Version to Use
If you are running MySQL on Windows this really doesn’t concern you. However, especially for Linux, this is a relevant discussion.
I guess this will be subject to much debate. However, in my mind, it is clear. I have for a long time used the compiled binary versions of MySQL (that MySQL AB provides) rather than .rpm files (on RedHat) or .deb files (on Debian). Why? Simply because I am not tied down to a vendor’s idea of what version of MySQL I should be using. I determine what version of MySQL we run in house. And I promise you that my servers run a OS that includes an older package of MySQL than I am currently running in production. I just checked. It is quite a bit older. Now, mind you, I have absolutely nothing against the operating system itself. It is a fine operating system. Using the compiled binaries of MySQL gives me control over what goes on and as a DBA I like that.
I suppose there is a downside to this. The vendor could fix a security problem before MySQL makes a fix available. Unlikely but theoretically possible.
Tip of the Day ? MySQL Users Conference
Every spring MySQL puts on the MySQL Users Conference in the San Francisco area of the United States. Almost 2,000 users gather for four days to listen to some of the experts of the MySQL world. Consider it a Users Group meeeting on steroids. There are other conferences in other countries, but this is the one that I am the most familiar and according to MySQL is the largest gathering of MySQL users and dbas.
This year the conference is April 14th through 17th and the home page is http://en.oreilly.com/mysql2008/public/content/home
I know there are many others who have talked about the MySQL conference — even recently - and more recently. It is because we realize how important it is.
If you are MySQL professional you owe it to yourself and your company to go “join the pod” at the Users Conference. You will learn more in four days than you thought possible. Hope to see you there!!
Tip of the Day ? Planetmysql
If you who don’t currently read planetmysql.org, add it to your favorite RSS reader. It is one of the best sources of information about MySQL available on the Internet. It is an aggregation of a large number of blogs by some of the best MySQL minds in the world.
Most importantly, the information that you get from these blogs is up-to-date and cutting edge.
Tip of the Day ? NULL Sort Order
The order that NULL is sorted in varies from database server to database server. Some sort NULL first (in an ascending list), some sort NULL last in an ascending list. As far as I know PostgreSQL, in the new version 8.3, is the only database server that allows you to specify which way the sort is applied.
When sorting your data it is important to know where NULL will fall when sorted. Without further ado:
mysql> create table sort (id int not null, data varchar(5));
Query OK, 0 rows affected (0.06 sec)
mysql> insert into sort (id,data) values (1,’a');
Query OK, 1 row affected (0.39 sec)
mysql> insert into sort (id,data) values (2,’b');
Query OK, 1 row affected (0.00 sec)
mysql> insert into sort (id,data) values (3,’c');
Query OK, 1 row affected (0.00 sec)
mysql> insert into sort (id,data) values (4,’c');
Query OK, 1 row affected (0.00 sec)
mysql> insert into sort (id,data) values (5,’d');
Query OK, 1 row affected (0.02 sec)
mysql> insert into sort (id,data) values (6,’e');
Query OK, 1 row affected (0.00 sec)
mysql> insert into sort (id,data) values (7,null);
Query OK, 1 row affected (0.01 sec)
mysql> select * from sort;
+—-+——-+
| id | data |
+—-+——-+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | c |
| 5 | d |
| 6 | e |
| 7 | NULL |
+—-+——+
7 rows in set (0.00 sec)
mysql> select id, data from sort order by data asc;
+—-+——+
| id | data |
+—-+——+
| 7 | NULL |
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | c |
| 5 | d |
| 6 | e |
+—-+——+
7 rows in set (0.24 sec)
mysql> select id, data from sort order by data desc;
+—-+——-+
| id | data |
+—-+——-+
| 6 | e |
| 5 | d |
| 3 | c |
| 4 | c |
| 2 | b |
| 1 | a |
| 7 | NULL |
+—-+——+
7 rows in set (0.00 sec)
mysql>
As you can see NULL is sorted before the other characters in the default (ascending) collation. Just something to keep in mind!
Tip of the Day ? MySQL Users Group
Tonight here in the Raleigh/Durham/Chapel Hill “Triangle” area we are having our monthly MySQL Users Group meeting. It is a great chance to meet other MySQL’ers, talk, network and learn something new. This month we have a DBA coming up from Columbia, SC to talk about his experience with migrating to MySQL from Oracle.
If you don’t belong to a MySQL Users Group, join one. The main page to see if you have one in your area is here: http://forge.mysql.com/wiki/List_of_MySQL_User_Groups
If there isn’t one in your area consider starting one yourself. The benefits are invaluable!
Tip of the Day ? Your Toolkit
As a DBA you will develop a “bag of tools” that you use on a day-in and day-out basis. I will outline some of the tools I use every day:
mytop (http://jeremy.zawodny.com/mysql/mytop/) - this is a tool that is very similar in use to the “top” tool of the Unix world. In almost real time it provides information such as what queries are running, how many queries per second and how many threads are being utilized.
bash (for shell programming) - while there are many choices I use tried and true bash for scripting mysql server installs and the hundred other tasks a DBA ends up having to script to automate tasks and make life easier.
maatkit (http://maatkit.sourceforge.net) - If I had to have one tool in my bag — this would be it. The collection of utilities is astonishing and cover everything from a dump and restore utility that runs multiple threads to a table sync tool that compares tables on two servers and can correct any problems between them. The complete list of utilities is too long to list here. Do yourself a favor and download it if you don’t have it already.
nagios (http://www.nagios.org) - I don’t enjoy getting paged at 2:00 a.m. but sometimes it is necessary. Nagios can monitor everything from disk space to cpu usage and there are many plug ins written specifically for MySQL. To keep on top of things you need monitoring. Nagios is among the best for that task.
ganglia (http://ganglia.sourceforge.net/) - ganglia is also a monitoring system, but it is of a slightly different bent. Ganglia produces nice graphs that you can use to track various system statistics over time and see how your system loads are changing.
That’s all for now. I am sure if you have been a DBA for a while you might have some more. Feel free to comment. If you aren’t using these tools already give them a try.
Tip of the Day ? Speeding up Imports by Sorting
When doing large imports into a table it can be very helpful to do different optimizations to speed up the import. Sometimes this can make a dramatic difference. For example, just recently I imported 106 million rows into a MyISAM table. Initially I just took the data and imported it into the server. This took about 60 hours. Then, because a mistake was made in the setup of the data, we had to re-import. This time the data was sorted before importing. While this operation took several hours, the import time dropped to one hour. Sorting the data might not always be practical, but in this case it worked — and worked dramatically.
There are other things to do that can speed up data imports. Sounds like good topics for future tips!!
Tip of the Day ? innodb_force_recover
In preparation for disaster recovery and for training purposes I asked our Junior DBA to document the innodb_force_recovery options in our internal Wiki. I am modestly familiar with the command having used it a few times because of data corruption. However, somehow, I completely missed something that I discovered when discussing his findings with the other DBA. I am passing it along since some of you might not know of it.
If you are doing a large operation that is modifying a transactional table and something goes wrong and you end up aborting the operation mysql will normally roll back the operation. Because this is a disk-bound operation it will be many times slower than the original transaction.
Solution is this: if you abort a large operation and it begins to roll back you can kill the entire server process, add the innodb_force_recovery=3 line to your my.cnf file (the mysqld section) and restart the mysql server. You can then do any cleanup necessary because the server doesn’t begin transaction rollback as it normally would. If you were doing a large import that you cancled you could drop the table for example. Then just remove the innodb_force_recovery line from my.cnf and restart the mysql server. You should tail the error log as it there will be lots of complaining by the server because of what you did, but in ten or fifteen minutes you should be back online. I would restart the server again just to make sure everything begins as normal but at that point you can consider yourself in business. If you can restart the server (which might not always be possible) this is much faster than waiting for a long transaction to roll back.
You should test this in a controlled test environment before having to use it “in the field”.
Resource: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
Tip of the Day ? MyISAM vs Innodb Portability
You can copy the three files (table_name.frm, table_name.MYI, and table_name.MYD) that form a MyISAM table from one server to another and just add them to the appropriate database directory and they will “appear” in the new database. However, with Innodb this is not possible. To copy Innodb databases you either need to dump the database/tables involved and re-import or copy the entire dataset and tablespace files from one server to another.
One caveat about the MyISAM table copy, you should lock the table while copying it to keep the copy from being corrupt due to changes to the file during the copy operation. Also, check and make sure your owner/permissions are the same after the copy.
Tip of the Day ? the .my.cnf File
Here is somethingthat you might be interested in. If you are using Linux on your servers and you ssh to the server and then run the mysql client to access the server you can use a .my.cnf file to speed up access. This is put in your home directory and is automatically parsed by the mysql client when it is invoked. For example here is a really simple one:
[client]
user =username
password =x&r%@ldc
host = localhost
Now you can just type ‘mysql’ and it connnects you up automatically. There are many other options you can put in here. Not sure how this works on Windows — I don’t think it does. Of course if you are using Windows as a desktop OS you can ssh to the server and then go from there. The reference page for this is: http://dev.mysql.com/doc/refman/5.0/en/option-files.html
Tip of the Day ? Row Count
There are several ways to count rows in a table.
SELECT count(*) from table_name;
or
SHOW TABLE STATUS LIKE ‘table_name%’;
or
SELECT table_name, table_rows FROM information_schema.tables where table_name=’table_name’ and table_schema=’database_name’;
However, the last two methods of getting a row count are only approximate row counts for Innodb tables. Keep that in mind.
Tip of the Day
I came up with an orginal *wink* idea of doing a small tip each day. The post I just did about pagination gave me the idea. I will do one tip a day as long as I can come up with something original. Something short. And I am soliciting input from the readership. If YOU have an idea for a Tip of the Day shoot me an email at bmurphy AT paragon-cs.com. If you have a previous post I can “reprint” with appropriate kudos and links I would love that too.