PlanetMysql.ru — информация о СУБД MySQL

iiBench measures the rate at which a database can insert new rows while maintaining several secondary indexes. We ran this for 1 billion rows with TokuDB and InnoDB starting last week, right after we launched TokuDB v5.2. While TokuDB completed it in 15 hours, InnoDB took 7 days.

The results are shown below. At the end of the test, TokuDB’s insertion rate remained at 17,028 inserts/second whereas InnoDB had dropped to 1,050 inserts/second. That is a difference of over 16x. Our complete set of benchmarks for TokuDB v5.2 can be found here.

Benchmark Details: Ubuntu 10.10; 2x Xeon X5460; 16GB RAM; 8x 146GB 10k SAS in RAID10. Each data point is the average insertion rate for the last 2 million rows.

We developed the iiBench benchmark to measure performance for a use case that occurs commonly in production applications, such as online advertising, social media, and network management.

iiBench simulates a pattern of usage for always-on applications that:

• Require fast query performance and hence require indexes
• Have high data insert rates
• Cannot wait for offline batch processing and hence require the indexes be maintained as data comes in

Note that iiBench was created as an open-source benchmark, which allows others to freely use it, extend it, and contribute their changes back. We originally unveiled the benchmark in the context of a challenge issued at the 2008 OpenSQL camp. Since then, iiBench has been downloaded and used many times, and ported by the community (in this case, Mark Callaghan) to a Python Script.

Please let us know any feedback you have on iiBench. For additional information on…

• iibench overview click here
• TokuDB version 5.2 Overview click here
• TokuDB version 5.2 Performance, including iibench, SysBench, Compression, and TPCC-like, click here

PlanetMySQL Voting: Vote UP / Vote DOWN

When I talked about the usability improvements of Tungsten Replicator, I did not mention the procedure for upgrading.I was reminded about it by a question in the TR mailing list, and since the question was very relevant, I updated the Tungsten Cookbook with some quick upgrading instructions.A quick upgrading procedure is as important as the installer. Since we release software quite often, either because we have scheduled features to release or because of bug fixes, users want to apply a new release to an existing installation without much fuss. You can do the upgrade with a very quick and painless procedure.Let's suppose that you have installed one Tungsten Replicator cluster using this command:

#
# using tungsten-replicator 2.0.4
#
TUNGSTEN_HOME=/home/tungsten/installs/master_slave
./tools/tungsten-installer \
  --master-slave \
  --master-host=r1 \
  --datasource-user=tungsten \
  --datasource-password=secret \
  --service-name=dragon \
  --home-directory=$TUNGSTEN_HOME \
  --cluster-hosts=r1,r2,r3,r4 \
  --start-and-report

If you want to upgrade to the very latest Tungsten Replicator 2.0.5, build 321, this is what you need to do.

• Get the latest tarball, and expand it;
• Stop the replicator;
• Run the update command (this will also restart the replicator)
• Check that the replicator is running again.

The actual upgrade command is in bold in the following script.

#
# using tungsten-replicator 2.0.5-321 (get it from bit.ly/tr20_builds) 
#
TUNGSTEN_HOME=/home/tungsten/installs/master_slave
HOSTS=(r1 r2 r3 r4)
for HOST in ${HOSTS[*]} 
do 
   ssh $HOST $TUNGSTEN_HOME/tungsten/tungsten-replicator/bin/replicator stop 
   ./tools/update --host=$HOST --user=tungsten --release-directory=$TUNGSTEN_HOME -q 
   $$TUNGSTEN_HOME/tungsten/tungsten-replicator/bin/trepctl -host $HOST services
done

One benefit of this procedure, in addition to being brief and effective, is that the previous binaries are preserved.Before the upgrade, you will see:

$ ls -lh ~/installs/master_slave/ ~/installs/master_slave/releases
/home/tungsten/installs/master_slave/:
total 32K
drwxrwxr-x 3 tungsten tungsten 4.0K Sep 22 22:03 backups
drwxrwxr-x 2 tungsten tungsten 4.0K Sep 22 22:03 configs
drwxrwxr-x 3 tungsten tungsten 4.0K Sep 22 22:03 relay
drwxrwxr-x 4 tungsten tungsten 4.0K Sep 22 22:06 releases
drwxrwxr-x 2 tungsten tungsten 4.0K Sep 22 22:03 service-logs
drwxrwxr-x 2 tungsten tungsten 4.0K Sep 22 22:03 share
drwxrwxr-x 3 tungsten tungsten 4.0K Sep 22 22:03 thl
lrwxrwxrwx 1 tungsten tungsten   75 Sep 22 22:06 tungsten -> /home/tungsten/installs/master_slave/releases/tungsten-replicator-2.0.4
/home/tungsten/installs/master_slave/releases:
total 8.0K
drwxr-xr-x 6 tungsten tungsten 4.0K Sep 22 22:03 tungsten-replicator-2.0.4

The 'tungsten' directory is a symlink to the actual binaries inside the 'releases' directory.After the upgrade, the same directory looks like this:

ls -lh ~/installs/master_slave/ ~/installs/master_slave/releases
/home/tungsten/installs/master_slave/:
total 32K
drwxrwxr-x 3 tungsten tungsten 4.0K Sep 22 22:03 backups
drwxrwxr-x 2 tungsten tungsten 4.0K Sep 22 22:03 configs
drwxrwxr-x 3 tungsten tungsten 4.0K Sep 22 22:03 relay
drwxrwxr-x 4 tungsten tungsten 4.0K Sep 22 22:06 releases
drwxrwxr-x 2 tungsten tungsten 4.0K Sep 22 22:03 service-logs
drwxrwxr-x 2 tungsten tungsten 4.0K Sep 22 22:03 share
drwxrwxr-x 3 tungsten tungsten 4.0K Sep 22 22:03 thl
lrwxrwxrwx 1 tungsten tungsten   75 Sep 22 22:06 tungsten -> /home/tungsten/installs/master_slave/releases/tungsten-replicator-2.0.5-321

/home/tungsten/installs/master_slave/releases:
total 8.0K
drwxr-xr-x 6 tungsten tungsten 4.0K Sep 22 22:03 tungsten-replicator-2.0.4
drwxr-xr-x 6 tungsten tungsten 4.0K Sep 22 22:06 tungsten-replicator-2.0.5-321

If you did some manual change to the files in 2.0.4, you will be able to retrieve them. Upgrading from earlier versions of Tungsten Replicator is not as smooth. Since we changed the installation format, it has become incompatible from previous versions. Clusters running TR 2.0.3 need to be reinstalled manually. The next upgrade, though, will be much faster!
PlanetMySQL Voting: Vote UP / Vote DOWN

Unless you are Aston Kutcher (@aplusk), or one of his Hollywood buddies, you don’t need to read any further. Allow me to explain…

Over the weekend, we launched our new website. This type of announcement used to be interesting in the high-tech world. I heard Kara Swisher of the WSJ’s All things D speak at a MassTLC event in May.  She admitted back in the 1990s, when the web was just getting into high gear, that a new website from an interesting company might actually get some coverage. Not anymore.

I’ve also been told at all the SEO classes I’ve taken that as much as marketing folks sweat over every detail, link, font and color, it all doesn’t matter. And as far as Google is concerned, your site could be all in 5 point courier grey font on a black background – as long as you have the right keywords and lots of links to you, you’ll be ranked well and the right people could find you.

So, who can I share my excitement with over our new site? It occurred to me this weekend – Ashton Kutcher and his Hollywood pals!

With Ashton’s recent investment in MemSQL maybe Hollywood is finally getting hip to databases! If that’s the case, then Ashton, do we have a site for you! The all new Tokutek.com brings you:

• A Technology Overview section packed with such insight on Fractal Tree ™ indexing that we can guarantee you’ll impress Demi (@mrskutcher) with your new-found knowledge
• The latest updates on the database space right on the home page, ready for you to instantly retweet to your 7M+ Twitter followers
• The chance to practice “getting into character” for a Hollywood database movie with our opening for a support engineer
• A shared passion for snowboarding
• The opportunity to see how databases have evolved since That 70s Show, the time period when the oft-used but very outdated B-Trees were first described.

We hope you’ll swing by and check us out!

PlanetMySQL Voting: Vote UP / Vote DOWN

The bug I talked about a little while ago has now also had the fix I wrote committed to the mysql-trunk 5.5.6-m3 repository.

PlanetMySQL Voting: Vote UP / Vote DOWN

If you wonder why there hasn't been an update from me for quite a while — I just returned from two months of paternal leave, in which I actually managed to stay away from the PC most of the time. In the meanwhile, I've officially become an Oracle employee and there is a lot of administrative things to take care of... But it feels good to be back!

During my absence, Giuseppe and Felix kicked off the Call for Papers for this year's European OpenSQL Camp, which will again take place in parallel to FrOSCon in St. Augustin (Germany) on August 21st/22nd. We've received a number of great submissions, now we would like to ask our community about your favourites!

Basically it's "one vote per person per session" and you can cast your votes in two ways, either by twittering @opensqlcamp or via the opensqlcamp mailing list. The procedure is outlined in more detail on this wiki page.

As we need to finalize the schedule and inform the speakers, the voting period will close this coming Sunday, 18th of July. So don't hesitate, cast your votes now! Based on your feedback we will compile the session schedule for this year's camp. Thanks for your help!

PlanetMySQL Voting: Vote UP / Vote DOWN

The analysis that shows how to make deletions really fast by using clustering keys and TokuDB’s fractal tree based engine also applies to make updates really fast. (I left it out of the last post to keep the story simple). As a quick example, let’s look at the following statement:

update foo set price=price+1 where product=toy;

Executing this statement has two steps:

• a query to find where product=toy
  
• a combination of insertions and deletions to change old rows to new rows
  

The analysis is identical to that for deletions. Just like for deletes, clustering keys make the query go fast, as explained here. In this case, the appropriate clustering key would be on ‘product’. And fractal tree data structures make the insertions and deletions go fast, as explained here and here.

So, the same story applies. Updates are a combination of queries and value changes. To make updates fast, both the query and the value changes need to be fast. With B-tree based storage engines, users may be hesitant to add indexes to speed up the query, due to the added cost of value changes in the index. With TokuDB, this tradeoff does not exist, because TokuDB uses fractal tree data structures. Using a clustering key to drive down the cost of the query, and using fractal tree indexes to keep the cost of the data changes down, leads to very fast updates.

PlanetMySQL Voting: Vote UP / Vote DOWN

Shortly after I posted my last summary of MySQL releases, our son Mats was born and I went on a 2.5-week vacation. Our developers did not rest in the meanwhile and I'd like to give you a quick update of what's new since then:

MySQL Connector/Net 6.3.0

• Visual Studio 2010 RC support
• Nested transaction scope support

MySQL Workbench 5.2.16 Beta 6

• Fixed 67 bugs
• Saving your profile/connection passwords in OSX keychain, gnome-keyring or in an encrypted password-vault-file.
• New rapid development features for generating complete SQL Select/DML statements or names for selected objects in Query Editor to either the query area or clipboard.
• The ability to set a preference for the placement  (left or right side) of the sidebar in the Query Editor (Currently on Windows only, coming to Mac and Linux soon).
• Further optimization and stabilization of the administrator components

MySQL Server 5.1.44

• This release now includes InnoDB Plugin version 1.0.6, wich is considered to be of Release Candidate (RC) quality.
• Lots of bug fixes - see the ChangeLog for details.

MySQL Server 5.5.2 Milestone 2

• Also includes the updated InnoDB Plugin 1.0.6 and several fixes - see the ChangeLog for details. For an overview of new features in the 5.5 code base, check out the "What's new in 5.5" page in the reference manual.

Please note that the MySQL downloads section has been split into two parts. As usual, you will find downloads of both GA and development versions of all MySQL products and releases on the MySQL DevZone. In addition to that, we've now added a pointer to the downloads of officially released (GA) versions to the main web site on http://www.mysql.com/downloads/.

PlanetMySQL Voting: Vote UP / Vote DOWN

I am happy to announce that mylvmbackup version 0.13 has now been released. This release includes a fix for a nasty bug in on of the recently added Perl hooks (precleanup.pm) and some added functionality (better support for remote rsync backups).

From the ChangeLog: 

• Deleted sample precleanup.pm hook as it has potential to cause harm and is too specialized on a particular use case (BUG#394668)
• Added support for rsync via SSH (BUG#392462)
• Fixed InnoDB recovery in case a relative path to the MySQL data directory is defined (BUG#38337), improved the documentation of relpath in the man page.

 

PlanetMySQL Voting: Vote UP / Vote DOWN

Some time ago I posted a compilation of applications and programming languages that provide an API to connect to the MySQL Server. As it turned out, I forgot a few that I would like to mention here:

• Apache DBD API: a MySQL driver for mod_apr_dbd is not included in the official distribution, but can be obtained seperately from here. Some distributions (e.g. openSUSE) actually provide installable packages of this driver module.
• GRASS MySQL driver: The MySQL database driver in GRASS enables GRASS applications to store vector attributes in a MySQL server. Alternatively, data can be stored in an embedded version of the MySQL Server.
• HDBC: HDBC provides an abstraction layer between Haskell programs and SQL relational databases. This lets you write database code once, in Haskell, and have it work with any number of backend SQL databases.
• libdrizzle: This is the client and protocol library for Drizzle and MySQL that provides both client and server interfaces. A number of APIs (e.g. Java, Lua, Perl and Ruby as well as Python) have been built on top of it, but I'm not quite sure if they all support the MySQL client/server protocol as well.
• MySQL Connector/Python: MySQL Connector/Python is implementing the MySQL Client/Server protocol completely in Python. This means you don't have to compile anything - the MySQL client library doesn't even have to be installed on your system.

Do you know of any other MySQL Connectors/APIs? Let me know!

PlanetMySQL Voting: Vote UP / Vote DOWN

We've now published a new set of binary packages including the extended GIS functionality from the mysql-5.1-wl1326 source tree.

This release is based on the MySQL 5.1.35 code base and fixes the bugs mentioned below. It includes some improvements to the GIS functionality as well, so please use these packages for future testing of the MySQL GIS functionality.

The following GIS-related bugs were fixed in this version:

• Bug#31753: Buffer/area functions only return first row of set
• Bug#32032: Contains() does not work on MultiPolygons, may force a disconnect and/or result in extremely long query times.
• Bug#32100: contains, intersects functions never return. Query disconnects or times out.
• Bug#33035: Intersection function returns 'Function doesn't exist' error
• Bug#40874: Buffer function doesn't work with LINESTRING
• Bug#41481: Buffer function never returns, cpu 100%, system locks up
• Bug#43493: union function returns polygon not multipolygon for non-intersecting polygons
• Bug#44753: nan error in union function

I'd like to explicitly thank John Powell for his extensive testing and bug reporting so far, he helped us quite a lot to improve the quality of the GIS code.

Please help us by by testing this new release and reporting bugs at our public bug database. For convenience, please tag your Bug reports with "gis" and make sure to put them in the "Server: GIS" category!