Key notes from the "scaling up and scaling out" session:
Cost: scale-up and scale-out come out at cost, however while talking about high availability the focus should not be on cost
what's the best solution: There are no "perfect" solutions. Different application can use different strategies to achive scale-up/scale-out/high availability solutions
Memory: Memory is king.. use it wisely. Look into key_reads/key_read_request miss rates (not just key_buffer_size)..
Disks: Use RAID if you are no doing so. Use the right interface/disk. Consider CRAM or CPU load
Partitioning: I agree... spread the load. Key benefits of partitioning in general: spread the information, smaller data/index gfragments, parallelizations (better DML). examples can be logging data (for example based on date). partition is daily, weekly or monthly basis. usually applications are looking for past x days of information . By Paritioning on date key you will end up accessing smaller set of data/index segments (plus dropping older data is so easy!!)
replication:spread reads..
Monday, April 20, 2009
MySQL conference 2009 : Facebook usage of open source
Facebook is a key user of memcached, and has done significant improvements in its usage (UDP, 64-bit standing out the most)
They are also key used of Hadoop, and have create a special sql interface (hive) that can access collected unstructered data set... worth looking out
They are also key used of Hadoop, and have create a special sql interface (hive) that can access collected unstructered data set... worth looking out
Wednesday, February 4, 2009
Setting up MySQL replication using mysqldump
1. Set up new replication user on master database
GRANT REPLICATION SLAVE ON *.* TO ‘repuser’@'%’ IDENTIFIED BY 'passwd';
2. Ensure binary logging is turned on in the master server. Also check for server-id value in my.cnf:
Log-bin=/some-directory/
Server-id=1
3. Install mysql binaries on new slave server (Please see my article on installing new MySQL server)
4. Set up my.cnf on slave server. Ensure relay related parameter are added in slave’s my.cnf
master-info-file=/Some-directory/master.info
relay-log=/Some-directory/JWIKIPROD-relay-bin
relay-log-index=/Some-directory/JWIKIPROD-relay-bin.index
relay-log-info-file=/Some-directory/relay-log.info
server-id=2
5. Take backup of Master database using mysqldump:
mysqldump --all-databases --master-data --lock-all-tables >db_name_timestamp.dmp
6. Copy backup dump to slave server
9. start the slave:
cd $MYSQL_HOME
bin/mysqld_safe --user=mysql --skip-slave
Check logfile to ensure there are no issues while starting the database
10. Import the data in slave server:
mysql -u root < db_name_timestamp.dmp
11. Connect to mysql database as root mysql -u root-p (password is null for new installation)
CHANGE MASTER TO
MASTER_HOST=master_hostname,
MASTER_PORT=3306,
MASTER_USER='repluser',
MASTER_PASSWORD='passwd',
MASTER_LOG_FILE='DB_arch.455, -- look in dump file created in step5 MASTER_LOG_POS=23343; -- -- look in dump file created in step 5
12. start slave server process
Mysql> start slave;
13 Ensure slave server is catching up properly
Mysql> show slave status/G
14. Using this mechanism, none of the user privileges are copied over to slave server. You need to export users and privileges separately (if required)
15 set up monitors to ensure replication is working properly
GRANT REPLICATION SLAVE ON *.* TO ‘repuser’@'%’ IDENTIFIED BY 'passwd';
2. Ensure binary logging is turned on in the master server. Also check for server-id value in my.cnf:
Log-bin=/some-directory/
Server-id=1
3. Install mysql binaries on new slave server (Please see my article on installing new MySQL server)
4. Set up my.cnf on slave server. Ensure relay related parameter are added in slave’s my.cnf
master-info-file=/Some-directory/master.info
relay-log=/Some-directory/JWIKIPROD-relay-bin
relay-log-index=/Some-directory/JWIKIPROD-relay-bin.index
relay-log-info-file=/Some-directory/relay-log.info
server-id=2
5. Take backup of Master database using mysqldump:
mysqldump --all-databases --master-data --lock-all-tables >db_name_timestamp.dmp
6. Copy backup dump to slave server
9. start the slave:
cd $MYSQL_HOME
bin/mysqld_safe --user=mysql --skip-slave
Check logfile to ensure there are no issues while starting the database
10. Import the data in slave server:
mysql -u root < db_name_timestamp.dmp
11. Connect to mysql database as root mysql -u root-p (password is null for new installation)
CHANGE MASTER TO
MASTER_HOST=master_hostname,
MASTER_PORT=3306,
MASTER_USER='repluser',
MASTER_PASSWORD='passwd',
MASTER_LOG_FILE='DB_arch.455, -- look in dump file created in step5 MASTER_LOG_POS=23343; -- -- look in dump file created in step 5
12. start slave server process
Mysql> start slave;
13 Ensure slave server is catching up properly
Mysql> show slave status/G
14. Using this mechanism, none of the user privileges are copied over to slave server. You need to export users and privileges separately (if required)
15 set up monitors to ensure replication is working properly
Setting up MySQL replication without any production downtime (only for InnoDB based database)
Setting up MySQL replication without any production downtime (only for InnoDB based database)
This method requires purchasing ibbackup hotbackup tool. Also, please note that setting up replication without any downtime is only possible only if all underlying tables are based on InnoDB engine. Please see my separate article for setting up replication for MyISAM based databases
1. Set up new replication user on master database
GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%’ IDENTIFIED BY 'passwd';
2. Ensure binary logging is turned on in the master server. Also check for server-id value in my.cnf:
Log-bin=/some-directory/
Server-id=1
3. Install mysql binaries on new slave server (Please see my article on installing new MySQL server)
4. Set up my.cnf on slave server. Ensure relay related parameter are added in slave’s my.cnf
master-info-file=/Some-directory/master.info
relay-log=/Some-directory/JWIKIPROD-relay-bin
relay-log-index=/Some-directory/JWIKIPROD-relay-bin.index
relay-log-info-file=/Some-directory/relay-log.info
server-id=2
5. Take snapshot of Master database using ibbackup tool:
Please read instructions in http://www.innodb.com/support/documentation/innodb-hot-backup-manual/#options for how to take hot backup using ibbackup tool
6. Copy backed up files to slave server
7. Apply logs to backup to make it a consistent copy
Please read instructions in http://www.innodb.com/support/documentation/innodb-hot-backup-manual/#options for how to apply logs to make a consistent backup copy
8. Put the restored backup and the log files at appropriate place for the new slave. Check my.cnf in production to figure out appropriate location for innodb files
9. start the slave:
cd $MYSQL_HOME
bin/mysqld_safe --user=mysql --skip-slave
Check logfile to ensure there are no issues while starting the database
10. connect to mysql database as root mysql -u root-p (password is null for new installation)
CHANGE MASTER TO
MASTER_HOST=master_hostname,
MASTER_PORT=3306,
MASTER_USER='repluser',
MASTER_PASSWORD='passwd',
MASTER_LOG_FILE='DB_arch.12345', -- whatever you find in log file of slave server while applying backup
MASTER_LOG_POS=1312184; -- -- whatever you find in log file of slave server while applying backup
9. start slave server process
Mysql> start slave;
10 Ensure slave server is catching up properly
Mysql> show slave status/G
11. Using this mechanism, none of the user privileges are copied over to slave server. You need to export users and privileges separately (if required)15 set up monitors to ensure replication is working properly
12. Setup up replication monitoring scripts
This method requires purchasing ibbackup hotbackup tool. Also, please note that setting up replication without any downtime is only possible only if all underlying tables are based on InnoDB engine. Please see my separate article for setting up replication for MyISAM based databases
1. Set up new replication user on master database
GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%’ IDENTIFIED BY 'passwd';
2. Ensure binary logging is turned on in the master server. Also check for server-id value in my.cnf:
Log-bin=/some-directory/
Server-id=1
3. Install mysql binaries on new slave server (Please see my article on installing new MySQL server)
4. Set up my.cnf on slave server. Ensure relay related parameter are added in slave’s my.cnf
master-info-file=/Some-directory/master.info
relay-log=/Some-directory/JWIKIPROD-relay-bin
relay-log-index=/Some-directory/JWIKIPROD-relay-bin.index
relay-log-info-file=/Some-directory/relay-log.info
server-id=2
5. Take snapshot of Master database using ibbackup tool:
Please read instructions in http://www.innodb.com/support/documentation/innodb-hot-backup-manual/#options for how to take hot backup using ibbackup tool
6. Copy backed up files to slave server
7. Apply logs to backup to make it a consistent copy
Please read instructions in http://www.innodb.com/support/documentation/innodb-hot-backup-manual/#options for how to apply logs to make a consistent backup copy
8. Put the restored backup and the log files at appropriate place for the new slave. Check my.cnf in production to figure out appropriate location for innodb files
9. start the slave:
cd $MYSQL_HOME
bin/mysqld_safe --user=mysql --skip-slave
Check logfile to ensure there are no issues while starting the database
10. connect to mysql database as root mysql -u root-p (password is null for new installation)
CHANGE MASTER TO
MASTER_HOST=master_hostname,
MASTER_PORT=3306,
MASTER_USER='repluser',
MASTER_PASSWORD='passwd',
MASTER_LOG_FILE='DB_arch.12345', -- whatever you find in log file of slave server while applying backup
MASTER_LOG_POS=1312184; -- -- whatever you find in log file of slave server while applying backup
9. start slave server process
Mysql> start slave;
10 Ensure slave server is catching up properly
Mysql> show slave status/G
11. Using this mechanism, none of the user privileges are copied over to slave server. You need to export users and privileges separately (if required)15 set up monitors to ensure replication is working properly
12. Setup up replication monitoring scripts
Thursday, January 29, 2009
Best Methods to repair corrupted large MySQL MyISAM tables
Usually the easiest way to repair a corrupted table is:
Repair table [table name] quick; [provided corruption occurred in index file]
This repair method attemps to “repair by key cache”. The approach is okay if you are dealing with small tables. However, this can take forever for large MyISAM tables. The best approach to repair large multi-gigabyte MyISAM tables is using sort method. Following methods repairs table via sort mechanism:
USING MYISAMCHK:
1. Create a copy of corrupted file and run following command :
myisamchk --sort-recover --sort-buffer-size=2G/table_name
This would repair the table using sort method. You can also specify tmpdir at command prompt i.e
myisamchk --sort-recover --sort-buffer-size=2G/table_name –tmpdir=/tmp/repairdir
Make sure tmpdir has enough space available.
Once repaired, repair corrupted file with this repaired file.
As a good practice, always keep a backup of original file.
USING myisam_max_sort_file_size:
This variable specifies maximum size of the temporary file that MySQL can use to repair a table. If the file size will be larger, repair is done using key cache method. Set it to a real high value and then use the repair command:
1. set global myisam_max_sort_file_size=223372036853727231;
2. repair table table_name;
Repairing tables (for example 20GB+) can be a big pain. It is very important to understand various repair options available, and choose the right strategy
Repair table [table name] quick; [provided corruption occurred in index file]
This repair method attemps to “repair by key cache”. The approach is okay if you are dealing with small tables. However, this can take forever for large MyISAM tables. The best approach to repair large multi-gigabyte MyISAM tables is using sort method. Following methods repairs table via sort mechanism:
USING MYISAMCHK:
1. Create a copy of corrupted file and run following command :
myisamchk --sort-recover --sort-buffer-size=2G
This would repair the table using sort method. You can also specify tmpdir at command prompt i.e
myisamchk --sort-recover --sort-buffer-size=2G
Make sure tmpdir has enough space available.
Once repaired, repair corrupted file with this repaired file.
As a good practice, always keep a backup of original file.
USING myisam_max_sort_file_size:
This variable specifies maximum size of the temporary file that MySQL can use to repair a table. If the file size will be larger, repair is done using key cache method. Set it to a real high value and then use the repair command:
1. set global myisam_max_sort_file_size=223372036853727231;
2. repair table table_name;
Repairing tables (for example 20GB+) can be a big pain. It is very important to understand various repair options available, and choose the right strategy
Wednesday, January 28, 2009
How to install MySQL in an enterprise manner
I am not a big fan of default MySQL installations. Just like any other enterprise rdbms installation, I prefer installing MySQL under a dedicated db mount. Following steps talk about installing MySQL in an "enterprise" manner:
1. Get rid of /etc/my.cnf. This cnf has the highest priority and I have seen many individuals running in issue just because they cannot identify the my.cnf setting being picked my MySQL engine
2. Ask system admin to create user mysql with dba group. Also ask for a separate DB mount. For example: /MYDB with permission granted to mysql:dba
3. set up following environment variables (preferrably in .bash_profile):
MYSQL_DFLT_DB=TESTDB;export MYSQL_DFLT_DB
MYSQL_HOME=/MYDB/$MYSQL_DFLT_DB/product/5.1.xx;export MYSQL_HOME
PATH=$PATH:$MYSQL_HOME/bin;export PATH
MYSQL_DATA=$MYSQL_HOME/data;export MYSQL_DATA
MYSQL_UNIX_PORT=$MYSQL_HOME/data/mysql.sock;export MYSQL_UNIX_PORT
MYSQL_TCP_PORT=3306; export MYSQL_TCP_PORT
4. confirm that your environment is setup correctly:
env | grep -i mysql
5. Now is time to download the software
if you are mysql customer, download latest appropriate binary release from http://enterprise.mysql.com (preferred). If you are not a customer, and intend to use gpl license sofware, download the community software from : http://dev.mysql.com/downloads/
Make sure to download binaries that match your operating system
6. install the software (using a generic install example):
cd /MYDB/TESTDB/product
unzip
scripts/mysql_install_db --user=mysql
Check the output of this command. This step completes binary installation
7. post configuration tasks:
Configure MySQL instance:
cd $MYSQL_DATA
copy my.cnf from support folders on to this folder. Change the settings as per your requirements
create appropriate folders for archival redo (for example /MYDB/TESTDB/arch etc)
8. Now you are all set to start the database:
cd $MYSQL_HOME
bin/safe_mysqld --user=mysql &
9. Confirm that instance is running
ps -ef | grep mysqld
check logfile
10. logging for the first time:
mysql -u root -p
for password press enter
11. Change mysql root password:
grant all on *.* to 'root'@'localhost' identified by 'test123'
or
Now you are all set to reap benefits of wonderful MySQL. I think it is always to follow appropriate structures for install MySQL (no matter how small your database is going to be). Companies grow and so do their databases. Following standard approaches just makes your life easier... more to come
1. Get rid of /etc/my.cnf. This cnf has the highest priority and I have seen many individuals running in issue just because they cannot identify the my.cnf setting being picked my MySQL engine
2. Ask system admin to create user mysql with dba group. Also ask for a separate DB mount. For example: /MYDB with permission granted to mysql:dba
3. set up following environment variables (preferrably in .bash_profile):
MYSQL_DFLT_DB=TESTDB;export MYSQL_DFLT_DB
MYSQL_HOME=/MYDB/$MYSQL_DFLT_DB/product/5.1.xx;export MYSQL_HOME
PATH=$PATH:$MYSQL_HOME/bin;export PATH
MYSQL_DATA=$MYSQL_HOME/data;export MYSQL_DATA
MYSQL_UNIX_PORT=$MYSQL_HOME/data/mysql.sock;export MYSQL_UNIX_PORT
MYSQL_TCP_PORT=3306; export MYSQL_TCP_PORT
4. confirm that your environment is setup correctly:
env | grep -i mysql
5. Now is time to download the software
if you are mysql customer, download latest appropriate binary release from http://enterprise.mysql.com (preferred). If you are not a customer, and intend to use gpl license sofware, download the community software from : http://dev.mysql.com/downloads/
Make sure to download binaries that match your operating system
6. install the software (using a generic install example):
cd /MYDB/TESTDB/product
unzip
MySQL-server-5.1.30-0.glibc23.i386.rpm
rename folder to 5.1.30
cd $MYSQL_HOMEscripts/mysql_install_db --user=mysql
Check the output of this command. This step completes binary installation
7. post configuration tasks:
Configure MySQL instance:
cd $MYSQL_DATA
copy my.cnf from support folders on to this folder. Change the settings as per your requirements
create appropriate folders for archival redo (for example /MYDB/TESTDB/arch etc)
8. Now you are all set to start the database:
cd $MYSQL_HOME
bin/safe_mysqld --user=mysql &
9. Confirm that instance is running
ps -ef | grep mysqld
check logfile
10. logging for the first time:
mysql -u root -p
for password press enter
11. Change mysql root password:
grant all on *.* to 'root'@'localhost' identified by 'test123'
or
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('test123');Now you are all set to reap benefits of wonderful MySQL. I think it is always to follow appropriate structures for install MySQL (no matter how small your database is going to be). Companies grow and so do their databases. Following standard approaches just makes your life easier... more to come
Wednesday, July 2, 2008
decision to blog
Well,
My MBA professor once said "If you are going to be in technology, make sure your write at least one book before you die"... Well I might not get time to write a book.. but at least can share some knowledge through this blog.
I personally have benefited a lot from some nice tech blogs and I guess its time to return the favor to database community
I will be sharing some insights on key topics in oracle and MySQL databases
My MBA professor once said "If you are going to be in technology, make sure your write at least one book before you die"... Well I might not get time to write a book.. but at least can share some knowledge through this blog.
I personally have benefited a lot from some nice tech blogs and I guess its time to return the favor to database community
I will be sharing some insights on key topics in oracle and MySQL databases
Subscribe to:
Posts (Atom)