Feed aggregator

Database security: Why should you review yours?

MySQL Performance Blog - Wed, 26/03/2014 - 03:27

Ah database security… the black sheep of topics and something you would really rather not have to deal with right?

I mean surely all the fanfare and paranoia is reserved for the neck beards with tinfoil hats whom live in their own D.I.Y Faraday cage … that must be it … it just has to be?

No, the hard reality is the world is not rose tinted and “they” are out to get you be it for fun or for profit; from defacements to theft compromising your applications, and more importantly your data is big business. For some these acts are nothing short of sheer entertainment for an otherwise boring evening. (I’ll be speaking about this topic next week in much more detail at the Percona Live MySQL Conference and Expo in Santa Clara, California. My session, “Security and why you need to review yours” will go into much more detail on April 2. Use the code “SeeMeSpeak” on the registration page and save 10 percent.)

Note I’m avoiding discussing corporate espionage/government spying, as this re-enforces the image of it all being cloak and dagger; admittedly some of it is … the part that’s going to affect you, your business, your livelihood isn’t.

It’s time for that wake-up caffeine infusion and drink the “kool aid” – this is not something you can shun and pretend it’s a monster under the bed/in the closet that doesn’t really exist. Unlike the “bogey man” these threats are real, and have real impacts to peoples livelihood.

F.U.D? The fear part I certainly am wanting to portray here; a level of Fear is healthy;  it keeps you alert, the uncertainty and doubt? No these should and will be removed so please allow me to continue.

Removing Uncertainty
As with anything that requires research I’m sure you the reader would carry out proper “Due Diligence.” You wouldn’t want anything to adversely affect your product/business, taking hosting as an example you’ll look at the providers reputation their S.L.A. agreements etc.

What about their Security credentials? PCI / SOX / HIPAA … there’s numerous classifications.

“But I don’t need PCI / SOX / HIPAA!” to this I say you need a compromise of your application/business even less… what’s the harm in asking the provider if they have been through any regulatory compliance? I just don’t get the stigma some people seem to feel when asking a question related to security. Remember when deploying your application you’re building upon your hosting providers infrastructure.

“A foolish man who built his house on sand” in short if your foundations are not sound you’re opening yourself up to failure and compromise, an example Paypal and godaddy socially engineered resulting in the loss of a $50K twitter username and Barclays £1.37M theft both of which are due to the same level of Failure, the term for which is “Social Engineering” … which really is just a new term for conning someone into doing what you want them to do.

“The art of the con” is hardly anything new; and has been around for centuries take Victor Lustig whose infamous example of con artistry was to sell the Eiffel tower for scrap … twice.

Dispelling Doubt
“By failing to prepare you are preparing to fail” - Benjamin Franklin

Let’s look at this a little more with some common misconceptions.

“I don’t really need to look at security, my project business is small and will not be attacked.” I’d liken this statement to saying you don’t need seat belts and air bags because you’re a careful driver; driving at night with no lights on because “I have good night vision.” You have safety and security measures in your everyday life which because they fall as part of the routine are not thought about: locks on doors, car/home/business alarms, cctv, gps locators for phones/cars/tablets/laptops … we need to eliminate this thinking that information security is anything other than a requirement which should form part of our every day “norms”.

“Security is too expensive.” Have you looked at the cost of a compromise of your system, how much is the potential loss of your entire customer base worth to you? …not looking quiet so expensive now is it? Liken an investment in security to an investment in High Availability: you want your application to be “always on” … why do many think “secure” is prohibitively expensive to achieve?

“We simply don’t have the resources to implement security measures.” Yet you have the resources for development, DBA’s, sysadmins? One of the best ways to introduce security into your application is “from the ground up,” so that it becomes part of your general practise – this requires a “state of mind” orientated toward security.

What many fail to realize is assuming your business is successful you already have a state of mind orientated to best practises which work for your business to produce an application/service of value; minor tweaking to this could also introduce a mindset of security.

Remediation - “the action of remedying something, in particular of reversing or stopping environmental damage.”

It’s not going to be a painful or expensive as you may think; the first most powerful step is a minor change of development/sysadmin attitudes to consider security implications of code/services/configurations, let’s bring back the healthy attitude of asking questions.

Do I really need to disable SELinux to get this to work? – the answer is of course no you shouldn’t, this should be the same vein of thought of do I really need to chmod this 777 to make it work?

Does this service really need to be installed? – e.g. bluetoothd doesn’t need to be on your production machines.

We’re adding a user input form, we should really sanitize the input – seems obvious to most now, though this was met with just as much “resistance to change” before it became a best practise standard.

Does MySQL really need to be accessible from everywhere on the internet? – again may seem obvious to most now, though this was and sometimes still met with resistance of “I may need to run queries from home, off my mobile, from the open wifi at the local coffee shop …” (those of a security orientated nature I apologize for this statement and I can sense the cringing now in progress as this is read …).

The above is just a small example of reducing your attack surface. Your attack surface being the potential entry points into your system/network/application which can be potentially attacked.

The thinking behind the need to invest a lot of money into some expensive appliance / magic solution is for the most part misguided and throwing a “security blanket” over or in front of your application isn’t going to be as effective as ensuring consideration of security at every layer of your business.

Over the coming months I will be working on more related blogs, webinars – and a reminder that I’ll also be giving a talk on “Security and why you need to review yours” at Percona Live in Santa Clara this April 2nd.

The post Database security: Why should you review yours? appeared first on MySQL Performance Blog.

Percona Server 5.5.36-34.2 is now available

MySQL Performance Blog - Wed, 26/03/2014 - 00:29

Percona Server version 5.5.36-34.2

Percona is glad to announce the release of Percona Server 5.5.36-34.2 on March 25th, 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.2 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.2 milestone at Launchpad.

This release fixes number of packaging bugs that were introduced in the last two releases.

Bugs Fixed:

  • The upgrade to Percona Server 5.5.36-34.1 would silently comment out any options in my.cnf that have paths specified that contain share/mysql. Bug fixed #1293867.
  • Percona Server could fail to start after upgrade if the lc-messages-dir option was set in the my.cnf configuration file. Bug fixed #1294067.
  • Dependency on mysql-common package, introduced in Percona Server 5.5.36-34.0 could lead to wrongly chosen packages for upgrade, spurious removes and installs with some combination of packages installed which use the mysql libraries. Bug fixed #1294211.
  • These three bugs were fixed by removing the dependency on mysql-common package.
  • Percona Toolkit UDFs and HandlerSocket were missing from Debian/Ubuntu packages, this regression was introduced in Percona Server 5.5.36-34.0. Bugs fixed #1296416 and #1294216.
  • Percona Server installer will create the symlinks from libmysqlclient to libperconaserverclient during the installation on CentOS. This was implemented in order to provide the backwards compatibility after the libmysqlclient library has been renamed to libperconaserverclient .

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

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

Percona Server 5.6.16-64.2 is now available

MySQL Performance Blog - Wed, 26/03/2014 - 00:01

Percona Server version 5.6.16-64.2

Percona is glad to announce the release of Percona Server 5.6.16-64.2 on March 25th, 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.2 milestone at Launchpad.

Bugs Fixed:

  • The upgrade to Percona Server 5.6.16-64.1 would silently comment out any options in my.cnf that have paths specified that contain share/mysql. Bug fixed #1293867.
  • Percona Server could fail to start after upgrade if the lc-messages-dir option was set in the my.cnf configuration file. Bug fixed #1294067.
  • Dependency on mysql-common package, introduced in Percona Server 5.6.16-64.0 could lead to wrongly chosen packages for upgrade, spurious removes and installs with some combination of packages installed which use the mysql libraries. Bug fixed #1294211.
  • These three bugs were fixed by removing the dependency on mysql-common package.
  • Percona Toolkit UDFs were missing from Debian/Ubuntu packages, this regression was introduced in Percona Server 5.6.16-64.0. Bug fixed #1296416.
  • Percona Server installer will create the symlinks from libmysqlclient to libperconaserverclient during the installation on CentOS. This was implemented in order to provide the backwards compatibility after the libmysqlclient library has been renamed to libperconaserverclient .

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

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

innodb_flush_logs_on_trx_commit and Galera Cluster

Open Query blog - Tue, 25/03/2014 - 10:02

We deploy Galera Cluster (in MariaDB) for some clients, and innodb_flush_logs_on_trx_commit is one of the settings we’ve been playing with. The options according to the manual:

  • =0 don’t write or flush at commit, write and flush once per second
  • =1 write and flush at trx commit
  • =2 write log, but only flush once per second

The flush (fsync) refers to the mechanism the filesystem uses to try and guarantee that written data is actually on the physical medium/device and not just in a buffer (of course cached RAID controllers, SANs and other devices use some different logic there, but it’s definitely written beyond the OS space).

In a non-cluster setup, you’d always want it to be =1 in order to be ACID compliant and that’s also InnoDB’s default. So far so good. For cluster setups, you could be more lenient with this as you require ACID on the cluster as a whole, not each individual machine – after all, if one machine drops out at any point, you don’t lose any data.

Codership docu recommended =2, so that’s what Open Query engineer Peter Lock initially used for some tests that he was conducting. However, performance wasn’t particularly shiny – actually not much higher than =1. That in itself is interesting, because typically we regard the # of fsyncs/second a storage system can deal with as a key indicator of performance capacity. That is, as our HD Latency tool shows when you run it on a storage device (even your local laptop harddisk), the most prominent aspect of what limits the # of writes you can do per second appears to be the fsyncs.

I then happened to chat with Oli Sennhauser (former colleague from MySQL AB) who now runs the FromDual MySQL/MariaDB consulting firm in Switzerland, and he’s been working with Galera for quite a long time. He recognised the pattern and said that he too had that experience, and he thought =0 might be the better option.

I delved into the InnoDB source code to see what was actually happening, and the code indeed concurs with what’s described in the manual (that hasn’t always been the case ;-). I also verified this with Jeremy Cole whom we may happily regard as guru on “how InnoDB actually works”. The once-per-second flush (and optional preceding write) is performed by the InnoDB master thread. Take a peek in log/log0log.c and trx/trx0trx.c, specifically trx_commit_off_kernel() and srv_sync_log_buffer_in_background().

In conclusion:

  1. Even with =0, the log does get written and flushed once per second. This is done in the background so connection threads don’t have to wait for it.
  2. There is no setting where there is never a flush/fsync.
  3. With =2, the writing of the log takes place in the connection thread and this appears to incur a significant overhead, at least relative to =0. Aside from the writing of the log at transaction commit, there doesn’t appear to be a difference.
  4. Based on the preceding points, I would say that if you don’t want =1, you might as well set =0 in order to get the performance you’re after. There is of course a slight practical difference between =0 and =2. With =2 the log is immediately written. If the mysqld process were to crash within a second after that, the OS would close the file and have that log write stored. With =0 that log data wouldn’t have been written. If the OS or machine fails, that log write is lost either way.

In production environments, we tend to mainly want to mitigate trouble from system failures, so =0 appears to be a suitable/appropriate option – for a Galera cluster environment.

What remains is the question of why the log write operation appears to reduce transaction commit performance so much, in a way more so than the flush/fsync. Something to investigate further!
Your thoughts?

7 Key MySQL clustering technologies – A joint webinar with 451 Research

MySQL Performance Blog - Tue, 25/03/2014 - 03:01

I’m looking forward to Wednesday’s joint webinar on MySQL clustering technologies with Matt Aslett, research director of data management and analytics over at 451 Research. We’ll be participating in a live, in-depth discussion of MySQL Clustering for High Availability and Scalability.

Matt will present an overview of the trends driving adoption of clustering technology. He’ll also discuss the key technologies and criteria that developers and administrators need to consider when thinking about clustering, in order to improve their likelihood of success.

For my part, I’ll be focusing on a more technical overview of benefits and drawbacks of different clustering approaches as well as looking at specific technologies based on experience with Hundreds of HA and Scaling cases which we implemented or serviced at Percona. These will include:

- MySQL replication
- Storage level replication and shared storage, such as DRBD and SAN
- MySQL Cluster by Oracle
- Galera, such as Percona XtraDB Cluster
- External replication, such as Continuent Tungsten
- Proxy, such as Tesora
- MySQL-compatible standalone implementations, such as Clustrix and MemSQL

The objective of our joint webinar is to give you the information needed when considering the implementation of a MySQL clustering solution for your business and how the various approaches to clustering might fit your unique needs. The webinar starts at 10 a.m. Pacific time on March 26. Register here to reserve your spot. Feel free to post questions in advance for either Matt or me here in the comments section. See you Wednesday!

The post 7 Key MySQL clustering technologies – A joint webinar with 451 Research appeared first on MySQL Performance Blog.

Creating GEO-enabled applications with MySQL 5.6

MySQL Performance Blog - Mon, 24/03/2014 - 23: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.

Notes from the Newb

MySQL Performance Blog - Sat, 22/03/2014 - 09: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.

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

MySQL Performance Blog - Fri, 21/03/2014 - 19: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.

Percona XtraDB Cluster 5.6.15-25.5 is now available

MySQL Performance Blog - Fri, 21/03/2014 - 00: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.

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

MySQL Performance Blog - Thu, 20/03/2014 - 17: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.

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

MySQL Performance Blog - Wed, 19/03/2014 - 22: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.

Hardening your Cacti setup

MySQL Performance Blog - Wed, 19/03/2014 - 19: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.

Percona Server with TokuDB: Packing 15TB into local SSDs

MySQL Performance Blog - Tue, 18/03/2014 - 23: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.

Percona Server 5.6.16-64.1 is now available

MySQL Performance Blog - Tue, 18/03/2014 - 03: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.

Percona Server 5.5.36-34.1 is now available

MySQL Performance Blog - Tue, 18/03/2014 - 03: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.

Ghosts of MySQL Past, part 11: Why are you happy about this?

Stewart Smith - Mon, 17/03/2014 - 10:00

This is part 11 in what’s shaping up to be the best part of a 6 week series (Part 1, 2, 3, 4, 5, 6, 7, 7.1, 8, 8.1, 9 and 10) on various history bits of MySQL, somewhat following my LCA2014 talk (video here).

One of my favorite MySQL stories is the introduction of the Random Query Generator – or randgen. While I’ve talked for a while now about the quality problems that were plaguing MySQL, there were positive steps going on. The introduction of continuous regression testing with pushbuild, increasing test case coverage and a focus on bug fixing were all helping. However… a database server is a complex beast that can accept arbitrary queries and transactions and at the very least is expected not to segfault. How on earth do you test it beyond simple manual testing (or automated manual testing: running the same set of static queries)?

Well, at some point in development, David Axmark (one of the founders of MySQL AB) decided that if they were going to be a real database, they should have a test suite – so he wrote one. It was comprised of two parts: a small binary named mysqltest which basically read a file of SQL, ran it against the server and then displayed the results (and also had a couple of small bits of language such as variables and loops) and the other half was a shell script that started a mysqld and ran mysqltest for each .test file in the mysql-test/t/ directory and diffed the output against the mysql-test/r/*.result files.

This system still exists today, although the shell script has been replaced by a perl script which has been replaced with mysql-test-run.pl version 2 which dealt with replication, starting MySQL Cluster daemons, running tests in parallel and dealt with Windows a lot better. The mysqltest binary is largely unchanged however, and so the now MUCH larger suite of SQL based tests would be familiar a dozen years ago, when the mysql-test suite was in infancy.

But how do you ensure you’re testing enough of the server? Well… you can do code coverage analysis and write more tests manually, but wouldn’t it be much better if you could automatically generate tests? Wouldn’t it be great if you could automatically find a set of queries that caused the server to crash? Or a set of queries where the results differed between versions or other database servers (which would strongly indicate that somebody was buggy)?

Well, it turns out that Microsoft had a similar problem back in the 1990s with Microsoft SQL Server and a system called RAGS was used in the development of Microsoft SQL Server 7.0 (released in 1998).

So, fast forward about ten years and a relatively new hire of MySQL AB, Philip Stoev was working on something called “RQG” or Random Query Generator for MySQL AB. The basic idea was to be able to supply a grammar of what type of queries you would like it to generate, point it at a MySQL server and see what happens.

It turns out that what happened was that after a while, the MySQL server would segfault. To a room of MySQL Server developers, this was a pretty amazing advancement in testing the server – an automated way to find new bugs!

Throwing the Random Query Generator at the in-progress MySQL 6.0 optimizer caused a new innovation: automatic stack trace deduplication and test case minimization. RQG would automatically pair down the list of generated queries to the minimal set required to crash the server, so that you could import that set of queries into the existing mysqltest based regression test suite.

The dataset that RQG would work against was rather fixed, it was about 10 already prepared tables with some easily generated data. In MySQL Cluster, we had a set of C++ classes that ran a multithreaded application that would use integer columns as a checksum for the whole row, so updates would also update the checksum and at the end of your test (which may cause node failures in various scenarios) you can check that each row is intact and hasn’t been corrupted. So, IIRC it may have been me (or Jonas, I honestly cannot remember) who asked if such a feature would be added to RQG and the reply was “when I can no longer crash the server in 4 or 5 queries… I’ll think about it”.

So, developers expressed happiness, and Philip expressed this: “Why are you happy about this? We are about 10 years behind Microsoft in QA, why are you possibly happy about this?”

So with this great new tool, the true state of the current server version, the in-development server version (6.0) and all the optimizer enhancements it was bringing, the new Falcon storage engine and the in progress Maria (now Aria) storage engine would be exposed.

The news was not good.

(although, in the long run, the news was really good, and it’s because of RQG that we now have a really solid MySQL Server)

Further reading:

The joy of Unicode

Stewart Smith - Sat, 15/03/2014 - 15:22

So, back in late 2008, rather soon after we got to start working on Drizzle full time, someone discovered unicodesnowmanforyou.com, or:

Since we had decided that Drizzle was going to be UTF-8 everywhere,(after seeing for years how hard it was for people to get character sets correct in MySQL) we soon added ☃.test to the tree, which tried a few interesting things:

CREATE TABLE ☃; CREATE DATABASE ☃; etc etc

Because what better to show off UTF-8 than using odd Unicode characters for table names, database names and file names. Well… it turns out we were all good except if you attempted to check out the source tree on Solaris. It was some combination of Python, Bazaar and Solaris that meant you just got python stacktraces and no source tree. So, if you look now it’s actually snowman.test and has been since the end of 2008, because Solaris 10.

A little while later, I was talking to Anthony Baxter at OSDC in Sydney and he mentioned Unicode above 2^16 in UTF-8…. so, we had clef.test (we’d learned since ☃ and we were not going to tall it

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

MySQL Performance Blog - Sat, 15/03/2014 - 01: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.

Percona Monitoring Plugins 1.1.2, now with Amazon RDS support

MySQL Performance Blog - Sat, 15/03/2014 - 00: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.

Ghosts of MySQL past, part 10

Stewart Smith - Fri, 14/03/2014 - 11:15

At the end of 2007, the first alpha of MySQL 6.0 was released. Alpha is the key word here, 5.0 was in decent shape by this stage, 5.1 was not ready and there were at least rumors of a MySQL 6.1 tree. I’ll go into MySQL 6.0 later, but since you’re no doubt not running it, you can probably guess the end of that story.

The big news of 2008 was that at the last MySQL AB all company meeting, the first and last one to occur while I was with the company, in January 2008 in Orlando, Florida, it was announced that Sun Microsystems was going to acquire MySQL AB for the sum of ONE BILLION DOLLARS. Unfortunately, there were no Dr Evil costumes at the event. However, much to the amusement of the Sun employees who were present at the announcement, there was Vodka.

So, while the storage engine project that was to be the savior of and the future of the company, Falcon, had the same name as a Swedish beer (that I’ve actually not seen outside Sweden), the code name for the Sun acquisition was Heineken, and there were a few going around in celebration after the acquisition was announced.

I cannot remember where I got this badge (or button if you speak American), but they were going around for a little while, and it was certainly around the Sun era, possibly either at a User Conference or around the Sun acquisition.

Sun was very interested in Falcon. With the new T1000 SPARC processor, with eleventy billion threads over several cores and squarely aimed at internet/web companies, it was also thought there could be some.. err.. synergy there.

So what happened with the Sun acquisition?

Well, we soon discovered that although Sun made a lot of noise as to its commitment to free and open source software, its employment contract, IP agreements and processes were sorely not what we expected. There is, in fact, probably a good book in the process of MySQL AB engineers joining Sun, but the end result ended up being rather good. We changed Sun and made it a whole lot easier for anybody inside Sun to contribute to open source projects.

There was some great people inside Sun that we were able to work with, yet there was also a good dose of WTF every so often as well. There was a great “benchmark” that showed what kind of performance you could get out of a T2000 with MySQL once – you just had to run 32 instances of mysqld for it to scale!

One thing Sun had worked out how to do was release software that worked relatively well. Solaris was known for many things: Slowaris, ancient desktop environment, a userspace more suited to 1994 than 2004 but one thing it was not known for was awful quality of releases.

Solaris required code to work before it was merged into the main branch and if code wasn’t ready to be released, it wouldn’t be merged, missing the train. While this is something that would seem obvious today, it was kind of foreign to MySQL – and it would take a while for MySQL to change its development process.

The biggest problem with Sun was that it was not making money. If you want to know what was most wrong with the company, talk to one of the MySQL sales people who went across to Sun. It was basically near impossible to actually sell something to someone. Even once you had someone saying “I want to give you money for these goods and/or services”, actually getting it through the process Sun had was insane. At the same time I ran a little experiment: how easy was it to buy support for Red Hat Enterprise Linux versus Solaris… you could guess which one had a “buy” button on redhat.com and which one was impossible to find on sun.com.

We (of course) got extra tools, resources and motivation to have MySQL work well on Solaris, especially OpenSolaris which was going to be the next big thing in Solaris land.

I should note, the acquisition did not make millionaires out of all the developers. It did make some VCs some money, as well as executives – but if you look around all the developers at MySQL AB, not a single one has subsequently called in rich.

Syndicate content