Colin CharlesMariaDB 10.0.3: installing the additional engines (12.6.2013, 17:40 UTC)

So MariaDB 10.0.3 Alpha is out. Download it and remember to provide feedback.

When you run SHOW ENGINES by default, you don’t get CassandraSE or the CONNECT engine. Make sure you do a yum install MariaDB-cassandra-engine and a yum install MariaDB-connect-engine

You will run into conflicts if you had an older MariaDB-CassandraSE engine (so yum remove MariaDB-CassandraSE).

Once you’ve got the packages installed, you can either install the plugin or just restart mysqld.

Happy testing!

Related posts:

  1. Using MariaDB on CentOS 6
  2. Plugins & Storage Engines Summit for MySQL/MariaDB
  3. Testing Fedora 19
Link
Peter ZaitsevPercona MySQL University @Portland next Monday! (12.6.2013, 10:00 UTC)

Percona MySQL University @Portland, June 17, 2013We’re less than a week away from Percona MySQL University at Portland, Oregon next Monday, June 17. The latest in a series of FREE one-day educational events, we are pleased to feature 10 technical talks by members of Team Percona as well as local members of the MySQL Community:

The daylong event will be held at Portland State University’s Smith Memorial Student Union, located at 1825 SW Broadway, Suite 327/8/9 Portland, Oregon 97201. Afterward, we’ll have a networking reception at the famed Paddy’s Bar and Grill sponsored by Tag1 Consulting featuring great networking possibilities and free drinks for event attendees.

If you’re in the Portland area and work with MySQL, then this is an event you can’t afford to miss… :)   So register now!

Please also join the Portland MySQL Meetup group for more MySQL-focused events in Portland

If you love the ideal of Percona MySQL University and would like us to bring the event to your city, please let us know!

The post Percona MySQL University @Portland next Monday! appeared first on MySQL Performance Blog.

Link
Monty Program Group BlogMariaDB 10.0.3 alpha Now Available (11.6.2013, 16:00 UTC)

The MariaDB project is pleased to announce the immediate availability of MariaDB 10.0.3. This is an alpha release. See the release notes and changelog for details.

Download MariaDB 10.0.3

Release Notes Changelog What is MariaDB 10.0?

APT and YUM Repository Configuration Generator

About this Release

MariaDB 10.0 is the development version of MariaDB. It is built on the MariaDB 5.5 series with backported and reimplemented features from MySQL 5.6 and entirely new features not found anywhere else.

This is the fourth release in the MariaDB 10.0 series. We are releasing it now to get it into the hands of any who might want to test it. Not all features planned for this series are included in this release. Additional features will be pushed in future releases. See the release notes and changelog for details on what is new in this release.

Do not use alpha releases on production systems.

User Feedback plugin

MariaDB includes a User Feedback plugin. This plugin is disabled by default. If enabled, it submits basic, completely anonymous MariaDB usage information. This information is used by the developers to track trends in MariaDB usage to better guide development efforts.

If you would like to help make MariaDB better, please add “feedback=ON” to your my.cnf (my.ini on Windows) file!

See the User Feedback Plugin page for more information.

Quality

The project always strives for quality, but in reality, nothing is
perfect. Please take time to report any issues you encounter at:

http://mariadb.org/jira

We hope you enjoy MariaDB!

Link
Colin CharlesHomebrew (Mac OS X) and MariaDB 10.0 series (11.6.2013, 12:27 UTC)

Today I performed a brew update. I noticed that MariaDB now exists as stable (5.5.30) and devel (10.0.2). Brew formulas also exist for MySQL (5.6.10) and Percona Server (5.5.30-30.2) now. 10.0.3 is around the corner but I wanted to run 10.0.2 now. This is how I did it:

brew unlink mariadb
brew install --devel mariadb

It’s that simple!

Related posts:

  1. Using MariaDB on CentOS 6
  2. Testing Fedora 19
  3. Paybox Services and seeing MariaDB in use
Link
SkySQL TeamDavid Axmark & Monty Widenius will be Berliners on June 18th - SkySQL & MariaDB Roadshow (11.6.2013, 09:20 UTC)

Join us at the next MySQL & Cloud Database Solutions Day hosted by SkySQL & MariaDB in Berlin on June 18th

read more

Link
Peter ZaitsevHow people are using MySQL… from 1 user to 100 million (upcoming conference talk) (11.6.2013, 08:00 UTC)

Peter Boros will present on MySQL deployments at the RAMP conference July 11-12 in Budapest, Hungary. MySQL can be deployed in several ways, and that means you can choose a tailor-made path that best meets your needs. With simple services or development systems, many people are using a single server with some backups configured, and then simply take the downtime when a restore is needed.

As the application evolves, additional requirements will appear like hot backups, online schema changes, replication based high availability (which has some caveats). Also, because of asynchronous replication, you can end up having inconsistent data on the nodes and encounter replication errors, which you have to deal with.

Even later, when asynchronous or single-threaded replication is not enough, people can use Percona XtraDB Cluster, which has some of its own caveats, but you can also gain a lot from write set replication.

If the application gets really popular, sooner or later you have to think about write scalability, and implement sharding ultimately.

I will give a talk on this topic at next month’s RAMP conference in Budapest, Hungary. My presentation will provide an overview of some possible deployment and scaling scenarios, when it makes sense to use one or an other, and common pitfalls we typically see in our consulting practice. Please let me know if you plan on attending – and also feel free to post questions in advance below.

The post How people are using MySQL… from 1 user to 100 million (upcoming conference talk) appeared first on MySQL Performance Blog.

Link
Peter ZaitsevPercona Server 5.1.69-14.7 now available: A drop in replacement for MySQL (10.6.2013, 18:32 UTC)
Percona Server for MySQL version 5.1.69-14.7

Percona Server for MySQL version 5.1.69-14.7

Percona is glad to announce the release of Percona Server 5.1.69-14.7 on June 10, 2013. A drop in replacement for MySQL, downloads are available  here and from the Percona Software Repositories. Based on MySQL 5.1.69, this release will include all the bug fixes in it. All of Percona’s software is open-source and free, all the details of the release can be found in the 5.1.69-14.7 milestone at Launchpad.

Bugs Fixed:

  • In Ubuntu Precise libmysqlclient18 package was chosen from the distribution’s repository instead of Percona’s which could lead to package conflicts. Bug fixed #1174271.
  • Fixed the RPM Percona-Server-shared-compat package naming issue that could lead to unresolved package dependencies when installing Percona Server 5.1. Bug fixed #893860.
  • The log tracker thread was unaware of the situation when the oldest untracked log records are overwritten by the new log data. In some corner cases this could lead to assertion errors in the log parser or bad changed page data. Bug fixed #1108613.
  • Percona Server wouldn’t start if the XtraDB changed page tracking was enabled and variable innodb_flush_method was set to ALL_O_DIRECT. Bug fixed #1131949.
  • Fixed the RPM package dependencies for different major versions of Percona Server. Bug fixed #1167109.
  • Fixed the CVE-2012-5627 vulnerability, where an unprivileged MySQL account owner could perform brute-force password guessing attack on other accounts efficiently. This bug fix comes originally from MariaDB (see MDEV-3915). Bug fixed #1172090.
  • OpenSSL libraries were not found in 32-bit builds due to a typo. Bug fixed #1175447.
  • Query to the INNODB_CHANGED_PAGES table would cause server to stop with an I/O error if a bitmap file in the middle of requested LSN range was missing. Bug fixed #1179974.
  • Server would crash if an INNODB_CHANGED_PAGES query is issued that has an empty LSN range and thus does not need to read any bitmap files. Bug fixed #1184427.
  • Incorrect schema definition for the User Statistics tables in INFORMATION_SCHEMA (CLIENT_STATISTICS, INDEX_STATISTICS, TABLE_STATISTICS, THREAD_STATISTICS, and USER_STATISTICS)

Truncated by Planet PHP, read more at the original (another 3649 bytes)

Link
Peter ZaitsevMigrating between MySQL schemas with Percona Xtrabackup (10.6.2013, 10:00 UTC)

Percona XtraBackup for MySQLRecently, I was working with a client that asked about using Percona Xtrabackup to take a snapshot of a particular MySQL schema and then reload it with a different schema name on the same server.  It caught me off guard because I’d never really thought about it – typically, I’ve used Xtrabackup simply to clone a server (for replication or migration) or migrate an existing schema to a new server.  However, given the import/export functionality of Xtrabackup combined with Percona Server (Exporting and Importing Tables), it did seem possible.

Further discussion with the client clarified the use case: clients make a mistake and need to compare their old data to their current data on the current live server.  Mysqldump works well for this, but can quite slow on larger schemas.

One of the downsides to mysqldump is the need to scan the full tables and in turn, load that data into and pollute the buffer pool.  Note that this can be somewhat mitigated using innodb_old_blocks_time, but that is outside the scope of this post.  Similarly, reloading the data will be a very IO intense operation (redo logs, binlogs, etc) as well further polluting the buffer pool.  Enter Xtrabackup…

As a quick refresher, Xtrabackup works by copying the dirty tablespace files while streaming the redo-logs to ensure that all transactions are also captured.  The –apply-logs phase simply utilizes the built in crash recovery and applies the redo-logs to the dirty tablespace and voila, you have a consistent binary backup at a point in time.

When running Percona Server, you can utilize the –export flag during the –apply-logs phase and then re-import those files to a running server.  However, you need to have existing table structures in place.  Fortunately, this can be done easily using mysqldump –no-data.

Now, enough with the theory, here is the procedure I used:

  1. Locate an existing snapshot that contains the schema you are interested in (/tmp/snapshot/2013-06-03_11-30/orig)
  2. Get the table structures:  mysqldump –no-data orig > /tmp/orig.schema.sql
  3. Create the new target database:  mysqladmin create orig_old
  4. Load the schema into the target database:  mysql orig_old < /tmp/orig.schema.sql
  5. Ensure innodb_import_table_from_xtrabackup = 1  (dynamic variable)
  6. Prepare the backup using the –export flag:  innobackupex –apply-log –export /tmp/snapshot/2013-06-03_11-30
  7. For each table, run:  ALTER TABLE tblname DISCARD TABLESPACE
  8. Copy the .exp and .ibd files from the snapshot to the new instance:  cp /tmp/snapshot/2013-06-03_11-30/orig/*[.exp|.ibd] /var/lib/mysql/orig_old
  9. Make sure that the files are owned by mysql: chown mysql:mysql /var/lib/mysql/orig_old/*
  10. For each table, run: ALTER TABLE tblname IMPORT TABLESPACE

Now, you have a old version of the schema running side by side with the current version on the same server.  This will allow you to compare and restore values (potentially corrupted via user error or other issues) with SQL rather than needing to import from a remote server.  This can allow for more targeted restores, easier comparison, and allow remote users to compare on a live system without needed to grant access to another “backup” server.

As noted by one of my colleagues (thanks Bill Karwin!), the cumbersome part of this process is the DISCARD/IMPORT TABLESPACE step as that is done manually for each table (currently a blueprint in innobackupex).  He also included this helpful script to generate all of those statements for steps 7 and 10 in two scripts:

mysql -N -B <<’EOF’ > discard-ddl.sql
SELECT CONCAT(‘ALTER TABLE `

Truncated by Planet PHP, read more at the original (another 1671 bytes)

Link
Shlomi NoachEasy SELECT COUNT(*) with split() (8.6.2013, 04:41 UTC)

The two conservative ways of getting the number of rows in an InnoDB table are:

  • SELECT COUNT(*) FROM my_table:
    provides with an accurate number, but makes for a long running transaction which take ages on large tables. Long transactions make for locks
  • SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='my_schema' AND TABLE_NAME='my_table', or get same info via SHOW TABLE STATUS.
    Gives immediate response, but the value can be way off; it can be two times as large as real value, or half the value. For query execution plans this may be a "good enough" estimation, but typically you just can't trust it for your own purposes.

Get a good estimate using chunks

You can get a good estimate by calculating the total number of rows in steps. Walk the table 1,000 rows at a time, and keep a counter. Each chunk is its own transaction, so, if the table is modified while counting, the final value does not make for an accurate account at any point in time. Typically this should be a far better estimate than TABLE_ROWS.

QueryScript's split() construct provides you with the means to work this out. Consider this script:

set @total := 0;

split(SELECT COUNT(*) FROM world.City INTO @chunk) {
  set @total = @total + @chunk;
}

select @total;

split() breaks the above SELECT COUNT(*) into distinct chunks, like:

SELECT COUNT(*) FROM world.City WHERE ((((`City`.`ID` > '3000'))) AND (((`City`.`ID` < '4000')) OR ((`City`.`ID` = '4000')))) INTO @chunk

You can make this a one liner like this:

call common_schema.run("set @total := 0;split(SELECT COUNT(*) FROM world.City INTO @chunk) set @total = @total + @chunk; select @total;");

If you like to watch the progress, add some verbose:

call common_schema.run("set @total := 0;split(SELECT COUNT(*) FROM world.City INTO @chunk) {set @total = @total + @chunk; select $split_step, @total} select @total;");

QueryScript is available via common_schema.

Link
Peter ZaitsevChoosing a MySQL HA Solution – Post-Webinar Q&A (7.6.2013, 11:00 UTC)

Percona MySQL webinar Q&AThanks to everyone who was in attendance on 05 June 2013 for my “Choosing a MySQL HA Solution” webinar. If you weren’t able to make it but are interested in listening to the presentation, it’s currently up and available for viewing over at percona.com.

My apologies if we weren’t able to get to your question during the initial session, so I’ll address those lingering questions in this post, along with providing a bit more detail on some of the questions that I did cover during the session.

Q: What is the reason that I recommended DRBD be used only on physical hardware and not on virtual machines?
A: I covered this a bit during the session, but to provide a bit more commentary. There are really two main reasons that I don’t like DRBD with virtual machines. First is the disk IO performance hit that comes with DRBD. When you run a virtual machine, in most cases your virtual disk is basically nothing more than a file on the host filesystem rather than an actual physical volume. So, in effect, you’re adding performance hit to performance hit. The other reason is because virtual machines tend to handle process scheduling and timing much differently than traditional hardware, and I’ve seen situations where even under just a moderate load on the host system, DRBD can end up in a split-brain situation. Typically DRBD is run in combination with heartbeat or pacemaker or something similar, and if one of the servers in the DRBD pair is having issues receiving packets from the other side, things start to degrade.

If you’re just setting up a test environment to get familiar with the technology, certainly there’s nothing wrong with a virtual-machine approach, but for the optimal production deployment you want two identical physical boxes with at least 4 NIC ports. Why 4? Two of those NICs should be directly connected from one machine to another and configured with Linux network interface bonding in balance-rr mode; this is the only NIC bonding mode which will allow you to stripe a TCP connection over multiple ports, and with a two-NIC bonded pair you’ll get roughly 1.67x the throughput of a single port. [Don't try more than 2; the additional work that the kernel has to do in reordering TCP packets can actually result in performance that's worse than a single NIC.] The other two NIC ports should be bonded with active-backup or LACP (depending on your switching infrastructure). In essence, you’re looking for two of everything.

Q: Is it possible to replicate only some tables from master to slave, and if so, how?
A: Yes. There are a few of ways to do it. One way is to set up replication filters on the slave, as described in the MySQL manual. You can configure the slave to only replicate a specific database, a set of databases, or specific tables; you can also configure the slave to replicate everything EXCEPT a specified set of databases and tables. With this method, every event is still written to the binary log on the master, but the slave determines what to do with it. The other approach is to filter what gets written to the binary log on the master. This can be useful if you’re trying to limit the amount of data sent over the wire, but it comes at the cost of having binary logs which are no longer complete or useful for point-in-time recovery. Generally if you’re considering the binary log filtering approach, I think it’s safer to set up a relay master (it can be on the same machine if you use the BLACKHOLE storage engine) in between the actual master and the slaves at the bottom of the replication topology. On the top-level master, you leave all filters disabled; on the relay master you add binary log filters; this ensures that the top-level master and its binary logs are fully intact, and then the binary log filters are executed on the intermediate server, thus resulting in less binary log data being sent down to the lower-level slaves. You can also use the slave_compressed_protocol option in /etc/my.cnf if bandwidth is a concern.

If you’re thinking about employing a filtering solution of any sort and it’s something you’re not that familiar with, I’d suggest reading over the manual’s description of how replication filtering rules are processed

Truncated by Planet PHP, read more at the original (another 4841 bytes)

Link
LinksRSS 0.92   RDF 1.
Atom Feed