Personal blog of Yzmir Ramirez

Percona is pleased to announce the availability of Percona Toolkit 2.2.11.  Released on Sept. 25, Percona Toolkit is a collection of advanced command-line tools to perform a variety of MySQL server and system tasks that are too difficult or complex for DBAs to perform manually. Percona Toolkit, like all Percona software, is free and open source.

This release contains bug fixes for pt-query-digest, pt-mysql-summary, pt-stalk, as well as other tools and is the current GA (Generally Available) stable release in the 2.2 series. Downloads are available here and from the Percona Software Repositories.

Bugs Fixed:

• Fixed bug #1262456: pt-query-digest didn’t report host details when host was using skip-name-resolve option. Fixed by using the IP of the host instead of its name, when the hostname is missing.
• Fixed bug #1264580: pt-mysql-summary was incorrectly parsing key/value pairs in the wsrep_provider_options option, which resulted in incomplete my.cnf information.
• Fixed bug #1318985: pt-stalk is now using SQL_NO_CACHE when executing queries for locks and transactions. Previously this could lead to situations where most of the queries that were waiting on query cache mutex were the pt-stalk queries (INNODB_TRX).
• Fixed bug #1348679: When using -- -p option to enter the password for pt-stalk it would ask user to re-enter the password every time tool connects to the server to retrieve the information. New option --ask-pass has been introduced that can be used to specify the password only once.
• Fixed bug #1368379: A parsing error caused pt-summary ( specifically the report_system_info module) to choke on the “Memory Device” parameter named “Configured Clock Speed” when using dmidecode to report memory slot information.

Details of the release can be found in the release notes and the 2.2.11 milestone at Launchpad. Bugs can be reported on the Percona Toolkit launchpad bug tracker.

The post Percona Toolkit 2.2.11 for MySQL is now available appeared first on Percona Performance Blog.

 BlackBerry could be about to become the most interesting company in the smartphone business: The Canadian telecommunications firm will deliver a minimum of one “unconventional device” per year, according to a new report from Reuters, following the launch of the square-screened Passport last week.
The Passport is definitely an outlier gadget, with its wide-bodied construction… Read More

MySQL is the database of choice for most OpenStack components (Ceilometer is a notable exception). If you start with a small deployment, it will probably run like a charm. But as soon as the dataset grows, you will suddenly face several challenges. We will write a series of blog posts explaining the issues you may hit and how to overcome them.

Where is MySQL used in OpenStack?

Have a look at the logical diagram of OpenStack below (click the image for a larger view).

 

The diagram is a bit outdated: Neutron appears as Quantum and newer components like Heat are not pictured. But it shows that a database has to be used to store metadata or runtime information. And although many databases are supported, MySQL is the most common choice. Of course MySQL can also be used in instances running inside an OpenStack cloud.

What are the most common issues?

As with many applications, when you start small, the database is running well and maintenance operations are fast and easy to perform. But with a dataset that grows, you will find that the following operations are becoming increasingly difficult:

1. Having good backups: mysqldump is the standard backup tool for small deployments. While backups of instances having 100GB of data is still quite fast, restore is single-threaded and will take hours. You will probably need to use other tools such as Percona XtraBackup, but what are the tradeoffs?
2. Changing the schema: whenever you have to add an index, change a datatype or add a column, it can trigger a table rebuild which will prevent writes to proceed on the table. While the rebuild is fast when the table has only a few hundreds of MBs of data, ALTER TABLE statements can easily take hours or days for very large tables. Using pt-online-schema-change from Percona Toolkit is a good workaround, but it doesn’t mean that you can blindly run it without any precaution.
3. Making the database highly available: whenever the database is down, the whole platform is down or runs in a degraded state. So you need to plan for a high availability solution. One option is to use Galera, but that can introduce subtle issues.
4. Monitoring the health of your database instances: MySQL exposes hundreds of metrics, how do you know which ones to looked at to quickly identify potential issues?

1. and 2. are not likely to be issues for the MySQL instance backing your OpenStack cloud as it will be very small, but they can be big hurdles for guest databases that can grow very large depending on the application.

3. and 4. are highly desirable no matter the size of the database.

Stay tuned for more related posts on MySQL & OpenStack – and feel free to give us your feedback! And remember that if MySQL is showing bad performance in your OpenStack deployment, Percona is here to help. Just give us a call anytime, 24/7. I also invite you and your team to attend the inaugural OpenStack Live 2015 conference, which runs April 13-14, 2015 in Santa Clara, Calif. It runs alongside the Percona Live MySQL Conference and Expo (April 13-16) at the Hyatt Regency Santa Clara and the Santa Clara Convention Center.

The post MySQL & OpenStack: How to overcome issues as your dataset grows appeared first on Percona Performance Blog.

 At some point, speculating about what will become quickly obvious is difficult. Still, on the cusp as we are of the release of the first preview of what may be called Windows 9, it’s reasonable to take a few notes of the latest rumor cycle: Will Windows 9 be free? Current gossip indicates that for Windows 8 and Windows XP users, the new code could be in the case of the former, free, and… Read More

 Enterprise application marketplaces have grown significantly in number and stature in recent years. This trend has emerged as a response to developers in corporate IT departments who are stifled by lengthy procurement processes, locked into long contracts and highly restricted when it comes to deploying exciting products offered by startup vendors. Read More

The media train is in full steam today over the the CVE-2014-6271 programming flaw, better known as the “Bash Bug” or “Shellshock” – the original problem was disclosed on Wednesday via this post. Firstly this issue exploits bash environment variables in order to execute arbitrary commands; a simple check for this per the Red Hat security blog is the following:

env x='() { :;}; echo vulnerable’ bash -c “echo this is a test”

If you see an error:

bash: warning: x: ignoring function definition attempt
bash: error importing function definition for `x’

Your version of bash is not vulnerable, if you see the text “vulnerable” – however you are.

The question becomes “how much of a problem is this really?” It’s a valid question given that even with the potential to exploit this issue via AcceptEnv for ssh connections; the attack appears to be a “local user” exploit.

I’d like to point out that it has been noted that there’s the potential for this to be exploitable in CGI web applications; and it’s also worth being aware of this new metasploit module which exploits an issue in dhclient where code injection can occur via crafted hostname response to DHCP requests, in my personal opinion this is a far wider issue in dhclient itself.

Redhat also notes that the current fix for “shellshock” is incomplete as per CVE-2014-7169 

Is MySQL affected?

It does not appear to be directly affected at this time; unless you have a UDF allowing shell command execution.

MySQL documentation on environment variables as per here modified local behavior of the client only not the server. (without local modification server side).

Additional resources:

• Redhat bugzilla
• Redhat CVE-2014-6271 page
• Redhat announcement
• Qualys CVE-2014-6271 page
• Mubix’s github page covering Shellshock and variants 

Is my application affected?

There’s no singular answer here given the diversity of applications.  For instance if you’re using PHP and putenv then you potentially have quiet a large attack surface in you application for this specific vulnerability; the best recourse here is to ensure your follow best practices – e.g. update to the latest packages, test the vulnerability, ensure you application is running as a non privileged user, ensure you application only has access to the MySQL permissions it needs; and ensure you’re running a mandatory access control e.g. SELinux / Apparmor as an additional layer of defense.

Suricata and Snort signatures for shellshock as per this volexity blog post

Suricata

alert http $EXTERNAL_NET any -> $HOME_NET any (msg:”Volex – Possible CVE-2014-6271 bash Vulnerability Requested (header)”; flow:established,to_server; content:”() {“; http_header;  threshold:type limit, track by_src, count 1, seconds 120; sid:2014092401;


Snort


alert tcp $EXTERNAL_NET any -> $HOME_NET $HTTP_PORTS (msg:”Volex – Possible CVE-2014-6271 bash Vulnerability Requested (header) “; flow:established,to_server; content:”() {“; http_header;  threshold:type limit, track by_src, count 1, seconds 120; sid:2014092401;)

The post ‘Bash Bug’ giving you Shellshock? CVE-2014-6271 update appeared first on Percona Performance Blog.

We are pleased to announce the third release in the 0.6 series of mydumper, a tool for performing logical MySQL backups. In this release, we focused on simplifying compiling the code and added new features for making logical backups. These new features include enhancements to AWS RDS support and extending TokuDB support.

Due to recent changes (or not so much) on mysql libs, it became impossible to compile mydumper without the complete mysql source code. To simplify this, we had to disable the binlog functionality by default, as it was the one affected by the mysql libs changes. Now you should be able to compile against any mysql version without issues. If you still want the binlog feature, it is still there and you can enable it with:

cmake . -DWITH_BINLOG=ON

The one caveat is that you will need to compile against a mysql version greater than 5.5.34. It’s also possible to compile with binlog enabled against the latest 5.6 versions. In this case, you will need the source code to make some changes. You can find related information about this issue at these locations:

• http://bugs.mysql.com/bug.php?id=70672
• https://bugs.launchpad.net/mydumper/+bug/1316001.

Download mydumper-0.6.2 source code here.

Bugs Fixed:

• #1347392 Last row of table not dumped if it brings statement over statement_size
• #1157113 Compilation of latest branch fails on CentOS 6.3 64bit
• #1326368 Can’t make against Percona-Server-devel-55 headers
• #1282862 Unknown type name ‘HASH’
• #1336860 k is used twice
• #913307 Can’t compile – missing libs crypto and ssl
• #1364393 Rows chunks doesn’t increase non innodb jobs

New MySQL Backup Features:

• --lock-all-tables

Use LOCK TABLE for all instead of FLUSH TABLES WITH READ LOCK. With this option you will be able to backup RDS instances and also get the binlog coordinates (5.6).

• TokuDB support

Now TokuDB tables are dumped within the consistent snapshot instead of being locked like MyISAM.

• Support to dump tables from different schemas

mydumper has two arguments,

    --database (-B)

and

    --tables-list (-T)

so until now you were able to do;

-B db1

or

-B db1 -T t1,t2,tn

To dump a whole database or a list of tables from one database respectively. In 0.6.2 you can list tables in different databases in this way;

-T db1.t1,db1.t2,db2.t1,db2.t1

NOTE: You should not use -B here because mydumper will take the -T list as table names.

The post Logical MySQL backup tool mydumper 0.6.2 now available appeared first on Percona Performance Blog.

I’m excited to once again be heading to San Francisco next week for Oracle OpenWorld, and also very pleased to have a booth there this year along with some great speakers from Percona.

The scope of Oracle OpenWorld 2014, which runs Sept. 28-Oct. 2, is enormous and there are several keynotes and sessions I’m looking forward to attending. Especially the talks focused on the areas of:

• MySQL architecture and application development
• Cloud and Big Data
• Database administration and DevOps
• High-Availability and Replication
• Performance and Scalability

I also have the honor of speaking on the topic of “Practical MySQL Optimization.” Other sessions led by Percona staff include:

• “MySQL Query Patterns, Optimized,” by Bill Karwin
• “Schema Management: Versioning and Automation with Puppet and MySQL Utilities,” by Frederic Descamps
• And BOF Sessions:
  • “How to Create a Useful MySQL Bug Report,” by Valerii Kravchuk
  • “MySQL and GIS,” by Alexander Rubin

All of us will, at one time or another, be at the Percona booth (Booth 2413), so please stop by and say “hello.” And while you’re there, register to win a new Kindle – the Fire HDX 8.9 Tablet.

We’ll also be raffling off passes to Percona Live London (Nov. 3-4), the annual Percona Live MySQL Conference and Expo 2015 (PLMCE, April 13-16) and the new OpenStack Live 2015 conference, which runs alongside PLMCE April 13-14 – also at the Hyatt Regency Santa Clara & the Santa Clara Convention Center.

Let me know if you’ll be attending and do be sure to stop by our booth and say “hello!” I hope to see you there!

 

 

 

 

The post Looking forward to Oracle OpenWorld 2014; visit us at Booth 2413! appeared first on Percona Performance Blog.

Recently I encountered a specific situation in which a customer was forced to stay with the MyISAM engine due to a legacy application using tables with over 1000 columns. Unfortunately InnoDB has a limit at this point. I did not expect to hear this argument for MyISAM. It is usually about full text search or spatial indexes functionality that were missing in InnoDB, and which were introduced in MySQL 5.6 and 5.7, respectively, to let people forget about MyISAM. In this case though, InnoDB still could not be used, so I gave the TokuDB a try.

I’ve created a simple bash script to generate a SQL file with CREATE TABLE statement with the number of columns I desired and then tried to load this using different storage engines. Bit surprisingly, InnoDB failed with column count above 1017, so little more then documented maximum of 1000:

mysql> source /home/vagrant/multicol2.sql
ERROR 1117 (HY000): Too many columns

MyISAM let me to create maximum 2410 columns and I could achieve the same result for the TokuDB table! Tried with tinyint or char(10) datatype, same maximum cap applied, not quite sure why it’s exactly 2410 though.

mysql> SELECT tables.TABLE_NAME,count(*) columns,engine,row_format FROM information_schema.columns JOIN information_schema.tables USING (TABLE_NAME) where TABLE_NAME like "multicol%" group by TABLE_NAME;
+-----------------+---------+--------+-------------+
| TABLE_NAME      | columns | engine | row_format  |
+-----------------+---------+--------+-------------+
| multicol_innodb |    1017 | InnoDB | Compact     |
| multicol_myisam |    2410 | MyISAM | Fixed       |
| multicol_toku   |    2410 | TokuDB | tokudb_zlib |
+-----------------+---------+--------+-------------+
3 rows in set (0.31 sec)

So if you have that rare kind of table schema with that many columns and you wish to be able to use a transaction storage engine, you may go with TokuDB, available also with recent Percona Server 5.6 versions.

You can find more details about column number limits in MySQL in this post, “Understanding the maximum number of columns in a MySQL table.”

The post More then 1000 columns – get transactional with TokuDB appeared first on Percona Performance Blog.

 Hootsuite, a platform that lets businesses and individuals monitor and manage social media interactions across some 35 different social networks, is stepping up its game with yet more funding and another acquisition. It has raised a further $60 million, and it is acquiring Zeetl, which creates shortlinks to convert text-based interactions between businesses on customers on social media… Read More