Planet MySQL

Implementing asynchronous cascade delete in MySQL

+1

-0

posted by Ike Walker on Tue 21 May 2013 23:12 UTC
Tags:

(edit)

A while back one of my foreign keys started causing trouble. The problem was that some parent rows had tens of thousand of child rows, and the foreign key was defined with CASCADE DELETE enabled. When we deleted one of those parent rows on a master database, it took several seconds to execute the delete because of the cascade. This led to latency for the end user, and also led to replication delays.

The immediate solution was make the application tolerant of orphaned rows in the child table and to drop the explicit foreign key constraint.

I didn't really want to leave those orphaned rows hanging around in the child table, so I decided to implement an asynchronous process to delete the orphaned rows on a scheduled basis. Read on for a description of that process.

Using the

[Read more...]

Shard-Query 2.0 Beta 1 released

+1

-0

posted by Justin Swanhart on Tue 21 May 2013 20:11 UTC
Tags:

(edit) mysql, Announcements, release, Introduction, optimizer, parallelism, shard-query, repository

It is finally here. After three years of development, the new version of Shard-Query is finally available for broad testing.

This new version of Shard-Query is vastly improved over previous versions in many ways. This is in large part due to the fact that the previous version of Shard-Query (version 1.1) entered into production at a large company. Their feedback during implementation was invaluable in building the new Shard-Query features. The great thing is that this means that many of the new 2.0 features have already been tested in at least one production environment.

This post is intended to highlight the new features in Shard-Query 2.0. I will be making posts about individual features as well as posting benchmark results.

[Read more...]

PHPTek

+1

-0

posted by Dave Stokes on Tue 21 May 2013 18:08 UTC
Tags:

(edit) PHP, community team

The PHPTek Conference bills itself as the premier professional PHP conference with a community flair. Having been to dozens of PHP conferences, I thought that was a pretty bold claim.

MySQL Boogiebot

But as they say in Texas, it ain’t bragging if you can do it.

MySQL plush dolphins and BoogieBots. The wind up dancing robots were popular in Chicago

And the organizers picked perfect weather for this Chicago based show.

The quality of the presenters and presentations was amazing high. Most of these sessions covered intricacies with the PHP language. The talks covered the range from

+2

-0

posted by MySQL Performance Blog on Tue 21 May 2013 10:00 UTC
Tags:

(edit) mysql, Replication, MySQL 5.6, gtid

Global Transactions Identifiers are one of the new features regarding replication in MySQL 5.6. They open up a lot of opportunities to make the life of DBAs much easier when having to maintain servers under a specific replication topology. However you should keep in mind some limitations of the current implementation. This post is the first one of a series of articles focused on the implications of enabling GTIDs on a production setup.

The manual describes very nicely how to switch to GTID-based replication, I won’t repeat

+1

-0

posted by Steve Meyers on Mon 20 May 2013 21:54 UTC
Tags:

(edit)

Most serious users of MySQL have moved their tables to InnoDB years ago. For those who haven't, let's discuss why InnoDB is a more scalable solution than MyISAM. MyISAM was designed to be very fast for read queries. It does not handle higher loads of writes very well. It also suffers a more serious flaw: it isn't crash-safe. In other words, you better have frequent backups. MyISAM tables

Why I do what I do?

+1

-0

posted by Cedric PEINTRE on Mon 20 May 2013 20:46 UTC
Tags:

(edit) admin

I was sincerely affected by this last MySQL post and this other very long post from Jeremy Cole.
Yes, these two guys are MySQL rock stars and they are really impressives, their involvement in the MySQL community is utter!

I don’t want to write a long long speech about my simple life…
I just want to clarify why I do what I do.

Many people have asked or wondered without asking why I do what I do
(Jeremy Cole - 2013)

A few years ago Ashley Unitt asked me what I was most proud of, and now, I can make a complete answer. I’m very proud to take part of a community, MySQL has transformed my job into a passion and an incredible desire to share this

+1

-0

posted by Oracle MySQL Group on Mon 20 May 2013 20:03 UTC
Tags:

(edit) mysql, Oracle, Training, DBA, mysql training

After taking this MySQL for Database Administrators course, you will be equipped to use all the features of MySQL to get the best out of your Web, Cloud, and embedded applications, whether you work with the command line or graphical tools such as MySQL Workbench and MySQL Enterprise Monitor, whether your application uses complex queries or the NoSQL API, and whether your preferred challenge is replicated servers or highly-tuned transactional systems.

You can take this 5-day live instructor-led course as a:

Live-Virtual Event: Take this course from your own desk, no travel required. You can choose from a wide selection of events on the schedule to suit different timezones.

+0

-0

posted by Valeriy Kravchuk on Mon 20 May 2013 18:12 UTC
Tags:

(edit) mysql, Presentation, bugs, 5.6 GA

I've already shared my presentation few hours before I made it during PLMCE 2013, back on April 24. Let's just have it here for the reference: http://www.slideshare.net/ValeriyKravchuk/mysql-56experiencesbugssolutions50mins.

During the upcoming weeks I plan to explain every slide in more details (as 50 minutes were not enough for this) here and check status of all the active bugs mentioned in it. I'll also check new bugs for each major feature mentioned (if any). So, stay tuned...

Webinar: SQL Query Patterns, Optimized

+1

-0

posted by Bill Karwin of team blog MySQL Performance Blog on Mon 20 May 2013 10:00 UTC
Tags:

(edit) mysql, tips, optimizer, Bill Karwin, MySQL Webinars, SQL Query Patterns

Next Friday, May 31 at 10 a.m. Pacific, I’ll present Percona’s next webinar, “SQL Query Patterns, Optimized.”

Based on my experiences solving tough SQL problems for Percona training and consulting, I’ll classify several common types of queries with which developers struggle. I’ll test several SQL solutions for each type of query objective, and show how you can use MySQL 5.6 built-in methods to analyze them for optimal query

+4

-1

posted by Mark Leith on Mon 20 May 2013 09:06 UTC
Tags:

(edit) mysql

Interested in an introduction to using Performance Schema to profile statement activity on your MySQL instances? Head on over to Detailed Profiling of SQL Activity in MySQL 5.6, which was recently published on the Dr.Dobbs site!

Easier Overview of Current Performance Schema Setting

+2

-0

posted by Jesper Krogh of team blog MySQL Support Team on Mon 20 May 2013 04:31 UTC
Tags:

(edit) mysql, performance schema, MySQL 5.6, MySQL Connect 2012

While I prepared for my Hands-On Lab about the Performance Schema at MySQL Connect last year, one of the things that occurred to me was how difficult it was quickly getting an overview of which consumers, instruments, actors, etc. are actually enabled. For the consumers things are made more complicated as the effective setting also depends on parents in the hierarchy. So my thought was: “How difficult can it be to write a stored procedure that outputs a tree of the hierarchies.” Well, simple enough in principle, but trying to be general ended up making it into a lengthy project and as it was a hobby project, it often ended up being put aside for more urgent tasks.

[Read more...]

Experimenting with MySQL 5.7

+1

-0

posted by Morgan Tocker on Mon 20 May 2013 04:00 UTC
Tags:

(edit)

I was playing around with MySQL 5.7 this weekend and before having read the changelog, I managed to spot these two little gems.

Duplicate Indexes

“The server now issues a warning if an index is created that duplicates an existing index, or an error in strict SQL mode.” Bug #37520

Example Testcase:

mysql> SHOW CREATE TABLE city\G
*************************** 1. row ***************************
       Table: city
Create Table: CREATE TABLE `city` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT

[Read more...]

What's the deal with NoSQL?

+0

-0

posted by Anders Karlsson on Sun 19 May 2013 18:49 UTC
Tags:

(edit) mysql, mariadb, json

Everybody seems to be looking at and debating NoSQL these days, and so am I and I thought I'd say a few words about it. Which is not to say I haven't said stuff before, bit them I was mainly targeting specific attributes of many NoSQL solutions (like "eventual consistency" or, as you might call it, "instant inconsistency", What I was opposing is that "eventual consistency" has anything to do with just that, consistency. Rather, what this means is that at any point in time the system is inconsistent, and even if it might be consistent, you cannot rely on it being so. Which is fine, but don't call it consistency, call it inconsistency. Allowing a database to be somewhat inconsistent doesn't necessarily mean that it's something wrong with it).

All this said, what is going on here, why are we MySQL and MariaDB users seeing so many

[Read more...]

HeidiSQL 8.0 released

+1

-0

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

(edit) Anouncements

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

[Read more...]

MySQL 5.6, InnoDB and fast storage

+4

-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

-1

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

+1

-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 The Pythian Group 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

+6

-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

+1

-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

-1

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

[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

Duplicate Indexes

Secondary indexes