Percona XtraDB Cluster 5.5.33-23.7.6 is now available

Percona is glad to announce the release of Percona XtraDB Cluster 5.5.33-23.7.6 on September 18, 2013. Binaries are available from the downloads area or from our software repositories.

New Features:

  • Default State Snapshot Transfer method, defined in wsrep_sst_method variable has been changed from mysqldump to rsync.
  • New wsrep_reject_queries variable has been implemented that can be used to reject queries for that node. This can be useful if someone wants to manually run maintenance on the node like mysqldump without need to change the settings on the load balancer.
  • Variable wsrep_sst_donor has been extended to accept multiple hostnames that are preferred as State Snapshot Transfer donors. This can be helpful in case other nodes on the list go down or it can be used as a whitelist during automated provisioning.
  • Desync functionality has now been exposed to the client. This can be done either via /*! WSREP_DESYNC */ comment on the query or by setting the global wsrep_desync variable to 1.

Improvements to XtraBackup SST:

  • Percona XtraDB Cluster has implemented progress indicator for XtraBackup SST.
  • Percona XtraDB Cluster has implemented new rate limiting, rlimit, option for XtraBackup SST that can be used to avoid saturating the donor node.
  • Percona XtraDB Cluster has added new XtraBackup SST time option that can be used to see how much time different stages of State Snapshot Transfer are taking.
  • Percona XtraDB Cluster has implemented additional XtraBackup SST encryption option. Beside standard Percona XtraBackup encryption, new OpenSSL based encryption can be specified in the encrypt option.
  • XtraBackup SST now works in two stages. This was implemented to avoid issues like bug #1193240.

Bugs fixed:

  • When multiple slave threads were configured, if there was a query on different transaction that inserts a row, and a query on another transaction within the same thread id that depends on the first row (FK constraint), sometimes the second transaction would be executed first causing the Foreign Key violation. Bug fixed #1217653.
  • When variable wsrep_provider was set to none it would cause cluster to hang. Bug fixed #1208493.
  • Percona XtraDB Cluster would crash with message: Error "no such a transition EXECUTING -> COMMITTED" on the master node. This bug was fixed only for some cases. Bug fixed #1123233.
  • Running DDL commands while variable wsrep_OSU_method was set to TOI, Total Order Isolation, could lead to server deadlock. Bug fixed #1212955.
  • Stopping mysql process with inet script didn’t work if PID file was provided as pid_file option in the my.cnf configuration file. Bug fixed #1208865.
  • When read-only variable was set to ON, Percona XtraDB Cluster would block SELECT statements as well. Bug fixed #1091099.
  • In geo-DR setup using garbd, performance would degrade with node count when cross data center link was down. Bug fixed #1182960.
  • wsrep_recover was being run even if it wasn’t used. Script now checks if grastate.dat file has non-zero uuid and -1 seqno before it decides to start with wsrep_recover option. Bug fixed #1193781.
  • PID detection in the init script wasn’t working correctly if the PID file was specified with the relative path. Bug fixed #1194998.
  • State Snapshot Transfer authentication password was displayed in the ps output. Bug fixed #1200727.
  • Fixed the packaging issue caused by shared-compat linking. Bug fixed #1201393.
  • Fixed the platform dependent code in wsrep which was required to make the code portable to MacOS X and FreeBSD. Bug fixed #1201893.
  • Percona XtraDB Cluster donor node would get stuck during the State Snapshot Transfer when the threadpool plugin was used. Bug fixed #1206565.
  • pyclustercheck script did not work correctly with HAProxy. Bug fixed #1210239.
  • pyclustercheck script didn’t work as expected when available node was a donor. Bug fixed #1211249.
  • New bootstrap method, bootstrap-pxc, which was introduced in 5.5.31-23.7.5 didn’t check if mysqld process was already running before starting the new process. Bug fixed #1211505.
  • When table was created with LIKE and the source table was temporary table, create statement would be replicated to the slave nodes where it couldn’t be applied because the temporary table wasn’t present on the slave nodes. This would cause other nodes to crash if there were later DML commands for this table. Bug fixed #1212247.
  • Non-unique indexes in a parent table (as referenced by some foreign key constraint), would be included in write set population. i.e. key values in all non-unique indexes will be appended in the write set’s key set. This could cause excessive multi-master conflicts, especially if the parent table has non-unique indexes with low selectivity. Bug fixed #1216805.
  • Added information to InnoDB status if the transaction is waiting on TOI. Bug fixed #1219856.
  • Binary build was linked against libssl.so.10 and libcrypto.so.10 which was making it hard to run on SUSE Linux Enterprise Server 11. Bug fixed #1213855.

Other bug fixes: bug fixed #1210638, bug fixed #1222777, bug fixed #1216904, bug fixed #1205467, bug fixed #1196898, bug fixed #1195355, bug fixed #1049599, bug fixed #1191395, bug fixed #1017526, bug fixed #1213073, bug fixed #1171759, bug fixed #1210618, bug fixed #1190756.

Known issues:

  • For Debian/Ubuntu users: Percona XtraDB Cluster 5.5.33-23.7.6 includes a new dependency, the socat package. If the socat is not previously installed, percona-xtradb-cluster-server-5.5 may be held back. In order to upgrade, you need to either install socat before running the apt-get upgrade or by writing the following command: apt-get install percona-xtradb-cluster-server-5.5. For Ubuntu users the socat package is in the universe repository, so the repository will have to be enabled in order to install the package.

Based on Percona Server 5.5.33-31.1 including all the bug fixes in it and on Codership wsrep API 5.5.33-23.7.6, Percona XtraDB Cluster 5.5.33-23.7.6 is now the current stable release. All of Percona’s software is open-source and free.

This is a General Availability release. We did our best to eliminate bugs and problems during the testing release, but this is a software, so bugs are expected. If you encounter them, please report them to our bug tracking system.

Release notes for Percona XtraDB Cluster 5.5.33-23.7.6 are available in our online documentation.

The post Percona XtraDB Cluster 5.5.33-23.7.6 is now available appeared first on MySQL Performance Blog.

Read more at: http://www.mysqlperformanceblog.com/

Let’s connect at MySQL Connect

MySQL ConnectLater this week I’m traveling to San Francisco to attend MySQL Connect – which looks like an exciting event to be at. I’m going to deliver two talks at this show, both on Sunday – MySQL Indexing Best Practices for MySQL 5.6 and Optimizing MySQL Configuration for MySQL 5.6. I will also be there for a Percona Toolkit BoF on Saturday and at our Booth (6004) during select expo hall hours.

What I’m looking forward to at this event the most is an update on MySQL technology both from Oracle’s engineering team as well as MySQL users. People from Facebook, Twitter, LinkedIn, Paypal and Mozilla will also be sharing their respective MySQL experiences at the show.

One of the talks I’m most excited about: Dimitri Kravchuk’s MySQL Performance Tuning and Best Practices tutorial on Monday is likely to be great. Dimitri always provides in-depth analyses and has a deep understanding of source of Performance Issues. Another is Performance Schema and ps_helper by Mark Leith. Chances are you do not know as much about MySQL 5.6 Performance schema as you should and Mark is the guy when it comes to practical use of Performance Schema. Another talk is What’s New in MySQL Database 5.7 is a presentation I hope to give us a good preview of MySQL Future.

See you in SF. Let’s Connect at MySQL Connect!

The post Let’s connect at MySQL Connect appeared first on MySQL Performance Blog.

Read more at: http://www.mysqlperformanceblog.com/

Percona XtraBackup 2.1.5 is now available

Percona XtraBackup for MySQL Percona is glad to announce the release of Percona XtraBackup 2.1.5 on September 19th, 2013. Downloads are available from our download site here and Percona Software Repositories.

This release is the current GA (Generally Available) stable release in the 2.1 series. Percona XtraBackup is an open source, free MySQL hot backup software that performs non-blocking backups for InnoDB and XtraDB databases. Percona XtraBackup is an open source, free MySQL hot backup software that performs non-blocking backups for InnoDB and XtraDB databases.

New Features:

  • Percona XtraBackup now supports new form of incremental backups for Percona Server 5.6 that uses Log Archiving for XtraDB feature.
  • Percona XtraBackup now supports new innobackupex –version-check option. When specified, innobackupex will perform a version check against the server on the backup stage after creating a server connection.

Bugs Fixed:

  • Percona XtraBackup did not close temporary files created when preparing a compact backup, which would lead to excessive disk space usage until the prepare process finished. Bug fixed #1111380.
  • Depending on the subroutine innobackupex could sometimes leave the child processes running in case of the error. innobackupex now makes sure that all child processes are killed if an error occurs in the script. Bug fixed #1135441.
  • The 5.6-based binary ( xtrabackup_56), which is used to backup both MySQL 5.6 and Percona Server 5.6 servers, did not support Percona Server-specific innodb_log_block_size option in Percona Server 5.6.11+ and would fail when trying to backup a server with a non-default innodb_log_block_size value. Bug fixed #1194828.
  • Percona XtraBackup would stop in case log block numbers had to wrap around, which only happens once per 1 GB of log writes, and the wrap-around point was between the last checkpoint and the current log tail at the time the backup starts. Bug fixed #1206309.
  • xtrabackup_56 binary would fail to create a suspend file, which would result in an error. Bug fixed #1210266.
  • Regression was introduced in Percona XtraBackup 2.1.4 which lead to cp utility being used to copy metadata files even if the innobackupex –rsync option was used. Bug fixed #1211263.

Other bugs fixed: bug fixed #1214272, bug fixed #1214730, bug fixed #1213102, bug fixed #1213036, bug fixed #1204045, bug fixed #1154476, bug fixed #1195402, bug fixed #1195055.

Release notes with all the bugfixes for Percona XtraBackup 2.1.5 are available in our online documentation. All of Percona‘s software is open source and free, all the details of the release can be found in the 2.1.5 milestone at Launchpad. Bugs can be reported on the launchpad bug tracker.

The post Percona XtraBackup 2.1.5 is now available appeared first on MySQL Performance Blog.

Read more at: http://www.mysqlperformanceblog.com/

InnoDB performance optimization basics (redux)

InnoDB performance optimization basicsI recently stumbled upon a post that Peter Zaitsev published back in 2007 titled “Innodb Performance Optimization Basics.” It’s a great post and reading it inspired me to examine what’s changed in the nearly six years that have followed in terms of MySQL, Percona Server – as well as in all of the other now-available infrastructures.

And a lot has in fact changed! In this post I am going to highlight most of the InnoDB parameters critical for InnoDB – specifically from a performance perspective. I’m a support engineer and I can tell you that Percona Support gets many questions related to the right sizing of basic InnoDB parameters.
So hopefully this post will help others with similar questions and issues.

Hardware:
For larger datasets, nowadays memory counted in hundreds of giga- and even in terabytes is not surprising. MySQL requires significant memory amounts in order to provide optimal performance. By caching hot datasets, indexes, and ongoing changes, InnoDB is able to provide faster response times and utilize disk IO in a much more optimal way. From a CPU standpoint, faster processors with many cores provide better throughput. CPUs with 32/64 cores or more are becoming common now, and the latest MySQL versions are able to utilize them much better then before. In terms of storage, SSD disks are replacing traditional spindles with great success, offering the best performance for the money. RAID 10 is still the most recommended level for most workloads, but first make sure your RAID controller is able to utilize the SSD drive’s performance and will not become the actual bottleneck. There are also many PCI-e Flash drives out there if you need even more IOPS.

Operating System:
Linux is the most common operating system for high performance MySQL servers. Make sure to use modern filesystems, like EXT4 or XFS on Linux, combined with the most recent kernel. Each of them has it’s own limits and advantages: for example XFS is fast in deleting large files, while EXT4 can provide better performance on fast SSD drives. Benchmark before you decide. Check this blog post to see how EXT4 can outperform 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. The default I/O scheduler in Linux is Completely Fair Queuing (CFQ), while Noop/Deadline will be much better in most cases.. Setting swappiness to zero is generally recommended for the MySQL dedicated host, which will lower the tendency of swapping. Make sure the MySQL host does not run out of memory. Swapping is bad for MySQL and defeats the purpose of caching in memory. To learn more about swapping, check this blog post

MySQL Innodb Settings
From 5.5 InnoDB is the default engine, so these parameters are even more important for performance than before. The most important ones are:

  • innodb_buffer_pool_size: InnoDB relies heavily on the buffer pool and should be set correctly, so be sure to allocate enough memory to it. Typically a good value is 70%-80% of available memory. More precisely, if you have RAM bigger than your dataset setting it bit larger should be appropriate with that keep in account of your database growth and re-adjust innodb buffer pool size accordingly. Further, there is improvement in code for InnoDB buffer scalability if you are using Percona Server 5.1 or Percona Server 5.5 You can read more about it here.
  • innodb_buffer_pool_instances: Multiple innodb buffer pools introduced in InnoDB 1.1 and MySQL 5.5. In MySQL 5.5 the default value for it was 1 which is changed to 8 as new default value in MySQL 5.6. Minimum innodb_buffer_pool_instances should be lie between 1 (minimum) & 64 (maximum). Enabling innodb_buffer_pool_instances is useful in highly concurrent workload as it may reduce contention of the global mutexes.
  • Dump/Restore Buffer Pool: This feature speed up restarts by saving and restoring the contents of the buffer pool. This feature is first introduced in Percona Server 5.5 you can read about it here. Also Vadim benchmark this feature You can read more about it in this post. Oracle MySQL also introduced it in version 5.6, To automatically dump the database at startup and shutdown set innodb_buffer_pool_dump_at_shutdown & innodb_buffer_pool_load_at_startup parameters to ON.
  • innodb_log_file_size: Large enough InnoDB transaction logs are crucial for good, stable write performance. But also larger log files means that recovery process will slower in case of crash. However this is not such big issue since great improvements in 5.5. Default value has been changed in MySQL 5.6 to 50 MB from 5 MB (old default), but it’s still too small size for many workloads. Also, in MySQL 5.6, if innodb_log_file_size is changed between restarts then MySQL will automatically resize the logs to match the new desired size during the startup process. Combined log file size is increased to almost 512 GB in MySQL 5.6 from 4 GB. To get the optimal logfile size please check this blog post.
  • innodb_log_buffer_size: Innodb writes changed data record into lt’s log buffer, which kept in memory and it saves disk I/O for large transactions as it not need to write the log of changes to disk before transaction commit. 4 MB – 8 MB is good start unless you write a lot of huge blobs.
  • innodb_flush_log_at_trx_commit: When innodb_flush_log_at_trx_commit is set to 1 the log buffer is flushed on every transaction commit to the log file on disk and provides maximum data integrity but it also has performance impact. Setting it to 2 means log buffer is flushed to OS file cache on every transaction commit. The implication of 2 is optimal and improve performance if you are not concerning ACID and can lose transactions for last second or two in case of OS crashes. 
  • innodb_thread_concurrency: With improvements to the InnoDB engine, it is recommended to allow the engine to control the concurrency by keeping it to default value (which is zero). If you see concurrency issues, you can tune this variable. A recommended value is 2 times the number of CPUs plus the number of disks. It’s dynamic variable means it can set without restarting MySQL server.
  • innodb_flush_method: DIRECT_IO relieves I/O pressure. Direct I/O is not cached, If it set to O_DIRECT avoids double buffering with buffer pool and filesystem cache. Given that you have hardware RAID controller and battery-backed write cache.
  • innodb_file_per_table: innodb_file_per_table is ON by default from MySQL 5.6. This is usually recommended as it avoids having a huge shared tablespace and as it allows you to reclaim space when you drop or truncate a table. Separate tablespace also benefits for Xtrabackup partial backup scheme.

Along with that, there are lot of enhancements for InnoDB, specifically in Percona Server 5.5 and in Oracle MySQL 5.6. Persistent optimizer statistics is one of the features first introduced in Percona Server 5.5 that requires the enabling of the innodb_use_sys_stats_table in XtraDB. You can read more about it here. This feature is now included in Oracle MySQL 5.6, too. In MySQL 5.6 persistent stats are stored in two new tables: mysql.innodb_index_stats and mysql.innodb_table_stats. Through this query plans are much more accurate and consistent. You can read more about it in documentation. Also Percona Server 5.5 introduced a Thread Pool feature which is ported from MariaDB. You can read more about it in this documentation. On a related note, I recommend reading this blog post from Vadim on the Thread Pool feature.

Percona Server free and open source. An enhanced drop in Oracle MySQL replacement and some of the mentioned features are only applicable to Percona Server.

There are bunch of other options which you may want to tune but in this post we focus only InnoDB specifically.

Application tuning for Innodb:
Especially when coming from a MyISAM background, there will be some changes you would like to make 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 should 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).

Conclusion:
We covered almost all basic and important InnoDB parameters, OS related tweaking and hardware for optimal MySQL server performance. By setting all mentioned variables appropriately certainly help to boost overall MySQL server performance.

The post InnoDB performance optimization basics (redux) appeared first on MySQL Performance Blog.

Read more at: http://www.mysqlperformanceblog.com/

Percona Server 5.6.13-60.6 fourth Release Candidate is now available

Percona Server version 5.6.13-60.6

Percona Server version 5.6.13-60.6

Percona is glad to announce the fourth Release Candidate of Percona Server 5.6.13-60.6 (downloads are available here and from the Percona Software Repositories).

Based on MySQL 5.6.13, including all the bug fixes in it, Percona Server 5.6.13-60.6 is the fourth RC release in the Percona Server 5.6 series. All of Percona‘s software is open-source and free, all the details of the release can be found in the 5.6.13-60.6 milestone at Launchpad.

New Features:

  • Improved Buffer Pool Scalability feature has been ported from Percona Server 5.5. This feature splits the single global InnoDB buffer pool mutex into several mutexes. The goal of this change is to reduce mutex contention, which can be very impacting when the working set does not fit in memory.
  • Multiple Adaptive Hash Search Partitions feature has been ported from Percona Server 5.5. This feature splits the adaptive hash index across several partitions and decreases the AHI latch contention. This feature fixes the upstream bug #62018 (#1216804).
  • Utility user feature has been extended by adding a new utility_user_privileges variable that allows a comma separated value list of extra access privileges that can be granted to the utility user.
  • Percona Server now provides additional information in the slow query log when log_slow_rate_limit variable is enabled.
  • A new variable slow_query_log_always_write_time has been introduced. It can be used to specify an additional execution time threshold for the slow query log, that, when exceeded, will cause a query to be logged unconditionally, that is, log_slow_rate_limit will not apply to it.

Bugs Fixed:

  • The unnecessary overhead from persistent InnoDB adaptive hash index latching has been removed, potentially improving stability of the Multiple Adaptive Hash Search Partitions feature as well. Upstream bug fixed #70216, bug fixed #1218347.
  • Adaptive hash index memory size was incorrectly calculated in SHOW ENGINE INNODB STATUS and XTRADB_INTERNAL_HASH_TABLES. Bug fixed #1218330.
  • An unnecessary buffer pool mutex acquisition has been removed, potentially improving performance. Upstream bug fixed #69258, bug fixed #1219842.
  • Fixed the build warnings caused by User Statistics code on non-Linux platforms. Bug fixed #711817.
  • Adaptive hash indexing partitioning code has been simplified, potentially improving performance. Bug fixed #1218321.

Other bugs fixed: upstream bug fixed #69617 bug fixed #1216815, upstream bug fixed #70228 bug fixed #1220544.

Release notes for Percona Server 5.6.13-60.6 are available in our online documentation. Bugs can be reported on the launchpad bug tracker.

The post Percona Server 5.6.13-60.6 fourth Release Candidate is now available appeared first on MySQL Performance Blog.

Read more at: http://www.mysqlperformanceblog.com/

Percona XtraDB Cluster: Setting up a simple cluster

Percona XtraDB Cluster (PXC) is different enough from async replication that it can be a bit of a puzzle how to do things the Galera way.  This post will attempt to illustrate the basics of setting up 2 node PXC cluster from scratch.

Requirements

Two servers (could be VMs) that can talk to each other.  I’m using CentOS for this post.  Here’s a dirt-simple Vagrant setup: https://github.com/jayjanssen/two_centos_nodes to make this easy (on Virtualbox).

These servers are talking over the 192.168.70.0/24 internal network for our example.

jayj@~/Src $ git clone https://github.com/jayjanssen/two_centos_nodes.git
jayj@~/Src $ cd two_centos_nodes
jayj@~/Src/two_centos_nodes $ vagrant up
 Bringing machine 'node1' up with 'virtualbox' provider...
 Bringing machine 'node2' up with 'virtualbox' provider...
 [node1] Importing base box 'centos-6_4-64_percona'...
 [node1] Matching MAC address for NAT networking...
 [node1] Setting the name of the VM...
 [node1] Clearing any previously set forwarded ports...
 [node1] Creating shared folders metadata...
 [node1] Clearing any previously set network interfaces...
 [node1] Preparing network interfaces based on configuration...
 [node1] Forwarding ports...
 [node1] -- 22 => 2222 (adapter 1)
 [node1] Booting VM...
 [node1] Waiting for machine to boot. This may take a few minutes...
 [node1] Machine booted and ready!
 [node1] Setting hostname...
 [node1] Configuring and enabling network interfaces...
 [node1] Mounting shared folders...
 [node1] -- /vagrant
 [node2] Importing base box 'centos-6_4-64_percona'...
 [node2] Matching MAC address for NAT networking...
 [node2] Setting the name of the VM...
 [node2] Clearing any previously set forwarded ports...
 [node2] Fixed port collision for 22 => 2222. Now on port 2200.
 [node2] Creating shared folders metadata...
 [node2] Clearing any previously set network interfaces...
 [node2] Preparing network interfaces based on configuration...
 [node2] Forwarding ports...
 [node2] -- 22 => 2200 (adapter 1)
 [node2] Booting VM...
 [node2] Waiting for machine to boot. This may take a few minutes...
 [node2] Machine booted and ready!
 [node2] Setting hostname...
 [node2] Configuring and enabling network interfaces...
 [node2] Mounting shared folders...
 [node2] -- /vagrant

Install the software

These steps should be repeated on both nodes:

jayj@~/Src/two_centos_nodes $ vagrant ssh node1
Last login: Tue Sep 10 14:15:50 2013 from 10.0.2.2
[[email protected] ~]# yum localinstall http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
Loaded plugins: downloadonly, fastestmirror, priorities
Setting up Local Package Process
percona-release-0.0-1.x86_64.rpm | 6.1 kB 00:00
Examining /var/tmp/yum-root-t61o64/percona-release-0.0-1.x86_64.rpm: percona-release-0.0-1.x86_64
Marking /var/tmp/yum-root-t61o64/percona-release-0.0-1.x86_64.rpm to be installed
Determining fastest mirrors
epel/metalink | 15 kB 00:00
* base: mirror.atlanticmetro.net
* epel: mirror.seas.harvard.edu
* extras: centos.mirror.netriplex.com
* updates: mirror.team-cymru.org
base | 3.7 kB 00:00
base/primary_db | 4.4 MB 00:01
epel | 4.2 kB 00:00
epel/primary_db | 5.5 MB 00:04
extras | 3.4 kB 00:00
extras/primary_db | 18 kB 00:00
updates | 3.4 kB 00:00
updates/primary_db | 4.4 MB 00:03
Resolving Dependencies
--> Running transaction check
---> Package percona-release.x86_64 0:0.0-1 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================================================================
Package Arch Version Repository Size
================================================================================================================================
Installing:
percona-release x86_64 0.0-1 /percona-release-0.0-1.x86_64 3.6 k
Transaction Summary
================================================================================================================================
Install 1 Package(s)
Total size: 3.6 k
Installed size: 3.6 k
Is this ok [y/N]: y
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : percona-release-0.0-1.x86_64 1/1
Verifying : percona-release-0.0-1.x86_64 1/1
Installed:
percona-release.x86_64 0:0.0-1
Complete!
[[email protected] ~]# yum install -y Percona-XtraDB-Cluster-server
Loaded plugins: downloadonly, fastestmirror, priorities
Loading mirror speeds from cached hostfile
* base: mirror.atlanticmetro.net
* epel: mirror.seas.harvard.edu
* extras: centos.mirror.netriplex.com
* updates: mirror.team-cymru.org
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package Percona-XtraDB-Cluster-server.x86_64 1:5.5.31-23.7.5.438.rhel6 will be installed
--> Processing Dependency: xtrabackup >= 1.9.0 for package: 1:Percona-XtraDB-Cluster-server-5.5.31-23.7.5.438.rhel6.x86_64
--> Processing Dependency: Percona-XtraDB-Cluster-client for package: 1:Percona-XtraDB-Cluster-server-5.5.31-23.7.5.438.rhel6.x86_64
--> Processing Dependency: libaio.so.1(LIBAIO_0.4)(64bit) for package: 1:Percona-XtraDB-Cluster-server-5.5.31-23.7.5.438.rhel6.x86_64
--> Processing Dependency: rsync for package: 1:Percona-XtraDB-Cluster-server-5.5.31-23.7.5.438.rhel6.x86_64
--> Processing Dependency: Percona-XtraDB-Cluster-galera for package: 1:Percona-XtraDB-Cluster-server-5.5.31-23.7.5.438.rhel6.x86_64
--> Processing Dependency: nc for package: 1:Percona-XtraDB-Cluster-server-5.5.31-23.7.5.438.rhel6.x86_64
--> Processing Dependency: Percona-XtraDB-Cluster-shared for package: 1:Percona-XtraDB-Cluster-server-5.5.31-23.7.5.438.rhel6.x86_64
--> Processing Dependency: libaio.so.1(LIBAIO_0.1)(64bit) for package: 1:Percona-XtraDB-Cluster-server-5.5.31-23.7.5.438.rhel6.x86_64
--> Processing Dependency: libaio.so.1()(64bit) for package: 1:Percona-XtraDB-Cluster-server-5.5.31-23.7.5.438.rhel6.x86_64
--> Running transaction check
---> Package Percona-XtraDB-Cluster-client.x86_64 1:5.5.31-23.7.5.438.rhel6 will be installed
---> Package Percona-XtraDB-Cluster-galera.x86_64 0:2.6-1.152.rhel6 will be installed
---> Package Percona-XtraDB-Cluster-shared.x86_64 1:5.5.31-23.7.5.438.rhel6 will be obsoleting
---> Package libaio.x86_64 0:0.3.107-10.el6 will be installed
---> Package mysql-libs.x86_64 0:5.1.69-1.el6_4 will be obsoleted
---> Package nc.x86_64 0:1.84-22.el6 will be installed
---> Package percona-xtrabackup.x86_64 0:2.1.4-656.rhel6 will be installed
--> Processing Dependency: perl(DBD::mysql) for package: percona-xtrabackup-2.1.4-656.rhel6.x86_64
--> Processing Dependency: perl(Time::HiRes) for package: percona-xtrabackup-2.1.4-656.rhel6.x86_64
---> Package rsync.x86_64 0:3.0.6-9.el6 will be installed
--> Running transaction check
---> Package perl-DBD-MySQL.x86_64 0:4.013-3.el6 will be installed
--> Processing Dependency: perl(DBI::Const::GetInfoType) for package: perl-DBD-MySQL-4.013-3.el6.x86_64
--> Processing Dependency: perl(DBI) for package: perl-DBD-MySQL-4.013-3.el6.x86_64
--> Processing Dependency: libmysqlclient.so.16(libmysqlclient_16)(64bit) for package: perl-DBD-MySQL-4.013-3.el6.x86_64
--> Processing Dependency: libmysqlclient.so.16()(64bit) for package: perl-DBD-MySQL-4.013-3.el6.x86_64
---> Package perl-Time-HiRes.x86_64 4:1.9721-131.el6_4 will be installed
--> Running transaction check
---> Package Percona-Server-shared-compat.x86_64 0:5.5.33-rel31.1.566.rhel6 will be obsoleting
---> Package perl-DBI.x86_64 0:1.609-4.el6 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================================================================
Package Arch Version Repository Size
================================================================================================================================
Installing:
Percona-Server-shared-compat x86_64 5.5.33-rel31.1.566.rhel6 percona 3.4 M
replacing mysql-libs.x86_64 5.1.69-1.el6_4
Percona-XtraDB-Cluster-server x86_64 1:5.5.31-23.7.5.438.rhel6 percona 15 M
Percona-XtraDB-Cluster-shared x86_64 1:5.5.31-23.7.5.438.rhel6 percona 648 k
replacing mysql-libs.x86_64 5.1.69-1.el6_4
Installing for dependencies:
Percona-XtraDB-Cluster-client x86_64 1:5.5.31-23.7.5.438.rhel6 percona 6.3 M
Percona-XtraDB-Cluster-galera x86_64 2.6-1.152.rhel6 percona 1.1 M
libaio x86_64 0.3.107-10.el6 base 21 k
nc x86_64 1.84-22.el6 base 57 k
percona-xtrabackup x86_64 2.1.4-656.rhel6 percona 6.8 M
perl-DBD-MySQL x86_64 4.013-3.el6 base 134 k
perl-DBI x86_64 1.609-4.el6 base 705 k
perl-Time-HiRes x86_64 4:1.9721-131.el6_4 updates 47 k
rsync x86_64 3.0.6-9.el6 base 334 k
Transaction Summary
================================================================================================================================
Install 12 Package(s)
Total download size: 35 M
Downloading Packages:
(1/12): Percona-Server-shared-compat-5.5.33-rel31.1.566.rhel6.x86_64.rpm | 3.4 MB 00:04
(2/12): Percona-XtraDB-Cluster-client-5.5.31-23.7.5.438.rhel6.x86_64.rpm | 6.3 MB 00:03
(3/12): Percona-XtraDB-Cluster-galera-2.6-1.152.rhel6.x86_64.rpm | 1.1 MB 00:00
(4/12): Percona-XtraDB-Cluster-server-5.5.31-23.7.5.438.rhel6.x86_64.rpm | 15 MB 00:04
(5/12): Percona-XtraDB-Cluster-shared-5.5.31-23.7.5.438.rhel6.x86_64.rpm | 648 kB 00:00
(6/12): libaio-0.3.107-10.el6.x86_64.rpm | 21 kB 00:00
(7/12): nc-1.84-22.el6.x86_64.rpm | 57 kB 00:00
(8/12): percona-xtrabackup-2.1.4-656.rhel6.x86_64.rpm | 6.8 MB 00:03
(9/12): perl-DBD-MySQL-4.013-3.el6.x86_64.rpm | 134 kB 00:00
(10/12): perl-DBI-1.609-4.el6.x86_64.rpm | 705 kB 00:00
(11/12): perl-Time-HiRes-1.9721-131.el6_4.x86_64.rpm | 47 kB 00:00
(12/12): rsync-3.0.6-9.el6.x86_64.rpm | 334 kB 00:00
--------------------------------------------------------------------------------------------------------------------------------
Total 1.0 MB/s | 35 MB 00:34
warning: rpmts_HdrFromFdno: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-percona
Importing GPG key 0xCD2EFD2A:
Userid : Percona MySQL Development Team <[email protected]>
Package: percona-release-0.0-1.x86_64 (@/percona-release-0.0-1.x86_64)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-percona
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : libaio-0.3.107-10.el6.x86_64 1/13
Installing : 1:Percona-XtraDB-Cluster-shared-5.5.31-23.7.5.438.rhel6.x86_64 2/13
Installing : 1:Percona-XtraDB-Cluster-client-5.5.31-23.7.5.438.rhel6.x86_64 3/13
Installing : Percona-XtraDB-Cluster-galera-2.6-1.152.rhel6.x86_64 4/13
Installing : nc-1.84-22.el6.x86_64 5/13
Installing : 4:perl-Time-HiRes-1.9721-131.el6_4.x86_64 6/13
Installing : perl-DBI-1.609-4.el6.x86_64 7/13
Installing : rsync-3.0.6-9.el6.x86_64 8/13
Installing : Percona-Server-shared-compat-5.5.33-rel31.1.566.rhel6.x86_64 9/13
Installing : perl-DBD-MySQL-4.013-3.el6.x86_64 10/13
Installing : percona-xtrabackup-2.1.4-656.rhel6.x86_64 11/13
Installing : 1:Percona-XtraDB-Cluster-server-5.5.31-23.7.5.438.rhel6.x86_64 12/13
ls: cannot access /var/lib/mysql/*.err: No such file or directory
ls: cannot access /var/lib/mysql/*.err: No such file or directory
130917 14:43:16 [Note] WSREP: Read nil XID from storage engines, skipping position init
130917 14:43:16 [Note] WSREP: wsrep_load(): loading provider library 'none'
130917 14:43:16 [Note] WSREP: Service disconnected.
130917 14:43:17 [Note] WSREP: Some threads may fail to exit.
130917 14:43:17 [Note] WSREP: Read nil XID from storage engines, skipping position init
130917 14:43:17 [Note] WSREP: wsrep_load(): loading provider library 'none'
130917 14:43:18 [Note] WSREP: Service disconnected.
130917 14:43:19 [Note] WSREP: Some threads may fail to exit.
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h node1 password 'new-password'
Alternatively you can run:
/usr/bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the manual for more instructions.
Please report any problems with the /usr/bin/mysqlbug script!
Percona recommends that all production deployments be protected with a support
contract (http://www.percona.com/mysql-suppport/) to ensure the highest uptime,
be eligible for hot fixes, and boost your team's productivity.
/var/tmp/rpm-tmp.rVkEi4: line 95: x0: command not found
Percona XtraDB Cluster is distributed with several useful UDFs from Percona Toolkit.
Run the following commands to create these functions:
mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"
mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"
mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"
See http://code.google.com/p/maatkit/source/browse/trunk/udf for more details
Erasing : mysql-libs-5.1.69-1.el6_4.x86_64 13/13
Verifying : 1:Percona-XtraDB-Cluster-server-5.5.31-23.7.5.438.rhel6.x86_64 1/13
Verifying : 1:Percona-XtraDB-Cluster-client-5.5.31-23.7.5.438.rhel6.x86_64 2/13
Verifying : perl-DBD-MySQL-4.013-3.el6.x86_64 3/13
Verifying : Percona-Server-shared-compat-5.5.33-rel31.1.566.rhel6.x86_64 4/13
Verifying : rsync-3.0.6-9.el6.x86_64 5/13
Verifying : perl-DBI-1.609-4.el6.x86_64 6/13
Verifying : percona-xtrabackup-2.1.4-656.rhel6.x86_64 7/13
Verifying : 1:Percona-XtraDB-Cluster-shared-5.5.31-23.7.5.438.rhel6.x86_64 8/13
Verifying : 4:perl-Time-HiRes-1.9721-131.el6_4.x86_64 9/13
Verifying : nc-1.84-22.el6.x86_64 10/13
Verifying : libaio-0.3.107-10.el6.x86_64 11/13
Verifying : Percona-XtraDB-Cluster-galera-2.6-1.152.rhel6.x86_64 12/13
Verifying : mysql-libs-5.1.69-1.el6_4.x86_64 13/13
Installed:
Percona-Server-shared-compat.x86_64 0:5.5.33-rel31.1.566.rhel6 Percona-XtraDB-Cluster-server.x86_64 1:5.5.31-23.7.5.438.rhel6
Percona-XtraDB-Cluster-shared.x86_64 1:5.5.31-23.7.5.438.rhel6
Dependency Installed:
Percona-XtraDB-Cluster-client.x86_64 1:5.5.31-23.7.5.438.rhel6 Percona-XtraDB-Cluster-galera.x86_64 0:2.6-1.152.rhel6
libaio.x86_64 0:0.3.107-10.el6 nc.x86_64 0:1.84-22.el6
percona-xtrabackup.x86_64 0:2.1.4-656.rhel6 perl-DBD-MySQL.x86_64 0:4.013-3.el6
perl-DBI.x86_64 0:1.609-4.el6 perl-Time-HiRes.x86_64 4:1.9721-131.el6_4
rsync.x86_64 0:3.0.6-9.el6
Replaced:
mysql-libs.x86_64 0:5.1.69-1.el6_4
Complete!

Disable IPtables and SElinux

It is possible to run PXC with these enabled, but for simplicity here we just disable them (on both nodes!):

[[email protected] ~]# echo 0 > /selinux/enforce
[[email protected] ~]# service iptables stop
iptables: Flushing firewall rules:                         [  OK  ]
iptables: Setting chains to policy ACCEPT: filter          [  OK  ]
iptables: Unloading modules:                               [  OK  ]

Configure the cluster nodes

Create a my.cnf file on each node and put this into it:

[mysqld]
datadir = /var/lib/mysql
binlog_format = ROW
wsrep_cluster_name = twonode
wsrep_cluster_address = gcomm://192.168.70.2,192.168.70.3
wsrep_node_address = 192.168.70.2
wsrep_provider = /usr/lib64/libgalera_smm.so
wsrep_sst_method = xtrabackup
wsrep_sst_auth = sst:secret
innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode = 2

Note that the wsrep_node_address should be the proper address on each node.  We only  need this because in this environment we are not using the default NIC.

Bootstrap node1

Bootstrapping is simply starting up the first node in the cluster.  Any data on this node is taken as the source of truth for the other nodes.

[[email protected] ~]# service mysql bootstrap-pxc
Bootstrapping PXC (Percona XtraDB Cluster)Starting MySQL (Percona XtraDB Cluster).. SUCCESS!
[[email protected] mysql]# mysql -e "show global status like 'wsrep%'"
+----------------------------+--------------------------------------+
| Variable_name              | Value                                |
+----------------------------+--------------------------------------+
| wsrep_local_state_uuid     | 43ac4bea-1fa8-11e3-8496-070bd0e5c133 |
| wsrep_protocol_version     | 4                                    |
| wsrep_last_committed       | 0                                    |
| wsrep_replicated           | 0                                    |
| wsrep_replicated_bytes     | 0                                    |
| wsrep_received             | 2                                    |
| wsrep_received_bytes       | 133                                  |
| wsrep_local_commits        | 0                                    |
| wsrep_local_cert_failures  | 0                                    |
| wsrep_local_bf_aborts      | 0                                    |
| wsrep_local_replays        | 0                                    |
| wsrep_local_send_queue     | 0                                    |
| wsrep_local_send_queue_avg | 0.000000                             |
| wsrep_local_recv_queue     | 0                                    |
| wsrep_local_recv_queue_avg | 0.000000                             |
| wsrep_flow_control_paused  | 0.000000                             |
| wsrep_flow_control_sent    | 0                                    |
| wsrep_flow_control_recv    | 0                                    |
| wsrep_cert_deps_distance   | 0.000000                             |
| wsrep_apply_oooe           | 0.000000                             |
| wsrep_apply_oool           | 0.000000                             |
| wsrep_apply_window         | 0.000000                             |
| wsrep_commit_oooe          | 0.000000                             |
| wsrep_commit_oool          | 0.000000                             |
| wsrep_commit_window        | 0.000000                             |
| wsrep_local_state          | 4                                    |
| wsrep_local_state_comment  | Synced                               |
| wsrep_cert_index_size      | 0                                    |
| wsrep_causal_reads         | 0                                    |
| wsrep_incoming_addresses   | 192.168.70.2:3306                    |
| wsrep_cluster_conf_id      | 1                                    |
| wsrep_cluster_size         | 1                                    |
| wsrep_cluster_state_uuid   | 43ac4bea-1fa8-11e3-8496-070bd0e5c133 |
| wsrep_cluster_status       | Primary                              |
| wsrep_connected            | ON                                   |
| wsrep_local_index          | 0                                    |
| wsrep_provider_name        | Galera                               |
| wsrep_provider_vendor      | Codership Oy <[email protected]>    |
| wsrep_provider_version     | 2.6(r152)                            |
| wsrep_ready                | ON                                   |
+----------------------------+--------------------------------------+

We can see the cluster is Primary, the size is 1, and our local state is Synced.  This is a one node cluster!

Prep for SST

SST is how new nodes (post-bootstrap) get a copy of data when joining the cluster.  It is in essence (and reality) a full backup.  We specified Xtrabackup as our backup and a username/password (sst:secret).  We need to setup a GRANT on node1 so we can run Xtrabackup against it to SST node2:

[[email protected] ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 4
Server version: 5.5.31 Percona XtraDB Cluster (GPL), wsrep_23.7.5.r3880
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sst'@'localhost' IDENTIFIED BY 'secret';
Query OK, 0 rows affected (0.00 sec)

This GRANT should not be necessary to re-issue more than once if you are adding more nodes to the cluster.

Start node2

Assuming you’ve installed the software and my.cnf on node2, then it should be ready to start up:

[[email protected] ~]# service mysql start
Starting MySQL (Percona XtraDB Cluster).....SST in progress, setting sleep higher. SUCCESS!

If we check the status of the cluster again:

[[email protected] mysql]# mysql -e "show global status like 'wsrep%'"
+----------------------------+--------------------------------------+
| Variable_name              | Value                                |
+----------------------------+--------------------------------------+
| wsrep_local_state_uuid     | 43ac4bea-1fa8-11e3-8496-070bd0e5c133 |
| wsrep_protocol_version     | 4                                    |
| wsrep_last_committed       | 0                                    |
| wsrep_replicated           | 0                                    |
| wsrep_replicated_bytes     | 0                                    |
| wsrep_received             | 6                                    |
| wsrep_received_bytes       | 393                                  |
| wsrep_local_commits        | 0                                    |
| wsrep_local_cert_failures  | 0                                    |
| wsrep_local_bf_aborts      | 0                                    |
| wsrep_local_replays        | 0                                    |
| wsrep_local_send_queue     | 0                                    |
| wsrep_local_send_queue_avg | 0.000000                             |
| wsrep_local_recv_queue     | 0                                    |
| wsrep_local_recv_queue_avg | 0.000000                             |
| wsrep_flow_control_paused  | 0.000000                             |
| wsrep_flow_control_sent    | 0                                    |
| wsrep_flow_control_recv    | 0                                    |
| wsrep_cert_deps_distance   | 0.000000                             |
| wsrep_apply_oooe           | 0.000000                             |
| wsrep_apply_oool           | 0.000000                             |
| wsrep_apply_window         | 0.000000                             |
| wsrep_commit_oooe          | 0.000000                             |
| wsrep_commit_oool          | 0.000000                             |
| wsrep_commit_window        | 0.000000                             |
| wsrep_local_state          | 4                                    |
| wsrep_local_state_comment  | Synced                               |
| wsrep_cert_index_size      | 0                                    |
| wsrep_causal_reads         | 0                                    |
| wsrep_incoming_addresses   | 192.168.70.3:3306,192.168.70.2:3306  |
| wsrep_cluster_conf_id      | 2                                    |
| wsrep_cluster_size         | 2                                    |
| wsrep_cluster_state_uuid   | 43ac4bea-1fa8-11e3-8496-070bd0e5c133 |
| wsrep_cluster_status       | Primary                              |
| wsrep_connected            | ON                                   |
| wsrep_local_index          | 1                                    |
| wsrep_provider_name        | Galera                               |
| wsrep_provider_vendor      | Codership Oy <[email protected]>    |
| wsrep_provider_version     | 2.6(r152)                            |
| wsrep_ready                | ON                                   |
+----------------------------+--------------------------------------+

We can see that there are now 2 nodes in the cluster!

The network connection is established over the default Galera port of 4567:

[[email protected] ~]# netstat -ant
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address               Foreign Address             State
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN
tcp        0      0 0.0.0.0:22                  0.0.0.0:*                   LISTEN
tcp        0      0 0.0.0.0:4567                0.0.0.0:*                   LISTEN
tcp        0      0 10.0.2.15:22                10.0.2.2:61129              ESTABLISHED
tcp        0    108 192.168.70.2:4567           192.168.70.3:50170          ESTABLISHED
tcp        0      0 :::22                       :::*                        LISTEN

Summary

In these steps we:

  • Installed PXC server package and dependencies
  • Did the bare-minimum configuration to get it started
  • Bootstrapped the first node
  • Prepared for SST
  • Started the second node (SST was copied by netcat over port 4444)
  • Confirmed both nodes were in the cluster

The setup can certainly be more involved in this, but this gives a simple illustration at what it takes to get things rolling.

The post Percona XtraDB Cluster: Setting up a simple cluster appeared first on MySQL Performance Blog.

Read more at: http://www.mysqlperformanceblog.com/

MySQL 5.6 Configuration Optimization Webinar, Sept. 25

Webinar: MySQL 5.6 Configuration OptimizationThis Wednesday in our next webinar I’ll share how to configure a better-performing MySQL 5.6 server. You’ll lean a practical approach to generating a sensible configuration file that sets what is needed and omits what is not.

Why dedicate an entire webinar to the new configuration settings within MySQL 5.6? Mainly because the default configuration files that come with MySQL 5.6 are not designed for high volume production use, and I’ve seen many MySQL incidents caused by poor configuration. Hopefully my advice will save you the headache of tweaking the variables within MySQL’s configuration files in order to work within your organization’s unique business environment.

And while I’ll be doing most of the talking, I do look forward to your questions during the webinar, titled “MySQL 5.6 Configuration Optimization.” It will begin at 10 a.m. Pacific time and run for about an hour. So please tune in on Wednesday, Sept. 25 at 10 a.m. You can register here to reserve your spot.

I welcome your questions both during the webinar and in the comments section below. See you Wednesday!

The post MySQL 5.6 Configuration Optimization Webinar, Sept. 25 appeared first on MySQL Performance Blog.

Read more at: http://www.mysqlperformanceblog.com/

How to reclaim space in InnoDB when innodb_file_per_table is ON

When innodb_file_per_table is OFF and all data is going to be stored in ibdata files. If you drop some tables of delete some data then there is no any other way to reclaim that unused disk space except dump/reload method.

When Innodb_file_per_table is ON, each table stores data and indexes in it’s own tablespace file. However, the shared tablespace-ibdata1 can still grow and you can check more information here about why it grows and what are the solutions.

http://www.mysqlperformanceblog.com/2013/08/20/why-is-the-ibdata1-file-continuously-growing-in-mysql/

Following the recent blog post from Miguel Angel Nieto titled “Why is the ibdata1 file continuously growing in MySQL?“, and since this is a very common question for Percona Support, this post covers how to reclaim the space when we are using innodb_file_per_table. Also, I will show you how to do it without causing performance or availability problems with the help of our Percona Toolkit.

When you remove rows, they are just marked as deleted on disk but space will be consumed by InnoDB files which can be re-used later when you insert/update more rows but it will never shrink. Very old MySQL bug : http://bugs.mysql.com/bug.php?id=1341

But, if you are using innodb_file_per_table then you can reclaim the space by running OPTIMIZE TABLE on that table. OPTIMIZE TABLE will create a new identical empty table. Then it will copy row by row data from old table to the new one. In this process a new .ibd tablespace will be created and the space will be reclaimed

mysql> select count(*) From test;
+----------+
| count(*) |
+----------+
| 3145728 |
+----------+
[email protected]:/var/lib/mysql/mydb# ll -h
...
-rw-rw---- 1 mysql mysql 168M Sep 5 11:52 test.ibd
mysql> delete from test limit 2000000;
mysql> select count(*) From test;
+----------+
| count(*) |
+----------+
| 1145728 |
+----------+
[email protected]:/var/lib/mysql/mydb# ll -h
...
-rw-rw---- 1 mysql mysql 168M Sep 5 11:52 test.ibd

You can see that after deleting 2M records, the test.ibd size was 168M.

mysql> optimize table test;
+-----------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------+----------+----------+-------------------------------------------------------------------+
| mydb.test | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| mydb.test | optimize | status | OK |
+-----------+----------+----------+-------------------------------------------------------------------+
[email protected]:/var/lib/mysql/mydb# ll -h
...
-rw-rw---- 1 mysql mysql 68M Sep 5 12:47 test.ibd

After OPTIMIZE, you will be able to reclaim the space. As you can see, test.ibd file size is decreased from 168M to 68M.

I would like to mention here that during that process the table will be locked.(Table locked for just Writes) Which can affect to the performance when you’ll have large table. So If you don’t want to lock the table then you can use one of the best utility by Percona, pt-online-schema-change. It can ALTER without locking tables. You can run ALTER TABLE with ENGINE=INNODB which will re-create the table and reclaim the space.

(It’s always recommended to use latest version of pt-* utilities)

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 2991456 |
+----------+
mysql> delete from test limit 2000000;
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 991456 |
+----------+
[email protected]:/var/lib/mysql/mydb# ll -h
...
-rw-rw---- 1 mysql mysql 157M Sep 6 11:52 test.ibd
[email protected]:~$ pt-online-schema-change --alter "ENGINE=InnoDB" D=mydb,t=test --execute
Operation, tries, wait:
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `mydb`.`test`...
Creating new table...
Created new table mydb._test_new OK.
Altering new table...
Altered `mydb`.`_test_new` OK.
2013-09-06T15:37:46 Creating triggers...
2013-09-06T15:37:47 Created triggers OK.
2013-09-06T15:37:47 Copying approximately 991800 rows...
Copying `mydb`.`test`: 96% 00:01 remain
2013-09-06T15:38:17 Copied rows OK.
2013-09-06T15:38:17 Swapping tables...
2013-09-06T15:38:18 Swapped original and new tables OK.
2013-09-06T15:38:18 Dropping old table...
2013-09-06T15:38:18 Dropped old table `mydb`.`_test_old` OK.
2013-09-06T15:38:18 Dropping triggers...
2013-09-06T15:38:18 Dropped triggers OK.
Successfully altered `mydb`.`test`.
[email protected]:/var/lib/mysql/mydb# ll -h
...
-rw-rw---- 1 mysql mysql 56M Sep 6 15:38 test.ibd

Same here, you can notice that test.ibd file size decreased from 157M to 56M.

NOTE: Please make sure that you have ample space before you run pt-online-schema-change because it will create a temporary table that contains roughly the size of the original table. By running this on the primary node, the changes will be replicated to your slaves.

The post How to reclaim space in InnoDB when innodb_file_per_table is ON appeared first on MySQL Performance Blog.

Read more at: http://www.mysqlperformanceblog.com/

Experimental Git mirror of Oracle MySQL trees

I’ve been working on setting up mirrors on github of all our BZR branches. My first efforts that are at a suitable stage to share are mirrors of the Oracle MySQL trees. This is currently a snapshot of MySQL 5.1, 5.5 and 5.6 with all the tags preserved. I’ve managed to get GIT to compact down the repository to a mere 177MB on disk for all the history, which is rather impressive.

Go check it out: https://github.com/percona/mysql

This should be considered experimental and I may end up pushing up something better at some point soon – i.e. don’t rely on being able to merge later update (think rebase rather than merge).

The post Experimental Git mirror of Oracle MySQL trees appeared first on MySQL Performance Blog.

Read more at: http://www.mysqlperformanceblog.com/

Experimental GIT Mirrors of Percona XtraBackup, Percona Server plus Oracle MySQL trees

I recently blogged on setting up Experimental Git mirror of Oracle MySQL trees up on GitHub. I’m now happy to announce that there are also mirrors for:

I’ve also updated the Oracle MySQL GIT mirror to include MySQL 5.7 and the (now abandoned) MySQL 6.0. I include the abandoned 6.0 tree as it can provide useful archaeology as to where some code came from.

I’d love to hear about any positive/negative experiences using these mirrors. Hopefully shortly I’ll fix up the last two bits of missing metadata in the transfer: bzr branch names for each commit and the “bugs fixed” by each commit (what’s added with “bzr commit –fixes lp:1234″).

The post Experimental GIT Mirrors of Percona XtraBackup, Percona Server plus Oracle MySQL trees appeared first on MySQL Performance Blog.

Read more at: http://www.mysqlperformanceblog.com/