Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Previous 30 Newer Entries Showing entries 91 to 120 of 146 Next 26 Older Entries

Displaying posts with tag: storage engine (reset)

Indexing: The Director’s Cut
+0 Vote Up -0Vote Down

Thanks again to Erin O’Neill and Mike Tougeron for having me at the SF MySQL Meetup last month for the talk on “Understanding Indexing.” The crowd was very interactive, and I appreciated that over 100 people signed up for the event and left some very positive comments and reviews.

Thanks to Mike, a video of the talk is now available:

As a brief overview – Application performance often depends on how fast a query can respond and query performance almost always depends on good indexing. So one of the quickest and least expensive ways to increase application performance is to optimize the indexes. This talk presents three simple and effective rules on how to construct

  [Read more...]
Don’t Thrash: How to Cache your Hash on Flash
+1 Vote Up -0Vote Down

Last week I gave a talk entitled “Don’t Thrash: How to Cache your Hash.” The talk took place at the Workshop on Algorithms and Data Structures (ADS) in a medieval castle turned conference center in Bertinoro, Italy. An earlier version of this work (with the same title) appeared at the HotStorage conference in Portland, OR. Tokutek co-founders Bradley, Martin, and I are coauthors on the work, along with students and other faculty at Stony Brook University.

The talk title is colorful and doggerel-y. Here’s what the title means. “Cache your hash”—the so-called Bloom Filter type data structure. A Bloom filter acts like

  [Read more...]
Shard-Query turbo charges Infobright community edition (ICE)
+2 Vote Up -1Vote Down

Shard-Query is an open source tool kit which helps improve the performance of queries against a MySQL database by distributing the work over multiple machines and/or multiple cores. This is similar to the divide and conquer approach that Hive takes in combination with Hadoop. Shard-Query applies a clever approach to parallelism which allows it to significantly improve the performance of queries by spreading the work over all available compute resources. In this test, Shard-Query averages a nearly 6x (max over 10x) improvement over the baseline, as shown in the following graph:


  [Read more...]
+2 Vote Up -0Vote Down
I had a good and lively audience at my presentation today. I was happy to give them a sneek preview of the Mk.III implementation of OQGRAPH. Oh yeah! I guess I'm announcing the availability of the source code of the MkIII implementation. Links are in my slides... http://goo.gl/UrybZ Btw, building the storage engine requires Boost libraries and libJudy installed. It's currently in a MariaDB
MySQL/Drizzle as a Cloud Storage access layer
+2 Vote Up -0Vote Down

 Just got done with day 1 from the MySQL conf 2011... tutorial day. I decided to attend a session a bit outside of my comfort zone, so I chose MySQL Plugin Development. I haven't written any serious C/C++ for over 10 years, but lately it's been more appealing to me.


read more

Innodb row size limitation
+0 Vote Up -0Vote Down

I recently worked on a customer case where at seemingly random times, inserts would fail with Innodb error 139. This is a rather simple problem, but due to it’s nature, it may only affect you after you already have a system running in production for a while.

Suppose you have the following table structure:

CREATE TABLE example (
fcomment TEXT,
fsubtitle TEXT NOT NULL,
fcontent TEXT NOT NULL,
fheader TEXT,
ffooter TEXT,
fdisclaimer TEXT,
fcopyright TEXT,
fstylesheet TEXT,
fterms TEXT,
) Engine=InnoDB;

Now you insert some test data into it:
mysql> INSERT INTO example
->   NULL,
->   'First example',
->   'First comment',
->   'First title',

  [Read more...]
Book review: MySQL 5.1 plugin development
+8 Vote Up -0Vote Down
MySQL 5.1 Plugin Development,
by Sergei Golubchik and Andrew Hutchings.
Packt Publishing, 2010.
Executive summary: Highly recommended. If you want to develop MySQL extensions, buy this book. It's a must, written by two expert professionals who probably know more than anyone else on this matter. The book is full of practical examples explained with the theoretical information necessary to make it stick.

This book fills a gap in the world of MySQL documentation. Although the MySQL docs are extensive and thorough, to the point that sometimes you wished that the

  [Read more...]
PBXT early impressions in production use
+3 Vote Up -2Vote Down

With Paul McCullagh’s PBXT storage engine getting integrated into MariaDB 5.1, it’s never been easier to it out. So we have, on a slave off one of our own production systems which gets lots of inserts from our Zabbix monitoring system.

That’s possibly an ideal usage profile, since PBXT is a log based engine (simplistically stated, it indexes its transaction logs, rather than rewriting data from log into index and indexing that) so it should require less disk I/O than say InnoDB. And that means it should be particularly suited to for instance logging, which have lots of inserts on a sustained basis. Note that for short insert burst you may not see a difference with InnoDB because of caching, but sustain it and then you can notice.

Because PBXT has such

  [Read more...]
OpenSQL (2009 Portland) talk on an Open Storage Engine API
+0 Vote Up -0Vote Down

I just spotted the youtube video of my OpenSQL Camp (Portland 2009) talk on An Open Storage Engine API. I talked about some of technical issues for implementing storage engines across many SQL front ends, not just MySQL.

You can find this talk and other mostly technical material at http://tokutek.com/technology/.

The Drizzle (and MySQL) Key tuple format
+1 Vote Up -0Vote Down

Here’s something that’s not really documented anywhere (unless you count ha_innodb.cc as a source of server documentation). You may have some idea about the MySQL/Drizzle row buffer format. This is passed around the storage engine interface: in for write_row and update_row and out for the various scan and index read methods.

If you want to see the docs for it that exist in the code, check out store_key_val_for_row in ha_innodb.cc.

However, there is another format that is passed to your engine (and that your engine is expected to understand) and for lack of a better name, I’m going to call it the key tuple format. The first place you’ll probably see this is when implementing the index_read function for a Cursor (or handler in MySQL speak).

You get two things: a pointer to the buffer and the length of the buffer. Since a

  [Read more...]
Abusing MySQL (& thoughts on NoSQL)
+2 Vote Up -2Vote Down

The NoSQL/relational database debate has been going on for quite some time. MariaDB, like MySQL is relational. And if you read these series of blog posts, you’ll realise that if you use MySQL correctly, you can achieve quite a lot.

  • It all starts with Kellan Elliott-McCrea with his introductory post on Using, Abusing and Scaling MySQL at Flickr. Follow the entire series.
  • He starts of the series with Ticket Servers: Distributed Unique Primary Keys on the
  •   [Read more...]
    OpenSQLCamp Videos online!
    +2 Vote Up -0Vote Down

    OpenSQLCamp was a huge success! I took videos of most of the sessions (we only had 3 video cameras, and 4 rooms, and 2 sessions were not recorded). Unfortunately, I was busy doing administrative stuff for opensqlcamp for the opening keynote and first 15 minutes of the session organizing, and when I got to the planning board, it was already full….so I was not able to give a session.

      [Read more...]
    OpenSQLCamp Lightning Talk Videos
    +3 Vote Up -0Vote Down

    OpenSQLCamp was a huge success! Not many folks have blogged about what they learned there….if you missed it, all is not lost. We did take videos of most of the sessions (we only had 3 video cameras, and 4 rooms, and 2 sessions were not recorded).

    All the videos have been processed, and I am working on uploading them to YouTube and filling in details for the video descriptions. Not all the videos are up right now….right now all the lightning talks are up.

      [Read more...]
    Paul McCullagh answers your questions about PBXT
    +5 Vote Up -0Vote Down

    Following on from our earlier announcement, Paul McCullagh has responded with the answers to your questions - as well as a few I gathered from other Percona folks, and attendees of OpenSQL Camp. Thank you Paul!

    What’s the "ideal" use case for the PBXT engine, and how does it compare in performance?  When would I use PBXT instead of a storage engine like MyISAM, InnoDB or XtraDB?

    Unfortunately it is not possible to point to a specific category of applications and say, "PBXT will be better here, so try it".  PBXT is a general purpose transactional storage engine, designed to perform well on a broad range of tasks, much like InnoDB.  However, PBXT's log-based architecture makes performance characteristics different to both MyISAM and InnoDB/XtraDB. Tests show that PBXT's performance is similar to InnoDB but, depending on your

      [Read more...]
    Interviews for InfiniDB and TokuDB are next
    +2 Vote Up -0Vote Down

    I forwarded on a list of questions about PBXT to Paul McCullagh today.  While Paul's busy answering them, I'd like to announce that Robert Dempsey (InfiniDB storage engine) and Bradley C. Kuszmaul (TokuDB storage engine) have also accepted an interview. If you have any questions about either storage engine, please post them here by Friday 20th November.

    Entry posted by Morgan Tocker | No comment

    Add to:

      [Read more...]
    Testing TokuDB – Faster and smaller for large tables
    +3 Vote Up -0Vote Down

    For the past two months, I have been running tests on TokuDB in my free time. TokuDB is a storage engine put out by Tokutek. TokuDB uses fractal tree indexes instead of B-tree indexes to improve performance, which is dramatically noticeable when dealing with large tables (over 100 million rows).

    For those that like the information “above the fold”, here is a table with results from a test comparing InnoDB and TokuDB. All the steps are explained in the post below, if you want more details, but here’s the table:

    Importing ~40 million rows119 min 20.596 sec69 min 1.982 sec
    INSERTing again, ~80 million rows total5 hours 13 min 52.58 sec56 min 44.56 sec
    INSERTing again, ~160 million rows total20 hours 10 min 32.35 sec2 hours 2 min 11.95 sec
    Size of table on

      [Read more...]
    MySQL > YourSQL
    +0 Vote Up -0Vote Down

    Since I started doing the occasional consulting job for Open Query, I've seen a lot of MySQL servers that have been installed once and then forgotten about. This gave me the idea to do a short presentation about some basic MySQL server configuration. The first go was at DrupalCampMelbourne and I recently tried (and failed) to cram it into a three minute lightning talk slot at the LUV September meeting.

    The title of the talk is (now) MySQL > YourSQL. I chose this not because I think that MySQL is better than the $other_database you use or because I may or may not run a newer version of MySQL on better hardware, but because I use InnoDB and not MyISAM as the default table

      [Read more...]
    Video: The ScaleDB shared-disk clustering Storage Engine for MySQL
    +4 Vote Up -0Vote Down

    Mike Hogan, CEO of ScaleDB spoke at the Boston MySQL User Group in September 2009:

    ScaleDB is a storage engine for MySQL that delivers shared-disk clustering. It has been described as the Oracle RAC of MySQL. Using ScaleDB, you can scale your cluster by simply adding nodes, without partitioning your data. Each node has full read/write capability, eliminating the need for slaves, while delivering cluster-level load balancing. ScaleDB is looking for additional beta testers, there is a sign up at http://www.scaledb.com.

    Slides are online (and downloadable) at http://www.slideshare.net/Sheeri/scale-db-preso-for-boston-my-sql-meetup-92009

    Watch the video online at http://www.youtube.com/watch?v=emu2WfNx4KA or directly embedded here:

    The mysterious Storage Engine Independent Test Suite
    +4 Vote Up -0Vote Down
    Recently Mark observed that we now all need a storage engine independent test suite, Sun included! Well, as far as I know, there is such a thing at Sun, sort of. Apparently it has been used to test PBXT and other engines, but I've heard it is not in good enough shape to be released.

    But my question is, why not release it anyway? We could turn it into an engine community project. I believe there are enough engine developers out there to get this moving forward.

    The secret is to start small, and just get a few tests to run with all engines. Then additional tests can be added step by step. Engines need a way to specify that they want to skip a test entirely (e.g. transactional tests), and it should be easy to customize results for various engines.

    An example of a simple and

      [Read more...]
    How do I create a simple MySQL database
    +1 Vote Up -0Vote Down

    I was asked this question recently “I am wanting to create a simple MySQL database consisting of 5 tables”?

    While it’s easy to tell people to RTFM, the question does warrant an answer for the MySQL beginner to provide a more specific guidance as to where to start, and what to do. As a expert in MySQL it’s easy to forget how you would describe what to do. Here are my tips to getting started.

    Step 1. Download the MySQL 5.1 software for your platform (e.g. Linux, Windows, Mac etc) from MySQL 5.1 Downloads. There are many different versions of MySQL, MySQL 5.1 is the current production version.

    Step 2. You will need to install the MySQL software. The MySQL reference manual is the place to go, Chapter 2

      [Read more...]
    How do I find the storage engine of a MySQL table
    +2 Vote Up -0Vote Down

    This seems quite a trivial question, but developers don’t often know what a MySQL storage engine is and how to determine what storage engine is used for a table.

    The first choice is to describe the table with the DESC[RIBE] command. Side Note: people often don’t realize that DESC is a short acceptable version here.

    mysql> desc stats;
    | Field   | Type                | Null | Key | Default           | Extra          |
    | stat_id | int(10) unsigned    | NO   | PRI | NULL              | auto_increment |
    | created | timestamp           | NO   |     | CURRENT_TIMESTAMP |                |
    | version | tinyint(3)
      [Read more...]
    Sharding for the masses: Introducing the SPIDER storage engine (OpenSQLCamp @ FrOSCon)
    +4 Vote Up -0Vote Down

    This is the Sharding for the masses: Introducing the SPIDER storage engine by Giuseppe Maxia, given at OpenSQLCamp, at FrOSCon, in August 2009. These are somewhat live notes, and the slides are available too.

    Sharding for the masses View more documents from Giuseppe Maxia.

    Why sharding? Scaling, of course. The MySQL way to solve this, is replication (even Yahoo! and Google use this).

    When the master doesn’t have enough resources to cope with what you do (i.e. large data sets), replication

      [Read more...]
    RethinkDB all the rage today
    +3 Vote Up -0Vote Down

    RethinkDB is all the rage today, as its a Y Combinator funded startup, which also launched a developer pre-alpha today. So what is RethinkDB you ask? Yet-another-MySQL-storage-engine, that’s what. But this time, its tuned for solid-state drives (SSDs), which also happen to be all the rage these days.

    Anyway, check them out more, and the materials currently tell me that they’re using append-only algorithms, which allow for live schema changes and hot backups, with instantaneous recovery from power failure. Those are just some of the exciting bits.

    What didn’t excite me so much was the fact that you were only getting 32-bit or 64-bit Linux binaries, built against MySQL 5.1.31 and you’ll just

      [Read more...]
    Kickfire Basics – The KFDB columnar storage engine
    +0 Vote Up -0Vote Down

    This is the first post in a new series of “Kickfire Basics” blog posts by myself and others here at Kickfire.  This series will review the basics of the Kickfire appliance starting from this post describing how data is stored on disk, to future posts on topics such as loading data into the appliance and writing queries which best leverage the capabilities of the SQL chip.

    The Kickfire Equation
    Column store + Compression + SQL Chip = performance

    The Kickfire Analytic Appliance features the new KFDB storage engine which was built from scratch to handle queries over vast amounts of data.  KFDB is a column store in contrast to most MySQL storage engines which are row stores.  What follows is a description of our column oriented storage engine and how it improves performance over typical row stores.

    This post concerns itself with the first part

      [Read more...]
    ScaleDB for MySQL Needs Cluster-Ready Beta Testers!
    +1 Vote Up -0Vote Down

    ScaleDB provides a pluggable storage engine for MySQL that delivers shared-disk clustering . Brian Akers once described ScaleDB as "the closest thing to Oracle RAC for MySQL." The ScaleDB storage engine turns MySQL into a clustered database, where all of the nodes share the same data. It eliminates the need to partition the data. It also allows you to add and remove nodes without interrupting the application. It will (in time) provide high-availability, but we can’t promise that for the beta version.

    So we’re looking for companies with problems we can uniquely solve. We view the beta process as an investment on both sides, we invest in supporting you and making you successful and you invest your time using our software. I have found

      [Read more...]
    ScaleDB for MySQL Needs Cluster-Ready Beta Testers!
    +0 Vote Up -0Vote Down

    ScaleDB provides a pluggable storage engine for MySQL that delivers shared-disk clustering . Brian Akers once described ScaleDB as "the closest thing to Oracle RAC for MySQL." The ScaleDB storage engine turns MySQL into a clustered database, where all of the nodes share the same data. It eliminates the need to partition the data. It also allows you to add and remove nodes without interrupting the application. It will (in time) provide high-availability, but we can’t promise that for the beta version.

    So we’re looking for companies with problems we can uniquely solve. We view the beta process as an investment on both sides, we invest in supporting you and making you successful and you invest your time using our software. I have found

      [Read more...]
    What is a MySQL storage engine anyway?
    +1 Vote Up -0Vote Down

    "New Shimmer is both a floor wax and a dessert topping!" - Chevy Chase, Saturday Night Live

    On a Saturday Night Live comedy skit, a husband is arguing that Shimmer is a dessert topping, while his wife insists that it’s a floor wax. Then the Shimmer spokesman-Chevy Chase-explains that it is both. While this juxtaposition is humorous, in many respects this is what databases have been doing for years. Instead of specializing on one specific task, most mainstream databases provide an acceptable combination of performance and capabilities across all use cases. The pluggable storage engine architecture is now changing this, ushering in an era of on-demand specialization.

    MySQL allows you to select the storage engine not just for the application, but for each table used by the application. Let me use an analogy to describe how powerful

      [Read more...]
    GPL Licensing and MySQL Storage Engines
    +0 Vote Up -0Vote Down

    The spirit and intent of the Free Software Foundation (FSF) and the GPL license are right on target. However, we must be careful to ensure that the GPL license is interpreted in a manner fulfills the spirit and intent behind its framing. Richard Stallman and associates set out to draft a license agreement that ensures that free software remains free. They didn’t want to see open source become corrupted with the insertion of proprietary code that would eat away at the freedoms they envisioned.

    To protect the eternal purity of the open source software, they created constraints on how proprietary code can interact with the GPL code. Their one weapon in this battle is the automatic and forced expansion of their GPL license to any code that integrates with the base GPLed code. I often refer to this process as acting like a virus. I don’t use this term to infer

      [Read more...]
    Detailed review of Tokutek storage engine
    +2 Vote Up -0Vote Down

    (Note: Review was done as part of our consulting practice, but is totally independent and fully reflects our opinion)

    I had a chance to take look TokuDB (the name of the Tokutek storage engine), and run some benchmarks. Tuning of TokuDB is much easier than InnoDB, there only few parameters to change, and actually out-of-box things running pretty well.

    There are some rumors circulating that TokuDB is ”.. only an in memory or read-only engine, and that's why inserts are so fast”. This is not actually the case, as TokuDB is a disk-based, read-write transactional storage engine that is based on special “fractal tree indexes”. Fractal Trees are a drop-in-replacement for a B-tree (based on current research in data structures by professors at Stony Brook, Rutgers, and MIT). I can't say exactly how it is improved, because the engine itself is

      [Read more...]
    Its a storage engine world, after all…
    +1 Vote Up -0Vote Down

    While Zack covered the storage engine and appliances sessions pretty well, I feel he’s missed out on a few important new engines (or engine related talks):

      [Read more...]
    Previous 30 Newer Entries Showing entries 91 to 120 of 146 Next 26 Older Entries

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.