Planet MySQL

+1

-0

posted by Ansgar Becker on Sun 19 May 2013 09:11 UTC
Tags:

343 revisions after the 7.0 release follows the new 8.0 release of HeidiSQL now.

Get it from the download page.

Here are the most noticable changes:

- Available in 23 languages now. Thanks to all translators and Transifex hereby!
- Database tree: Introduce optional folders for tables, views, routines etc.
- Introduce session folders in session manager.
- Make routine editor work on MS SQL servers.
- Support search and replace in data and query results.
- Add support for microseconds in temporal datatypes of MariaDB 5.3+ and MySQL 5.6.
- Introduce a query history, available in the right side helpers box. Can be turned off.
- Implement grid export as PHP array.
- Host > Variables: Add "Global" column, and highlight values different to their session pendant
- Add

[Read more...]

MySQL 5.6, InnoDB and fast storage

+1

-0

posted by Mark Callaghan on Sat 18 May 2013 22:03 UTC
Tags:

(edit) mysql

I used a simple workload with sysbench to determine the rate at which InnoDB can read blocks from disk. The workload is read-only and each query fetches 1 row by PK. The workload was IO-bound with a 2G InnoDB buffer pool and 32G database. Storage was fast courtesy of buffered IO and enough RAM to cache the database in the OS filesystem cache.

Using MySQL 5.6.11 and InnoDB with a few hacks the peak throughput was about 240,000 QPS and 210,000 block reads/second. The test server has 32 cores (16 physical cores, 32 logical cores with HT enabled). This is a great result that can probably be even better. Contention on fil_system->mutex was the bottleneck and I think that can be improved (see feature request #69276). I wonder if 400,000 block reads/second is possible?

A few years back, in 2009 or 2010,

[Read more...]

New Feature Qualification

+0

-0

posted by Anitha Gopi on Sat 18 May 2013 11:00 UTC
Tags:

(edit) MySQL testing

Early this year Oracle released MySQL 5.6 - Best MySQL Release Ever. This release delivered not only quality, but also quantity in terms of number of features. See a comprehensive list here . The blogs below also refer to the massive changes introduced in 5.6
http://www.mysqlperformanceblog.com/2013/01/27/mysql-5-6-improvements-in-the-nutshell/
http://www.flamingspork.com/blog/2013/03/05/mysql-code-size/

It is no mean task to deliver so many features with high quality that too for a feature rich product like MySQL. This was made possible by

[Read more...]

MySQL binlogs - Don't forget to do your homework!

+0

-0

posted by Phil Hildebrand on Sat 18 May 2013 04:34 UTC
Tags:

(edit) mysql, Replication, configuration, maintenance, troubleshooting

Now that I'm back doing just database stuff, I've come to realize I've gotten a little sloppy about doing my homework. Homework's never been my favorite thing in the world, but it often reduces stress when your under the gun during an outage or upgrade... We had a MySQL database server that's been slow on DML changes, and based on the slowest statements being 'COMMIT', we had a good mind

Percona XtraBackup 2.1.2 for MySQL available for download

+0

-0

posted by MySQL Performance Blog on Sat 18 May 2013 04:25 UTC
Tags:

(edit) mysql, perl, DBD, Percona Software, innobackupex, Percona XtraBackup

Percona is glad to announce the release of Percona XtraBackup 2.1.2 for MySQL on May 18, 2013. Downloads are available from our download site here and Percona Software Repositories.

This release fixes number of high-priority bugs since version 2.1 became GA. It’s advised to upgrade your latest 2.1 version to 2.1.2. This release is the latest stable release in the 2.1 series.

Bugs Fixed:

Using

[Read more...]

OurSQL Episode 140: More Performance

+1

-0

posted by Technocation on Fri 17 May 2013 18:20 UTC
Tags:

(edit) performance, Podcasts

This week we explain performance_schema a bit deeper. In Ear Candy, we talk about max_binlog_cache_size and At the Movies presents Max Mether of SkySQL talking about "High Availability Solutions for MySQL".

Events
DB Hangops - every other Wednesay at noon Pacific time
Upcoming MySQL events (http://www.mysql.com/news-and-events/events/)

Training
SkySQL Trainings
Tungsten University trainings

+0

-1

posted by Marco tusa (Pythian) on Fri 17 May 2013 12:23 UTC
Tags:

(edit) mysql, Oracle, Log Buffer, SQL Server

The red carpet has been laid down at this Log Buffer Edition, and you can witness and cheer the cat-walking blog posts from Oracle, SQL Server and MySQL. Every one of them is chic, elegant, sensual in its own right. Enjoy.
Oracle:

Create colored heat maps in SQL*Plus with Kyle Hailey.

Here’s a quick and dirty script to create a procedure (in the SYS schema – so be careful) to check the Hakan Factor for an object.

Connor has a good post about default null for collection parameter.

This is yet another blogpost on

+3

-0

posted by Nuno Carvalho on Fri 17 May 2013 11:17 UTC
Tags:

(edit) mysql, Replication

2013 is on it's initial months and we already have 5.6 GA and the first release of 5.7, 5.7.1 DMR with lots of exciting new features, future is promising!

A new feature added in replication was the NONBLOCKING option to SHOW SLAVE STATUS command.

In the past if we stop slave during a big transaction, until the transaction was applied we cannot execute SHOW SLAVE STATUS to see the slave progress. The latter operation would block until the former finish, this would disable all external monitoring or third-party applications that need a immediate response from server.

To solve this problem, NONBLOCKING option was added to SHOW SLAVE STATUS, when it is used we can

[Read more...]

Virident vCache vs. FlashCache: Part 2

+1

-0

posted by MySQL Performance Blog on Fri 17 May 2013 10:00 UTC
Tags:

(edit) mysql, Benchmarks, sysbench, virident, Hardware and Storage, mlc, Ernie Souhrada, FlashCache, PCIe, vCache

This is the second part in a two-part series comparing Virident’s vCache to FlashCache. The first part was focused on usability and feature comparison; in this post, we’ll look at some sysbench test results.

Disclosure: The research and testing conducted for this post were sponsored by Virident.

First, some background information. All tests were conducted on Percona’s Cisco UCS C250 test machine, and both the vCache and FlashCache tests used the same 2.2TB Virident FlashMAX II as the cache storage device. EXT4 is the filesystem, and CentOS 6.4 the operating system, although the pre-release modules I received from Virident required the use of the CentOS 6.2 kernel, 2.6.32-220, so that was the kernel in use for all of the benchmarks on

[Read more...]

MyQuery 3.5.1 beta released!

+1

-0

posted by Anders Karlsson on Thu 16 May 2013 22:38 UTC
Tags:

(edit) mysql windows query

After a lot of fuzz, I am now releasing MyQuery version 3.5.1. This version introduces one big feature, a brand new Dictionary viewer. In addition to that, there are numerous bug fixes and the removal of one feature, which is the option to run with just 1 connection: In this version, 2 connections will always be used, and I have some good reasons to remove this as being optional, fact is, running with 1 connection was hard to diagnose, caused a lot of problems, and had no real benefit actually, just drawbacks.

So, for you Windows users, MyQuery 3.5.1 is now out there, but it is really a beta. The beta is caused by the new Dictionary viewer, the rest should be pretty stable.

Download it from sourceforge.

Happy SQLing
/Karlsson

Reestablishing a MySQL Tradition

+4

-0

posted by Dave Stokes on Thu 16 May 2013 16:45 UTC
Tags:

(edit) community team

Every so often you see something from the past and wonder “Why don’t we do that anymore?” Well, in this case it was a former co-worker wearing his MySQL Contributor shirt.

This is Antony Curtis in one of the original MySQL Community Contributor shirt

So the MySQL Community Team had a quick meeting and the result is that we are reestablishing the tradition. So if you have a signed Oracle Contributor Agreement and have contributed to MySQL, you should have in your inbox a request for your shirt size and a shipping address. If you do not see an email and you qualify for a short, let us know (we probably have an old email on record for you). And if you are working on some

+0

-0

posted by Effective MySQL on Thu 16 May 2013 14:40 UTC
Tags:

(edit) Storage Engines, performance, article, storage engine, indexes, Tokutek, compression

Download PDF Presentation

Thanks to Tim Callaghan for speaking Tuesday night at the Effective MySQL New York meetup on Fractal Tree Indexes : Theory and Practice (MySQL and MongoDB). There was a good turnout and a full room to learn how the TokuDB storage engine from Tokutek is changing how to handle big data in MySQL.

Also interesting is how the same technology has been applied for use in MongoDB including giving MongoDB transactions; a big change for NoSQL.

Spreading the word about the Performance Schema

+4

-0

posted by Marc Alff on Thu 16 May 2013 10:02 UTC
Tags:

(edit) mysql, performance_schema

In case you missed it, more and more people are now spreading the word about the Performance Schema, which is a very good thing.

#DBHangOps 4/10/13

Mark Leith presents the Performance Schema and ps_helpers.

Random quotes from the recording:
"I am already seeing so many benefits out of this, ..."
"This looks fantastic"
"Very cool"

OurSQL Episode 139: Starting to Perform

Sheeri and Gerry present the Performance Schema.

Ramdom quote from the recording:
"I am looking at this feature [digests], and I think it's amazing"

Webinar: MySQL 5.6 Performance

[Read more...]

Virident vCache vs. FlashCache: Part 1

+0

-0

posted by MySQL Performance Blog on Thu 16 May 2013 10:00 UTC
Tags:

(edit) mysql, Benchmarks, ssd, virident, Hardware and Storage, Ernie Souhrada, FlashCache, PCIe, vCache

(This is part one of a two part series) Over the past few weeks I have been looking at a preview release of Virident’s vCache software, which is a kernel module and set of utilities designed to provide functionality similar to that of FlashCache. In particular, Virident engaged Percona to do a usability and feature-set comparison between vCache and FlashCache and also to conduct some benchmarks for the use case where the MySQL working set is significantly larger than the InnoDB buffer pool (thus leading to a lot of buffer pool disk reads) but still small enough to fit into the cache device. In this post and the next, I’ll present some of those

+0

-0

posted by Slava Akhmechet on Thu 16 May 2013 07:00 UTC
Tags:

(edit)

We are pleased to announce RethinkDB 1.5 (The Graduate), so go download it now!

This release includes the long-awaited support for secondary indexes, a new algorithm for batched inserts that results in an ~18x performance improvement, support for soft durability (don't worry -- off by default), and over 180 bug fixes, features, and enhancements.

Upgrading to 1.5? Make sure to migrate your data before upgrading to RethinkDB 1.5. →

Secondary indexes

[Read more...]

Index-only queries for Prefix indexes

+1

-0

posted by of team blog MySQL@Facebook on Wed 15 May 2013 21:27 UTC
Tags:

(edit)

MySQL has two great features which historical haven't played well together:

Index-only queries: In some cases, MySQL can resolve a query directly from the index, without having to read the underlying table.

Prefix indexes: This allows you to specify how many bytes to index, which can reduce index size or allow you to index the larger data types (ie. BLOB/TEXT). The drawback being that the entire field isn't stored in the index, so you can't do index-only queries.

One common optimization we do to reduce IOP consumption on database servers is to add additional columns to indexes in order to allow more queries to be index-only. However, sometimes we have these large TEXT fields in order to allow for larger content -- even if the content is normally very small.

For example:

CREATE TABLE tbl

+2

-0

posted by Tokuview Blog on Wed 15 May 2013 19:43 UTC
Tags:

(edit) mysql, conference, MySQL User Conference, Benchmarking, TokuView, percona live

I finally posted a copy of the slides from my Percona Live presentation, “Creating a Benchmarking Infrastructure that Just Works”. The PDF is available via this link.

The content comes from my personal experiences over many years benchmarking and testing databases, usually focusing on performance. It was an opportunity to see how far my personal benchmark infrastructure has evolved, but even better has inspired me to improve it in several areas.

I never had a chance to to my own post-conference wrap-up regarding the Percona Live show. While waiting for my flight home at SFO airport I concluded that

+0

-0

posted by Daniel van Eeden on Wed 15 May 2013 16:21 UTC
Tags:

(edit) mysql, innodb, information_schema

This is part 2, you can find part 1 here.

So in part 1 we learned how to calculate the free space within InnoDB. But unfortunately that won't always work perfectly.

The first issue: the DATA_FREE column in the INFORMATION_SCHEMA.TABLES table will not show a sum of the free space of each partition. This means that if you have innodb_file_per_table disabled and are using partitioning then you must divide DATA_FREE by the number of partitions.
This is Bug #36312.

Example:

mysql> SELECT CONCAT(T.TABLE_SCHEMA,'.',T.TABLE_NAME) AS TABLE_NAME,
    -> P.PARTITION_NAME AS PART,IBT.SPACE,IBD.PATH,T.DATA_FREE AS T_DATA_FREE,
    -> P.DATA_FREE AS P_DATA_FREE FROM

[Read more...]

The Outer Join to Inner Join Coversion

+1

-0

posted by Martin Hansson on Wed 15 May 2013 12:41 UTC
Tags:

(edit)

It is a central part of the MySQL philisophy to try and help you as much as you can. There are many occasions when it could tell you that what you are asking for is utterly stupid or give you a bad execution plan because "you asked for it". But we're friendly here. We don't do that. One of those cases is when you run a query with an outer join but you really meant an inner join, or you don't care.

Inner joins are almost always cheaper to execute than outer joins and that is why MySQL rewrites them to inner joins whenever it can.

An outer join may have WHERE conditions on any of the columns in the result set. In fact, it is a common trick to find non-matching rows using the IS NULL predicate. Here's an example:

TABLE person

name id
Tom 1
Dick 2
Harry 3

[Read more...]

Year of Anniversaries

+2

-0

posted by Mikael Ronström on Wed 15 May 2013 12:10 UTC
Tags:

(edit)

Many people write blogs and emails about various anniversaries. I had an anniversary when I started writing this blog on the 2 may where I celebrated 23 years since I my start date according to my employment contract. 2 may 1990 was the first day I worked at Ericsson where I started working on databases and where NDB Cluster was born that later grew into MySQL Cluster.

Actually this is a year of anniversaries for me. I started by becoming half a century old a few months ago, then I celebrated a quarter of a century as member in the LDS church, my wife and I just celebrated our 25th wedding day and in september I will celebrate 10 years of working with MySQL together with the other people that joined MySQL from Ericsson 10 years ago.

So I thought this was an appropriate timing to write down a little history of my career and particulary focused on how it relates

[Read more...]

Announcing Percona XtraBackup 2.1.1 GA

+3

-0

posted by MySQL Performance Blog on Wed 15 May 2013 12:00 UTC
Tags:

(edit) mysql, Percona XtraBackup

Percona is glad to announce the release of Percona XtraBackup 2.1.1 on May 15th 2013. Downloads are available from our download site here and Percona Software Repositories.

Percona XtraBackup enables backups without blocking user queries, making it ideal for companies with large data sets and mission-critical applications that cannot tolerate long periods of downtime. Offered free as an open source solution,

[Read more...]

Upcoming EMEA Events with MySQL!

+1

-0

posted by Oracle MySQL Group on Wed 15 May 2013 11:00 UTC
Tags:

(edit) mysql, community, conference, event

MySQL Community team is pleased to announce following events as the ones supported by us with a great MySQL staff attending. Find more details below (or at our Community wikis).

NoSQL Roadshow, Berlin Germany, May 16, 2013

Talk: NoSQL & MySQL by Mario Beck

Solutions Libres & Open Source, Paris, France, May 28-29, 2013

Talk: NoSQL & MySQL by Olivier Zemrag
Meet MySQL, Oracle

[Read more...]

Calculating the InnoDB free space

+2

-0

posted by Daniel van Eeden on Wed 15 May 2013 06:28 UTC
Tags:

(edit) mysql, innodb, information_schema, DATA_FREE

Recently someone asked my if it's possible to find the total free space within InnoDB. I thought this would be very easy as the INFORMATION_SCHEMA.TABLES table has a DATA_FREE column. So we could just use SELECT SUM(DATA_FREE) FROM INFORMATION_SCHEMA.TABLES couldn't we?
&nbsp
So what does the DATA_FREE column tell us? It tells us the free data within InnoDB for that particular table. A table can share a tablespace with multiple other tables.
&nbsp
The tablespace which is used by a table depends on whether the innodb_file_per_table was enabled during table creation and/or at the last time the table was rebuild (e.g. by OPTIMIZE TABLE).
&nbsp
If innodb_file_per_table was always disabled then this query probably reports the correct free space:

SELECT DATA_FREE FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='InnoDB' LIMIT 1;

This is

[Read more...]

How to get from MySQL SQL to C

+1

-1

posted by Eric Bergen on Wed 15 May 2013 05:50 UTC
Tags:

(edit) mysql, Geek, mariadb

Occasionally it is useful to know what a MySQL command is doing internally. Just looking into the MySQL source directory can be overwhelming. Knowing the basics of the handler interface and the sql parser can be a great start for reading the source code to understand what MySQL does under the hood. Here I will cover a little bit about how the SQL syntax is defined.

Everything starts with lex.h and sql_yacc.yy in the sql/ dir. lex.h contains all the functions and symbols used to make up the SQL syntax. The sql_yacc.yy file describes the relationships between these symbols and the C functions responsible for executing them. I’m not sure why some symbol definitions end in _SYM and others don’t. Looking in lex.h “FLUSH” is defined as FLUSH_SYM. To see all the places where flush is allowed in the SQL go back to sql_yacc.yy and grep for it.

The

[Read more...]

MySQL 5.6: Improvements in Thread Pool

+0

-0

posted by MySQL on Wed 15 May 2013 00:59 UTC
Tags:

(edit)

MySQL Thread Pool has now been updated for the MySQL 5.6 version. Obviously, with the much higher concurrency of the MySQL Server in 5.6 it's important that the thread pool doesn't add any new concurrency problem when scaling up to 60 CPU threads. The good news is that the thread pool works even better in MySQL 5.6 than in MySQL 5.5. MySQL 5.6 has fixed even more issues when it comes to execution of many concurrent queries and this means that the thread pool provides even more stable throughput almost independent of the number of queries sent to it in parallel.

The EXAMPLE storage engine

+2

-0

posted by Stewart Smith on Wed 15 May 2013 00:12 UTC
Tags:

(edit) mysql, code, innodb, storage engine api

The Example storage engine is meant to serve mainly as a code example of the stub of a storage engine for example purposes only (or so the code comment at the start of ha_example.cc reads). In reality however, it’s not very useful. It likely was back in 2004 when it could be used as a starting point for starting some simple new engines (my guess would be that more than a few of the simpler engines started from ha_example.cc).

The sad reality is the complexity of the non-obviousness of the bits o the storage engine API you actually care about are documented in ha_ndbcluster.cc, ha_myisam.cc and ha_innodb.cc. If you’re doing something that isn’t already done by one of those three engines: good luck.

Whenever I looked at ha_example.cc I always wished there was something

+4

-1

posted by Mark Callaghan on Tue 14 May 2013 19:54 UTC
Tags:

(edit) mysql

I ran single-threaded performance tests to compare MySQL using releases 4.0.30, 5.0.85, 5.1.63 and 5.6.11. On my simple tests 4.0.30 is almost 1.5X faster than 5.6.11. I think it is important to reduce these regressions. Maybe this is an area in which the forks (MariaDB, Percona) will lead the way? I previously opened bug 68825 for this and will update it with the results I report here. Peter and I have written about this previously. Bug 69236 is also open for this now.

I used most of the advice from a

[Read more...]

How to tell whether MySQL Server uses yaSSL or OpenSSL

+2

-0

posted by Todd Farmer of team blog MySQL Support Team on Tue 14 May 2013 17:59 UTC
Tags:

(edit) mysql, Security, MySQL 5.6

Starting with MySQL 5.6, MySQL commercial-license builds use OpenSSL. yaSSL – previously used as the default SSL library for all builds – remains the implementation for Community (GPL) builds, and users comfortable building from source can choose to build with OpenSSL instead. Daniel van Eeden recently requested a global variable to indicate which SSL library was used to compile the server (bug#69226), and it’s a good request. It’s something I’ve previously requested as well, having been fooled by the use of have_openssl as a synonym for

+4

-0

posted by Oracle MySQL Group on Tue 14 May 2013 14:37 UTC
Tags:

(edit) mysql, Events, community, meeting

I would like to invite everyone who is around Trondheim, Norway to the "MySQL mini-seminar" taking place on June 19, 2013 in Oracle office. Do not miss this great opportunity to meet great MySQL engineers who are looking forward to meeting you and talking about MySQL!!!

Please see the official invitation in English and Norwegian below.

Invitation to MySQL mini-seminar

Many people are unaware that a great number of the MySQL developers are located in Trondheim, Norway, and that we have a lot of competence we would love to share.

We therefore invite anyone in Trondheim who's interested in MySQL to a mini-seminar located the Oracle's offices on Lade, June 19 at 3 pm.

The agenda will be:

Presentation: MySQL Query Optimizer: an overview, by Jørgen Løland, MySQL Optimizer

+4

-0

posted by Geert Vanderkelen on Tue 14 May 2013 12:15 UTC
Tags:

(edit) mysql, work, Python, myconnpy

Last week we released MySQL (http://www.mysql.com) Connector/Python v1.0.10. Release notes can be found in the MySQL Developver Zone.

A notable fix in Connector/Python v1.0.10 which might interest a few users is adding support for LOAD DATA LOCAL INFILE. It allows you to import CSV using a simple SQL statement.

Please use the MySQL Bugs website to report any problem.

Some useful links:

Documentation:

[Read more...]

Search

Profile

Top Voted (Last Week)

Top Voted (Last Month)

Top 20 Most Active Authors

Top 10 Most Active Team Blogs

Oracle MySQL Blogs

MySQL Links

Other Links

Planetarium

Secondary indexes