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.