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

OblakSoft is pleased to announce the release of WebStor 1.0b.1.1– the library providing high-performance cloud storage access.

WebStor is used in ClouSE – the Cloud Storage Engine for MySQL.   ClouSE makes cloud storage to be a drop-in replacement for local storage.   The outmost efficient and reliable cloud storage access is one of the key requirements that make the solution viable.

We’ve got a lot of questions about how ClouSE achieves such a great performance working with cloud storage.  Cloud storage has a perception of being slow, but ClouSE works with cloud storage as fast as with local storage.  WebStor is one of three pillars for high-performance cloud storage access (compression and caching are the other two).

In our benchmarks we were able to achieve up to 80+ MB/s transfer rate with Amazon S3.  While ClouSE needs much, much less than that, thanks to compression and caching, when push comes to shove, WebStor is up for the job.

Here is the WebStor performance benchmark chart for transferring data from / to Amazon S3:

The chart shows how the transfer rate was growing, as the number of parallel operations was increasing from 1 to 64.  It started with ~8 MB/sec with one operation running at a time, and grew all the way to ~80 MB/sec with 64 parallel operations.  The benchmark was run in the Amazon US-standard region.  The object size used in the benchmark was 1 MB.   The benchmark source code is included along with the WebStor library.

WebStor has the following features:

-          Supports Amazon S3 and Eucalyptus Walrus.  Other cloud storage provides may be added in the future.

-          Runs on Linux and Microsoft Windows.  Should be easily portable to Linux-like platforms.

-          Supports parallel put / get / del operations which allow achieving high throughput.

-          Supports SSL and plain-text HTTP.

-          Supports HTTP proxy.

-          Supports Amazon S3 multi-part uploads.

-          Provides C++ API.  APIs for other programming languages may be added in the future.

WebStor source code is available under the Apache 2.0 license absolutely FREE.  Get your own copy now from at http://www.oblaksoft.com/downloads.

PlanetMySQL Voting: Vote UP / Vote DOWN

Yesterday we ran a webinar discussing the demands of next generation web services and how blending the best of relational and NoSQL technologies enables developers and architects to deliver the agility, performance and availability needed to be successful.

Attendees posted a number of great questions to the MySQL developers, serving to provide additional insights into areas like auto-sharding and cross-shard JOINs, replication, performance, client libraries, etc. So I thought it would be useful to post those below, for the benefit of those unable to attend the webinar.

Before getting to the Q&A, there are a couple of other resources that maybe useful to those looking at NoSQL capabilities within MySQL:

- On-Demand webinar (coming soon!)

- Slides used during the webinar

- Guide to MySQL and NoSQL whitepaper 

- MySQL Cluster demo, including NoSQL interfaces, auto-sharing, high availability, etc. 

So here is the Q&A from the event 

Q. Where does MySQL Cluster fit in to the CAP theorem?

A. MySQL Cluster is flexible. A single Cluster will prefer consistency over availability in the presence of network partitions. A pair of Clusters can be configured to prefer availability over consistency. A full explanation can be found on the MySQL Cluster & CAP Theorem blog post. 

Q. Can you configure the number of replicas? (the slide used a replication factor of 1)

Yes. A cluster is configured by an .ini file. The option NoOfReplicas sets the number of originals and replicas: 1 = no data redundancy, 2 = one copy etc. Usually there's no benefit in setting it >2.

Q. Interestingly most (if not all) of the NoSQL databases recommend having 3 copies of data (the replication factor).   

Yes, with configurable quorum based Reads and writes. MySQL Cluster does not need a quorum of replicas online to provide service. Systems that require a quorum need > 2 replicas to be able to tolerate a single failure. Additionally, many NoSQL systems take liberal inspiration from the original GFS paper which described a 3 replica configuration. MySQL Cluster avoids the need for a quorum by using a lightweight arbitrator. You can configure more than 2 replicas, but this is a tradeoff between incrementally improved availability, and linearly increased cost.

Q. Can you have cross node group JOINS? Wouldn't that run into the risk of flooding the network?

MySQL Cluster 7.2 supports cross nodegroup joins. A full cross-join can require a large amount of data transfer, which may bottleneck on network bandwidth. However, for more selective joins, typically seen with OLTP and light analytic applications, cross node-group joins give a great performance boost and network bandwidth saving over having the MySQL Server perform the join.

Q. Are the details of the benchmark available anywhere? According to my calculations it results in approx. 350k ops/sec per processor which is the largest number I've seen lately

The details are linked from Mikael Ronstrom's blog

The benchmark uses a benchmarking tool we call flexAsynch which runs parallel asynchronous transactions. It involved 100 byte reads, of 25 columns each. Regarding the per-processor ops/s, MySQL Cluster is particularly efficient in terms of throughput/node. It uses lock-free minimal copy message passing internally, and maximizes ID cache reuse. Note also that these are in-memory tables, there is no need to read anything from disk.

Q. Is access control (like table) planned to be supported for NoSQL access mode?

Currently we have not seen much need for full SQL-like access control (which has always been overkill for web apps and telco apps). So we have no plans, though especially with memcached it is certainly possible to turn-on connection-level access control. But specifically table level controls are not planned.

Q. How is the performance of memcached APi with MySQL against memcached+MySQL or any other Object Cache like Ecache with MySQL DB?

With the memcache API we generally see a memcached response in less than 1 ms. and a small cluster with one memcached server can handle tens of thousands of operations per second.

Q. Can .NET can access MemcachedAPI?

Yes, just use a .Net memcache client such as the enyim or BeIT memcache libraries.

Q. Is the row level locking applicable when you update a column through memcached API?

An update that comes through memcached uses a row lock and then releases it immediately. Memcached operations like "INCREMENT" are actually pushed down to the data nodes. In most cases the locks are not even held long enough for a network round trip.

Q. Has anyone published an example using something like PHP? I am assuming that you just use the PHP memcached extension to hook into the memcached API. Is that correct?

Not that I'm aware of but absolutely you can use it with php or any of the other drivers

Q. For beginner we need more examples.

Take a look here for a fully worked example

Q. Can I access MySQL using Cobol (Open Cobol) or C and if so where can I find the coding libraries etc?

A. There is a cobol implementation that works well with MySQL, but I do not think it is Open Cobol. Also there is a MySQL C client library that is a standard part of every mysql distribution

Q. Is there a place to go to find help when testing and/implementing the NoSQL access?

If using Cluster then you can use the cluster@lists.mysql.com alias or post on the MySQL Cluster forum

Q. Are there any white papers on this? 

Yes - there is more detail in the MySQL Guide to NoSQL whitepaper

If you have further questions, please don’t hesitate to use the comments below!

PlanetMySQL Voting: Vote UP / Vote DOWN

In a previous post, I demonstrated a simple Python database API with a syntax similar to jQuery. The goal was to provide a simple API that would allow Python programmers to use a database without having to resort to SQL, nor having to use any of the good, but quite heavy, ORM implementations that exist. The code was just an experimental implementation, and I was considering putting it up on Launchpad.

I did some basic cleaning of the code, turned it into a Python package, and pushed it to Launchpad. I also added some minor changes, such as introducing a define function to define new tables instead of automatically creating one when an insert was executed. Automatically constructing a table from values seems neat, but in reality it is quite difficult to ensure that it has the right types for the application. Here is a small code example demonstrating how to use the define function together with some other operations.

import mysql.api.simple as api

server = api.Server(host="example.com")

server.test_api.tbl.define(
    { 'name': 'more', 'type': int },
    { 'name': 'magic', 'type': str },
    )

items = [
    {'more': 3, 'magic': 'just a test'},
    {'more': 3, 'magic': 'just another test'},
    {'more': 4, 'magic': 'quadrant'},
    {'more': 5, 'magic': 'even more magic'},
    ]

for item in items:
    server.test_api.tbl.insert(item)

The table is defined by providing a dictionary for each row that you want in the table. The two most important fields in the dictionary is name and type. The name field is used to supply a name for the field, and the type field is used to provide a type of the column. The type is denoted using a basic Python type constructor, which then maps internally to a SQL type. So, for example, int map to the SQL INT type, and bool map to the SQL type BIT(1). This choice of deciding to use Python types are simply because it is more natural for a Python programmer to define the tables from the data that the programmer want to store in the database. I this case, I would be less concerned with how the types are mapped, just assuming that it is mapped in a way that works. It is currently not possible to register your own mappings, but that is easy to add.

So, why provide the type object and not just a string with the type name? The idea I had here is that since Python has introspection (it is a dynamic language after all), it would be possible to add code that read the provided type objects and do things with them, such as figuring out what fields there are in the type. It's not that I plan to implement this, but even though this is intended to be a simple database interface, there is no reason to tie ones hands from start, so this simple approach will provide some flexibility if needed in the future.

Links

Some additional links that you might find useful:

Connector/Python

You need to have Connector/Python installed to be able to use this package.

Sequalize

This is a JavaScript library that provide a similar interface to a database. It claims to be an ORM layer, but is not really. It is more similar to what I have written above.

Roland's MQL to SQL and Presentation on SlideShare is also some inspiration for alternatives.

PlanetMySQL Voting: Vote UP / Vote DOWN

There has been a lot of discussions lately about various non-SQL languages that provide access to databases without having to resort to using SQL. I wondered how difficult it would be to implement such an interface, so as an experiment, I implemented a simple interface in Python that similar to the document-oriented interfaces available elsewhere. The interface generate SQL queries to query the database, but does not require any knowlegdge of SQL to use. The syntax is inspired by JQuery, but since JQuery works with documents, the semantics is slightly different.

A simple example would look like this:

from native_db import *
server = Server(host='127.0.0.1')
server.test.t1.insert({'more': 3, 'magic': 'just a test', 'count': 0})
server.test.t1.insert({'more': 3, 'magic': 'just another test', 'count': 0})
server.test.t1.insert({'more': 4, 'magic': 'quadrant', 'count': 0})
server.test.t1.insert({'more': 5, 'magic': 'even more magic', 'count': 0})
for row in server.test.t1.find({'more': 3}):
  print "The magic is:", row['magic']
server.test.t1.update({'more': 3}, {'count': 'count+1'})
for row in server.test.t1.find({'more': 3}, ['magic', 'count']):
  print "The magic is:", row['magic'], "and the count is", row['count']
server.test.t1.delete({'more': 5})

The first line define a server to communicate with, which is simply done by creating a Server object with the necessary parameters. The constructor accepts the normal parameters for Connector/Python (which is what I'm using internally), but the user defaults to whatever getpass.getuser() returns, and the host default to 127.0.0.1, even though I've provided it here.

After that, the necessary methods are overridden so that server.database.table will refer to the table with name table in database with name database on the given server. One possibility would be to just skip the database and go directly on the table (using some default database name), but since this is just an experiment, I did this instead. After that, there are various methods defined to support searching, inserting, deleting, and updating.

Since this is intended to be a simple interface, autocommit is on. Each of the functions generate a single SQL statement, so they will be executed atomically if you're using InnoDB.

table.insert(row)

This function will insert the contents of the dictionary into the table. using the keys of the dictionary as column names. If the table does not exist, it will be created with a "best effort" guess of what types to use for the columns.

table.delete(condition)

This function will remove all rows in the table that matches the supplied dictionary. Currently, only equality mapping is supported, but see below for how it could be extended.

table.find(condition, fields="*")

This will search the table and return an iterable to the rows that match condition. If fields is supplied (as a list of field names), only those fields are returned.

table.update(condition, update)

This will search for rows matching condition and update each matching row according to the update dictionary. The values of the dictionary is used on the right side of the assignments of the UPDATE statement, so expressions can be given here as strings.

That's all folks!

The code is available at http://mats.kindahl.net/python/native_db.py if you're interested in trying it out. The code is very basic, and there's potential for a lot of extensions. If there's interest, I could probably create a repository somewhere.

Note that this is not a replacement for an ORM library. The intention is not to allow storing arbitrary objects in the database: the intention is to be able to query the database using a Python interface without resorting to using SQL.

I'm just playing around and testing some things out, and I'm not really sure if there is any interest in anything like this, so what do you think? Personally, I have no problems with using SQL, but since I'm working with MySQL on a daily basis, I'm strongly biased on the subject. For simple jobs, this is probably easier to work with than a "real" SQL interface, but it cannot handle as complex queries as SQL can (at least not without extensions).

There is a number of open issues for the implementation (this is just a small list of obvious ones):

Only equality searching supported

Searching can only be done with equality matches, but it is trivial to extend to support more complex comparisons. To allow more complex conditions, the condition supplied to find, delete, and update can actually be a string, in which case it is used "raw".

Conditions could be extended to support something like {'more': '>3'}, or a more object-oriented approach would be to support something similar to {'more': operator.gt(3)}.

No support for indexes

There's no support for indexes yet, but that can easily be added. The complication is what kind of indexes should be generated.

For example, right now rows are identified by their content, but if we want unique rows to be handled as a set? Imagine the following (not supported) query where we insert :

server.test.t1.insert(content with some more=3).find({'more': eq(3)})

In this case, we have to fetch the row identifiers for the inserted rows to be able to manipulate exactly those rows and none other. Not sure how to do this right now, but auto-inventing a row-identifier would mean that tables lacking it cannot be handled naturally.

Creating and dropping tables

The support for creation of tables is to create tables automatically if they do not exist. A simple heuristic is used to figure out the table definition, but this has obvious flaws if later inserts have more fields than the first one.

To support extending the table, one would have to generate an ALTER TABLE statement to "fix" the table.

There is no support for dropping tables... or databases.

PlanetMySQL Voting: Vote UP / Vote DOWN

At OSCON 2011 last week, Oracle delivered more early access (labs) features for MySQL 5.6 replication. These features are focused on better integration, performance and data integrity:

- The Binlog API: empowering the community to seamlessly integrate MySQL with other applications and data stores;

- Binlog Group Commit and Enhanced Multi-Threaded Slaves: continuing to deliver major improvements to replication performance;

- Durable Slave Reads: further enhancing data integrity.

These new features build on the significant replication enhancements announced as part of the MySQL 5.6.2 Development Milestone Release back in April.

We are always listening to our customers and community. And, based on their needs and input, the MySQL engineering team continues to take replication to the next level.

This new functionality is available for evaluation now and can be downloaded today from http://labs.mysql.com/

Highlights for each of the new capabilities are discussed below with links to blogs written by the MySQL engineers, describing implementation and how to get started in evaluating them.

We value your feedback and therefore encourage you to share your input and experiences through the comments sections of this and the blogs referenced below.

Binlog API

The Binlog API empowers the MySQL community to seamlessly integrate MySQL with both new and legacy applications and data stores.

Data volumes around the world now growing at the rate of 40% per annum, driving users to implement more integrated data management technologies to capture, integrate and analyze the trillions of bytes coming daily from web applications, social networking, mobile broadband networks, embedded sensors, etc.

The Binlog API enables developers to reduce the complexity of integration by standardizing their SQL data management operations on MySQL, while replicating data to other applications within their data management infrastructure.

The Binlog API exposes a programmatic C++ interface to the binary log, implemented as a standalone library. 
Using the API, developers can read and parse binary log events both from existing binlog files as well as from running servers and replicate the 
changes into other data stores.

The Binlog API can be evaluated with the MySQL 5.6.2 Development Milestone Release as well as the current GA 5.5 release.

Learn more >> 

You can download the code as part of MySQL Server Snapshot: mysql-5.6-labs-binary-log-api from http://labs.mysql.com

To demonstrate the possibilities of the Binlog API, an example application for replicating changes from MySQL Server to Apache Solr 
 (a full text search server) has been developed. The example is available with the source download on Launchpad 

Enhanced Multi-Threaded Slaves

Multi-threaded slaves were previewed as part of a MySQL 5.6 Labs build in April 2011, delivering significant performance enhancements by allowing updates to be applied in parallel across databases, rather than sequentially.

On-going testing and feedback from the community has enabled Oracle to release an enhanced implementation of multi-threaded slaves including:

- code refactoring;

- bug fixes;

- crash-recovery processes;

- new slave management options - now, when a user issues a STOP SLAVE command, the operation waits until all threads across each database have applied their updates before stopping, ensuring slave consistency. Slaves can still be stopped immediately by killing the SQL thread on the slave;

- improved support for statement-based replication by enhancing the handling of temporary tables;

- simplified configuration through the renaming, reworking and reduction in parameters, without affecting overall tuning ability.

The enhancements delivered as part of this labs release enables the community to extend evaluation and prototyping of new applications using a more robust and feature-complete implementation of multi-threaded slaves.

Learn more >>

You can download the code as part of MySQL Server Snapshot: mysql-5.6-labs-multi-threaded-slave from http://labs.mysql.com

Binlog Group Commit

Designed to improve the performance of MySQL replication, Group Commit applies updates to the binary log in parallel and then commits them as a group to the binlog on disk.

Users have complete control over the frequency of commits to disk – providing two options:

1. Configure the number of transactions that should be grouped together before commit

2. Define the time interval, with millisecond granularity, before the binary log is persisted.

Checksums are used to ensure all events have been written to the binary log.

The current Binlog Group Commit is a snapshot of a work-in-progress.  We have not benchmarked the implementation at this time, and we expect results from that exercise to influence final implementation decisions.  This is a great opportunity for the community to evaluate the implementation and feedback to the MySQL development team.

Learn more >>

You can download the code as part of MySQL Server Snapshot: mysql-5.6-labs-binlog-group-commit from http://labs.mysql.com

Durable Slave Reads

Users now have the option to control when a slave reads the master’s binary log. There are two options:

- Read the binlog as soon as updates are applied to it (with the risk of the data being lost in the event of a master crash).

- Read the binlog only once the updates have been committed to disk, making them read-durable and therefore not risking lost transactions, but meaning the slave will be behind the master.

This new flexibility means users can configure for performance or read-durability, depending on the requirements of their application.

Summary

So in summary, the enhancements delivered by these latest early access features deliver new capabilities and flexibility that benefits almost all users of MySQL replication.

You can evaluate them today by downloading the snapshot from http://labs.mysql.com/

Let us know what you think of these enhancements directly in comments for each blog. We look forward to working with the community to perfect these new features.

PlanetMySQL Voting: Vote UP / Vote DOWN

Okay… So I’ve blogged many times before about the Storage Engine API in Drizzle. This API is somewhat inherited from MySQL. We have very much attempted to make it a much cleaner interface. Our goals in making changes include: make it much easier to write and maintain a storage engine, make the upper layer code obviously correct and clear in what it’s doing and being able to more easily introduce optimisations.

I’ve recently added a Storage Engine that is only used in testing: storage_engine_api_tester. I’ve blogged on it producing call graphs (really state transition graphs) before both for Storage Engine and Cursor.

I’ve been expanding the test. My test engine is now a wrapper around a real engine instead of just a fake one. This lets us run real queries (and test cases) while testing what’s going on. At some point in the near future I plan to make it so that it will be able to log what calls go on to the engine and produce a graph just of those.

I added a lot more to the Storage Engine part of the wrapper. Below is what you can see is the current graph:

I’ve coded what I consider to be bugs as red and what I consider suspect as blue.

Also for the Cursor (colours mean the same):

As you can see, there’s currently some wacky possibilities. I’m investigating exactly what’s going on here – If I’m somehow missing some calls that I should be wrapping (I don’t think so) or if we are really doing some dumb-ass things in the upper layer.

Also, please do not be under any impression that any of this means that we’re going to have a stable API. We’re not. To stabilise on this would just be insane – way too much of it still makes not much sense.

PlanetMySQL Voting: Vote UP / Vote DOWN

In the current HailDB we have a couple of new API calls that you may like:

• ib_status_get_all()
  Is very similar to ib_cfg_get_all(). This allows the library to add new status variables without applications having to know about them – because we return a list of what there are. For Drizzle, this means that the DATA_DICTIONARY.HAILDB_STATUS table will automatically have any new status variables we add to HailDB without a single extra line of code having to be written.
• ib_set_panic_handler()
  Having a shared library call exit() is generally considered impolite. Previously, if HailDB hit corruption (or some other nasty conditions), it could call exit() and you’d never get a chance to display a sensible error message to your user (especially bad in a GUI app where the printed to console error message would be unseen). This call allows an application to specify a callback in the case of HailDB entering such a condition. We’ll still be unable to continue (and we strongly advise that you do in fact exit the process in your callback) but you’re at least now able to (for example) pop up a dialog box saying sorry.
• ib_trx_set_client_data()
  This call lets you associate a void* with a transaction. HailDB keeps this pointer in its transaction data structure and in some callbacks (e.g. ib_set_trx_is_interrupted_handler(), see below) will pass this pointer back to you for you to use to help make a decision. In InnoDB in MySQL, this is the THD. In Drizzle, it’s the Session.
• ib_set_trx_is_interrupted_handler()
  In various wait conditions (e.g. waiting for a row lock), HailDB will call the callback you set with this function with the client data (set with ib_trx_set_client_data()) to work out if the transaction has been cancelled. This enables an application to implement something like the MySQL/Drizzle KILL command to cancel a transaction in another thread.
• ib_get_duplicate_key()
  If you just got a duplicate key error, this function will tell you what key it was. This allows you to implement a nicer error message.
• ib_get_table_statistics()
  This function gives you access to some basic table statistics that HailDB maintains. This includes an approximate row count, clustered index size, total of secondary indexes as well as a “modified counter” which can give you a rough idea about how out of date these statistics are.

All of these are new to HailDB (and weren’t available in embedded_innodb), many in the new 2.3 development release. You can see usage examples both in the HailDB test suite and (for most of them) in the Drizzle HailDB Storage Engine.

PlanetMySQL Voting: Vote UP / Vote DOWN

As anybody who has ever implemented a Storage Engine for MySQL will know, a bunch of the DDL calls got passed a parameter named “path”. This was a filesystem path. Depending on what platform you were running, it may contain / or \ (and no, it’s not consistent on each platform). Add to that the difference if you were creating temporary tables (table name of #sql_somethingsomething) and the difference if you were one of the two (built in) engines that were able to be used for creating internal temporary tables (temp tables that are created during query execution that do not belong in a schema). Well… you had a bit of a mess.

My earlier attempts involved splitting everything up into two strings: schema name and table name. This ended badly. The final architecture we decided on was to have an object passed around that would deal with various transformations (from what the user entered to what we can store on file systems, or to what temporary table maps to what unique name). This is TableIdentifier.

Brian has been introducing it around the code for a while now, and we just got it to now most of the places where table names are passed to Storage Engines. This means that if you’re writing a Storage Engine that doesn’t just blindly store things in files, you can sensibly use the getSchemaName() and getTableName() methods to call your API.

PlanetMySQL Voting: Vote UP / Vote DOWN