What is hot in mySQL april 2010

1- The MySQL documentation is not always right
Let me premise this post with the statement I think the MySQL documentation is an excellent and highly accurate resource. I think the MySQL docs team do a great job, however like software and people, documentation is not perfect. As members of the MySQL community you can always contribute to improve the process by reading the documentation and logging any issues as Documentation Bugs. Some time ago in a discussion with a friend and colleague, we were talking about changes in historical defaults that had been improved finally in MySQL 5.4 The specific discussion was on the new default innodb_buffer_pool_size and we both agreed it increased significantly. One said 1GB, the other said 128MB. Who was right? Well we both were, and we were both inaccurate depending on versions. Referencing the 5.4 Manual in InnoDB Startup Options and System Variables the current value for Linux is 128M, but for Windows it’s 1GB. However I was confident I was told in a presentation, perhaps even the keynote the value was 1GB. Firing up my server and seeing the original version I used of 5.4.0 (which was not available on Windows) we find that the default for Linux was 1GB at some time, i.e. the first release. mysql> show global variables like 'innodb_buffer_pool_size'; +-------------------------+------------+ | Variable_name | Value | +-------------------------+------------+ | innodb_buffer_pool_size | 1073741824 | +-------------------------+------------+ 1 row in set (0.00 sec) mysql> select 1073741824/1024/1024 as MB; +---------------+ | MB | +---------------+ | 1024.00000000 | +---------------+ 1 row in set (0.00 sec) mysql> show global variables like 'version%'; +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | version | 5.4.0-beta | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | unknown-linux-gnu | +-------------------------+------------------------------+ 4 rows in set (0.00 sec) I’m not trying to nit pick here, I’m highlighting that MySQL is a evolving product with many different versions and architectures. It’s our job of the MySQL community to help make the documentation the best for all readers. In this above case I’ve not logged the issue, because 5.4 is a defunct product, however if you want an example of how I identified a problem, provided a test case, and saw that my contribution was reviewed, verified and implemented check out Bug #51739 –core-file is not default TRUE (incorrect docs). In conclusion, always read the documentation but pay special attention to the current version that matches the documentation, and the version you are actually running. Defaults change between versions, e.g. innodb_thread_concurrency is a complex example, and I’ve been caught with a large enterprise client with assuming the default of a Connector/J options as true, when it was in 5.0.6, but in 5.0.5 the version the client was running it was false. An old saying, “trust by verify” is a good motto to consider.PlanetMySQL Voting: Vote UP / Vote DOWN source...

2- State of the Internet Operating System Part Two: Handicapping the Internet Platform Wars
This post is Part Two of my State of the Internet Operating System. If you haven't read Part One, you should do so before reading this piece. As I wrote last month, it is becoming increasingly clear that the internet is becoming not just a platform, but an operating system, an operating system that manages access by devices such as personal computers, phones, and other personal electronics to cloud subsystems ranging from computation, storage, and communications to location, identity, social graph, search, and payment. The question is whether a single company will put together a single, vertically-integrated platform that is sufficiently compelling to developers to enable the kind of lock-in we saw during the personal computer era, or whether, Internet-style, we will instead see services from multiple providers horizontally integrated via open standards. There are many competing contenders to the Internet Operating System throne. Amazon, Apple, Facebook, Google, Microsoft, and VMware all have credible platforms with strong developer ecosystems. Then there is a collection of players with strong point solutions but no complete operating system offering. Let's take them in alphabetical order. Amazon With the introduction in 2006 of S3, the Simple Storage Service, and EC2, the Elastic Compute Cloud, Amazon electrified the computing world by, for the first time, offering a general-purpose cloud computing platform with a business model that made it attractive to developers large and small. An ecosystem quickly grew up of companies providing developer and system-management tools. Companies like RightScale provide higher level management frameworks; EngineYard and Heroku provide Ruby-on-Rails based stacks that make it easy to use familiar web tools to deploy applications against an Amazon back-end; the Ubuntu Enterprise Cloud and Eucalyptus offer Amazon-compatible solutions. A number of competitors, including Rackspace, Terremark, Joyent, GoGrid, and AppNexus are going head to head with Amazon in providing cloud infrastructure services. Many analysts are simply handicapping these providers and comparing them to offerings from Microsoft and Google. But to compare only cloud infrastructure providers is to miss the point. It's a bit like leaving out Microsoft while comparing IBM, Compaq, and Dell when handicapping the PC operating system wars. Hardware was no longer king; the competition had moved up the stack. The key subsystems of the Internet Operating System are not storage and computation. Those are merely table stakes to get into the game. What will distinguish players are data subsystems. Data is hard to acquire and expensive to maintain. Delivering it algorithmically at the speeds applications required for reasonable real-time performance is the province of very few companies. In this regard, Amazon has three major subsystems that give it an edge: its access to media (notably books, music, and video); its massive database of user contributed reviews, ratings, and purchase data, and its One-Click database of hundreds of millions of payment accounts. As yet, only one of these, payment, has been turned into a web service, Amazon Flexible Payment Service. Despite having an early lead in internet payment, Amazon reserved its use for too long for competitive advantage for its own e-commerce site, and didn't deploy it as an internet-wide service usable by developers until recently. And even then, Amazon lacks significant payment presence on mobile devices. Amazon has its own Kindle device for ebook sales, and its iPhone and Android apps for e-commerce, but powerful as these apps may be for driving sales to Amazon, they give the company no leverage in supporting third party developers. If anything, they will hinder the development of a mobile e-commerce ecosystem based on Amazon because Amazon is the largest competitor for many potential e-commerce developers. Amazon's use of its media database as a back-end for its own proprietary e-reader device, the Kindle, highlights one of the fronts in what I've elsewhere called the War for the Web, namely the use of a dedicated front-end device giving preferential access to a player's back-end services. Apple and Google are in a much stronger position in this regard, with general-purpose smartphones as the device front-ends for their platforms. But Amazon has moved quickly to deploy its Kindle software on iPhone and Andr source...

3- Seeking volunteers for Percona documentation
Percona wants to upgrade our documentation to improve its readability and to make it more useful for you, our clients and partners. We are so busy developing software and handling your needs that we have trouble finishing all the documentation! We think you can help. Helping us will give you a chance to interact closely with lead Perona developers and learn more about Percona’s products as well as our development process. Tasks include talking to developers, writing text, and interacting with an editor. (Andy Oram, our O’Reilly editor on the book High Performance MySQL, will take on the volunteer role.) You should have some understanding of MySQL and Percona’s extensions, and of way XtraDB and XtraBackup work. Documents we want to start with include: * Product features (we made a start at http://www.percona.com/docs/wiki/percona-xtradb:features:start) * Release notes * Installation instructions for XtraBackup Eventually we’ll also upgrade our user guides, a bigger job. If this work intrigues you, please contact Andy Oram and andyo@praxagora.com. Andy has offered to coordinate efforts for free, although we will pay him for professional editing. Entry posted by Vadim | No comment Add to: | | | | PlanetMySQL Voting: Vote UP / Vote DOWN source...

4- Binary Log Group Commit - An Implementation Proposal
It is with interest that I read Kristian's three blogs on the binary log group commit. In the article, he mentions InnoDB's prepare_commit_mutex as the main hindrance to accomplish group commits—which it indeed is—and proposes to remove it with the motivation that FLUSH TABLES WITH READ LOCK can be used to get a good binlog position instead. That is a solution—but not really a good solution—as Kristian points out in the last post. The prepare_commit_mutex is used to ensure that the order of transactions in the binary log is the same as the order of transactions in the InnoDB log—and keeping the same order in the logs is critical for getting a true on-line backup to work, so removing it is not really an option, which Kristian points out in his third article. In other words, it is necessary to ensure that the InnoDB transaction log and the binary log have the same order of transactions. To understand how to solve the problem, it is necessary to take a closer look at the XA commit procedure and see how we can change it to implement a group commit of the binary log. The transaction data is stored in a per-thread transaction cache and the transaction size is the size of the data in the transaction cache. In addition, each transaction will have a transaction binlog position (or just transaction position) where the transaction data is written in the binary log. The procedure can be outlined in the following steps: Prepare InnoDB [ha_prepare]: Write prepare record to log buffer fsync() log file to disk (this can currently do group commit) Take prepare_commit_mutex Log transaction to binary log [TC_LOG_BINLOG::log_xid]: Lock binary log Write transaction data to binary log Sync binary log based on sync_binlog. This forces the binlog to always fsync() (no group commit) due to prepare_commit_mutex Unlock binary log Commit InnoDB: Release prepare_commit_mutex Write commit record to log buffer Sync log buffer to disk (this can currently do group commit) InnoDB locks are released There are mainly two problems with this approach: The InnoDB row level and table level locks are released very late in the sequence, which affects concurrency. Ideally, we need to release the locks very early, preferably as soon as we have prepared InnoDB. It is not possible to perform a group commit in step 2 As you can see here, the prepare of the storage engines (in this case just InnoDB) is done before the binary log mutex is taken, and that means that if the prepare_commit_mutex is removed it is possible for another thread to overtake a transaction so that the prepare and writing to the binary log is done in different order. To solve this, Mark suggests using a queue or a ticket system to ensure that transactions are committed in the same order, but we actually already have such a system that we can use to assign tickets: namely the binary log. The idea is to allocate space in the binary log for the transaction to be written. This gives us a sequence number that we can use to order the transactions. In the worklog on binary log group commits you will find the complete description as well as the status of the evolving work. In this post, I will outline an approach that Harrison and I have discussed, which we think will solve the problems mentioned above. In this post, I will outline the procedure during normal operations, in the next post I will discuss recovery, and in the third post (but likely not the last on the subject), I will discuss some optimizations that can be done. I want to emphasize that the fact that we have a worklog does not involve any guarantees or promises of what, when, or even if any patches will be pushed to any release of MySQL. In Worklog #4007 an approach for writing the binary log is suggested where space is allocated for the transaction in the binary log before actually starting to write it. In addition to avoiding unnecessary locking of the binary log, it also allow us to use the binary log to order the transactions in-place. We will use this idea of reserving space in the binary log to implement the b source...

5- Ignoring, laughing, fighting, winning
A now-famous quote that I probably don’t need to attribute: “First they ignore you, then they laugh at you, then they fight you, then you win.” Where is Drizzle in this lifecycle? I’ve been hearing and reading some comments to the tune of “those Drizzle guys think it’s easy to rip MySQL stuff out and start over, wait till they see how hard it’s going to get when the real world sinks in.” Maybe, maybe. But maybe not, too. Maybe not. I’ve seen more than one software project that was belittled as “never gonna amount to anything, save your time” and went on to do quite well. Never underestimate the power of a handful of passionate and talented people. I personally feel that Drizzle has a bright future. Related posts:Why MySQL might not benefit from having a mother ship As I was dPlease re-license the MySQL documentation In the pas Related posts brought to you by Yet Another Related Posts Plugin.PlanetMySQL Voting: Vote UP / Vote DOWN source...

6- On Writing a Book, Pt. 2 – Outline and Schedule
This is part two of an ongoing series about my experiences while writing the MySQL Admin Cookbook for Packt Publishing. All previous parts can be found under the mysql-admin-cookbook label. While last time I focused on the initial contact with the publishing company (just referred to as "Packt" from now on), this issue is about the process of putting together an outline proposal and coming up with things to write about in the first place. As from this point on in the process Udo was involved with everything, I will be referring to "us" and write "we" most of the time from now on. The Publisher's Expectations The only thing we knew about the books would-be content was a chapter template for Packt's cookbook series as well as the general description provided by Sarah earlier: As I'm sure everyone is aware, MySQL is a relational database management system. Administrators of MySQL will be tasked with things such as maintaining the database, tuning the server, managing users etc etc.This cookbook will have all the MySQL recipes an administrator could dream of, spanning from creating tables to managing views, from improving performance to securing the database, from using monitoring tools to using storage engines. DBAs of all levels will be catered for with recipes of varying difficulty, allowing the reader to administer MySQL to their hearts' content. Before actually beginning the outlining process there were a few more emails sent back and forth, mostly questions on our part, for example whether we should include programming related materials or tool descriptions in addition to more "real" database themes, what spectrum of experience to expect from potential readers and so on. Packt did not provide a list of topics they had in mind - instead we were completely free to make suggestions for what we deemed interesting for MySQL administrators. There were no hard rules on what topics would be out-of-scope, but in general we were asked to find a balance and suggest roughly the same number of topics that would be interesting for relatively inexperienced readers, people already familiar with MySQL and experienced, professional DBAs - with a general tendency toward the latter. At the time I was a little disappointed as to how little guidance they provided, but in hindsight I believe this was definitely for everyone's benefit. Packt's position on this apparently is that their authors generally are closer to their designated audience than someone who can only have so much of an insight into a lot of technical topics; something I can certainly agree on :). As for the MySQL versions targeted, they naturally asked us to concentrate on the most up-to-date version at that time - which was MySQL 5.1. In our day jobs we are still stuck with 5.0, but it was a good opportunity to get a feeling for what changes we will have to make to our processes and practices when eventually we decide to tackle the upgrade. Considering the fact the writing the whole thing would take its time this was of course a reasonable decision. Midway during the process we thought about looking at 5.4, too, but decided against it - checking everything on 5.0 and 5.1 was enough work already. The cookbook format as it was described to us would allow for "spin-off" recipes: A general recipe with detailed instructions, followed by shorter, more concise ones that described a variation of the original baseline, sometimes more advanced, sometimes just with a different focus. So in general Packt recommended to go for a greater number of shorter individual recipes, because that would allow the reader of the book to skim through the table of contents and not have to interpret too much into the titles in order to grasp what each recipe would do for her.While we actually ended up with a few of these in the final book I did relate to the idea too much. As a matter of fact I think we could have better used those pages for genuinely different content, but that might simply be a matter of personal preference. Brainstorming On January 23rd, not just yet one month after the initial contact, we sat down together in front of Google Docs and held a little brainstorming session on topics that occurred to us while talking about our experiences with MySQL. Of course some of the "bigger" milestones from our day jobs sprang to mind first, but over the course of a couple hours with some snacks and drinks we came up with quite a lot of individual topics. L source...

7- MySQLconf impressions 2: Thoughts on MySQL on top of NoSQL / Hadoop
We then finally came to the topic that comes naturally to anyone familiar with the MySQL architecture. Could Hadoop, or Hive, or whichever, be plugged into MySQL as a storage engine? And why would you want to do that? And can Timour's work to push down JOIN conditions be of any help? (The last question was interesting since Ted and his team were inherently against talking about JOINs at all :-) We ended up concluding that 2 things could be interesting and considered "low hanging fruit": SQL to PIG compiler. (Btw, it is not immediately obvious if this task needs MySQL at all.) read morePlanetMySQL Voting: Vote UP / Vote DOWN source...

8- Mastering the art of indexing
Check out this SlideShare Presentation: I'd have to say that this is the coolest presentation on Indexing and how it relates to INNODB. I have written on this subject many times in the pass but this presentation covers all the bases and does a great job at explaining WHY you should do certain statements over others.More mastering the art of indexingView more presentations from Yoshinori Matsunobu.PlanetMySQL Voting: Vote UP / Vote DOWN source...

9- Exchanging partitions with tables
While I was presenting my partitioning tutorial at the latest MySQL Conference, I announced a new feature that was, as far as I knew, still in the planning stage. Mattias Jonsson, one of the partitions developers, was in attendance, and corrected me, explaining that the feature was actually available in a prototype. So, we can have a look at this improvement, which I am sure will make DBAs quite happy. The new feature is an instantaneous exchange between a partition and a table with the same structure. Using this feature, you can transfer the contents of one partition to one table, and vice versa. Since the transition is done only in the attribution of the data, there is no copy involved. The data stays where it is at the moment. What is in the table ends up in the partition and what's in the partition ends up in the table. Let's see an example. With the data in figure 1, where we have a partitioned table t1 and an empty table t2 with the same structure, we can issue the following statement:ALTER TABLE t1EXCHANGE PARTITION p2WITH TABLE t2 After the exchange, partition p2 is empty, and table t2 contains 4 records.If we repeat the command, the contents will be swapped again, leaving table t2 empty and partition p2 with its original contents.If you want to test on your own, you can get the code from Launchpad. Once you get the code, you can use cmake to build the server.$ cmake-gui .# add the options you need. For example, enable innodb # or else you will need to load it as a plugin.$ make && ./scripts/make_binary_distributionYou can then use this script to test the new functionality. You may want to change Innodb with MyISAM to test it thoroughly. At the moment, it doesn't work with the archive engine (yet). UPDATE 2010-04-30 Now it does! Mattias has fixed the bug.# ############################# test_exchange_partitions.sql# ############################use test;set default_storage_engine=innodb;drop procedure if exists compare_tables;delimiter //create procedure compare_tables (wanted int)reads sql databegin set @part_table := (select count(*) from t1); set @non_part_table := (select count(*) from t2); select @part_table, @non_part_table, if(@non_part_table = wanted, "OK", "error") as expected;end //delimiter ;drop table if exists t1, t2;create table t1 (i int) # not null primary key)partition by range (i) (partition p01 values less than (100001), partition p02 values less than (200001), partition p03 values less than (300001), partition p04 values less than (400001), partition p05 values less than (500001), partition p06 values less than (600001), partition p07 values less than (700001), partition p08 values less than (800001), partition p09 values less than (900001), partition p10 values less than (1000001), partition p11 values less than (maxvalue));create table t2 (i int ) ; # not null primary key);select table_name, engine from information_schema.tables where table_schema='test' and table_type='base table';select 'generating 1 million records. ...' as info;# generates 1 million records# see this article for details# http://datacharmer.blogspot.com/2007/12/data-from-nothing-solution-to-pop-quiz.htmlcreate or replace view v3 as select null union all select null union all select null;create or replace view v10 as select null from v3 a, v3 b union all select null;create or replace view v1000 as select null from v10 a, v10 b, v10 c;set @n = 0;insert into t1 select @n:=@n+1 from v1000 a,v1000 b;select partition_name, table_rows from information_schema . partitions where table_name='t1' and table_schema='test';call compare_tables(0);alter table t1 exchange partition p04 with table t2; call compare_tables(100000);s source...

10- Recovering a Schema From InnoDB .frm Files
Sometimes you find yourself in a bad situation where your only hope of recovering your InnoDB data lies in a handful of .frm and .ibd data files that were heretofore part of a working MySQL installation. It could be the case that someone thought backing up InnoDB tables was simply a matter of of copying the .ibd and .frm files somewhere safe. That mostly works for MyISAM files right? Perhaps your system table space (ibdata1) became corrupted or was accidently deleted. Whatever the reason, you have a handful of .frm and .ibd files, and what you want is them imported into a functioning database. The basics of how to do this are not too hard to understand, it’s the details that get tricky. The first part of this problem is how to extract the table definition from the .frm files. I’ll cover the second part of the problem in another post. You could write a program that reads and parses the .frm file and outputs the table definition, but that’s a lot of work — especially when you can trick MySQL into doing it for you. Here is how you would do this for the file foo.frm. mysql> CREATE TABLE `test`.`foo` (id int) ENGINE=InnoDB; We’ve created an InnoDB table called foo. MySQL has written a foo.frm and a foo.ibd file in $datadir/test. It has also made a record of this table in the data dictionary. mysql> FLUSH TABLES; This causes MySQL to close all open tables and flush the query cache. The idea is to force MySQL to forget about the table “foo` that we just created. bash# cp foo.frm /var/lib/mysql/test; We just overwrote the table definition for test.foo. We did it hot, while MySQL was still running. mysql> SHOW CREATE TABLE `test`.`foo`; That will output the table definition for the foo.frm file we just copied into the test directory. MySQL will probably be a bit confused, as the information in the .frm file is not likely to match what is in the system table space. You will probably see a message like this in your error log. [ERROR] Table ./test/foo has no primary key in InnoDB data dictionary, but has one in MySQL! If you created the table with a MySQL version < 3.23.54 and did not define a primary key, but defined a unique key with all non-NULL columns, then MySQL internally treats that key as the primary key. You can fix this error by dump + DROP + CREATE + reimport of the table. That’s OK though, we already got what we wanted, and now we can clean up. mysql> DROP TABLE `test`.`foo`; While this process works fine for a few tables, you can see how it’d be a real annoyance to have to go through this for a few hundered tables. I wrote a script that automates this process for you, I call it recover_schema.pl. Here is how you use it: bash# recover_schema.pl --user=root --password=seekrit *.frm This program needs to be run as root, and needs to have the credentials for a privileged MySQL user. It will accept a number of .frm files to convert to CREATE TABLE statements on then command line. It operates on the test database like the example above, and will create it if it does not exist. One CREATE TABLE statement will be output for every .frm file passed in on the command line. Related posts:s3fox does not create valid export manifest files Introducing the Bluegecko MySQL Training AMI Using Checksums to Ensure Table Consistency in MySQL PlanetMySQL Voting: Vote UP / Vote DOWN source...

11- Trying out MySQL Push-Down-Join (SPJ) preview
At the 2010 MySQL User Conference, Jonas Oreland presented on the work he’s been doing on improving the performance of joins when using MySQL Cluster – the slides are available for download. While not ready for production systems, a preview version is available for you to try out. The purpose of this blog is to step through  testing an example query as well as presenting the results (SPOILER: In one configuration, I got a 50x speedup!). SPJ is by no means complete and there are a number of constraints as to which queries benefit (and I’ll give an example of one that didn’t). For details of the current (April 2010) software and limitations, check out Jonas’s slides and then keep up to date by following his blog. We’re anxious to get feedback – please feel free to post results as comments to this blog but also make sure that you send them to spj-feedback@sun.com – describing your schema, the query or queries you tested, the output from EXPLAIN and your before and after timings. Joins in MySQL Cluster are implemented as nested-loop joins within the MySQL Server; this can be inefficient as it results in many trips to the data nodes to fetch the required data. SPJ works by pushing the join (actually a spec of the needed data) down into the data nodes where the data can be collected and sent back up to the MySQL Server much more efficiently. For my tests, I used 2 different configurations. In both cases there are 2 data nodes running on 2 physical hosts. In the first configuration the MySQL Server resides on one of those 2 hosts. In the second configuration, the MySQL Server is moved to a virtual machine running on a 3rd host. Setting up the Cluster On each of the 3 hosts, I downloaded the software from ftp://ftp.mysql.com/pub/mysql/download/cluster_telco/mysql-5.1.44-ndb-7.1.3-spj-preview/ and then compiled and installed it. If you’re not comfortable with that then you can find instructions in this earlier blog or if you’re used to using the tools from severalnines then check out the SPJ instructions on Johan’s blog. Create the schema The 3 tables I used can be created with these commands from the mysql client: mysql> create database clusterdb; use clusterdb; mysql> create table subs (sub_id int not null primary key, dept int,country int) engine=ndb; mysql> create table department (id int not null primary key, name int) engine=ndb; mysql> create table roles (dept int not null primary key, role varchar (30)) engine=ndb; Each of these tables is then populated with 100,000 rows (the files can be downloaded from here). Once extracted, the data should be loaded into the database: mysql> use clusterdb; mysql> load data local infile "/home/billy/Dropbox/LINUX/projects/SPJ/subs.csv" replace into table subs fields terminated by ','; mysql> load data local infile "/home/billy/Dropbox/LINUX/projects/SPJ/dept.csv" replace into table department fields terminated by ','; mysql> load data local infile "/home/billy/Dropbox/LINUX/projects/SPJ/roles.csv" replace into table roles fields terminated by ','; Running the tests (Config 1 – local mysqld) To get a baseline, ensure that SPJ is turned off: mysql> set ndb_join_pushdown=off; and then get the output from EXPLAIN: mysql> EXPLAIN SELECT count(*) FROM subs, department, roles WHERE subs.country=44 AND department.id=subs.dept AND roles.dept=department.n source...

12- SQLyog – MySQL GUI 8.4 GA | Redundant Index Finder, Compressed Scheduled Backups & much more..
Hi, SQLyog 8.4 happens to be a fairly major release with good number of features & enhancements. Listed below are some worth noting. May it be Query Profiler or Datatype Optimizer, SQLyog has grown beyond the definition of a GUI with these features with intelligence. One such feature that is being unveiled with this release is an option to find redundant indexes. You can find the redundant indexes in a Table with just a click of a button. If found, a ready query is generated to drop the redundant indexes. As you know eliminating redundant indexes improves INSERTs & UPDATEs and also saves hard-disk space. Scheduled Backups can be compressed. If you are one of those users who deal with databases having thousands of objects (tables, columns etc.) this release will definitely add to your productivity as auto-complete has been drastically improved for such databases. You can sort the data of Result set. The sorting is very fast as it is done at the client side unlike Table Data where a query is fired to server for every sort action. Redundant Index Finder This release also contains following enhancements: A file can be loaded to the tab from context menu. Added menu items (for exports and ‘copy to other..’) at table-level in Object Browser. Save dialog when closing a connection now has ’Yes to All’ and ‘No to All’ options. Added an option to send a mail alert if an error occurs while executing a ‘maintenance query’ from Notifications Services. SQLyog customers can download the latest installer from Webyog’s Customer Portal. To evaluate SQLyog, please download the 30-day trial. We are very excited about this release, and hope that you will like it. We would love to hear from you. Cheers, Team SQLyog PS: Redundant Index Finder is a feature of SQLyog Ultimate only.PlanetMySQL Voting: Vote UP / Vote DOWN source...

13- innodb index page format
Today I had to decode an innodb index page, so I documented the entire process here:E:\mysql-enterprise-gpl-5.0.66a-winx64\bin>mysqld-nt --console --skip-grant-tables --skip-name-resolveInnoDB: The first specified data file .\ibdata1 did not exist:InnoDB: a new database to be created!080919 14:29:00 InnoDB: Setting file .\ibdata1 size to 10 MBInnoDB: Database physically writes the file full: wait...080919 14:29:00 InnoDB: Log file .\ib_logfile0 did not exist: new to be createdInnoDB: Setting log file .\ib_logfile0 size to 5 MBInnoDB: Database physically writes the file full: wait...080919 14:29:01 InnoDB: Log file .\ib_logfile1 did not exist: new to be createdInnoDB: Setting log file .\ib_logfile1 size to 5 MBInnoDB: Database physically writes the file full: wait...InnoDB: Doublewrite buffer not found: creating newInnoDB: Doublewrite buffer createdInnoDB: Creating foreign key constraint system tablesInnoDB: Foreign key constraint system tables created080919 14:29:01 InnoDB: Started; log sequence number 0 0080919 14:29:01 [Note] mysqld-nt: ready for connections.Version: '5.0.66a-enterprise-gpl-nt' socket: '' port: 3306 MySQL Enterprise Server (GPL)create table t1(a varchar(20) primary key, b varchar(20), c varchar(20),key(b),key(c,b))engine=innodb;insert into t1(a,b,c) values ('aaaa','bbbbb','cccccc');insert into t1(a,b,c) values ('aaaaaaa',null,'ccc');insert into t1(a,b,c) values ('a','b',null);insert into t1(a,b,c) values ('aaaaaaaaaa','bbb','c');080919 14:37:59 [Note] mysqld-nt: Normal shutdown080919 14:37:59 InnoDB: Starting shutdown...080919 14:38:14 InnoDB: Shutdown completed; log sequence number 0 48536080919 14:38:14 [Note] mysqld-nt: Shutdown completewe have secondary indexes on this tablethis is key(c,b) (directly from ibdata1):000d0000h: 35 56 71 04 00 00 00 34 FF FF FF FF FF FF FF FF ; 5Vq....4ÿÿÿÿÿÿÿÿ000d0010h: 00 00 00 00 00 00 BD 92 45 BF 00 00 00 00 00 00 ; ......½’E¿......000d0020h: 00 00 00 00 00 00 00 02 00 C3 80 06 00 00 00 00 ; .........À.....000d0030h: 00 B5 00 05 00 00 00 04 00 00 00 00 00 00 03 05 ; .µ..............000d0040h: 00 00 00 00 00 00 00 00 00 11 00 00 00 00 00 00 ; ................000d0050h: 00 02 15 F2 00 00 00 00 00 00 00 02 15 32 01 00 ; ...ò.........2..000d0060h: 02 00 47 69 6E 66 69 6D 75 6D 00 05 00 0B 00 00 ; ..Ginfimum......000d0070h: 73 75 70 72 65 6D 75 6D 04 05 06 00 00 00 10 FF ; supremum.......ÿ000d0080h: EF 63 63 63 63 63 63 62 62 62 62 62 61 61 61 61 ; ïccccccbbbbbaaaa000d0090h: 07 03 02 00 00 18 FF E9 63 63 63 61 61 61 61 61 ; ......ÿécccaaaaa000d00a0h: 61 61 01 01 01 00 00 20 00 0B 62 61 0A 03 01 00 ; aa..... ..ba....000d00b0h: 00 00 28 FF E3 63 62 62 62 61 61 61 61 61 61 61 ; ..(ÿãcbbbaaaaaaa000d00c0h: 61 61 61 00 00 00 00 00 00 00 00 00 00 00 00 00 ; aaa.............Let's reformat this page into the correct fields as seen by InnoDB:0000: 35567104 -> FIL_PAGE_SPACE_OR_CHKSUM0004: 00000034 -> FIL_PAGE_OFFSET 0008: FFFFFFFF -> FIL_PAGE_PREV 0012: FFFFFFFF -> FIL_PAGE_NEXT 0016: 000000000000BD92 -> FIL_PAGE_LSN0024: 45BF -> FIL_PAGE_TYPE (#define FIL_PAGE_INDEX 17855)0026: 0000000000000000 -> FIL_PAGE_FILE_FLUSH_LSN0034: 00000000 -> FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID0038: 0002 -> PAGE_N_DIR_SLOTS0040: 00C3 -> PAGE_HEAP_TOP (195 ...)0042: 8006 -> PAGE_N_HEAP (6 records in heap (remove 15th bit)0044: 0000 -> PAGE_FREE0046: 0000 -> PAGE_GARBAGE0048: 00B5 -> PAGE_LAST_INSERT0050: 0005 -> PAGE_DIRECTION (PAGE_NO_DIRECTION)0052: 0000 -> PAGE_N_DIRECTION0054: 0004 -> PAGE_N_RECS0056: 0000000000000305 -> PAGE_MAX_TRX_ID (773)0064: 0000 -> PAGE_LEVEL0066: 0000000000000011 -> PAGE_INDEX_ID ( Page may be an index page where index id is 0 277385)0074: 000000000000000215F2 -> PAGE_BTR_SEG_LEAF0084: 00000000000000021532 -> PAGE_BTR_SEG_TOPinfimum:0094: 01 -> info_bits=0, n_owned=1 (always 1 for the infimum)0095: 00 -> heap number0096: 02 -> status bits0097: 0047 -> next record (71 bytes)0099: 696E66696D756D00 -> "infimum"supremum:0107: 05000b -> ex source...

14- Start MySQL at bootup
At the time of Server failure, you would want your MySQL to start at the boot. To achieve the same follow the below steps at the prompt. >cd /etc/rc.d/rc3.d >ln -s /etc/rc.d/init.d/mysql S98mysql The second line will create a symlink in the above directory. By doing this you are asking your server to run it during level 3 start up. However you can also run the below commands manually post startup. Using files in /etc/rc.d/init.d directly, for example: /etc/rc.d/init.d/mysql start /etc/rc.d/init.d/mysql stop -Death PlanetMySQL Voting: Vote UP / Vote DOWN source...

15- Database Workbench 4.0.1 released
Database Workbench 4.0.1, a bugfix release to the next major version of our multi-database-engine database development environment, has been released today. Version 4 introduced full Unicode support, 4.0.1 fixes some issues found in that initial release, like MySQL Stored Procedures/Functions metadata not getting fetched properly and failed data export to Excel. Version 4 feature highlights: - Unicode Enabled - Click header to sort data - Integrated BLOB Editor with SQL Editor - "Secure Object Drop" and "Secure Database Drop" functionality - Code editor enhancements and much more... Database Workbench supports: - MySQL - InterBase - Firebird - Oracle - NexusDB - Microsoft SQL Server - Sybase SQL ServerPlanetMySQL Voting: Vote UP / Vote DOWN source...

16- Free webinar – learn about MySQL Cluster 7.1
MySQL Cluster 7.1 was declared GA earlier this month and today (29 April) you have the chance to learn all about it by registering for this free webinar. In blazing speed we will cover the most important features of MySQL Cluster 7.1: NDB$INFO; MySQL Cluster Connector/Java and other features that push the limits of MySQL Cluster into new workloads and communities. NDB$INFO presents real-time usage statistics from the MySQL Cluster data nodes as a series of SQL tables, enabling developers and administrators to monitor database performance and optimize their applications. Designed for Java developers, the MySQL Cluster Connector for Java implements an easy-to-use and high performance native Java interface and OpenJPA plug-in that maps Java classes to tables stored in the MySQL Cluster database. It’s worth registering even if you can’t attend as  you should then receive a link to the replay and the charts. It starts at 9:00 Pacific / 5 pm UK / 6pm CET.PlanetMySQL Voting: Vote UP / Vote DOWN source...

17- [MySQL][Spider]Spider-2.18 released
I'm pleased to announce the release of Spider storage engine version 2.18(beta).Spider is a Storage Engine for database sharding.http://spiderformysql.com/The main changes in this version are following.  This release is bug fix release.Please see "99_change_logs.txt" in the download documents for more detail.Enjoy!PlanetMySQL Voting: Vote UP / Vote DOWN source...

18- building MySQL 5.5 with cmake
Yesterday I was testing a branch of MySQL 5.5 to help a colleague, and I was set aback at discovering that, with the default build options, the server did not include the Archive engine.In other times, I would have to dig into the build scripts or to examine the output of ./configure --help, but that is no longer necessary. MySQL 5.5 is built using cmake, the cross platform make.Why does this change make me feel better? Because cmake configuration is more user friendly than the old autoconf/automake/libtools horror syntax. Not only that, but there is a GUI!I am a command line guy, as you probably know, but when the purpose of a GUI is not only to show off but to make difficult choices easy, then I all for it.In my particular case, I enjoyed the idea of setting the options with a contextual help that told me the choices for each item.If you want to know more about the whole process of building MySQL with CMake, there is a comprehensive guide in MySQL Forge.Before I forget, though, there is something that reconciles my command line nature and the need for a good interface. Instead of using cmake-gui, I can get the same results with ccmakeIt is not as pretty as the graphical UI, but it has the advantage of working in a remote terminal, which for me is a must.So, if you want to try it, grab the latest MySQL 5.5 tree and follow the instructions.PlanetMySQL Voting: Vote UP / Vote DOWN source...

19- MySQLconf impressions 1: May as well look into this NoSQL thing since we are stuck anyway
(This is the first in a series of blogs written while I was trying to get home from Santa Clara. Posting them now as I'm back home and online.) These MySQL conferences certainly get more interesting every year. Last year we got acquired and I went home thinking I need to start looking for a new job. This year Eyjafjallajõkull erupted and I had trouble getting home at all! Pre-conference there was also the suspense to see whether the conference will take place at all, but O'Reilly and Colin pulled it together perfectly! It seems most if not all Europeans will eventually find their ways home, so all in all, all is well that ends well. To recount everything we've learned, I will step backwards in time through the highlights as I saw them. read morePlanetMySQL Voting: Vote UP / Vote DOWN source...

20- The Drizzle Census
One thing I have often wondered is just how many MySQL instances exist in the world and what MySQL versions and architectures are in use. We hear of 50,000 windows downloads per day but this is misleading because MySQL is basically bundled with Linux by default or installed from various repositories. Linux servers powers many websites. In Drizzle we have a proposed plan, the Drizzle Census. From the productive Drizzle Developers Day recently at the 2010 MySQL conference we sat down and created a blueprint, and subsequent high level spec of what we considered this optional plugin should do. We didn’t get as far as I would have liked in a code skeleton to at least gather and store a sample result, but the hope is that with the community we will in the near future. Here is the list of information we decided was appropriate for anonymous information of value. Kernel Version/Architecture CPU type SID – HASH(processor_id,listener address,first listener port) Drizzle Version Drizzle Uptime Drizzled process memory usage PlanetMySQL Voting: Vote UP / Vote DOWN source...

21- Tokutek’s Fractal Tree Indexes
Tokutek’s Bradley did a session on their Fractal Tree Index technology at the MySQL Conference (and an OpenSQL Camp before that – but I wasn’t at that one), and my first thought was: great, now we get to see what and where the magic is. On second thought, I realised you may not want to know. I know I’m going to be a party pooper here, but I do feel it’s important for people to be aware of the consequences of looking at this stuff (there’s slide PDFs online as well as video), and software patents in general. I reckon Tokutek has done some cool things, but the patents are a serious problem. Tokutek’s technology has patents pending, and is thus patent encumbered. What does this mean for you? It means that if you look at their “how they did it” info and you happen to code something that later ends up in a related patent lawsuit, you and the company you work for will be liable for triple damages. That’s basic US patent law, if you knowingly infringe you pay thrice. If you were at either session and are involved in database development work, you may wish to talk with your boss and legal council. I made the assessment for myself (although I’m in Australia, there’s the Free Trade Agreement with patent-related provisions, so I am exposed) and decided that since Open Query’s activities are well within my control, it’s a manageable risk. So yep I’ve looked at the details. I’ll review some broad aspects below – I am not a lawyer but if the above worries you, to be sure, now is the time to stop reading and not see the rest of this post. The insertion methodology is an interesting and nifty trick. It’s more CPU intensive but reduces disk I/O, and is thus faster for high volume inserts (the exact spot where B-trees and derivatives tend to be slower). First of all, it’s important to appreciate why the B-tree family of indexing algorithms exist. They acknowledge that disk I/O is a) relatively expensive and b) operates in blocks (that is, writing/grabbing a larger chunk is more efficient when you’re reading from disk anyway). So B-trees store groups of keys together and thus try to minimise disk I/O particularly on lookup, balanced B-trees (B+tree algorithm etc) go wide rather than deep so for billions of entries you could still have a max of 6-8 disk blocks to fetch. Inserts (and deletes) can be more costly, particularly with page splits (merges for deletes) and rebalancing operations. Blocks are also not full, which is technically wasteful on your storage – it’s a tradeoff. If you have an index purely in memory, algorithms that don’t work with blocks are more efficient, MySQL (NDB)Cluster uses T-trees and MySQL’s MEMORY tables have red/black trees which are a balanced (weighted) binary tree. If you’re interested in the structure and basic logic for each of the algorithms involved, Wikipedia tends to have good descriptions and diagrams, and there are many resources on the web including neatly animated demos of how inserts work, and so on. So, Tokutek’s method is basically an enhancement on B-trees, it’s relevant as long as we deal with not just spinning disks but block devices that operate in large(r) read/write chunks. For spinning disks, seek time is an important factor. For SSD it is not, but SSD still works with relatively large blocks of data: you can’t just write 3 bytes, if you do the SSD actually reads the rest of the block and rewrites it (with your new 3 bytes) elsewhere, marking the old block for re-use (since SSD requires an erase cycle before it can write again). These technologies will be with us for a while yet, so enhancements are useful. Monetisation models (and patents) aside, I reckon it’d be best to see enhancements such as these added to existing storage engines and indexing implementations (think text indexers and many other applications – it’s by no means limited to plain RDBMS or databases in general). Then it would quickly benefit a large group of users. Building a basic storage engine is not that hard for an experienced database coder, but it takes time to mature and there are many aspects and trade-offs to it. It’s taken years for InnoDB to mature and for people to understand how to optimally use it. Planting a new/separate storage engine on the market to monetise a new indexing scheme makes -to me- only sense in the monetisation context. It makes absolutely no sense when looking at the technical aspects or the needs of the users. For companies using MySQL/MariaDB because the code is available and they’re not locked into a single vendor for bugfixing and enhancements (just look at what Percona has done with InnoDB!), buying/using proprietary extensions makes no sense. I do by no means wish to diminish the accomplish source...

22- Massachusetts Data Protection Law
Recently I came across this new Massachusetts state data protection security law that has been passed and wondering if anyone took an initiative to fix their data storage, especially if it deals with MA residents. You can find more about this law from Google Search. One thing that might make a difference for database vendors and [...]PlanetMySQL Voting: Vote UP / Vote DOWN source...

23- MySQL HA , an alternative approach
For those who've seen my presentation on MySQL HA, you already know that I often use a multimaster setup with a meta OCF resource that groups my favoured MySQL instance with the service ip , using a meta resource means that pacemaker monitors mysql, but it doesn't actually manage it. It's an approach that works for us. One of the other approaches I will be looking at soon is the freshly released OCF resource that Florian announced last week. Back in the days our approach meant we didn't have to use clone resources, which you might remember being pretty buggy in the v2 era, not wanting to use clons resources isn't really a valid reason anymore these days . I've also frequently mentioned the combination of using DRBD and MultiMaster replication, using this set of OCF resource makes that a lot more easy .. Now all I need to do is find me some time to validate this setup. Technorati Tags: cluster ha mysql Share with Shareomatic! Trackback URL for this post: http://www.krisbuytaert.be/blog/trackback/1001 PlanetMySQL Voting: Vote UP / Vote DOWN source...

24- MySQL Connector/Net 6.1.4 has been released
MySQL Connector/Net 6.1.4, a new version of the all-managed .NET driver for MySQL has been released. This is our latest GA release and is suitable for use in all scenarios against servers ranging from version 4.1 to 5.5! It is now available in source and binary form from [http://dev.mysql.com/downloads/connector/net/6.1.html] and mirror sites (note that not all mirror sites may be up to date at this point of time – if you can’t find this version on some mirror, please try again later or choose another download site.) This is a maintenance release and includes many bug fixes.  Please review the change log for details. Thank you for using our product! PlanetMySQL Voting: Vote UP / Vote DOWN source...

25- MySQL Connector/Net 6.0.6 has been released
MySQL Connector/Net 6.0.6, a new version of the all-managed .NET driver for MySQL has been released. This is a maintenance release and is approved for use in all situations. It is now available in source and binary form from [http://dev.mysql.com/downloads/connector/net/6.0.html] and mirror sites (note that not all mirror sites may be up to date at this point of time - if you can’t find this version on some mirror, please try again later or choose another download site.) There are lots of bug fixes in this release so please review the changelog. Thank you! PlanetMySQL Voting: Vote UP / Vote DOWN source...

26- “How Fractal Trees Work” talk at MySQL 2010
Here’s the talk I presented at the MySQL User Conference. This talk is a fairly technical talk on how fractal trees work. You can find this talk and other mostly technical material at http://tokutek.com/technology/.PlanetMySQL Voting: Vote UP / Vote DOWN source...

27- From Behind The Ash Curtain
Like many other MySQL conference visitors, also Codership team was stranded in bay area due to the volcano eruption in Iceland. For us however, the extra time in silicon valley was not an issue, as we were working on local assignments anyways, and staying local enabled us to work really focused during this period.Also the "evacuation" from SFO worked out really well for us, thanks to KLM and Air France. We just visited SFO on Thursday (22) and got flights for the next morning. I actually got two bookings, which was a little embarrassing. There were many empty seats and especially the CDG-HEL leg was practically empty. Ash refugees had already left by train, I guess. The return from SFO happened somewhat too early for us, as we had holiday plans for the forthcoming weekend, and we had to cancel the fun part. So this trip ended up as all work and no joy for us...The conference itself, was fun and very interesting. We were busy first to prepare our presentation, then giving the presentation and finally sorting out mis-conceptions caused by the presentation. But it was fun altogether, and Galera got a lot attention there. We had many interesting conversations of Galera and replication strategies in general.To my disappointment, the expo hall was half empty, I'm not sure if this can be profitable and it makes me wonder of the future of this conference.On Fri 16th, there were both Drizzle and MariaDB conferences, which we planned to visit but were too busy to catch. We wanted to sort out the differences between the Drizzle replication API and wsrep API in very detail, but this work must be postponed a little. We, however, entered MariaDB conference just when they were closing, and hooked into short replication discussion with Kristian Nielsen, Sergei Golubchik and Paul McCullagh. MontyProgram is driving the Replication API design and implementation and we don't need to work inside MariaDB code base. Codership will just provide Galera plugin for the end solution. Paul brought to my attention an interesting fact about PBXT: rollback is low effort operation with PBXT. This is very favorable for Galera replication (and optimistic concurrency control in general). Odds are that PBXT will scale better in Galera cluster, even with hot spot work loads.Slides of our presentation are available here: http://en.oreilly.com/mysql2010/public/schedule/detail/13286PlanetMySQL Voting: Vote UP / Vote DOWN source...

28- CLI, Roller, Jersey, JavaOne... and More GlassFish News - April 27th, 2010
Install and Run Apache Roller 4.01 on GlassFish and OpenSolaris Dave Koelmeyer has posted Detailed Instructions on how to install Apache Roller 4.01 on GlassFish v2.1 using MySQL 5.1 for storage.  He uses OpenSolaris snv_134, the subject of a tea-leaf-reading thread. Slides and Code Samples on Jersey and JAX-RS The Slides and code from Paul Sandoz's presentation at Presentation at AlpesJug on Jersey, JAX-RS and Atmosphere are now now available.  The actual presentation was in French, but the slides are in English, and the code is... code. Invoke OSGi Service from JAX-WS Endpoint Arun has published yet another TOTD (Tip Of The Day), with complete instructions and code.  This one is  TOTD #130: Invoking a OSGi service from a JAX-WS Endpoint. Arun's approach is to document the demos he gives at his presentations through the TOTDs.  Quite a bit of work, but it makes the content useful to a world-wide audience. WAS V7 - Inching Towards JavaEE 6 IBM has recently been using a "Feature Pack" approach in upgrading its WebSphere AppServer; it seems to work pretty well for them and they released two packs for WAS V7: Feature Pack for OSGi and JPA 2.0 and Feature Pack for SCA.  IBM is, of course, one of the Java Licensees; WAS v7 is one of the JavaEE 5 Compatible App Servers, the feature pack aproach helps it move towards the JavaEE 6 list. VirtualBox at Oracle One of the challenges during Hands-On-Labs is setting up: the attendees usually bring their own laptops but each of them is different and requires slighlty different setup.  Asking for prep work before attending is not always successful.  A solution now being used in some DB HOLs at Oracle is to Use VirtualBox. Which is the same approach that both Arun and Alexis had advocated for a new series of GlassFish HOLs being planned. GlassFish CLI Masoud has a detailed post - actually a book chapter - that you should read to Learn the GlassFish v3 Command Line Administration Interface (CLI) JavaOne 2010 This year's JavaOne is the first under Oracle and will coincide with Oracle OpenWorld.  Some things will be different, but others are mostly the same - including how the content is being selected - see Sharat Chander's interview by Tori Wieldt for some answers; others will evolve as we get closer to the event. PlanetMySQL Voting: Vote UP / Vote DOWN source...

29- Level 2 Flash cache is there
As I mentioned in my talk An Overview of Flash Storage for Databases I see in the near and middle term future a lot of interest for using Flash storage in Level 2 caching level. The price-capacity trade-off makes Flash as the very good fit for a cache layer. Actually it is not the new idea, and it was implemented in L2ARC ZFS for two years already. It is also described in ACM article “Flash storage memory” by Adam Leventhal. So I am exciting to see it is available for Linux now. Paul Saab and Mohan Srinivasan from Facebook released Flashcache, which allows to use Flash as cache in front of regular hard drives. The implementation is based on dm-cache and introduces a new block level device to the system, which you use instead of a regular partition. I congrats Facebook with implementation of great idea and I am happy to see Facebook releases it under GPL license. I am working on builds to see if we can provide binaries for different platforms. And, I also see another idea implemented by David Jiang http://code.google.com/p/david-mysql-tools/wiki/innodb_secondary_buffer_pool, it allows to use a file on Flash partitions as L2 cache for InnoDB buffer pool. I see a lot of potential here, and I am going to evaluate it for including into XtraDB. Entry posted by Vadim | No comment Add to: | | | | PlanetMySQL Voting: Vote UP / Vote DOWN source...

30- RDBMS software is difficult
I spent the day today reviewing Dmitry's patch for Bug#46947. When looking at the quick fix, we discovered that MySQL <-> storage engine locking protocol is very weakly defined when it comes to more advanced features, such as subqueries, stored functions, views.It's badly defined, it's not documented, it's not tested. As a result, some bits of server behaviour flipped back and forth between 4.1, 5.1 and 5.5. The problem is that when a statement accesses the table via a subquery, view or a function, the lock that the server needs to take on that table is not a function of the SQL in the subquery/view itself, but depends on the context where this view or function is used.If we use the view in an UPDATE statement, and don't take sufficiently strong locks on its tables or rows, replication may break, or, in some engines, repeatable read consistency get violated.This problem does not depend on how effectively we optimize subqueries. It does not matter how fast the storage engine underneath is. It's a bug in the infrastructure that MySQL server provides to its engines, and one that is not easy to get right.This reminded me of my conversation with David, one of MySQL founders, on Drizzle, at the recent O'Reilly MySQL Conference.Obviously, there is no such bug in Drizzle, which has no subqueries, functions or views. And when they add any of those, the bug is very likely to resurface.That's why back then I said that it's impossible to rethink everything. At least, it takes ages to do. I mentioned that no one was able to "rethink" InnoDB, or throw away and replace the obscure code of MySQL optimiser. I mentioned that there is a lot more to it, than just killing badly designed and non-standard MySQL behaviour, or rewriting all the bits to use STL and boost. I said that when it comes to good stuff, there is no reason why MySQL can't or won't do it. The main reason it is harder to do changes with MySQL is a larger legacy, including political and managerial, but you get into exact same situation in any project after your first release. I said that all things considered, the current MySQL trunk is perhaps as good starting point for rethinking as the current Drizzle.Recent years there's been a serious fragmentation of technical thought in MySQL ecosystem. Drizzle, MariaDB, Percona are excellent for community, but are not at all good for our ability to make MySQL a universal database platform. I mean, ability to make MySQL a database platform comparable to what Linux/Unix is nowadays to operating systems. Truth be said, I am not at all sure that my current employer, Oracle, is a good host to seek this holy grail either. Perhaps we'll never get there, not with this project.I would not want to actually diminish importance of Drizzle (initially, I was fond of it and rather wanted to join; the reason I didn't, I've just spelled out). I'd love to be proven wrong, but I don't see it becoming such a universal piece of software that I personally would like to be contributing to. And I've never blogged about it before since, I thought, the more forks, the better.The reason for this blog post is the recent interview on Drizzle with our beloved community leader. Watching the interview, I thought that some properties of Drizzle or all forks, for that matter, are not clearly understood.PlanetMySQL Voting: Vote UP / Vote DOWN source...

31- MySQL Workbench 5.2.20 Beta 10 Available
Announcing a new beta 10 release of MySQL Workbench. Version 5.2.20 includes new fixes for 49 bugs (P1 – 1, P2 – 7 , P3 – 39, P4 – 2) A few new tips you might try. Some have been in the last 2 betas – but in case you haven’t noticed: Once connected in SQL Editor – type control-T or command-T to add a new editor Tab In the SQL Editor run a select. Highlight a cell (one with an image blob preferably) and right click (or command click) and select “Open Value in Viewer” or another option. In a results set — select multiple rows — right click and “copy row content” Click on snippets. Click on the selector and look at DDL and DML lists. Add your own snippets selection drop down option by creating an empty file in the snippets folder and re-opening Workbench. For example on Windows … C:\Documents and Settings\<your login>\Application Data\MySQL\Workbench\snippets called MyCode.txt Want to include your code.  No space — for label – single space for code. Look at ours for an example. MySQL Workbench 5.2 Beta 10 provides: Data Modeling Query (upgrade from MySQL Query Browser) Admin (upgrade from MySQL Administrator) if you are a current user of MySQL Query Browser or MySQL Administrator, we look forward to your feedback on all the new capabilities we are delivering in a single unified MySQL Workbench As always, you will find binaries for the various platforms on our download pages. Please get your copy from our Download http://dev.mysql.com/downloads/workbench/ To get started quickly, please take a look at this short tutorial. MySQL Workbench 5.2 Beta Tutorial http://wb.mysql.com/?p=406 Please be aware that this release is still a beta version – so please don’t use it on your production servers! Also note, MySQL Workbench files saved with version 5.2 cannot be opened with previous versions of our program. The files for several platforms have been pushed to our main server and should be available on our mirrors. Blog postings and general information – including build instructions for Linux – can be found on our Workbench Developer Central site. Workbench Developer Central http://wb.mysql.com Workbench Documentation and details on changes between releases can be found on these pages http://dev.mysql.com/doc/workbench/en/index.html http://dev.mysql.com/doc/workbench/en/wb-change-history.html If you need any additional info or help please get in touch with us. Post in our forums, leave comments on our blog pages or if you want to talk to us directly you can visit us on our IRC channel #workbench on irc.freenode.net. Again, thank you for trying out the Workbench beta, we look forward to your feedback and bug reports. - The MySQL Workbench TeamPlanetMySQL Voting: Vote UP / Vote DOWN source...

32- Releasing Flashcache
At the most recent MySQL conference many people asked us how we are and planning to scale MySQL. One of the ways we're planning on providing better quality of service to our users is the deployment of our caching module for Linux, Flashcache. Flashcache is a simple write back persistent block cache designed to accelerate reads and writes from slower rotational media by caching data in SSD's. We built Flashcache to help us scale InnoDB/MySQL, but it was designed as a generic caching module that can be used with any application built on top of any block device. For InnoDB, when the working set does not fit in the InnoDB buffer pool, read latency is significantly improved due to caching more of the working set in faster media, such as SSD's. We also improve write performance by first caching writes in SSD's and lazily flushing the data back to disk. We hope to share more information about our Flashcache deployment in the future, but the interest was so high in this piece of technology, we decided to release it now. You can find Flashcache hosted on GitHub at: http://www.github.com/facebook/flashcache -Paul, who's so excited about this, he's publishing it from 30,000 feetPlanetMySQL Voting: Vote UP / Vote DOWN source...

33- BLOBs are not just blobs
Recently when talking to someone about PBMS it occurred to me that I had been thinking about BLOBs in the traditional database sense in that they were atomic blocks of data the content of which the server knew nothing about. But with PBMS that need not be the case.The simplest enhancement would be to allow the client to send a BLOB request to the PBMS daemon with an offset and size to just return a chunk of the BLOB. Depending on the application and the BLOB contents this may make perfectly good sense, why force the client to retrieve the entire BLOB if it only want part of it.A much more interesting idea would be to enable the user to provide custom server side functions that they could run against the BLOB.So how would his work?The PBMS daemon would provide its own "BLOB functions" plugin API. The API would be quite simple where the plugin would register the function names it supports. When the PBMS daemon receives a BLOB request specifying a BLOB function name, it calls the BLOB function passing it a hook to the BLOB data and then returns to the client what ever the function returns.The first use of this that I can imagine would be to provide a function that would return the thumbnail from a jpeg image rather than the entire image. Other functions may just return the jpeg metadata.The idea is that BLOBs are not just blobs but are highly structured documents which, given the knowledge of the document structure, it is possible to return portions of the BLOB that are of interest to particular applications.PlanetMySQL Voting: Vote UP / Vote DOWN source...

34- 451 CAOS Links 2010.04.27
VMware and Salesforce.com launch VMforce. Red Hat provides Cloud Access. And more. Follow 451 CAOS Links live @caostheory on Twitter and Identi.ca “Tracking the open source news wires, so you don’t have to.” # VMware and Salesforce.com launched VMforce, a platform for developing and deploying Java cloud applications. # Red Hat Cloud Access enables enterprises to use their Red Hat Enterprise Linux subscription on Amazon Web Services. # Canonical announced Ubuntu 10.04 LTS Server Edition, Desktop Edition and ISV support. # Novell claimed 5,000 certified applications for SUSE Linux Enterprise. # Nokia released the first device based on the fully open source Symbian^3 OS. # Canonical claimed 12,000 active deployments of Ubuntu Enterprise Cloud, prepares LTS release. # The Ruby Association joined the Open Invention Network as a licensee. # Nearly 20% of SMBs plan to begin using open source software in the next 12 months, according to CompTIA. # Microsoft released its StyleCop source code style and consistency tool as open source, using the MS-PL. # LinMin supports the provisioning and imaging of systems running Ubuntu 10.04 LTS. # Puppet Labs launched Puppet Dashboard 1.0. # Microsoft signed the Joomla! Contributor Agreement and contributed code to the 1.6 trunk. # eWeek reported and Schooner declared that Gear6 is in liquidation. # What was Microsoft doing at DrupalCon? Brian Swan answered his own question. # The Apache Hadoop project was granted a license related to Google’s MapReduce patent. # InformationWeek reported on the formation of Riptano , a support provider for the Apache Cassandra database project, while Jonathan Ellis explained his plans for the company. # xTuple grew Q1 revenue by over 20%. # CIGNEX Technologies Inc and AGS Technology Group merged to form CIGNEX Holding Corporation. # Robert Hodges of Continuent shared his thoughts on the present state and potential future of MySQL. # The final version of the Procurement and Open Source Software Guideline has been published on OSOR.eu. PlanetMySQL Voting: Vote UP / Vote DOWN source...

35- VirtualBox images for MariaDB
Coming from a great MariaDB contributor, Mark, is: MariaDB 5.1.44 / 5.2.0 Beta Binaries for Solaris 10 SPARC, and Debian GNU/Linux SPARC. Mark does a fabulous job of building these binaries, and he does them really quickly. If you’re on the SPARC platform, give it a go. Send some feedback, also. Mark has also spent some time developing virtual machines. All you need to get started is download VirtualBox. Mark provides an OpenSolaris 0906 + MariaDB 5.1.44 VM as well as an Ubuntu 10.04 LTS + MariaDB 5.1.42 VM. It is expected by the end of this week, when Ubuntu 10.04 LTS is released, Mark will upgrade the image to include MariaDB 5.1.44. Thanks Mark! This is some fabulous work. Go give his VM’s a try and send feedback. Would you like to see any other VM’s? Any other distributions? Related posts:MariaDB in Gentoo; updates for Solaris/Debian SPARC Recently in MariaDB #1 MariaDB 5.1.44 released PlanetMySQL Voting: Vote UP / Vote DOWN source...

36- Dynamic MySQL Idle Client Connection Timeouts
MySQL 5.1 still suffers to some degree from reduced performance under high concurrency. Until MySQL 5.5 is ready for production, we need some way constrain the amount of work we try to do in parallel. innodb_thread_concurrency is one control we have available, but it isn't perfect. When threads are blocked on IO, for example, no other thread can reuse its concurrency slot and try to accomplish work in the meantime. So most of us probably set innodb_thread_concurrency higher than the number of CPUs our host has, and possibly set it to 0 (unlimited). Another way to control concurrency is limiting the number of client connections within MySQL by setting the max_connections variable. Usually we want to allow clients to stay connected longer than the duration of a single query to avoid having to reconnect for the next one so we set a connection limit of few thousand. We expect our clients to disconnect in a timely fashion after servicing a web request and return the connections for reuse. We can't allow a misbehaving client to hang onto a connection forever, so we set a wait_timeout value to disconnect idle connections after 10 seconds. On occasion we have many misbehaving clients. Either many more connections than usual, or clients that hang onto their connections for much longer than expected. Network issues, memcached server outages, hosts hitting swap, or misbehaving software have all been known to throw a deluge of connections our way. In these circumstances our usual values for max_connections and wait_timeout fail to keep enough connection slots open and our DBs start rejecting connections. Our DBAs scramble to adjust these and other values by hand or rely on scripts to manage these for them. Or goal is to respond to these changing workloads faster. In our latest version of our Facebook patch we've introduced dynamic wait_timeout values. The premise behind the change is pretty simple: as we begin to exhaust our connection slots, we dynamically reduce wait_timeout to free up additional slots. We do not disconnect interactive or superuser connections early, nor connections inside of a transaction. These will be disconnected after wait_timeout or interactive_timeout seconds as usual. The variables used to control this behavior are: connection_recycle: Set this to 1 to enable connection recycling. connection_recycle_min_timeout_ms: Minimum idle time before allowing a connection to be recycled, we will never disconnect an idle connection earlier than this even when we are out of connection slots. connection_recycle_pct_connections_min and ..._max: Defines the range of number of connections (specified as percentages of max_connections) where we recycle connections. The effective idle timeout decreases from wait_timeout to connection_recycle_min_timeout_ms as the number of current connections increases from connection_recycle_pct_connections_min to ..._max. connection_recycle_poll_ms: Polling interval to check if connection should be recycled. To prevent locking each idle network connection polls independently, so the rate of threads waking up to check is max_connections / (connection_recycle_poll_ms / 1000). The variables used to monitor this are: connection_recycle_count: Number of connections recycled early. connection_recycle_idle_time_ms: Sum of elapsed idle time for all recycled connections in milliseconds. If you divide this by connection_recycle_count you get the average idle time for recycled connections. Low average values here indicate overloaded servers. If the average approaches connection_recycle_min_timeout_ms then you are likely not disconnecting fast enough and are likely still rejecting connections.PlanetMySQL Voting: Vote UP / Vote DOWN source...