MySQL

Creating GEO-enabled applications with MySQL 5.6

MySQL Performance Blog - Mon, 2014-03-24 13:13

In my previous post I’ve showed some new MySQL 5.6 features which can be very helpful when creating geo-enabled applications. In this post I will show how we can obtain open-source GIS data, convert it to MySQL and use it in our GEO-enabled applications. I will also present at the upcoming Percona Live conference on this topic.

Data sources (US)

For the U.S. we may look at 2 major data sources:

1. ZIP codes with latitude, longitude and zip code boundaries (polygon). This can be downloaded from the U.S. Census website: US Zipcodes direct link

2. Point of interests, roads, boundaries, etc. The Openstreatmap website provides an excellent source of the GIS data. North American data can be downloaded here (updates frequently)

Data formats and conversion

U.S. Census data is stored in Shapefile (.shp, .shx, .dbf) format. Openstreetmap uses its own XML format (OSM)  and/or Protocolbuffer Binary Format. We can convert this to MySQL with GDAL server (on Linux) and ogr2ogr utility. To convert Shapefile any version of GDAL will work, however, for OSM/PBF we will need to use v. 1.10. The easiest way to get the GDAL 1.10 is to use Ubuntu + ubuntugis-unstable repo.

Here are the commands I’ve used to install:

apt-add-repository ppa:ubuntugis/ubuntugis-unstable apt-get update apt-get install gdal-bin

This will install gdal server. Make sure it is latest version and support OSM format:

ogr2ogr --version GDAL 1.10.1, released 2013/08/26 ogrinfo --formats|grep OSM -> "OSM" (readonly)

Now we can convert it to MySQL. First, make sure MySQL has the default storage engine = MyISAM (yes, GDAL will use MyISAM to be able to add a spatial index) and the max_allowed_packet is large enough:

mysql -e "set global max_allowed_packet = 16777216*10; set global default_storage_engine = MyISAM; "

ZIP codes and boundaries conversion

Now we can start conversion:

# ogr2ogr -overwrite -progress -f "MySQL" MYSQL:zcta,user=root tl_2013_us_zcta510.shp 0...10...20...30...40...50...60...70...80...90...100 - done.

The only thing we need to specify is db name and user name (assuming it will write to the localhost, otherwise specify the MySQL host). ogr2org will create all needed tables.

mysql> use zcta Database changed mysql> show tables; +--------------------+ | Tables_in_zcta | +--------------------+ | geometry_columns | | spatial_ref_sys | | tl_2013_us_zcta510 | +--------------------+ 3 rows in set (0.00 sec)

The  geometry_columns and spatial_ref_sys are the reference tables only. All zip codes and boundaries will be stored in tl_2013_us_zcta510 table:

mysql> show create table tl_2013_us_zcta510\G *************************** 1. row *************************** Table: tl_2013_us_zcta510 Create Table: CREATE TABLE `tl_2013_us_zcta510` ( `OGR_FID` int(11) NOT NULL AUTO_INCREMENT, `SHAPE` geometry NOT NULL, `zcta5ce10` varchar(5) DEFAULT NULL, `geoid10` varchar(5) DEFAULT NULL, `classfp10` varchar(2) DEFAULT NULL, `mtfcc10` varchar(5) DEFAULT NULL, `funcstat10` varchar(1) DEFAULT NULL, `aland10` double DEFAULT NULL, `awater10` double DEFAULT NULL, `intptlat10` varchar(11) DEFAULT NULL, `intptlon10` varchar(12) DEFAULT NULL, UNIQUE KEY `OGR_FID` (`OGR_FID`), SPATIAL KEY `SHAPE` (`SHAPE`) ) ENGINE=MyISAM AUTO_INCREMENT=33145 DEFAULT CHARSET=latin1 1 row in set (0.00 sec)

Example 1. Selecting zip code boundaries for a given zipcode (Durham, NC):

mysql> select astext(shape) from zcta.tl_2013_us_zcta510 where zcta5ce10='27701'\G *************************** 1. row *************************** astext(shape): POLYGON((-78.902351 35.988107,-78.902436 35.988116,-78.902597 35.98814,-78.902725 35.988147,-78.902992 35.988143,-78.903117 35.988129,... -78.902351 35.988107))

Example 2. Find ZIP code for the given point (Lat, Lon): Percona HQ in Durham, NC

mysql> SELECT zcta5ce10 as ZIP FROM tl_2013_us_zcta510 WHERE st_contains(shape, POINT(-78.90423, 36.004122)); +-------+ | ZIP | +-------+ | 27701 | +-------+ 1 row in set (0.00 sec)

Converting OpenStreetMap (OSM) data 

Converting OSM is the same:

ogr2ogr -overwrite -progress -f "MySQL" MYSQL:osm,user=root north-america-latest.osm.pbf

Please note, that it will take a long time to convert (8-12+ hours, depends upon the hardware).

Tables:

mysql> use osm Database changed mysql> show tables; +------------------+ | Tables_in_osm | +------------------+ | geometry_columns | | lines | | multilinestrings | | multipolygons | | other_relations | | points | | spatial_ref_sys | +------------------+ 7 rows in set (0.00 sec)

Points of interest are stored in “points” table. “Lines” and “multilinestrings” tables contain streets, hiking trails, bike paths, etc:

mysql> show create table points\G *************************** 1. row *************************** Table: points Create Table: CREATE TABLE `points` ( `OGR_FID` int(11) NOT NULL AUTO_INCREMENT, `SHAPE` geometry NOT NULL, `osm_id` text, `name` text, `barrier` text, `highway` text, `ref` text, `address` text, `is_in` text, `place` text, `man_made` text, `other_tags` text, UNIQUE KEY `OGR_FID` (`OGR_FID`), SPATIAL KEY `SHAPE` (`SHAPE`) ) ENGINE=MyISAM AUTO_INCREMENT=13660668 DEFAULT CHARSET=latin1 1 row in set (0.00 sec)

“Shape” is the point (in spatial format) and other_tags will contain some additional format (in JSON format), this is how ogr2ogr converts it by default. See the GDAL documentation on the OSM driver for more information.

OSM data may contain the zip code, but this is not guaranteed.  Here is the example how we can find all coffee shops in ZIP code 27701:

mysql> select shape into @shape from zcta.tl_2013_us_zcta510 where zcta5ce10='27701'; Query OK, 1 row affected (0.00 sec) mysql> SELECT name, st_distance(shape, centroid(@shape) ) as dist FROM points WHERE st_within(shape, @shape) and other_tags like '%"amenity"=>"cafe"%' limit 10; +--------------------+----------------------+ | name | dist | +--------------------+----------------------+ | Blue Coffee Cafe | 0.00473103443182092 | | Amelia Cafe | 0.013825134250907745 | | Serrano's Delicafe | 0.013472792849827055 | | Blend | 0.009123578862847042 | +--------------------+----------------------+ 4 rows in set (0.09 sec)

First, I have selected the ZIP code boundaries into MySQL variable (I could have used subquery, in MySQL 5.6 the performance will be very similar; this is a little bit outside of the current blog post topic, so I will not compare the 2 methods here).

Second I’ve used this variable to find all point which will fit into our boundaries and filter by ”amenity”=>”cafe”. I have to use like ‘%..%’ here, but I’m relying on the spatial index here. Explain plan:

mysql> explain SELECT name, st_distance(shape, centroid(@shape) ) as dist FROM osm.points WHERE st_within(shape, @shape) and other_tags like '%"amenity"=>"cafe"%' limit 10\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: points type: range possible_keys: SHAPE key: SHAPE key_len: 34 ref: NULL rows: 10 Extra: Using where

Conclusion

Using open source spatial data is a great way to enrich your application and add new features. You can store this data in MySQL so the application will be able to perform a join to the existing data. For example, if you store ZIP code for a user you can use OpenStreetMap data to show the appropriate content for this user. I will also provide more examples in my upcoming Talk @ Percona Live 2014 as well as share it in this blog in a future post.

I’ve also created a Public Amazon AMI: GIS-MySQL-Ubuntu - ami-ddfdf5b4. The AMI has the ZIP code and OSM data in MySQL 5.6 as well as the GDAL server installed (under /data, mounted on EBS). Please feel free to give it a try. As always I appreciate any comments/questions/thoughts/etc.

The post Creating GEO-enabled applications with MySQL 5.6 appeared first on MySQL Performance Blog.

Categories: MySQL

Notes from the Newb

MySQL Performance Blog - Fri, 2014-03-21 23:20

Notes from the Newb.

I’m relatively new to MySQL having come from the world of embedded micro-databases, and though I’m pretty familiar with a number of database systems, I’ve discovered that I have a lot to learn about MySQL.

As a new member to the Percona team, I thought I’d have an ongoing blog theme titled “Notes from the Noob” and discuss common problems that our consultants have encountered when working with customers who are also new to MySQL. To which end, I’m going to make the assumptions that (1) you use InnoDB as your engine of choice (a database that ain’t ACID ain’t no database) and that (2) you are using the latest release, 5.6.

So last week I polled a number of our senior consultants and asked them, what are the most common mistakes made by our customers who are also new to MySQL? Overwhelming, I heard back that it was using the default settings and not configuring my.cnf to their specific workload. Having said that, one of the dangers of MySQL is that because there are literally hundreds of configurable parameters that can be set, one is inclined to start messing around and thus ending up with worse performance, rather than better.

The key to working with the MySQL configuration file is that a light touch is recommended, and generally only a handful of parameters need be re-defined.

So what are some of more important parameters one should consider changing from the default values?

First and foremost is the system variable innodb_buffer_pool_size. This defines the size of the memory pool used for caching InnoDB tables and indices and its default size is a piddling 128 MB. On a dedicated server, it’s generally a good idea to set this at 70-80% of available memory but remember that InnoDB will take up to an additional 10% for buffers and control structures. The idea is that the more memory allocated to the database, the less I/O you can expect when accessing the same data. Other issues you might want to consider when defining this value is the size of your dataset and whether you will have multiple instances of MySQL running. Be careful though if you plan to have your entire LAMP stack on a single machine, you might end up having competition for memory resources resulting in paging in the operating system thus negating the whole purpose of reducing IO.

Next up would be the system variable, innodb_log_buffer_size which can be important if you expect to be committing large transactions. The idea is that if you do have large transactions, setting this variable high enough will prevent the requirement of writing the log to disk before the transaction is committed. The default value is 8 MB, but if you expect to have larger transactions, you should definitely consider upping the value.

As expected, the innodb_log_file_size represents the size of the log files. The larger the value, the less checkpoint flush activity is needed in the buffer pool thus reducing disk IO. The downside however to a large value is that crash recovery can be slower. The default value is 48MB. As usual, you should do a reality check with regards to your actual workload. If your system is doing mostly reads, you may not need a large value, whereas if you’re storing blobs (which by the way, is generally not a good idea) you may want to have a larger value.

Another system variable to consider is the innodb_flush_log_at_trx_commit, which by default is set to 1, which means that the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. This is great if you can’t risk losing any data, however it can also come with a serious performance penalty. If you’re able to assume more risk and are able to lose up to a second of data, you might want to consider setting this to either 0 or 2. In the case of small ‘write’ transactions, this can provide significant performance improvements.

If you don’t have many tables in your database, you should be aware of the innodb_file_per_table, which as of 5.6, has a default value of ON. This results in MySQL assigning a separate .ibd file for each table in the database. There are a number of reasons why this can be beneficial, but the primary being that you are able to reclaim disk space. One of the quirks of InnoDB tablespace is that when a record is deleted, disk space is not reclaimed. So how are you able to get back that disk space you ask? Let’s say you have a table called biggiesmall and you have deleted 90% of the records. Simple execute the query, ALTER TABLE biggiesmall ENGINE=InnoDB. This will result in a temporary file being created with only the undeleted records. Once the process in completed, the original table is removed and the temporary table is renamed as the original table.

And finally, the best settings are based upon actual runtime statistics. Monitor and record what happens with your database and base your settings upon real numbers.

The post Notes from the Newb appeared first on MySQL Performance Blog.

Categories: MySQL

Percona Monitoring Plugins 1.1.3. Addressed CVE-2014-2569.

MySQL Performance Blog - Fri, 2014-03-21 09:01

Percona is glad to announce the release of Percona Monitoring Plugins 1.1.3.

Changelog:

* Introduced more secure location of PHP script configs to harden a Cacti setup
* Addressed CVE-2014-2569

We have introduced a more secure location /etc/cacti/ for PHP script configs. Earlier, the only way was to keep .php.cnf configs inside of scripts/ folder which falls under the web directory of Cacti setup, thus provides a potential security vulnerability. We strongly recommend to move all .php.cnf files from /usr/share/cacti/scripts/ to /etc/cacti/ and also harden your Cacti setup.

A new tarball is available from downloads area or RPM and DEB packages from our software repositories. The plugins are fully supported for customers with a Percona Support contract and free installation services are provided as part of some contracts. In addition as part of Percona’s Remote DBA installation and setup of these tools are included with our services. You can find links to the documentation, forums and more at the project homepage.

About Percona Monitoring Plugins
Percona Monitoring Plugins are high-quality components to add enterprise-grade MySQL monitoring and graphing capabilities to your existing in-house, on-premises monitoring solutions. The components are designed to integrate seamlessly with widely deployed solutions such as Nagios, Cacti and Zabbix and are delivered in the form of templates, plugins, and scripts which make it easy to monitor MySQL performance.

The post Percona Monitoring Plugins 1.1.3. Addressed CVE-2014-2569. appeared first on MySQL Performance Blog.

Categories: MySQL

Percona XtraDB Cluster 5.6.15-25.5 is now available

MySQL Performance Blog - Thu, 2014-03-20 14:36


Percona is glad to announce the new release of Percona XtraDB Cluster 5.6 on March 20th 2014. Binaries are available from downloads area or from our software repositories.

Based on Percona Server 5.6.15-63.0 including all the bug fixes in it, Galera Replicator 3.4 and on Codership wsrep API 25.5 is now the current General Availability release in 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.15-25.5 milestone on Launchpad.

New Features

  • wsrep patch did not allow server to start with Query Cache enabled. This restriction and check have been removed now and query cache can be fully enabled from config file.
  • New SST options have been implemented: inno-backup-opts, inno-apply-opts, inno-move-opts which pass options to backup, apply and move stages of innobackupex.
  • The joiner would wait and not fall back to choosing other potential donor nodes (not listed in wsrep_sst_donor) by their state. This happened even when comma was added at the end. This fixes it for that particular case. Bug fixed #1285380.
  • Initial configurable timeout, of 100 seconds, to receive a first packet via SST has been implemented, so that if donor dies somewhere in between, joiner doesn’t hang. Timeout can be configured with the sst-initial-timeout variable.

Bugs fixed

  • Replication of partition tables without binlogging enabled failed, partition truncation didn’t work because of lack of TO isolation there. Bug fixed #1219605.
  • Using LOAD DATA INFILE in with autocommit set to 0 and wsrep_load_data_splitting set to ON could lead to incomplete loading of records while chunking. Bug fixed #1281810.
  • Garbd could crash on CentOS if variable gmcast.listen_addr wasn’t set. Bug fixed #1283100.
  • Node couldn’t be started with wsrep_provider_options option debug set to 1. Bug fixed #1285208.
  • Boostrapping a node in a NON-PRIMARY state would lead to crash. Bug fixed #1286450.
  • New versions of xtrabackup SST scripts were ignoring --socket parameter passed by mysqld. Bug fixed #1289483.
  • Regression in Galera required explicitly setting socket.ssl to Yes even if you set up variables socket.ssl_key and socket.ssl_cert. Bug fixed #1290006.
  • Fixed the clang build issues that were happening during the Galera build. Bug fixed #1290462.
  • Better diagnostic error message has been implemented when wsrep_max_ws_size limit has been succeeded. Bug fixed #1280557.
  • Fixed incorrect warnings and implemented better handling of repeated usage with same value for wsrep_desync. Bug fixed #1281696.
  • Fixed the issue with wsrep_slave_threads wherein if the number of slave threads was changed before closing threads from an earlier change, it could increase the total number of threads beyond value specified in wsrep_slave_threads. Bug fixed #1290612
  • A regression in mutex handling caused dynamic update of wsrep_log_conflicts to hang the server. Bug fixed #1293624.
  • Presence of /tmp/test directory and an empty test database caused Percona XtraBackup to fail, causing SST to fail. This has been fixed in Percona XtraDB Cluster’s xtrabackup SST script, by using unique temporary directories with Percona XtraBackup. Bug fixed #1294760.
  • After installing the auth_socket plugin any local user might get root access to the server. If you’re using this plugin upgrade is advised. This is a regression, introduced in Percona XtraDB Cluster 5.6.14-25.1. Bug fixed #1289599

Other bug fixes: #1289776, #1279343, #1259649, #1292533, #1272982, #1287098, #1284670, and #1264809.

Release notes for Percona XtraDB Cluster 5.6.15-25.5 are available in our online documentation along with the installation and upgrade instructions. 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.

Percona XtraDB Cluster Errata can be found in our documentation.

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

Categories: MySQL

How to log slow queries on Slave in MySQL 5.0 with pt-query-digest

MySQL Performance Blog - Thu, 2014-03-20 07:00

Working as a Percona Support Engineer, every day we are seeing lots of issues related to MySQL replication. One very common issue is slave lagging. There are many reasons for slave lag but one common reason is that queries are taking more time on slave then master. How to check and log those long-running queries?  From MySQL 5.1, log-slow-slave-statements variable was introduced, which you can enable on slave and log slow queries. But what if you want to log slow queries on slave in earlier versions like MySQL 5.0?  There is a good solution/workaround: pt-query-digest. How? let’s take a look….

If you want to log all queries that are running on slave (including those, running by sql thread), you can use pt-query-digest with –processlist and –print (In pt-query-digest 2.1.9) OR –output (In pt-query-digest 2.2.7) options and log all queries in specific file. I have tested it in my local environment and it works.

You can start pt-query-digest like below on Slave,

nil@Dell:~$ /percona-toolkit-2.1.9/bin/pt-query-digest --processlist u=msandbox,p=msandbox,S=/tmp/mysql_sandbox34498.sock --print --no-report OR nil@Dell:-$ /percona-toolkit-2.2.7/bin/pt-query-digest --processlist u=msandbox,p=msandbox,S=/tmp/mysql_sandbox34498.sock --no-report --output=slowlog

Run some long running queries on Master,

nil@Dell:~$ mysql -umsandbox -p --socket=/tmp/mysql_sandbox34497.sock Enter password: mysql> use percona Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> delete from test limit 5000000; Query OK, 5000000 rows affected (1 min 54.33 sec) mysql> delete from test limit 5000000; Query OK, 5000000 rows affected (1 min 56.42 sec)

mysql>

and you’ll see the output on Slave like this,

nil@Dell:~/Downloads/percona-toolkit-2.1.9/bin$ ./pt-query-digest --processlist u=msandbox,p=msandbox,S=/tmp/mysql_sandbox34498.sock --print --no-report # Time: 2014-03-18T12:10:57 # User@Host: system user[system user] @ [] # Query_time: 114.000000 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0 use percona; delete from test limit 5000000; nil@Dell:~/Downloads/percona-toolkit-2.2.7/bin$ pt-query-digest --processlist u=msandbox,p=msandbox,S=/tmp/mysql_sandbox34498.sock --no-report --output=slowlog # Time: 2014-03-18T12:21:05 # User@Host: system user[system user] @ [] # Query_time: 117.000000 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0 use percona; delete from test limit 5000000;

You can also run pt-query-digest in background like a daemon and send output to the specific file like slow.log and review it.

i.e /percona-toolkit-2.1.9/bin/pt-query-digest –processlist u=msandbox,p=msandbox,S=/tmp/mysql_sandbox34498.sock –print –no-report > slow.log 2>&1

OR

i.e /percona-toolkit-2.2.7/bin/pt-query-digest –processlist u=msandbox,p=msandbox,S=/tmp/mysql_sandbox34498.sock –no-report –output=slowlog > slow.log 2>&1

Here, the default output will be just like slow query log. If we have master-master replication where every master is slave too and we want to log only those statements that are executing by sql_thread then –filter option can be used like this:

pt-query-digest –filter ‘$event->user eq “system user”‘ –no-report –output=slowlog

Since pt-query-digest–processlist polls 10 times/second ( –interval option), it’s not reliable to use for collecting complete query logs, because quick queries could fall in between the polling intervals. And in any case, it won’t measure query time with precision any better than 1/10th of a second. But if the goal is to identify queries that are very long-running, it should be adequate.

The post How to log slow queries on Slave in MySQL 5.0 with pt-query-digest appeared first on MySQL Performance Blog.

Categories: MySQL

Great Speakers, Fun, and Insights at Percona Live MySQL Conference

MySQL Performance Blog - Wed, 2014-03-19 12:00

The Percona Live MySQL Conference 2014 is less than two weeks away, running April 1-4, and excitement is building for the great lineup of speakers, events, and networking opportunities it offers. This year’s conference also features the first-ever Open Source Appreciation Day, which takes place on March 31, 2014, and includes two separate events, CentOS Dojo Santa Clara and OpenStack Today, highlighting these two key technologies. A new keynote address about the OpenStack ecosystem by Boris Renski, a member of the OpenStack Board of Directors, has also been added.

Positive Energy at the Percona Live MySQL Conference

The energy at the Percona Live MySQL Conferences is palpable as everyone from experts to novices gather to learn, share, and enjoy. In an interview, Jeremy Cole, Google senior systems engineer and a presenter at the conference, said, “Aside from actual sessions, one of the things I look forward to most each year is the social aspects. This conference is the only place where nearly every member of the MySQL community comes together all at once. I am excited about Oracle’s participation and their sessions about MySQL 5.7, and the many talks from those who have deployed MySQL 5.6 in production.”

Similarly, Mats Kindahl, senior principal software developer in MySQL at Oracle and a conference presenter, is enthusiastic about the opportunity that Percona Live MySQL Conference and Expo 2014 presents: “I’m quite interested in anything related to high-availability and scalability — especially real-life experience from people managing large farms. There is a lot of value in seeing and understanding what kind of problems people run into in practice. Because it’s never what you see in a theoretical presentation on high-availability. It’s always the details that make a difference.”

Keynote Panel

I look forward to keynotes this year from Oracle’s Tomas Ulin, Fusion-io’s Nisha Talagala, Dropbox’s Renjish Abraham, Big Fish Games’ Sean Chighizola, Continuent’s Robert Hodges, and Percona’s Peter Zaitsev. This year we will also feature a keynote panel on “The Future of Operating MySQL at Scale”. I will host the panel which features Robert Hodges, Nisha Talagala, and Peter Zaitsev, and will focus on the challenges of operating MySQL at scale when downtime is very costly for mission-critical applications and more and more companies running MySQL in the Cloud. We will discuss topics such as high availability clusters, multi-datacenter replication, and data storage in addition to other important future challenges.

Friday Keynote on OpenStack

A new keynote has been added on Friday, April 4, 2014 about OpenStack. Boris Renski, OpenStack Board Member and CMO of Mirantis, will present “OpenStack Co-Opetition, A View from Within”. He will discuss the competitive and cooperative nature of the OpenStack ecosystem and touch upon topics such as TROVE which is relevant to Database-as-a-Service in relation to OpenStack.

Open Source Appreciation Day Details

On Monday, March 31, 2014, the day before the official start of the conference, we will host the first-ever Open Source Appreciation Day at the Percona Live MySQL Conference, which consists of two events:

CentOS Dojo Santa Clara – This free event, offered in cooperation with CentOS, brings together the CentOS community to discuss systems administration, best practices and emerging technologies. Due to space constraints, attendance is limited to 75 people and advanced registration is required. The event will take place from 10:00 a.m. to 6:00 p.m. in Room 203 of the Santa Clara Convention Center.
OpenStack Today – This free event is offered in cooperation with members of the OpenStack community and will provide an opportunity to hear from leading industry technologists who will speak about today’s fastest growing open source cloud infrastructure project, OpenStack, and how MySQL fits into the evolving OpenStack roadmap. Due to space constraints, attendance is limited to 100 people and advanced registration is required. The event will take place from 1:00 p.m. to 6:00 p.m. in Room 204 of the Santa Clara Convention Center.

Attendees who register for one of the two Open Source Appreciation Day events can use the discount code “OPEN” to receive a 15 percent discount on their Percona Live MySQL Conference and Expo 2014 registration. Registration for either of these events includes a free Expo Hall and Keynote Pass for the Percona Live MySQL Conference and Expo.

Lightning Talks and Birds of a Feather Sessions (BOFs)

Both the Lightning Talks and BOFs are a perennial crowd favorite at Percona Live conferences.

The always entertaining Lightning Talks are an opportunity for attendees to propose, explain, exhort, or rant on any MySQL-related topic for five minutes. Topics might include a new idea, successful project, cautionary story, quick tip, or demonstration. Lightning Talks will take place Thursday night, April 3, 2014, during the MySQL Community Networking Reception, which begins immediately following the breakout sessions.

Birds of a Feather sessions enable attendees with interests in the same project or topic to enjoy some quality face time. This year’s topics include MySQL 5.6 in production, best practices for MySQL data replication, extreme MySQL performance, and much more. We’ve even added a BOF on OpenStack this year. The BOFs will take place Wednesday night, April 2, from 6:00 p.m. to 7:00 p.m. BOFs are open to all members of the MySQL community, whether you attend the conference or not.

Community Dinner and Community Reception

The Percona Live MySQL Conference and Expo is a great place for networking, providing attendees with the opportunity to make connections that can help enhance their career, facilitate a current project, or inspire new ideas. This year we have a Welcome Reception on Tuesday night and the Community Networking Reception on Thursday night. Pythian is organizing the community dinner this year on Wednesday night, following the BOFs. For a complete list of events and times, see the Conference Program.

Sponsors

This year’s Percona Live MySQL Conference and Expo includes an extensive list of prominent sponsors. Recent additions to the list include Gold Sponsor Machine Zone, Silver Sponsor Tesora (formerly ParElastic), and lounge and recharging station sponsor Facebook. Sponsorship opportunities are still available. Sponsors of the Percona Live MySQL Conference and Expo 2014 become part of a dynamic and growing ecosystem and interact with more than 1,000 DBAs, sysadmins, developers, CTOs, CEOs, business managers, technology evangelists, solutions vendors, and entrepreneurs in the heart of Silicon Valley. The complete list of sponsors includes:

• Diamond Plus: Continuent, Fusion-io
• Platinum: Booking.com
• Gold: Machine Zone, Micron, Pythian, SkySQL
• Silver: AppDynamics, Box, InfiniDB, Diablo, Galera/Codership, Google, Rackspace, Tesora, Twitter, Yelp
• Exhibitors: Attunity, Blackbird (formerly PalominoDB), Dropbox, Etsy, FoundationDB, HGST, RSSBus, ScaleArc, ScaleBase, Severalnines, Sphinx, Tokutek, VividCortex
• Other: Devart, Facebook, Webyog, MailChimp
• Media: ADMIN Magazine, Datanami, DBTA, Linux Journal, Linux Pro Magazine, O’Reilly, Software Developer’s Journal
• Open Source Appreciation Day: Tesora, hastexo, CentOS

Visit the Percona Live MySQL Conference and Expo 2014 website for more information and to register. I hope to see you in Santa Clara in two weeks!

The post Great Speakers, Fun, and Insights at Percona Live MySQL Conference appeared first on MySQL Performance Blog.

Categories: MySQL

Hardening your Cacti setup

MySQL Performance Blog - Wed, 2014-03-19 09:00

If you are using Percona Monitoring Plugins for Cacti, this article should be important to you.

By default, the Cacti setup is closed from accessing from Web. Here is an excerpt from /etc/httpd/conf.d/cacti.conf:

# httpd 2.4 Require host localhost # httpd 2.2 Order deny,allow Deny from all Allow from localhost

In order, to access the Cacti web interface, most likely, you will be changing this configuration. Commenting out Deny/Require statements will open the Cacti to the local network or Internet. This will create a potential vulnerability to disclose MySQL password contained in scripts under the directory /usr/share/cacti/scripts/, in particular /usr/share/cacti/scripts/ss_get_mysql_stats.php and /usr/share/cacti/scripts/ss_get_mysql_stats.php.cnf, when trying to access them from Web.

Unfortunately, the folder /usr/share/cacti/scripts/ is not closed by default as it is done with /usr/share/cacti/log/ and /usr/share/cacti/rra/ directories.

We strongly recommend to close any access from the web for these additional directories or files:

* /usr/share/cacti/scripts/
* /usr/share/cacti/site/scripts/ (for Debian systems)
* /usr/share/cacti/cli/
* /usr/share/cacti/.boto

Here is an example of httpd configuration that can harden your setup (goes to /etc/httpd/conf.d/cacti.conf):

Redirect 404 / Require all denied Order deny,allow Deny from all

Even if you fully password-protected your Cacti installation using HTTP authentication, it is still recommended to double-secure the directories and files listed above.

Thanks to William Lightning for reporting this issue.

The post Hardening your Cacti setup appeared first on MySQL Performance Blog.

Categories: MySQL

Percona Server with TokuDB: Packing 15TB into local SSDs

MySQL Performance Blog - Tue, 2014-03-18 13:39

Two weeks ago we released an Alpha release of Percona Server with TokuDB. Right now I am on a final stage of evaluation of TokuDB for using in our project Percona Cloud Tools and it looks promising.

What is the most attractive in TokuDB? For me it is compression, but not just compression: TokuDB provides great performance over compressed data.

In my synthetic tests I saw a compression ratio of 10:1 (TokuDB LZMA to InnoDB uncompressed), in the real production data it is less, 6:1, but still impressive.

In our servers we have 4 x SSD Crucial M500 960GB combined in RAID5, which give 2877.0 GB of usable space. With TokuDB we should be able to pack around 15TB of raw data. Of course we can try InnoDB compression, but the best we can get is 2x compression without sacrificing performance.

And of course TokuDB is transaction, fully ACID-compliant with automatic crash-recovery storage engine.

This all makes TokuDB a very attractive choice for handling terabytes of data (or as it popular to say nowadays “BigData”).

One of first operational questions we have is how to handle backups.
For backups we use LVM partitions and the mylvmbackup tool. Unfortunately Percona XtraBackup is not able to handle TokuDB tables (and probably won’t be able anytime soon). The other choice is to use TokuDB Hot back-up, available by Tokutek Enterprise Subscription. I did not test it myself, so I can’t provide any feedback.

And of course there are things which I do not fully like in TokuDB:

  • No Foreign Keys support. It is not a big issue for us, but I know for some users this is a showstopper.
  • Time-based checkpoints. You may not notice a direct effect from this, but we clearly see it in our benchmarks. Every 60 sec (default timeperiod between checkpoints) we see a drop in throughput during write-intensive benchmarks. It is very similar to drops in InnoDB we tried to solve (and are still trying), for example see Adaptive flushing in MySQL 5.6. My advice to the Tokutek team would be to also look into a fuzzy check-pointing, instead of time-based.
  • All TokuDB files are stored in a single directory, sometime with mangled filenames. This especially becomes bad in sharding or multi-tenant environments when tens of thousands of files are in the same directory

Well, I guess for now, we will take these limitations as TokuDB specific and will deal with them.

Next week we plan on a Beta release of Percona Server with TokuDB, so stay tuned!

The post Percona Server with TokuDB: Packing 15TB into local SSDs appeared first on MySQL Performance Blog.

Categories: MySQL

Percona Server 5.6.16-64.1 is now available

MySQL Performance Blog - Mon, 2014-03-17 17:49

Percona Server version 5.6.16-64.1

Percona is glad to announce the release of Percona Server 5.6.16-64.1 on March 17th, 2014 (Downloads are available here and from the Percona Software Repositories.

Based on MySQL 5.6.16, including all the bug fixes in it, Percona Server 5.6.16-64.1 is the current GA 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.16-64.1 milestone at Launchpad.

Bugs Fixed:

  • After installing the auth_socket plugin any local user might get root access to the server. If you’re using this plugin upgrade is advised. This is a regression, introduced in Percona Server 5.6.11-60.3. Bug fixed #1289599
  • The new client and server packages included files with paths that were conflicting with the ones in mysql-libs package on CentOS. Bug fixed #1278516.
  • A clean installation of Percona-Server-server-55 on CentOS would fail due to a typo in mysql_install_db call. Bug fixed #1291247.
  • libperconaserverclient18.1 Debian/Ubuntu packages depended on multiarch-support, which is not available on all the supported distribution versions. Bug fixed #1291628.
  • The InnoDB file system mutex was being locked incorrectly if Atomic write support for Fusion-io devices was enabled. Bug fixed #1287098.
  • Slave I/O thread wouldn’t attempt to automatically reconnect to the master after a network time-out (error: 1159). Bug fixed #1268729 (upstream #71374).
Renaming the libmysqlclient to libperconaserverclient

This release fixes some of the issues caused by the libmysqlclient rename to libperconaserverclient in Percona Server 5.6.16-64.0. The old name was conflicting with the upstream libmysqlclient.

Except for packaging, libmysqlclient and libperconaserverclient of the same version do not have any differences. Users who previously compiled software against Percona-provided libmysqlclient will either need to install the corresponding package of their distribution, such as distribution or Oracle-provided package for CentOS and libmysqlclient18 for Ubuntu/Debian or recompile against libperconaserverclient. Another workaround option is to create a symlink from libperconaserverclient.so.18.0.0 to libmysqlclient.so.18.0.0.

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

The post Percona Server 5.6.16-64.1 is now available appeared first on MySQL Performance Blog.

Categories: MySQL

Percona Server 5.5.36-34.1 is now available

MySQL Performance Blog - Mon, 2014-03-17 17:43

Percona Server version 5.5.36-34.1

Percona is glad to announce the release of Percona Server 5.5.36-34.1 on March 17th, 2014 (Downloads are available here and from the Percona Software Repositories). Based on MySQL 5.5.36, including all the bug fixes in it, Percona Server 5.5.36-34.1 is now the current stable release in the 5.5 series. All of Percona‘s software is open-source and free, all the details of the release can be found in the 5.5.36-34.1 milestone at Launchpad.

Bugs Fixed:

  • After installing the auth_socket plugin any local user might get root access to the server. If you’re using this plugin upgrade is advised. This is a regression, introduced in Percona Server 5.5.31-30.3. Bug fixed #1289599.
  • The new client and server packages included files with paths that were conflicting with the ones in mysql-libs package on CentOS. Bug fixed #1278516.
  • A clean installation of Percona-Server-server-55 on CentOS would fail due to a typo in mysql_install_db call. Bug fixed #1291247.
  • Percona-Server-shared-55 package was still declared as providing mysql-libs, but it is not actually providing it anymore. Bug fixed #1291249.
  • Slave I/O thread wouldn’t attempt to automatically reconnect to the master after a network time-out (error: 1159). Bug fixed #1268729 (upstream #71374).
  • Slave I/O thread wouldn’t attempt to automatically reconnect to the master if setting master_heartbeat_period failed with a transient network error. Bug fixed #1268735 (upstream #71375).
Renaming the libmysqlclient to libperconaserverclient

This release fixes some of the issues caused by the libmysqlclient rename to libperconaserverclient in Percona Server 5.5.36-34.0. The old name was conflicting with the upstream libmysqlclient.

Except for packaging, libmysqlclient and libperconaserverclient of the same version do not have any differences. Users who previously compiled software against Percona-provided libmysqlclient will either need to install the corresponding package of their distribution, such as distribution or Oracle-provided package for CentOS and libmysqlclient18 for Ubuntu/Debian or recompile against libperconaserverclient. Another workaround option is to create a symlink from libperconaserverclient.so.18.0.0 to libmysqlclient.so.18.0.0.

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

The post Percona Server 5.5.36-34.1 is now available appeared first on MySQL Performance Blog.

Categories: MySQL

Tools and tips for analysis of MySQL’s Slow Query Log

MySQL Performance Blog - Fri, 2014-03-14 15:00

MySQL has a nice feature, slow query log, which allows you to log all queries that exceed a predefined about of time to execute. Peter Zaitsev first wrote about this back in 2006 – there have been a few other posts here on the MySQL Performance Blog since then (check this and this, too) but I wanted to revisit his original subject in today’s post.

Query optimization is essential for good database server performance and usually DBAs need to ensure the top performance possible for all queries. In MySQL, the desirable way is to generate a query log for all running queries within a specific time period and then run a query analysis tool to identify the bad queries. Percona Toolkit’s pt-query-digest is one of the most powerful tools for SQL analysis. That’s because pt-query-digest can generate a very comprehensive report that spots problematic queries very efficiently. It works equally well with Oracle MySQL server. This post will focus mainly on pt-query-digest.

Slow query log is great at spotting really slow queries that are good candidates for optimization. Beginning with MySQL 5.1.21, the minimum value is 0 for long_query_time, and the value can be specified to a resolution of microseconds. In Percona Server additional statistics may be output to the slow query log. You can find the full details here. For our clients, we often need to identify queries that impact an application the most. It does not always have to be the slowest queries – queries that runs more frequently with lower execution time per call put more load on a server than queries running with lower frequency. We of course want to get rid of really slow queries but to really optimize application throughput, we also need to investigate queries that generate most of the load. Further, if you enable option log_queries_not_using_indexes  then MySQL will log queries doing full table scans which doesn’t always reflect that the query is slow, because in some situations the query optimizer chooses full table scan rather than using any available index or probably showing all records from a small table.

Our usual recommendation is to generate the slow log with long_query_time=0. This will record all the traffic but this will be I/O intensive and will eat up disk space very quickly depending on your workload. So beware of running with long_query_time=0 for only a specific period of time and revert it back to logging only very slow queries. In Percona Server there is nice option where you can limit the rate of logging, log_slow_rate_limit is the option to handle it. Filtering slow query log is very helpful too in some cases e.g. if we know the main performance issue is table scans we can log queries only doing full table scans or if we see I/O is bottleneck we can collect queries doing full scans and queries creating on disk temporary tables. Again, this is only possible in Percona Server with the log_slow_filter option. Also, you may want to collect everything on slow query log and then filter with pt-query-digest. Depending on I/O capacity, you might prefer one or another way, as collecting everything in slow query log allows us to investigate other queries too if needed. Finally, use pt-query-digest to generate an aggregate report over slow query log which highlights the problematic part very efficiently. Again, pt-query-digest can bring up server load high so our usual recommendation on it is to move slow query log to some staging/dev server and run pt-query-digest over there to generate the report.

Note: changing the long_query_time parameter value only affects newly created connections to log queries exceeds long_query_time threshold. In Percona Server there is feature which changes variable scope to global instead of local. Enabling slow_query_log_use_global_control  log queries for connected sessions too after changing long_query_time parameter threshold. You can read more about this patch here.

I am not going to show you a detailed report of pt-query-digest and explain each part of it here, because it is well defined already by my colleague Ovais Tariq in this post. However, I will show you some of the other aspects of pt-query-digest tool here.

Let me show you code snippets that enable slow query log for only a specific time period with long_query_time=0 and log_slow_verbosity to ‘full’. log_slow_verbosity is a Percona Server variable which logs extra stats such as information on query cache, Filesort, temporary tables, InnoDB statistics etc. Once you are done collecting logs, revert back the values for long_query_time to the previous value, and finally run pt-query-digest on the log to generate report. Note: run the below code in same MySQL session.

-- Save previous settings mysql> SELECT @@global.log_slow_verbosity INTO @__log_slow_verbosity; mysql> SELECT @@global.long_query_time INTO @__long_query_time; mysql> SELECT @@global.slow_query_log INTO @__slow_query_log; mysql> SELECT @@global.log_slow_slave_statements INTO @__log_slow_slave_statements; -- Keep this in safe place, we'll need to run pt-query-digest mysql> SELECT NOW() AS "Time Since"; -- Set values to enable query collection mysql> SET GLOBAL slow_query_log_use_global_control='log_slow_verbosity,long_query_time'; mysql> SET GLOBAL log_slow_verbosity='full'; mysql> SET GLOBAL slow_query_log=1; mysql> SET GLOBAL long_query_time=0; mysql> SET GLOBAL log_slow_slave_statements=1; -- Verify settings are OK mysql> SELECT @@global.long_query_time, @@global.slow_query_log, @@global.log_slow_verbosity; -- wait for 30 - 60 minutes -- Keep this one too, also for pt-query-digest mysql> SELECT NOW() AS "Time Until"; -- Revert to previous values mysql> SET GLOBAL slow_query_log=@__slow_query_log; mysql> SET GLOBAL long_query_time=@__long_query_time; mysql> SET GLOBAL log_slow_verbosity=@__log_slow_verbosity; -- if percona server mysql> SET GLOBAL log_slow_slave_statements=@__log_slow_slave_statements; -- Verify settings are back to previous values mysql> SELECT @@global.long_query_time, @@global.slow_query_log, @@global.log_slow_verbosity, @@global.slow_query_log_file; -- Then with pt-query-digest run like (replace values for time-since, time-until and log name) $ pt-query-digest --since='<time-since>' --until='<time-until>' --limit=100% /path/to/slow_query_log_file.log > /path/to/report.out -- If you're not using Percona Server then you need to remove all references to log_slow_verbosity, slow_query_log_use_global_control and log_slow_slave_statements (priot MySQL 5.6).

My colleague Bill Karwin wrote bash script that does almost the same as the above code. You can find the script to collect slow logs here. This script doesn’t hold connection to the database session while you wait for logs to accumulate and it sets all the variables back to the state they were before. For full documentation view this.

Further, you can also get explain output into the report from the pt-query-digest tool. For that you need to use –explain parameter similar to as follows.

$ pt-query-digest --explain u=<user>,p=<password>,h=<hostname> /path/to/slow.log > /path/to/report.out

Explain output in query report will get you all the information for query execution plan and explain output signal towards how that particular query going to be executed. Note that, if you execute pt-query-digest over slow query log other than originated server of slow query log as I mentioned above e.g. staging/dev you may get different execution path for the query in the report or lower number of rows to examined, etc., because usually staging/dev servers has different data distribution, different MySQL versions, or different indexes. MySQL explain adds overhead as queries needs to be prepared on the server to generate intended query execution path. For this reason, you may want to run pt-query-digest with –explain on a production replica.

It’s worth mentioning that logging queries with log_slow_verbosity in Percona Server is really handy as it shows lots of additional statistics and it is more helpful in situations when the explain plan reports a different execution path than when the query is executed. On that particular topic, you may want to check this nice post.

pt-query-digest also supports filters. You can read more about it here. Let me show you an example. The following command will discard everything apart from insert/update/delete queries in pt-query-digest output report.

$ pt-query-digest --filter '$event->{arg} =~ m/^(insert|update|delete)/i' --since='<time-since>' --until='<time-until>' --limit=100% /path/to/slow_query_log_file.log > /path/to/report.out

If you’re looking for some GUI tools for pt-query-digest then I would recommend reading this nice blogpost from my colleague Roman. Further, our CEO Peter Zaitsev also wrote a post recently where he shows the comparison between performance_schema and slow query log. Check here for details.

In related new, Percona recently announced Percona Cloud Tools, the next generation of tools for MySQL. It runs a client-side agent (pt-agent) which runs pt-query-digest on the server with some intervals and uploads the aggregated data to the Percona Cloud Tools API which process it further.  Query Analytics is one tool from the Percona Cloud Tools that provides advanced query metrics. It  is a nice visualization tool. You may be interested to learn more about it here, and it’s also worth viewing this related webinar about Percona Cloud Tools from our CTO Vadim Tkachenko.

Conclusion:
pt-query-digest from Percona Toolkit is a versatile (and free) tool for slow query log analysis. It provides good insight about every individual query, especially in Percona Server with log_slow_verbosity enabled, e.g. log queries with microsecond precision, log information about the query’s execution plan. On top of that, Percona Cloud Tools includes Query Analytics which provides you with good visuals about query performance and also provides a view of historical data.

The post Tools and tips for analysis of MySQL’s Slow Query Log appeared first on MySQL Performance Blog.

Categories: MySQL

Percona Monitoring Plugins 1.1.2, now with Amazon RDS support

MySQL Performance Blog - Fri, 2014-03-14 14:56

Percona is glad to announce the release of Percona Monitoring Plugins 1.1.2.

Changelog:

* Added Nagios plugin and Cacti template for Amazon RDS
* Added Nagios config template to the documentation
* Added an option to pmp-check-pt-table-checksum to check MAX(ts) of latest checksum
* Added generic Nagios plugin for PT tables
* Extended pmp-check-mysql-processlist with max user connections check
* Zabbix MySQL.running-slave item failed with MySQL 5.6 (bug 1272358)
* ss_get_mysql_stats and MariaDB does not use have_response_time_distribution (bug 1285888)
* Cacti Monitoring plugins and SNMP via TCP (bug 1268552)

Shortly about the new features.

Amazon RDS support in Nagios and Cacti.
We have created the Nagios plugin and Cacti template to give you a chance to add Amazon RDS instances into your monitoring system. The plugins are getting various RDS metrics from Amazon CloudWatch. With Nagios you can have the following checks per instance: RDS Status, RDS Load Average, RDS Free Storage, RDS Free Memory. Also check out the examples of Cacti RDS graphs.

Nagios config template. We never have a good template of Nagios config in the documentation with examples on how to put together the plugins. So we have updated Configuration Best Practices section with it.

Freshness check for checksumming. A new option of the plugin pmp-check-pt-table-checksum allows you to specify an interval in days over which to ensure pt-table-checksum was run. It is useful in cases when the cron job doing the checksumming suddenly stopped working.

New generic Nagios plugin for PT tables. pmp-check-mysql-ts-count looks at a table and counts the number of rows since the last interval, and alerts if this exceeds the threshold. This could be the table referenced by tools from Percona Toolkit such as pt-deadlock-logger, pt-kill, pt-fk-error-logger, or a custom table supplied.

MySQL max user connections check. The plugin pmp-check-mysql-processlist has got a new option to alert when @@max_user_connections is configured on MySQL and any user reaches this limit.

A new tarball is available from downloads area or RPM and DEB packages from our software repositories. The plugins are fully supported for customers with a Percona Support contract and free installation services are provided as part of some contracts. In addition as part of Percona’s Remote DBA installation and setup of these tools are included with our services. You can find links to the documentation, forums and more at the project homepage.

About Percona Monitoring Plugins
Percona Monitoring Plugins are high-quality components to add enterprise-grade MySQL monitoring and graphing capabilities to your existing in-house, on-premises monitoring solutions. The components are designed to integrate seamlessly with widely deployed solutions such as Nagios, Cacti and Zabbix and are delivered in the form of templates, plugins, and scripts which make it easy to monitor MySQL performance.

The post Percona Monitoring Plugins 1.1.2, now with Amazon RDS support appeared first on MySQL Performance Blog.

Categories: MySQL

Open Source Appreciation Day at the Percona Live MySQL Conference

MySQL Performance Blog - Thu, 2014-03-13 11:42

I am very pleased to announce a new event in conjunction with the Percona Live MySQL Conference and Expo this year: Open Source Appreciation Day on Monday, March 31st in the Santa Clara Convention Center! We are pleased to announce two separate groups holding events this year under this new umbrella. We are hosting an event called “OpenStack Today” for those interested in learning more about developments in the OpenStack world. CentOS is holding the “CentOS Dojo Santa Clara” event. Registration is required, as space is limited, but the events are both free to attendees.

OpenStack Today

OpenStack Today will cover deployments in this rapidly evolving ecosystem. OpenStack is the fastest growing open source cloud infrastructure project today. Organizations are creating private OpenStack clouds in their datacenters or deploying applications to public OpenStack clouds and MySQL is at the heart of those deployments. Attendees will learn the latest trends in OpenStack and how MySQL fits into the OpenStack roadmap. Speakers from hastexo, Mirantis, Tesora, Scalr, and Percona will present. For the full agenda, visit the event webpage on the Percona Live website.

CentOS Dojo Santa Clara

CentOS Dojo Santa Clara will bring people from the CentOS communities together to talk about systems administration, best practices and emerging technologies. CentOS Dojo events emphasize local speakers and tutors that come together and discuss things that they care about most and to share stories from their experiences working with CentOS in various scenarios. Attendance is limited to ensure there are plenty of opportunities for everyone to interact and establish social connections to carry the conversations through the future. For the full agenda, visit the event webpage on the Percona Live website.

Both events are free but space is limited to 75 people for the CentOS event and 100 people for the OpenStack event. Attendees may only register for only one of these events, not both.

I wish to thank CentOS and Tesora who are helping sponsor these events.

Open Source Appreciation Day

I’m happy that Open Source Appreciation Day came together. We have been in discussions with a few open source projects about organizing events for the Monday before the conference. For those who attended the Percona Live MySQL Conference in 2013, you know that it was held on a Monday through Thursday. We had to move the conference to Tuesday through Friday this year because we couldn’t start moving into the facilities until Monday and it takes a full day to create the awesome experience conference attendees will enjoy (for those planning to attend Percona Live in 2015, we’ll be back to a Monday through Thursday event , April 13-16). But we have some meeting space available upstairs in the Santa Clara Convention Center this year on Monday that is perfect for meetings of 50 to 100 people. We decided to put the rooms to good use and launch Open Source Appreciation Day in cooperation with CentOS and multiple organizations and speakers from the OpenStack ecosystem. We reached out to other open source projects but were unable to pull together events for other projects.

I look forward to a great Percona Live conference this year. And I hope that Open Source Appreciation Day will prove to be popular with those within, and maybe even previously outside, the MySQL community.

The post Open Source Appreciation Day at the Percona Live MySQL Conference appeared first on MySQL Performance Blog.

Categories: MySQL

Monitoring MySQL with MONyog

MySQL Performance Blog - Wed, 2014-03-12 17:18

Monitoring MySQL and effectively managing it can be challenging. Identifying issues before they grow into performance problems that impact end users can be crucial. Knowing which tools to use, which key metrics to monitor, and how to resolve issues can be enormously important. When considering these facts, we at Percona decided to take steps to provide our Support customers with the tools, alerts, and advice they need to have higher performing, more secure, and easier to manage MySQL deployments.

Percona Support Now Includes MONyog and Percona Advisors

I am pleased to announce that Percona now provides our Gold and Platinum Percona Support for MySQL customers with the enterprise grade tools and advice they need for better performance, fewer database issues, and effective monitoring of MySQL. We have partnered with Webyog to provide our customers with MONyog Ultimate Edition, the leading MySQL monitoring and management tool. We then improve upon MONyog by providing Percona Advisors which provide MONyog users with best practices for monitoring MySQL and resolving performance issues. Percona Advisors combined with MONyog provides visibility into problems before they impact end user experience and it also provides guidance with links to relevant technical information on resolving issues quickly and effectively.

Percona Advisors is an installable set of rules, alerts, and recommended actions for MONyog. They capture the accumulated MySQL operational knowledge and insight of our Percona Support, Consulting, and Remote DBA teams. Percona Advisors modify the standard MONyog dashboard to highlight the most important performance metrics with alerts for when metrics fall outside of recommended limits. Percona Advisors is an ever-evolving solution with regular updates planned to incorporate additional best practices and guidance on issue resolution over time, as MySQL best practices continue to evolve.

“Monitoring MySQL with MONyog” Webinar on March 19, 2014

I am giving a webinar entitled “Monitoring MySQL with MONyog” on Wednesday, March 19, 2014 at 10:00 am Pacific Daylight Time to introduce this new solution. I will provide information on MONyog and how to create alerts which will make MySQL administration easier. I will also show how to leverage MONyog to understand trends, create alerts, and use other features to monitor performance and troubleshoot the source of issues. I hope you will join me then.

The post Monitoring MySQL with MONyog appeared first on MySQL Performance Blog.

Categories: MySQL

Introducing backup locks in Percona Server

MySQL Performance Blog - Tue, 2014-03-11 12:00

TL;DR version: The backup locks feature introduced in Percona Server 5.6.16-64.0 is a lightweight alternative to FLUSH TABLES WITH READ LOCK and can be used to take both physical and logical backups with less downtime on busy servers. To employ the feature with mysqldump, use mysqldump --lock-for-backup --single-transaction. The next release of Percona XtraBackup will also be using backup locks automatically if the target server supports the feature.

Now on to the gory details, but let’s start with some history.

In the beginning…

In the beginning there was FLUSH TABLES, and users messed with their MyISAM tables under a live server and were not ashamed. Users could do nice things like:

mysql> FLUSH TABLES; # execute myisamchk, myisampack, backup / restore some tables, etc.

And users were happy until someone realized that tables must be protected against concurrent access by queries in other connections. So Monty gave them FLUSH TABLES WITH READ LOCK, and users were enlightened.

Online backups

Users then started dreaming about online backups, i.e. creating consistent snapshots of a live MySQL server. mysqldump --lock-all-tables had been a viable option for a while. To provide consistency it used FLUSH TABLES WITH READ LOCK which was not quite the right tool for the job, but was “good enough”. Who cares if a mom-and-pop shop becomes unavailable for a few seconds required to dump ~100 MB of data, right?

With InnoDB gaining popularity users had realized that one could employ MVCC to guarantee consistency and FLUSH TABLES WITH READ LOCK doesn’t make much sense for InnoDB tables anyway (you cannot modify InnoDB tables under a live server even if the server is read-only). So Peter gave mysqldump the --single-transaction option, and users were enlightened. mysqldump --single-transaction allowed to avoid FTWRL, but there was a few catches:

  • one cannot perform any schema modifications or updates to non-InnoDB tables while mysqldump --single-transaction is in progress, because those operations are not transactional and thus would ignore the data snapshot created by --single-transaction;
  • one cannot get binary log coordinates with --master-data or
    --dump-slave, because in that case FTWRL would still be used to ensure that the binary log coordinates are consistent with the data dump;

Which makes --single-transaction similar to the --no-lock option in Percona XtraBackup: it shifts the responsibility for backup consistency to the user. Any change in the workload violating the prerequisites for those options may result in a broken backup without any signs for the user to take action.

Present

Fast forward to present day. MySQL is capable of handling over a million queries per second, MyISAM is certainly not a popular choice to store data, and there are many backup solutions to choose from. Yet all of them still rely on FLUSH TABLES WITH READ LOCK in one way or another to guarantee consistency of .frm files, non-transactional tables and binary log coordinates.

To some extent, the problem with concurrent DDL + mysqldump --single-transaction has been alleviated with metadata locks in MySQL 5.5, which however made some users unhappy, and that behavior was partially reverted in MySQL 5.6.16 with the fix for bug #71017. But the fundamental problem is still there: mysqldump --single-transaction does not guarantee consistency with concurrent DDL statements and updates to non-transactional tables.

So the fact that FTWRL is an overkill for backups has been increasingly obvious for the reasons described below.

What’s the problem with FTWRL anyway?

A lot has been written on what FLUSH TABLES WITH READ LOCK really does. Here’s yet another walk-through in a bit more detail than described elsewhere:

  1. It first invalidates the Query Cache.
  2. It then waits for all in-flight updates to complete and at the same time it blocks all incoming updates. This is one problem for busy servers.
  3. It then closes all open tables (the FLUSH part) and expels them from the table cache. This is also when FTWRL has to wait for all SELECT queries to complete. And this is another, even bigger problem for busy servers, because that wait happens to occur with all updates blocked. What’s even worse, the server at this stage is essentially offline, because even incoming SELECT queries will get blocked.
  4. Finally, it blocks COMMITs.

Action #4 is not required for the original purpose of FTWRL, but is rather a kludge implemented due to the fact that FTWRL is (mis)used by backup utilities.

Actions #1-3 make perfect sense for the original reasons why FTWRL has been implemented. If we are going to access and possibly modify tables outside of the server, we want the server to forget everything it knows about both schema and data for all tables, and flush all in-memory buffers to make the on-disk data representation consistent.

And that’s what makes it an overkill for backup utilities: they don’t require #1, because they never modify data. #2 is only required for non-InnoDB tables, because InnoDB provides other ways to ensure consistency for both logical and physical backups. And #3 is certainly not a problem for logical backup utilities like mysqldump or mydumper, because they don’t even access on-disk data directly. As we will see, it is not a big problem for physical backup solutions either.

To FLUSH or not to FLUSH?

So what exactly is flushed by FLUSH TABLES WITH READ LOCK?

Nothing for InnoDB tables, and no physical backup solution require it to flush anything.

For MyISAM it is more complicated. MyISAM key caches are normally write-through, i.e. by the time each update to a MyISAM table completes, all index updates are written to disk. The only exception is delayed key writing feature which you should not be using anyway, if you care about your data. MyISAM may also do data buffering for bulk inserts, e.g. while executing multi-row INSERTs or LOAD DATA statements. Those buffers, however, are flushed between statements, so have no effect on physical backups as long as we block all statements updating MyISAM tables.

The point is that without flushing each storage engine is not any less backup-safe as it is crash-safe, with the only difference that backups are guaranteed to wait for all currently executing INSERT/REPLACE/DELETE/UPDATE statements to complete.

Backup locks

Enter the backup locks feature. The following 3 new SQL statements have been introduced in Percona Server:

  • LOCK TABLES FOR BACKUP
  • LOCK BINLOG FOR BACKUP
  • UNLOCK BINLOG
LOCK TABLES FOR BACKUP

Quoting the documentation page from the manual:

LOCK TABLES FOR BACKUP uses a new MDL lock type to block updates to non-transactional tables and DDL statements for all tables. More specifically, if there’s an active LOCK TABLES FOR BACKUP lock, all DDL statements and updates to MyISAM, CSV, MEMORY and ARCHIVE tables will be blocked in the “Waiting for backup lock” status as visible in PERFORMANCE_SCHEMA or PROCESSLIST. SELECT queries for all tables and INSERT/REPLACE/UPDATE/DELETE against InnoDB, Blackhole and Federated tables are not affected by LOCK TABLES FOR BACKUP. Blackhole tables obviously have no relevance for backups, and Federated tables are ignored by both logical and physical backup tools.

Like FTWRL, the LOCK TABLES FOR BACKUP statement:

  • blocks updates to MyISAM, MEMORY, CSV and ARCHIVE tables;
  • blocks DDL against any tables;
  • does not block updates to temporary and log tables.

Unlike FTWRL, the LOCK TABLES FOR BACKUP statement:

  • does not invalidate the Query Cache;
  • never waits for SELECT queries to complete regardless of the storage engines involved;
  • never blocks SELECTs, or updates to InnoDB, Blackhole and Federated tables.

In other words, it does exactly what backup utilities need: block non-transactional changes that are included into the backup, and leave everything else to InnoDB MVCC and crash recovery.

With the only exception of binary log coordinates obtained with SHOW MASTER STATUS and SHOW SLAVE STATUS.

LOCK BINLOG FOR BACKUP

This is when LOCK BINLOG FOR BACKUP comes in handy. It blocks all updates to binary log coordinates as reported by SHOW MASTER/SLAVE STATUS and used by backup utilities. It has no effect when all of the following conditions apply:

  • when the binary log is disabled. If it is disabled globally, then all connections will not be affected by LOCK BINLOG FOR BACKUP. If it is enabled globally, but disabled for specific connections via sql_log_bin, only those connections are allowed to commit;
  • the server is not a replication slave;

Even if binary logging is used, LOCK BINLOG FOR BACKUP will allow DDL and updates to any tables to proceed until they will be written to binlog (i.e. commit), and/or advance Exec_Master_Log_* / Exec_Gtid_Set when executed by a replication thread, provided that no other global locks are acquired.

To release the lock acquired by LOCK TABLES FOR BACKUP there’s already UNLOCK TABLES. And the LOCK BINLOG FOR BACKUP lock is released with UNLOCK BINLOG.

Let’s look how these statements can be used by backup utilities.

mysqldump

mysqldump got a new option, --lock-for-backup which along with --single-transaction essentially obsoletes --lock-all-tables (i.e. FLUSH TABLES WITH READ LOCK). It makes mysqldump use LOCK TABLES FOR BACKUP before it starts dumping tables to block all “unsafe” statement that might otherwise interfere with backup consistency.

Of course, that requires backup locks support by the target server, so mysqldump checks if they are indeed supported and fails with an error if they are not.

However, at the moment if binary lock coordinates are requested with --master-data, FTWRL is still used even if --lock-for-backup is specified. mysqldump could use LOCK BINLOG FOR BACKUP, but there’s a better solution for logical backups implemented in MariaDB, which has already been ported to Percona Server and queued for the next release.

There is also another important difference between just mysqldump --single-transaction and mysqldump --lock-for-backup --single-transaction. As of MySQL 5.5 mysqldump --single-transaction acquires shared metadata locks on all tables processed within the transaction. Which will also block DDL statements on those tables when they will try to acquire an exclusive lock. So far, so good. The problems start when there’s an incoming SELECT query against a table that already has a pending DDL statement. It will also be blocked on a pending exclusive MDL request for no apparent reasons. Which was one of the complaints in bug #71017.

It’s better illustrated with an example. Suppose there are 3 sessions: one created by mysqldump, and 2 user sessions.

user1> CREATE TABLE t1 (a INT); mysqldump> START TRANSACTION WITH CONSISTENT SNAPSHOT; mysqldump> SELECT * FROM t1; # this acquires a table MDL user1> ALTER TABLE t1 ADD COLUMN b INT; # this blocks on the MDL created by mysqldump user2> SET lock_wait_timeout=1; user2> SELECT * FROM t1; # this blocks on a pending MDL request by user1 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

This is what would happen with mysqldump --lock-for-backup --single-transaction:

user1> CREATE TABLE t1 (a INT); mysqldump> LOCK TABLES FOR BACKUP; mysqldump> START TRANSACTION WITH CONSISTENT SNAPSHOT; mysqldump> SELECT * FROM t1; # this acquires a table MDL user1> ALTER TABLE t1 ADD COLUMN b INT; # this blocks on the backup MDL lock user2> SET lock_wait_timeout=1; user2> SELECT * FROM t1; # this one is not blocked

This immediate problem was partially fixed in MySQL 5.6.16 by releasing metadata locks after processing each table with the help of savepoints. There is a couple of issues with this approach:

  • there is still a table metadata lock for the duration of SELECT executed by mysqldump. Which, as before, blocks DDL. So there is still a chance that mysqldump --single-transaction may eventually block SELECT queries.
  • after the table is processed and the metadata lock is released, there is now an opportunity for RENAME to break the backup, see bug #71214.

Both issues above along with bug #71215 and bug #71216 do not exist with mysqldump --lock-for-backup --single-transaction as all kinds of DDL statements are properly isolated by backup locks, which do not block SELECT queries at the same time.

Percona XtraBackup

Percona XtraBackup 2.2 will support backup locks and use them automatically if supported by the server being backed up.

The current locking used by XtraBackup is:

# copy InnoDB data FLUSH TABLES WITH READ LOCK; # copy .frm, MyISAM, etc. # get the binary log coordinates # finalize the background copy of REDO log UNLOCK TABLES;

With backup locks it becomes:

# copy InnoDB data LOCK TABLES FOR BACKUP; # copy .frm, MyISAM, etc LOCK BINLOG FOR BACKUP; # finalize the background copy of REDO log UNLOCK TABLES; # get the binary log coordinates UNLOCK BINLOG;

Note that under the following conditions, no blocking occurs at any stage in the server:

  • no updates to non-transactional tables;
  • no DDL;
  • binary log is disabled;

They may look familiar, because they are essentially prerequisites for the --no-lock option. Except that with backup locks, you don’t have to take chances and take responsibility for backup consistency. All the locking will be handled automatically by the server, if and when it is necessary.

mylvmbackup

mylvmbackup takes the server read-only with FLUSH TABLES WITH READ LOCK while the snapshot is being created for two reasons:

  • flush non-transactional tables
  • ensure consistency with the binary log coordinates

For exactly the same reasons as with XtraBackup, it can use backup locks instead of FTWRL.

mydumper

mydumper developers may want to add support for backup locks as well. mydumper relies on START TRANSACTION WITH CONSISTENT SNAPSHOT to ensure InnoDB consistency, but has to resort to FLUSH TABLES WITH READ LOCK to ensure consistency of non-InnoDB tables and binary log coordinates.

Another problem is that START TRANSACTION WITH CONSISTENT SNAPSHOT is not supposed to be used by multi-threaded logical backup utilities. But that is an opportunity for another server-side improvement and probably a separate blog post.

The post Introducing backup locks in Percona Server appeared first on MySQL Performance Blog.

Categories: MySQL

Percona Server 5.6.16-64.0 is now available

MySQL Performance Blog - Mon, 2014-03-10 16:12

Percona Server version 5.6.16-64.0

Percona is glad to announce the release of Percona Server 5.6.16-64.0 on March 10th, 2014 (Downloads are available here and from the Percona Software Repositories.

Based on MySQL 5.6.16, including all the bug fixes in it, Percona Server 5.6.16-64.0 is the current GA 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.16-64.0 milestone at Launchpad.

New Features:

  • Percona Server has implemented Backup Locks that can be used as a lightweight alternative to FLUSH TABLES WITH READ LOCK for taking physical and logical backups.
  • Percona Server has split a new LRU manager thread out of the InnoDB cleaner thread, that performs LRU flushes and evictions to refill the free lists.
  • Percona Server now has server-side support for TokuDB Compression option syntax.
  • Percona Server has implemented Slow Query Log Rotation and Expiration feature to provide users with better control of slow query log disk space usage.
  • Ability to change database for mysqlbinlog has been ported from Percona Server 5.1.
  • In order to comply with Linux distribution packaging standards Percona‘s version of libmysqlclient has been renamed to libperconaserver. The old name was conflicting with the upstream libmysqlclient. Except for packaging, libmysqlclient and libperconaserverclient of the same version do not have any differences. Users wishing to continue using libmysqlclient will have to install the corresponding package of their distribution, such as mysql-lib for CentOS and libmysqlclient18 for Ubuntu/Debian. Users wishing to build software against libperconaserverclient should install libperconaserverclient-dev package. An old version of Percona-built libmysqlclient will be available for download.

Bugs Fixed:

  • The XtraDB version number in univ.i was incorrect. Bug fixed #1277383.
  • Percona Toolkit UDFs were only shipped with RPM packages. Bug fixed #1159625.
  • A debug server build will crash if, while performing a bulk insert to a partitioned table, one of the partitions will return a failure for end_bulk_insert handler call. Bug fixed #1204871 (upstream #71270).
  • Percona Server 5.6 installation on Debian would fail due to default config reference to /etc/mysql/conf.d which didn’t exist. Bug fixed #1206648.
  • Due to a regression in the buffer pool mutex split, a combination of InnoDB compression, write workload, and multiple active purge threads could lead to a server crash. Bug fixed #1247021.
  • Server would crash on startup when XA support functions were activated by a second storage engine. Fix for this bug was ported from MariaDB. Bug fixed #1255549 (upstream #47134).
  • FLUSH STATUS could cause a server crash on the next transaction commit if two XA-supporting storage engines are in use. Fix for this bug was ported from MariaDB. Bugs fixed #1255551 (upstream #70854).
  • auth_pam.so shared library needed for PAM Authentication Plugin was missing from RPM packages. Bug fixed #1268246.
  • Fix for bug #1227581, a buffer pool mutex split regression, was not complete, thus a combination of write workload to InnoDB compressed table and a tablespace drop could crash the server. Bug fixed #1269352.
  • Binary RPM packages couldn’t be built from source tarballs on Fedora 19. Bug fixed #1229598.
  • Percona Server compilation with Performance Schema turned off would fail due to regression introduced by the 5.6 priority mutex framework. Bug fixed #1272747.
  • The InnoDB page cleaner thread could have incorrectly decided whether the server is busy or idle on some of its iterations and consequently issue a too big flush list flushing request on a busy server, causing performance instabilities. Bug fixed #1238039 (upstream #71988).
  • Percona Server had different server version value when installing from Source and from Binary/RPM. Bug fixed #1244178.
  • InnoDB did not handle the cases of asynchronous and synchronous I/O requests completing partially or being interrupted. Bugs fixed #1262500 (upstream #54430), and #1263087 (Andrew Gaul).
  • The fix for upstream bug #70768 may cause a high rate of RW lock creations and destructions, resulting in a performance regression on some workloads. Bug fixed #1279671 (upstream #71708).
  • Debian and Ubuntu packaging has been reworked to meet the packaging standards. Bug fixed #1281261.
  • Fixed the CMake warnings that were happening when Makefile was generated. Bugs fixed #1274827, upstream bug fixed #71089 and #1274411 (upstream #71094).
  • On Ubuntu Precise multiple architecture versions of libmysqlclient18 couldn’t be installed side by side. Bug fixed #1052636.
  • Percona Server source tree has been reorganized to match the MySQL source tree layout closer. Bug fixed #1014477.
  • Performance Schema autosizing heuristics have been updated to account for Percona Server-specific wait/synch/mutex/sql/THD::LOCK_temporary_tables mutex. Bug fixed #1264952.

Other bugs fixed: #1276445, #1005787, #1285064, #1229598, and #1277505 (upstream #71624).

[UPDATE 2014-03-12]: Changes that were made for Ubuntu/Debian packages may cause the warnings during the upgrade (in some cases package manager might warn you that you are doing a downgrade). It is safe to ignore the warning. You won’t have that warning in next versions. If you’re experiencing any issues during the upgrade/install process please report a bug. These changes have been done in order to improve our packaging quality.

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

The post Percona Server 5.6.16-64.0 is now available appeared first on MySQL Performance Blog.

Categories: MySQL

New mydumper 0.6.1 release offers performance and usability features

MySQL Performance Blog - Mon, 2014-03-10 16:00

One of the tasks within Percona Remote DBA is to ensure we have reliable backups with minimal impact. To accomplish this, one of the tools our team uses is called mydumper. We use mydumper for logical backups because of several nice features. Some of them are:

  • multithreaded, producing very fast backups compared to mysqldump
  • almost no locking, if not using non innodb tables
  • built-in compression
  • separate files for each table, making it easy to restore single tables or schema, and the possibility to hardlink files reducing the space needed to keep history of backups. Also this feature give you the possibility to restore with more than one thread.

The mydumper project was started at the beginning of 2009 by Domas Mituzas, and a few months ago we started collaborating and adding new features to improve performance and usability.

And now we are glad to announce our second release: mydumper 0.6.1. You can download the source code here. It is highly recommended to upgrade it if you are using 0.6.0 as we fixed and improved the new less-locking feature.

New features in the mydumper 0.6 series:

  • Consistent backups with less locking
    This new feature consists of locking all non-innodb tables with the dumping threads at the beginning so in this way we can unlock the flush tables with read lock earlier and no need to wait until all non-innodb tables were dumped. You can take advantage of this feature when you have large archive or myisam tables.
  • File size chunks
    Now you can split tables dump into different files with fixed size. This is usefull to reduce storage capacity needed to keep history backups by using hardlinks. Think on big “log” tables or tables where old data didnt change, now you will be able to hardlink back those chunks.
  • Metadata Locking
    Added new option –use-savepoints to reduce metadata locking issues while backup is running.

Fixed Bugs in the mydumper 0.6 series:

mydumper 0.6.0

  • #1250269 ensure statement is not bigger than statement_size
  • #827328 myloader to set UNIQUE_CHECKS = 0 when importing
  • #993714 Reducing the time spent with a global read lock
  • #1250271 make it more obvious when mydumper is not successful
  • #1250274 table doesnt exist should not be an error
  • #987344 Primary key name not quoted in showed_nulls test
  • #1075611 error when restoring tables with dashes in name
  • #1124106 Mydumper/myloader does not care for 0-s in AUTO_INCREMENT fields
  • #1125997 Timestamp data not portable between servers on differnt timezones

mydumper 0.6.1

  • #1273441 less-locking breaks consistent snapshot
  • #1267501 mydumper erroneously always attempts a dummy read
  • #1272310 main_connection keep an useless transaction opened and permit infinite metadata table lock
  • #1269376 mydumper 0.6.0 fails to compile “cast from pointer to integer of different size”
  • #1265155 create_main_connection use detected_server before setting it
  • #1267483 Build with MariaDB 10.x
  • #1272443 The dumping threads will hold metadata locks progressively while are dumping data.

Note: #1267501 fix is important for any galera cluster deployment, because of this bug #1265656 that was fixed and released in Percona XtraDB Cluster 5.6.15-25.3.

The post New mydumper 0.6.1 release offers performance and usability features appeared first on MySQL Performance Blog.

Categories: MySQL

Percona Server 5.5.36-34.0 is now available

MySQL Performance Blog - Mon, 2014-03-10 14:53

Percona Server version 5.5.36-34.0

Percona is glad to announce the release of Percona Server 5.5.36-34.0 on March 10th, 2014 (Downloads are available here and from the Percona Software Repositories). Based on MySQL 5.5.36, including all the bug fixes in it, Percona Server 5.5.36-34.0 is now the current stable release in the 5.5 series. All of Percona‘s software is open-source and free, all the details of the release can be found in the 5.5.36-34.0 milestone at Launchpad.

New Features:

  • Debian and Ubuntu packaging has been reworked to meet the packaging standards.
  • Ability to change database for mysqlbinlog has been ported from Percona Server 5.1
  • Percona Server has implemented Slow Query Log Rotation and Expiration feature to provide users with better control of slow query log disk space usage.
  • In order to comply with Linux distribution packaging standards Percona‘s version of libmysqlclient has been renamed to libperconaserver. The old name was conflicting with the upstream libmysqlclient. Except for packaging, libmysqlclient and libperconaserverclient of the same version do not have any differences. Users wishing to continue using libmysqlclient will have to install the corresponding package of their distribution, such as mysql-lib for CentOS and libmysqlclient18 for Ubuntu/Debian. Users wishing to build software against libperconaserverclient should install libperconaserverclient-dev package. An old version of Percona-built libmysqlclient will be available for download.

Bugs Fixed:

  • The XtraDB version number in univ.i was incorrect. Bug fixed #1277383.
  • Percona Toolkit UDFs were only shipped with RPM packages. Bug fixed #1159625.
  • Server could crash if it was signaled with SIGHUP early in the server startup. Bug fixed #1249193 (upstream #62311).
  • Server could crash if XtraDB innodb_dict_size_limit option was set due to incorrect attempts to remove indexes in use from the dictionary cache. Bugs fixed #1250018 and #758788.
  • Fix for bug #1227581, a buffer pool mutex split regression, was not complete, thus a combination of write workload and tablespace drop could crash the server if InnoDB compression was used. Bug fixed #1269352.
  • Binary RPM packages couldn’t be built from source tarballs on Fedora 19. Bug fixed #1229598.
  • Percona Server that was compiled from source package had different server version string from that of binary packages. Bug fixed #1244178.
  • InnoDB did not handle the cases of asynchronous and synchronous I/O requests completing partially or being interrupted. Bugs fixed #1262500 (upstream #54430), and #1263087 (Andrew Gaul).
  • Fixed the CMake warnings that were happening when Makefile was generated. Bugs fixed #1274827 (upstream #71089).
  • Percona Server source tree has been reorganized to match the MySQL source tree layout closer. Bug fixed #1014477.
  • On Ubuntu Precise multiple architecture versions of libmysqlclient18 couldn’t be installed side by side. Bug fixed #1052636.

Other bugs fixed: #1005787.

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

The post Percona Server 5.5.36-34.0 is now available appeared first on MySQL Performance Blog.

Categories: MySQL

Q&A: Common (but deadly) MySQL Development Mistakes

MySQL Performance Blog - Fri, 2014-03-07 11:00

On Wednesday I gave a presentation on “How to Avoid Common (but Deadly) MySQL Development Mistakes” for Percona MySQL Webinars. If you missed it, you can still register to view the recording and my slides.

Thanks to everyone who attended, and especially to folks who asked the great questions. I answered as many as we had time for during the session, but here are all the questions with my complete answers:

Q: Does a JOIN operation between two tables always produce an “access table” on the rows of the first table of the join, or it is possible to define an index (COVERING INDEX) to avoid this access to the first table?

Yes, if your query references only columns in the index, then MySQL can create the query result without having to access table rows. That’s the way a covering index gives benefit, by eliminating many page accesses. MySQL can get this optimization on any table in your query, even the first table in a JOIN.

Q: What are the performance drawbacks of prepared statements?

Some people have supposed that a prepared statement causes overhead, because it involves two communications between the application and the database server. First to send the SQL query with placeholders, and second to send parameter values. But unless you have a very slow network between your app and the database server, this isn’t a significant amount of overhead, and the benefits of using prepared statements usually outweigh the cost.

In fact, in a past blog post, Peter Zaitsev showed in a test that prepared statements allow for a significantly higher rate of queries per second than non-prepared queries.

I have also done tests with PHP, and I found that the overhead to response time is due more to additional PHP code than the performance on the database server side.

One more performance consequence of using prepared statements: compatibility with the query cache. If you issue SQL statements with the text protocol, i.e. using PREPARE and EXECUTE statements, then MySQL cannot use the query cache. But applications typically run dynamic SQL using the binary protocol, and that is compatible with the query cache.

Q: What about using fixed length text fields rather than VARCHARs?

CHAR would be an example of a fixed-length string data type. MyISAM tables have an optimization when all columns of a table use fixed-length data types. Then the row position in the data file is simply a multiple of the row size.

But InnoDB does not have this optimization. Rows are always located on pages, and pages are referenced by pointers rather than by computing an offset in the file. So there is no benefit to using fixed-length string data types.

Please don’t take this as a recommendation to use MyISAM. You should use InnoDB in virtually all cases. InnoDB has better performance that MyISAM in most workloads, but even more importantly it supports ACID behavior and has crash recovery. MyISAM is gradually being phased out.

Q: Advice on fixing some of the datatype or character set problems? 

You can use a query modifier like PROCEDURE ANALYSE to help you find out what is the optimal datatype for each of your columns.

The tricky part, though, is changing a data type on a table populated with a lot of data. Using ALTER TABLE may lock the table for the duration of restructuring it to change a data type, and that can interrupt your work. It takes time to do this, proportional to the size of data in the table and the number of indexes that need to be rebuilt.

MySQL 5.6 has new features for InnoDB to perform ALTER TABLE as online DDL. This can help in some cases to avoid locking your table. In the case of changing a data type, you can continue to read the table, but you can’t make changes while the ALTER TABLE is running.

Another solution is to use Percona Toolkit’s pt-online-schema-change. This free tool performs table alterations during which you can continue to read and write data.

Q: Can you talk about index cardinality vs. selectivity and cases where an index does not help because the number of pages read by the database does not decrease by using an index?

Selectivity is the ratio of the rows matched by a given query to the number of rows. Say for instance, I am looking for blog posts written by a given user. There are 1500 blog posts, and 15 written by me. That’s a selectivity of 15/1500, or 1%.

MySQL’s optimizer pays attention to selectivity, and will prefer not to use an index if the selectivity goes over 20%.

To understand this, think of an analogy to a book with an index at the back. Why doesn’t the index contain common words like “the”? Because a word like “the” would occur on virtually every page of the book, so its entry in the index would simply list every page. The list of pages itself could be pages long! If we require to use the index to find all those pages, it seems like a waste of time to flip back and forth, using the index to tell us where the next matching page is. It would be easier and more efficient to simply read the book cover-to-cover.

It is similar in a database index. Loading pages of index structures has some cost, just like loading pages of data rows. So there’s a selectivity breakpoint at which it becomes more efficient to simply do a table-scan. Hopefully MySQL makes the correct estimate of selectivity and where that breakpoint is, but occasionally it can get that wrong.

In those cases, you can override the optimizer by using Index Hints. But those cases are generally rare; its better to avoid using optimizer hints unless you are forced to.

Cardinality is an estimate of the number of unique values in an index. You can see it in the output of SHOW INDEX. A unique index by definition has cardinality equal to the number of rows. Cardinality doesn’t come up as often for the optimizer, except that a unique index on a PRIMARY or UNIQUE KEY allows MySQL to access the table with a different join type, that being const or eq_ref.

Q: The blog post said the partitioning and sharding topic will be touched. Unfortunately, it wasn’t, but I’d like to try and bring it up.

Apologies for that, I covered as many topics as I could during a one-hour webinar. I’ll plan to do another webinar in the future, and I’ll make sure to cover partitioning and sharding in more detail then. But on to your question…

Q: What would be the dataset size when you should think about sharding.

The decision to use sharding has little to do with dataset size. It has to do with the rate of traffic — especially write traffic. That is, use sharding when you’re trying to INSERT or UPDATE at such a rapid rate of queries per second that your database server can’t physically handle the rate of changes. Sharding allows us to scale out to multiple database servers, each of which will handle a fraction of the writes.

Q: And what would be the best choice middleware for the task? I.e. MySQL proxy is still alpha, so it’s not something you would put into production yet.

Right, MySQL Proxy has been available for years (version 0.7.0 is dated 2009-01-13 in launchpad.net), but curiously, its documentation still explicitly says it’s in alpha.

Oracle is currently working hard on a new solution to manage sharded architectures, it’s called MySQL Fabric. It’s still under development, but it’s available at MySQL Labs and you can download it and try it out. No doubt the developers would greatly value community feedback.

Another tool developed by Percona veteran Justin Swanhart is Shard-Query. This works as a proxy, and transparently maps your SQL statements into multiple queries, executed in parallel against individual shards in your cluster. Then it combines the results into a single result set. You can watch the recording of Justin’s past webinar about Shard-Query.

Q: Would you recommend moving to 5.6 (in production)?. The benchmark numbers looks amazing.

Yes, MySQL 5.6 demonstrates some great benchmarks, and Percona Server 5.6 adds even greater scalability. I would certainly recommend upgrading if you haven’t already.

When 5.6 first came out, there were a few high-profile bugs. But that’s true of every initial release of a new major version of any software. It pays to test carefully before any major upgrade, and to have a fallback plan. But it’s been a year since the first GA release of 5.6, and we have worked with many customers who have deployed it successfully in production and are very happy with the performance improvements.

Q: Would also love to hear what you think about sharding plus partitioning, and MySQL cluster.

I’ll go into more detail on sharding plus partitioning in a future webinar.

But let me say this about MySQL Cluster: the NDB storage engine is brilliant, it provides high availability at the same time as high performance, and internally I am told it’s one of the best pieces of code with the best test suite of any component of the MySQL family.

However: you should be aware that using any kind of sharded architecture requires that you design your database schema and your queries to match the way the data is sharded. Sharding works best when your queries need to access only one shard. For example, reading or writing just one row at a time.

If your queries involve range conditions, for instance, this is likely to require all shards to respond to the query, and that would gain you nothing in regards to performance or scalability. In fact, we’ve seen cases where a company migrated an existing application from a traditional MySQL instance to MySQL Cluster without designing their schema for sharding, and their performance degraded instead of improving.

Another example is if you do a JOIN between related rows, but the sharding by primary key has spread the related rows across multiple shards, then the JOIN could actually be more expensive than if you were running in a non-sharded environment. The fix in that case is to make sure your keys are defined such that the foreign key in related rows has the same modulus as the primary key of their parent row.

Q: You said that varchar columns stores efficiently on disk in data and indexes and in the buffer pool. But a string in the SQL layer pads out by using as much space as the maximum length. Increased 3x for utf8 or x4 for utf8mb4. I don’t understand what the “SQL Layer” means. Can you explain that?

The MySQL server has an architecture that divides its work between two main areas of code: the first part is storage-independent tasks like accepting connections, session management, authentication, SQL parsing, query optimization, logging, replication, the query cache, etc. We call this part of MySQL Server the “SQL layer” (or sometimes the “MySQL layer”).

The other part is the storage engine, which is responsible for reading and writing rows, some kinds of caching (like the buffer pool in the case of InnoDB), indexes, I/O, and so on.

The SQL layer knows how to parse queries and turns them into a series of individual row requests. Then the storage engine for a given table fulfills those row requests, reading them off disk or from the buffer pool as needed. As the storage engine returns rows, they are copied from one part of the code (the storage engine) to the other part (the SQL layer), and then prepares to return them as a query result set.

My point in the webinar is that the storage engine knows how to store VARCHAR data in a compact form, by storing variable-length strings. But the SQL layer treats rows as fixed-length records (except for BLOB/TEXT) and therefore allocates memory for VARCHAR strings according to the maximum length the column could be.

*  *  *

Thanks again for attending my webinar! Remember that you can still view the slides and the recording for “Common (but deadly) MySQL Development Mistakes.”

If you want even more tips and content about MySQL from the best experts in the community, you should go to the Percona Live MySQL Conference & Expo, April 1-4 in Santa Clara, California. Register here: http://www.percona.com/live/mysql-conference-2014/

The post Q&A: Common (but deadly) MySQL Development Mistakes appeared first on MySQL Performance Blog.

Categories: MySQL

Percona XtraBackup 2.1.8 is now available

MySQL Performance Blog - Thu, 2014-03-06 15:58

Percona is glad to announce the release of Percona XtraBackup 2.1.8 on March 6th, 2014. 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.

Bugs Fixed

  • Due to incorrect usage of posix_fadvise() hints, Percona XtraBackup discarded read-ahead buffers which resulted in higher I/O rate on the backup stage. Bug fixed #1093385.
  • Spurious trailing data blocks that would normally be ignored by InnoDB could lead to an assertion failure on the backup stage. Bug fixed #1177201.
  • A spurious warning message could cause issues with third-party wrapper scripts. Bug fixed #1271956.
  • xbcrypt could fail with the xbcrypt:xb_crypt_read_chunk: unable to read chunk iv size at offset error under some circumstances. Bug fixed #1273196.
  • xbstream could sometimes hang when extracting a broken or incomplete input stream. Bug fixed #1273207.
  • Preparing backups of MySQL or Percona Server 5.6 could raise an assertion failure in Percona XtraBackup. Bug fixed #1273468.
  • The downtime is decreased when the --safe-slave-backup option is used to backup a replication slave. The SQL thread is now started before a temporary copy of redo log is streamed into the final location. Bug fixed #1284778.
  • Disabled the “binary version check” functionality in the VersionCheck module due to security concerns. Bug fixed #1285166.

Other bugs fixed: #1284078.

Release notes with all the bugfixes for Percona XtraBackup 2.1.8 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.8 milestone at Launchpad. Bugs can be reported on the launchpad bug tracker.

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

Categories: MySQL
Syndicate content