TokuDB : Hot Index Creation

Hot Index Creation

TokuDB allows you to add indexes to an existing table and still perform inserts and queries on that table while the index is being created.

The ONLINE keyword is not used. Instead, the value of the tokudb_create_index_online client session variable is examined. More information is available in TokuDB Variables.
Hot index creation is invoked using the CREATE INDEX command after setting
tokudb_create_index_online=on.

Here's an example:

SET tokudb_create_index_online=ON;
Query OK, 0 rows affected (0.00 sec)

CREATE INDEX index table (field_name);

Alternatively, using the ALTER TABLE command for creating an index will create the index offline (with the table unavailable for inserts or queries), regardless of the value of tokudb_create_index_online. The only way to hot create an index is to use the CREATE INDEX command.

Hot creating an index will be slower than creating the index offline, and progress depends how busy the mysqld server is with other tasks. Progress of the index creation can be seen by using the SHOW PROCESSLIST command (in another client). Once the index creation completes, the new index will be used in future query plans.

If more than one hot CREATE INDEX is issued for a particular table, the indexes will be created serially. An index creation that is waiting for another to complete will be shown as Locked in SHOW PROCESSLIST. We recommend that each CREATE INDEX be allowed to complete before the next one is started.

TokuDB : Hot Column Addition and Deletion

Hot Column Addition and Deletion

From 18 hours to 3 seconds!

Hot Column Addition and Deletion (HCAD) Overview

TokuDB v5.0 introduces several features that are new to the MySQL world. In this series of posts, we’re going to present some information on these features: what’s the feature, how does it work under the hood, and how do you get the most out of this feature in your MySQL setup.

Today we start with HCAD: Hot Column Addition and Deletion. Many users have had the experience of loading a bunch of data into a table and associated indexes, only to find that adding some columns or removing them would be useful.

alter table X add column Y int default 0;

or the like takes a long time — hours or more — during which time the table is write locked, meaning no insertions/deletions/updates and no queries on the new column until the alter table is done.
Mark Callaghan points out that changing the row format in InnoDB is a “significant project”, so it looked like slow alter tables were going to be a challenge for MySQL for the foreseeable future. Slow alter tables is a reason for the inability of MySQL to scale to large tables.

TokuDB v5.0 changes all that with the introduction of HCAD. You can add or delete columns from an existing table with minimal downtime — just the time for MySQL itself to close and reopen the table. The total downtime is seconds to minutes.

Here we present an example of HCAD in action. See this page for details of the experiment. Drum roll…
TokuDB:

mysql> alter table ontime add column totalTime int default 0;
Query OK, 0 rows affected (3.33 sec)

InnoDB:

mysql> alter table ontime add column totalTime int default 0;
Query OK, 122225386 rows affected (17 hours 44 min 40.85 sec)

That’s 19,000x faster! Goodbye long downtimes.

As a note, the “0 rows affected” for TokuDB means that the column addition work happens in the background. All queries on the table, however, will see the new column as soon as the alter table returns, in this case after 3.33 sec.

How can I optimize a mysqldump of a large database?

How can I optimize a mysqldump of a large database ?

I have a symfony application with an InnoDB database that is ~2GB with 57 tables. The majority of the size of the database resides in a single table (~1.2GB). I am currently using mysqldump to backup the database nightly. Due to my comcast connection, oftentimes if I am running a dump manually my connection to the server will timeout before the dump is complete causing me to have to rerun the dump. [I currently run a cron that does the dump nightly, this is just for dumps that I run manually.] Is there a way to speed up the dumps for the connection timeout issue, but also to limit the time the server is occupied with this process? BTW, I am currently working on reducing the size of the overall database to resolve this issue.

The main bottleneck in the dump like this is drive I/O. You are reading a load of data and writing it again. You can speed this up in a number of ways: Make sure your output is going to a different drive(s) than the one(s) the database files are stored on - this will make a massive difference with spinning disks as the drive heads will not be constantly flicking between the location being read from and the location being written to. The output of mysqldump will be very compressible, so if you can not separate the output from the input as mentioned above pipe the output through gzip or similar. This will reduce the amount of writing being done (so reduce the overall IO load, and the amount of head movement) at the expense of some CPU time (which you may have a lot of spare at these times anyway). Also, pass the output through a pipe utility (like pv) that supports large write buffers to group blocks written to the drives together more, again to reduce the effect of head-movement latency - this will make quite a difference if using the --quick option to reduce the RAM impact of backing up large tables). Only run your backup process when IO load is otherwise low. You may be fixing the wrong issue though: it might be easier to address the connection drops instead (though reducing the I/O load imposed by your backups will help reduce the effect you have on other users so is worth trying anyway). Could you run your manual backups through screen (or similar tools like tmux)? That way if your connection to the server drops you can just reconnect and reattach to the screen session without any processes getting interrupted. If you are sending the data directly over the connection (i.e. you are running mysqldump on your local machine against a remote database, so the dump appears locally) you might be better off running the dump on the server first, compressing as needed, then transferring the data over the network using a tool (such as rsync) which supports partial transfers so you can resume the transfer (instead of restarting) if a connection drop interrupts it. As part of your "reducing the size of the overall database to resolve this issue" I would guess that a large chunk of your data does not change. You might be able to move a large chunk of the 1.2Gb from that main table off into another and remove that from those that are copied by the mysqldump call. You don't need to backup this data every time if it never changes. Splitting data between tables and databases this way is usually referred to as data partitioning and can also allow you to spread the data and I/O load over multiple drives. High-end database have built in support for automatic partitioning, though in mysql you will probably have to do it manually and alter your data access layer to account for it. Straying off-topic for this site (so you should probably nip over to ServerFault or SuperUser to ask if you need more detail): If you seem to be losing connections due to inactivity, check the options in your SSH server and SSH client to make sure keep-alive packets are enabled and being sent often enough. If seeing drops even if the connection is active you could also try using OpenVPN or similar to wrap the connection - it should handle a short drop, even a complete drop if your entire connection is down for a few seconds, such that the SSH client and server don't notice.

INSIGHT INTO DOING BACKUPS WITH mysqldump IMHO Doing backups has become more of an art form if you know just how to approach it You have options Option 1 : mysqldump an entire mysql instance This is the easiest one, the no-brainer !!! mysqldump -h... -u... -p... --hex-blob --routines --triggers --all-databases | gzip > MySQLData.sql.gz Everything written in one file: table structures, indexes, triggers, stored procedures, users, encrypted passwords. Other mysqldump options can also export different styles of INSERT commands, log file and position coordinates from binary logs, database creation options, partial data (--where option), and so forth. Option 2 : mysqldump separate databases into separate data files Start by creating a list of databases (2 techniques to do this) Technique 1 mysql -h... -u... -p... -A --skip-column-names -e"SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','mysql')" > ListOfDatabases.txt Technique 2 mysql -h... -u... -p... -A --skip-column-names -e"SELECT DISTINCT table_schema FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql')" > ListOfDatabases.txt Technique 1 is the fastest way. Technique 2 is the surest and safest. Technique 2 is better because, sometimes, users create folders for general purposes in /var/lib/mysql (datadir) which are not database related. The information_schema would register the folder as a database in the information_schema.schemata table. Technique 2 would bypass folders that do not contain mysql data. Once you compile the list of databases, you can proceed to loop through the list and mysqldump them, even in parallel if so desired. for DB in `cat ListOfDatabases.txt` do mysqldump -h... -u... -p... --hex-blob --routines --triggers ${DB} | gzip > ${DB}.sql.gz & done wait If there are too many databases to launch at one time, parallel dump them 10 at a time: COMMIT_COUNT=0 COMMIT_LIMIT=10 for DB in `cat ListOfDatabases.txt` do mysqldump -h... -u... -p... --hex-blob --routines --triggers ${DB} | gzip > ${DB}.sql.gz & (( COMMIT_COUNT++ )) if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ] then COMMIT_COUNT=0 wait fi done if [ ${COMMIT_COUNT} -gt 0 ] then wait fi Option 3 : mysqldump separate tables into separate data files Start by creating a list of tables mysql -h... -u... -p... -A --skip-column-names -e"SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql')" > ListOfTables.txt Then dump all tables in groups of 10 COMMIT_COUNT=0 COMMIT_LIMIT=10 for DBTB in `cat ListOfTables.txt` do DB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $1}'` TB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $2}'` mysqldump -h... -u... -p... --hex-blob --triggers ${DB} ${TB} | gzip > ${DB}_${TB}.sql.gz & (( COMMIT_COUNT++ )) if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ] then COMMIT_COUNT=0 wait fi done if [ ${COMMIT_COUNT} -gt 0 ] then wait fi   Option 4 : USE YOUR IMAGINATION Try variations of the aforementioned Options plus techniques for clean snapshots Examples Order the list of tables by the size of each tables ascending or descending. Using separate process, run "FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" before launching mysqldumps. Kill this process after mysqldumps are complete. This is helpful if a database contains both InnoDB and MyISAM Save the mysqldumps in dated folders and rotate out old backup folders. Load whole instance mysqldumps into standalone servers. CAVEAT Only Option 1 brings everything. The drawback is that mysqldumps created this way can only be reloaded into the same majot release version of mysql that the mysqldump was generated. In other words, a mysqldump from a MySQL 5.0 database cannot be loaded in 5.1 or 5.5. The reason ? The mysql schema is total different among major releases. Options 2 and 3 do not include saving usernames and passwords. Here is the generic way to dump the SQL Grants for users that is readble and more portable mysql -h... -u... -p... --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -h... -u... -p... --skip-column-names -A | sed 's/$/;/g' > MySQLGrants.sql Option 3 does not save the stored procedures, so you can do the following mysqldump -h... -u... -p... --no-data --no-create-info --routines > MySQLStoredProcedures.sql & Another point that should be noted is concerning InnoDB. If your have a large InnoDB buffer pool, it makes sense to flush it as best you can before performing any backups. Otherwise, MySQL spends the time flushing tables with leftover dirty page out of the buffer pool. Here is what I suggest: About 1 hour before performing the backup run this SQL command SET GLOBAL innodb_max_dirty_pages_pct = 0; In MySQL 5.5 default innodb_max_dirty_pages_pct is 75. In MySQL 5.1 and back, default innodb_max_dirty_pages_pct is 90. By setting innodb_max_dirty_pages_pct to 0, this will hasten the flushing of dirty pages to disk. This will prevent or at least lessen the impact of cleaning up any incomplete two-phase commits of InnoDB data prior to performing any mysqldump against any InnoDB tables. FINAL WORD ON mysqldump Most people shy away from mysqldump in favor of other tools and those tools are indeed good. Such tools include MAATKIT (parallel dump/restore scripts, from Percona [Deprecated but great]) XtraBackup (TopNotch Snapshot Backup from Percona) CDP R1Soft (MySQL Module Option that takes point-in-time snapshots) MySQL Enterprise Backup (formerly InnoDB Hot Backups [commercial]) If you have the spirit of a true MySQL DBA, you can embrace mysqldump and have the complete mastery over it that can be attained. May all your backups be a reflection of your skills as a MySQL DBA.

Backups and Recover

Backups and Recovery

This is the most important task of an database administrator, you must protect your data at all costs, this means regular backups and regular restores even to another system just to check the integrity of those backups. There is no point in putting yourself in a position where you are holding your breathe when a restore is happening only to find out that the backup is corrupt, try if possible to perform regular restores if not then at least you should be performing a disaster recovery test once per year. Not being able to restore could be a disaster for your company and your job.
To check your backups you can use one or more of the below which I have used in the past

• use a reporting database if the customers don't need real time data and you have the money and time, Production data could be restored every day to this system which is a very good test
• use a performance test server with Production data, ideal to test releases of your software against Production data which is generally has more volume then a test system, restore perhaps once a week
• at least perform a DR once per year to prove the backup solution is working, for example you may have forgotten to backup something not only regarding the database but from the systems as well

Backups and restoring
First lets start with a few terms associated with backups

logical backup	this type of backup is created by saving information that represents the logical database structures using SQL statements like create database, create table and insert. This type of backup is ideal when you want to upgrade from one version of MySQL to another however it is a slower method of backing up.
physical backup	this type of backup is a backup of the actual database files or disk partitions, this type of backup can be very fast to backup and restore.
full backup	a full backup is a standalone backup containing everything in the database, this could then be restored on another server. A full backup can be either logical or physical.
incremental backup	this type of backup only contains the data that has changed from the last backup. The advantage of this type of backup is that it is faster as there is not some much data to backup, however the disadvantage is that it takes longer to recover.
consistent backup	this is a backup at an exact moment in time, generally you shutdown the database (or quiescent mode) then take the backup.
hot backup	this type of backup is taken when the database is running, during the backup both reads and writes are not blocked
warm backup	this type of backup is taken when the database is running, however reads are not blocked but writes are prohibited from making any modifications to the database.
cold backup	similar to a consistent backup as the database is shutdown before the backup begins
point-in-time restore	is a restoration of a database to a specified date and time , some databases use a full backup and recovery logs to restore to that point-in-time, others can only use the last full backup which means that data might have to be re-keyed into the system.

As well as obtaining a backup in your maintenance window you should also be aware on how long a restore will take thus to make sure that you meet you SLA agreements during a DR or if you have to recovery a database due to corruption or user error.
The $64,000 question is how often you should take your backups, and this i am afraid depends, so company are happy for once a month backups other may take two backups per day. The answer generally has to come from the business on what they are prepared to lose, amount of data lost or what has to be re-keyed into the system again. If you have a small company that say has to re-key in 20-50 invoices then that's no big deal, however if you have a trading company that many have to re-key in 10's of thousands of entries/trades then that becomes a problem. You have to add the time it takes to restore the system plus the time it takes to recover the system so that users are able to use it, it is this time that you give to the business to make there decision on what is a acceptable time period that the business can be down for, the shorter the time the more money that will have to be thrown at the solution, if you are talking about zero downtime then we would have to implement a high availability solution which could cost a lot of money, if you are happy with 1 days downtime then this should be enough to restore and recovery a database and to re-key in some entries to make the database consistent with the companies paper work.
As you saw above there are a number of ways to backup a database, depending on the the available time to perform a backup will make you decide on what method to use, if you have a short maintenance window with a large database then a incremental backup maybe the only option, but you have a large maintenance window with a small database then you could perform a full backup, remember what ever option you use with have a impact on the recovery time.
One point to make is that you backups should be taken off-site if held on tape or copied across to an other system in another location, if an incident happened on the original system for example a fire you don't want to lose your backups as well, the storing of off-site data should be part of you DR plan.
Enough of talking about backups lets see how you can actually take one, there are a number of backup tools that MySQL can use, see the table below

Backup tools for MySQL
Backup method	Storage engine	Impact	Backup speed	Recovery speed	Recovery granularity
mysqldump	ALL	WARM	MEDUIM	SLOWEST	MOST FLEXIBLE
mysqldump	INNODB	HOT	MEDUIM	SLOWEST	MOST FLEXIBLE
select into outfile	ALL	WARM	SLOW	SLOW	MOST FLEXIBLE
mk-parallel-backup	ALL	WARM	MEDUIM	MEDUIM	FLEXIBLE
ibbackup	INNODB	HOT	FAST	FAST	FLEXIBLE
ibbackup	ALL	WARM	FAST	FAST	FLEXIBLE
backup command in mysqld	ALL	HOT	FAST	FAST	FLEXIBLE
filesystem (copy files)	ALL	COLD	FASTEST	FASTEST	NOT FLEXIBLE
snapshot (using LVM, ZFS, VMWare)	ALL	ALMOST HOT	FAST	FAST	LEAST FLEXIBLE
mysqlhotcopy	MyISAM	MOSTLY COLD	FAST	FAST	FLEXIBLE

The mysqldump program has been around a long time, it provides a logical backup of the entire database, individual databases, individual tables or even subsets of data using the --where option, it is often called a data dump. The output is in ascii format which means that you can open it in vi or notepad and change the contains if desired. I am not going to detail all options of the mysqldump command but show you a few examples

mysqldump

## backup all databases mysqldump --user=root --password --all-databases > backup_<date>_all.sql ## backup a specific database mysqldump --user=root --password <database_name> > backup_<date>_<database_name>.sql ## backup multiple databases mysqldump --user=root --password <database_name>,<database_name> > backup_<date>.sql ## backup a table from a database mysqldump --user=root --password <database_name> <table_name> > backup_<date>_<database_name>_<table_name>.sql ## backup some specific data mysqldump --user=root --password <database_name> <table_name> --where "last_name='VALLE' order by first_name > backup_<date>.sql ## dumping from one database to another mysqldump --databases <database_name> | mysql -h <destination_host> <database_name>

restore a mysqldump

## all databases mysql --user=root --password < backup.sql ## specific database mysql --user=<user> --password <database_name> < backup_<dataabse_name>.sql

You can use the into outfile clause of the select statement to backup individual tables, the command used to load the dump created is load data infile

select into outfile / load data infile

## dump of the accounts table select * into outfile '/tmp/accounts.txt' from accounts; ## load the dump load data infile '/tmp/accounts.txt' into table accounts;

The Maatkit parallel dump and restore toolkit can be downloaded from http://www.maatkit.org basically it's a wrapper around mysqldump which provides the programs mk-parallel-dump and mk-parallel-restore, what this means is that if you have a 16 core server and you are dumping 32 tables, the script will start up 16 separate copies of mysqldump with each process dumping a separate table.

mk-parallel-dump, mk-parallel-restore

## backup a database mk-parallel-dump --basdir=/backups ## restore a database mk-parallel-restore /backups

Snapshots for a filesystem depend on what operating system or software you are using, here are some links to my web pages regarding LVM, ZFS and VMWare

• LVM snapshotting
• ZFS snapshotting
• VMWare snapshots

New in MySQL 5.6 is the online logical host backup, you can also use compression and encryption which is important when using sensitive data.

backup	backup database <database_name> to '<database_name>-backup.sql'
restore	restore from '<database_name>-backup.sql'
history	select * from backup_history where backup_id = 321\G

There currently is a number of limitations of this command

• no backup of the internal mysql datadisk
• no native driver for InnoDB tables
• no native driver for Maria or Falcon
• no backup of partitions
• no incremental backups

The mysqlhotcopy is a perl script written to provide a consistent backup of MyISAM and ARCHIVE tables, it does some limitations one of which when run it uses the lock tables command to create read locks on the tables being backed up, this allows for a consistent backup. again there are a number of options that you can use so have a look at the man page, here are a few examples

mysqlhotcopy

## backup a database mysqlhotcopy <database_name> /backups ## backup multiple databases mysqlhotcopy <database_name> accounts /backups ## backup a database to to another server mysqlhotcopy --method=scp <database_name> \ username@backup.server:/backup ## use pattern match to backup databases and tables mysqlhotcopy <database_name>./^employees/ /backup

Lastly ibbackup is a 3rd party software which allows you to perform non-blocking hot backups of InnoDB tables, it is entirely command-line driven which means that it is ideal for scripts, here is a link to the web site http://www.innodb.com/doc/hot_backup/manual.html
Recovering from Crashes
Most often you have to recover to a point-in-time after the last backup, the normal procedure is as follows

• restore the latest backup
• recovery the data to a point-in-time using recovery log files

MySQL server uses a binary format for the log files to save space, this means that you cannot view these files directly, a utility called mysqlbinlog is supplied to convert these log files into a text format that you can view. So the process for performing a point-in-time restore for MySQL is

• restore the database using the last backup
• determine the first binary log and starting position needed
• determine the last binary log needed
• convert the binary log to text format with the mysqlbinlog utility using options to specify the start and stop time
• check the text file to make sure it's what you need
• import the converted binary log(s)

convert the log files	## convert to a specific binary log file mysqlbinlog mysql-bin.010310 > mysql-bin.010310.sql ## use a date to end at a specific time mysqlbinlog --stop-datetime='201204-29 17:00:00' mysql-bin.010312 > mysql-bin.010312.sql ## other options are --stop-datetime --start-datatime --start-position --stop-position
restore the converted file	mysql --user=root -password < mysql-bin.010310.sql

Percona pt-online-schema-change tool syntax

I recall we had one problem with one table and we had to add a “_” to the beginning of the FK name.

 

[TOKU]# pt-online-schema-change  h=127.0.0.1,D=YSIUSER,t=ysi_files,P=3306,u=xxxx,p=xxxxx  --alter "DROP FOREIGN KEY _fk_files_user_id, DROP KEY idx_dropbox_dateuploaded_numdownloads, ADD KEY idx_dropbox_dateuploaded_numdownloads (dropbox_id,date_uploaded,num_downloads), DROP KEY idx_files_user_expdate_markdel_dropboxid, ADD KEY idx_files_user_expdate_markdel_dropboxid (user_id,sender_expiration,mark_delete,dropbox_id)" --progress time,3000 --chunk-size 50000 --lock-wait-time=360 --max-lag=5000  --no-drop-old-table --max-load=Threads_running:25  --recursion-method=processlist  --max-lag=60 --critical-load=Threads_running:2500 --chunk-index=i_batch_file_id --execute

Altering `YSIUSER`.`ysi_files`...

Creating new table...

Created new table YSIUSER._ysi_files_new OK.

Altering new table...

Altered `YSIUSER`.`_ysi_files_new` OK.

Creating triggers...

Created triggers OK.

Copying approximately 735564447 rows...

Copying `YSIUSER`.`ysi_files`:   4% 18:10:24 remain

Copying `YSIUSER`.`ysi_files`:   8% 18:00:27 remain

Copying `YSIUSER`.`ysi_files`:  12% 17:28:02 remain

HowTo: Check Swap Usage in Linux

How do I check swap (paging) usage under Linux operating systems using command bash/ksh line options?

Swap space (also known as paging) is nothing but computer memory management involving swapping regions of memory to and from storage. You can see swap usage summary by device using any one of the following commands. You may have to login as root user to use the following commands.

The maximum useful size of a swap area depends on the architecture and the kernel version. For Linuux kernels after v2.3.3+ there is no such limitation on swap size.

Option #1: /proc/swaps file

Type the following command to see total and used swap size:
# cat /proc/swaps
Sample outputs:

Filename    Type  Size Used Priority
/dev/sda3                               partition 6291448 65680 0

Option #2: swapon command

Type the following command:
# swapon -s
Sample outputs:

Filename    Type  Size Used Priority
/dev/sda3                               partition 6291448 65680 0

Option #3: free command

Use the free command as follows:
# free -g
# free -k
# free -m
Sample outputs:

             total       used       free     shared    buffers     cached
Mem:         11909      11645        264          0        324       8980
-/+ buffers/cache:       2341       9568
Swap:         6143         64       6079

Option #4: vmstat command

Type the following vmstat command:
# vmstat
# vmstat 1 5
Sample outputs:

procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa
 1  9 1209512 101352   1504 127980    0    3    11    20   60   55  3  1 95  1
 2 11 1209640 101292   1508 134132  844  424  5608   964 23280 15012  2  8 20 70
 0 10 1210052 108132   1532 125764  648  660 10548   916 22237 18103  3 10 11 77
 1 13 1209892 106484   1500 128052  796  240 10484   980 24024 12692  2  8 24 67
 1  9 1209332 113412   1500 124028 1608  168  2472   620 28854 13761  2  8 20 70

Note down the following output from swap field:

1. si: Amount of memory swapped in from disk (/s).
2. so: Amount of memory swapped to disk (/s).

Option #5: top/atop/htop command

Type the following commands:
# atop
# htop
# top
Sample outputs (from top command):

top - 02:54:24 up 15:24,  4 users,  load average: 0.45, 4.84, 6.75
Tasks: 266 total,   1 running, 264 sleeping,   0 stopped,   1 zombie
Cpu(s):  3.2%us,  1.4%sy,  0.0%ni, 94.4%id,  1.0%wa,  0.0%hi,  0.1%si,  0.0%st
Mem:   8120568k total,  7673584k used,   446984k free,     4516k buffers
Swap: 15859708k total,  1167408k used, 14692300k free,  1151972k cached
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
13491 vivek     20   0 1137m 279m 6692 S   10  3.5  19:17.47 firefox
 5663 vivek     10 -10 1564m 1.1g  59m S    8 14.5   5:10.94 vmware-vmx
 2661 root      20   0  352m 185m 8604 S    6  2.3  65:40.17 Xorg
 3752 vivek     20   0 3566m 2.6g  12m S    6 33.6  63:44.35 compiz
 4798 vivek     20   0  900m  50m 4992 S    2  0.6   0:11.04 chrome
 5539 vivek     20   0 1388m 838m 780m S    2 10.6   1:45.78 VirtualBox
 6297 root      20   0     0    0    0 S    2  0.0   0:00.15 kworker/2:0
 6646 root      20   0 19252 1404  936 R    2  0.0   0:00.01 top
    1 root      20   0  8404  644  608 S    0  0.0   0:03.32 init
    2 root      20   0     0    0    0 S    0  0.0   0:00.03 kthreadd
    3 root      20   0     0    0    0 S    0  0.0   0:02.30 ksoftirqd/0
    6 root      RT   0     0    0    0 S    0  0.0   0:00.00 migration/0
    7 root      RT   0     0    0    0 S    0  0.0   0:00.24 watchdog/0
   37 root       0 -20     0    0    0 S    0  0.0   0:00.00 cpuset
   38 root       0 -20     0    0    0 S    0  0.0   0:00.00 khelper
   39 root      20   0     0    0    0 S    0  0.0   0:00.00 kdevtmpfs
   40 root       0 -20     0    0    0 S    0  0.0   0:00.00 netns

Sample outputs from htop command:

Fig.01: Linux: Swap Memory Usage Command

how to free swap partition or re-size it to bigger space w/out creating swap file as a remedy?

Disable swap (Take care if the swap memory is in use: information goes from swap to RAM)
# swapoff -a
With lvm partition, you can resize it like this:
Suppose swap partition in /dev/vg0/swap
# lvresize -L +1G /dev/vg0/swap
next, (re)setup swap memory :
# mkswap /dev/vg0/swap
Now, You can re-enable swap like this:
# swapon -a

Innodb Performance Optimization Basics

Interviewing people for our Job Openings I like to ask them a basic question – if you have a server with 16GB of RAM which will be dedicated for MySQL with large Innodb database using typical Web workload what settings you would adjust and interestingly enough most people fail to come up with anything reasonable. So I decided to publish the answer I would like to hear extending it with basics of Hardware OS And Application optimization.

I call this Innodb Performance Optimization Basics so these are general guidelines which work well for wide range of applications, though the optimal settings of course depend on the workload.


Hardware
If you have large Innodb database size Memory is paramount. 16G-32G is the cost efficient value these days. From CPU standpoint 2*Dual Core CPUs seems to do very well, while with even just two Quad Core CPUs scalability issues can be observed on many workloads. Though this depends on the application a lot. The third is IO Subsystem – directly attached storage with plenty of spindles and RAID with battery backed up cache is a good bet. Typically you can get 6-8 hard drives in the standard case and often it is enough, while sometimes you may need more. Also note new 2.5″ SAS hard drives. They are tiny but often faster than bigger ones. RAID10 works well for data storage and for read-mostly cases when you still would like some redundancy RAID5 can work pretty well as well but beware of random writes to RAID5.

Operating System
First – run 64bit operating system. We still see people running 32bit Linux on 64bit capable boxes with plenty of memory. Do not do this. If using Linux setup LVM for database directory to get more efficient backup. EXT3 file system works OK in most cases, though if you’re running in particular roadblocks with it try XFS. You can use noatime and nodiratime options if you’re using innodb_file_per_table and a lot of tables though benefit of these is minor. Also make sure you wrestle OS so it would not swap out MySQL out of memory.

MySQL Innodb Settings
The most important ones are:
innodb_buffer_pool_size 70-80% of memory is a safe bet. I set it to 12G on 16GB box.
UPDATE: If you’re looking for more details, check out detailed guide on tuning innodb buffer pool
innodb_log_file_size – This depends on your recovery speed needs but 256M seems to be a good balance between reasonable recovery time and good performance
innodb_log_buffer_size=4M 4M is good for most cases unless you’re piping large blobs to Innodb in this case increase it a bit.
innodb_flush_log_at_trx_commit=2 If you’re not concern about ACID and can loose transactions for last second or two in case of full OS crash than set this value. It can dramatic effect especially on a lot of short write transactions.
innodb_thread_concurrency=8 Even with current Innodb Scalability Fixes having limited concurrency helps. The actual number may be higher or lower depending on your application and default which is 8 is decent start
innodb_flush_method=O_DIRECT Avoid double buffering and reduce swap pressure, in most cases this setting improves performance. Though be careful if you do not have battery backed up RAID cache as when write IO may suffer.
innodb_file_per_table – If you do not have too many tables use this option, so you will not have uncontrolled innodb main tablespace growth which you can’t reclaim. This option was added in MySQL 4.1 and now stable enough to use.
Also check if your application can run in READ-COMMITED isolation mode – if it does – set it to be default as transaction-isolation=READ-COMMITTED. This option has some performance benefits, especially in locking in 5.0 and even more to come with MySQL 5.1 and row level replication.
There are bunch of other options you may want to tune but lets focus only on Innodb ones today.
You can check about tuning other options here or read one of our MySQL Presentations.

Application tuning for Innodb
Especially when coming from MyISAM background there would be some changes you would like to do with your application. First make sure you’re using transactions when doing updates, both for sake of consistency and to get better performance. Next if your application has any writes be prepared to handle deadlocks which may happen. Third you would like to review your table structure and see how you can get advantage of Innodb properties – clustering by primary key, having primary key in all indexes (so keep primary key short), fast lookups by primary keys (try to use it in joins), large unpacked indexes (try to be easy on indexes).

With these basic innodb performance tunings you will be better of when majority of Innodb users which take MySQL with defaults run it on hardware without battery backed up cache with no OS changes and have no changes done to application which was written keeping MyISAM tables in mind.

Dumping multiple databases, but to separate files

Dumping multiple databases, but to separate files

As we’ve seen in the second section, the option –all-databases gives you the possibility to backup all the databases available. However, the databases will all be backed up to the same dump file, so sometimes you may prefer having a separate dump for each database. This can be useful in the case you need to restore one database, but need to leave the other ones alone. First, we need to find out which databases are available, and perhaps exclude some of them from the backup. Say, for instance, that you want to backup all the databases but mysql, information_schema. First, we list all the databases with

mysql -u... -p... -e "show databases;"

1 2	mysql -u... -p... -e "show databases;"

You should see something similar to this:

+-----------------------------+ | Database | +-----------------------------+ | information_schema | | database1 | | database2 | | mysql | +-----------------------------+

1 2 3 4 5 6 7 8 9

+-----------------------------+ | Database                    | +-----------------------------+ | information_schema          | | database1                   |       | database2                   |           | mysql                       | +-----------------------------+

We need to manipulate this output a little bit, so that we can extract from it just the database names, filtering out all the other characters that are not needed:

mysql -u... -p... -e "show databases;" | tr -d "| "

1 2	mysql -u... -p... -e "show databases;" | tr -d "| "

Which produces:

Database information_schema database1 database2 mysql

1 2 3 4 5 6

Database information_schema database1 database2 mysql

Almost there. We now want to remove the Database heading, and also exclude the databases mysql and information_schema, as per the example (unless you want to back them up too):

mysql -u... -p... -e "show databases;" \ | tr -d "| " \ | egrep -v "(Database|mysql|information_schema)"

1 2 3 4

mysql -u... -p... -e "show databases;" \ | tr -d "| "  \ | egrep -v "(Database|mysql|information_schema)"

Here we are:

database1 database2

1 2 3

database1 database2

We now have the clean list of the names of the databases we want to backup to separate files. All we have to do is chain this command with a simple for..do loop that will execute mysqldump for each database:

for db in \ `mysql -u... -p... -e "show databases;" \ | tr -d "| " \ | egrep -v "(Database|mysql|information_schema)"`; \ do mysqldump -u... -p... --opt --routines --databases $db > "$db.sql"; done

1 2 3 4 5 6

for db in \   `mysql -u... -p... -e "show databases;" \   | tr -d "| "  \   | egrep -v "(Database|mysql|information_schema)"`; \   do  mysqldump -u... -p... --opt --routines --databases $db  > "$db.sql"; done

By executing the command above, you will create as many .sql dump files as the number of databases you have backed up.

Restoring from multiple dumps

If you dump each database to a different file, you can restore all these databases -should you need to restore all of them- this way:

for file in `ls *.sql`; do echo $file && mysql -u... -p... \ "`echo $file | sed "s/\.sql//"`" < $file; done

1 2 3

for file in `ls *.sql`; do  echo $file && mysql -u... -p...  \   "`echo $file | sed "s/\.sql//"`" < $file; done

Faster MySQL dumps and loads with –tab and –use-threads

By default, mysqldump writes a series of sql DDL and inserts to standard out, that you can then pipe to another database server to recreate a given database.
The problem is that this is all serial, and if you’re having to do this task regularly (because you’re sharing databases between different development environments, for example), it’d be nice if this could be sped up, and it certainly can with the current 5.1.x versions of MySQL.

MySQL recently added two new features that help with this: mysqldump --tab=path and mysqlimport --use-threads=N.

Dumping

Here’s how you’d dump multiple databases without –tab (assuming your ~/.my.cnf told mysqldump how to connect to your database server):

mysqldump --opt --all-databases | gzip > dump.sql.gz

1	mysqldump --opt --all-databases | gzip > dump.sql.gz

There are a bunch of caveats to using the --tab option:

• The mysqldump command must be run on the database server, because mysqldump will invoke SELECT INTO OUTFILE on the server.
• The FILE permission must be granted to the mysqldump user
• The directory needs to be writeable by the mysqld euid
• If you want to dump multiple databases, you’ll need to create a new directory per database so same-named tables won’t clobber eachother

In the interests of simplicity, I used globally-read-write permissions here. If untrusted users had access to these directories, these permissions would be unacceptable, of course.

dir=$(date "+%Y-%m-%d_%Hh%Mm") mkdir -m 777 -p /tmp/$dir for db in $(mysql -BNe "show databases" | grep -v information_schema ) ; do mkdir -m 777 /tmp/$dir/$db mysqldump --tab=/tmp/$dir/$db --opt --single-transaction --quick $db done

1 2 3 4 5 6

dir=$(date "+%Y-%m-%d_%Hh%Mm") mkdir -m 777 -p /tmp/$dir for db in $(mysql -BNe "show databases" | grep -v information_schema ) ; do   mkdir -m 777 /tmp/$dir/$db   mysqldump --tab=/tmp/$dir/$db --opt --single-transaction --quick $db done

Loading

Loading from a .sql.gz file is trivial — just pipe it to mysql and call it a day:

gzcat dump.sql.gz | mysql

1	gzcat dump.sql.gz | mysql

Loading from tab files is a bit more work. Note that this NUKES AND PAVES your database with the content of the dump–including the mysql users, their passwords, and their permissions! You’ll also want to play with –use-threads, depending on the number of processors your machine hardware has.

cd ${DUMP_DIR} # <- where DUMP_DIR is the database dump you want to load, like /tmp/$dir from above for db in * ; do mysql -e "drop database $db; create database $db default charset utf8" cat $db/*.sql | mysql $db mysqlimport --use-threads=3 --local $db $db/*.txt done

1 2 3 4 5 6

cd ${DUMP_DIR} # <- where DUMP_DIR is the database dump you want to load, like /tmp/$dir from above for db in * ; do   mysql -e "drop database $db; create database $db default charset utf8"   cat $db/*.sql | mysql $db   mysqlimport --use-threads=3 --local $db $db/*.txt done