MySQL Fabric and 5.7 will be the topics of presentations this Thursday (5/22) at the Plug and Play Tech Center in Sunnyvale, California. Lee Stigile is presenting: What’s new in MySQL 5.7, and Sastry Vendantam is presenting MySQL Fabric.

Agenda is as follows:

5:00-5:30 Networking/Socialize over food and drinks
5:30-6:00 Lee will present MySQL 5.7
6:00-6:30 Sastry will present Fabric
6:30-7:00 Q&A and Socialize over food and drinks

Here is the link to register/RSVP

Plug and Play Tech Center
Thursday, May 22, 2014 from 5:00 PM to 7:00 PM (PDT)
440 N Wolfe Rd
Sunnyvale, CA 94085

MySQL Triggers are changing in 5.7 in a big way. Triggers have been around since 5.0 and have not changed much up to 5.6 but will gain the ability to have multiple triggers on the same event. Previously you had ONE trigger maximum on a BEFORE UPDATE, for example, and now you can have multiple triggers and set their order.

So what is a trigger? Triggers run either BEFORE or AFTER an UPDATE, DELETE, or INSERT is performed. You also get access to the OLD.col_name and NEW.col_name variables for the previous value and the newer value of the column.

So how do you use a trigger? Let say you are updating the price of an inventory item in a product database with a simple UPDATE statement. But you also want to track when the price change and the old price.

The table for products.
…

When the MySQL server receives a SELECT query, the query goes through several consecutive phases:

• parsing: SQL words are recognized, the query is split into different parts following the SQL grammar rules: a list of selected expressions, a list of tables to read, a WHERE condition, …
• resolution: the output of the parsing stage contains names of columns and names of tables. Resolution is about making sense out of this. For example, in “WHERE foo=3“, “foo” is a column name without a table name; by applying SQL name resolution rules, we discover the table who contains “foo” (it can be complicated if subqueries or outer joins are involved).
• optimization: finding the best way to read tables: the best order of tables, and for each table, the best way to access it (index lookup, index scan, …). The output …

The post Percona Live MySQL Conference and Expo appeared first on Pythian.

In years past, MySQL was a bit of a black box when it came to understanding what was happening and why. In MySQL 5.6 and 5.7, we’ve added many new features that provide much needed transparency and insight into the inner workings of MySQL. The single biggest feature was the new Performance Schema, but some other examples are:

1. The ability to see what query generated a row based binary log event.
2. The ability to see a tremendous amount of data points for InnoDB.
3. The ability to see an extended query …

“As the database gets used, shards can grow at an uneven rate and one shard might carry a majority of the load. MongoDB corrects this by balancing shards, but because of MongoDB’s lack of concurrency this operation can stall the database unacceptably.”–John Partridge.

I have interviewed John Partridge, President & CEO of Tokutek, Inc.

RVZ

Q1. Tokutek recently announced to have eliminated performance issues of MongoDB sharding. What was the problem?

John Partridge: The problem occurs after a shard is created. As the database gets used, shards can grow at an uneven rate and one shard might carry a majority of the load. MongoDB corrects this by balancing shards, but because of MongoDB’s lack of concurrency this operation can stall the database unacceptably (see the …

As many of you already know, PLMCE is an annual MySQL
community conference and Expo organized by Percona in the month of April
(usually). It is a great conference, not only to meet new and eminent people in
MySQL and related database fields, but also to attend interesting talks, and
also to give some.

This year I spoke about synchronous replication at a higher level. The talk was
titled “ACIDic Clusters: Review of current relational databases with synchronous replication”. Having previously given talks with boring titles (but interesting content), this time I decided to go with an interesting title, and it seemed to fit well with topic being discussed.
…

MySQL variables open_files_limit, table_open_cache and max_connections are
inter-related, and this is for obvious reasons: all deal with file descriptors
one way or another.

If one of the value is provided but others are left out, mysqld calculates
others using a formula and in some cases, emits a warning if not possible.

The whole calculation behind obtaining the final file descriptor limit is a bit
byzantine and is as follows (for Linux):

EDIT: This applies to MySQL 5.5, in 5.6, as Daniël in comments pointed out,
few things have changed, check comment for details. I will probably make a
followup post on the differences.

1. First it tries to calculate a minimum requirement based on max_connections …

MySQL is a great relational database, but at some point someone (management) in your company is probably going to say that they need to use NoSQL to store their data. After all, NoSQL is one of the latest buzzwords, so it must be good (correct?). Basically, NoSQL allows you to store data without all of the characteristics of a relational database. A very simple explanation is that you are storing all of a data set with just one primary key, and the primary key is how you also retrieve the data. While NoSQL may be good in some cases, it is hard to beat “old-fashioned” SQL relational databases – especially if that is what you know. But, with MySQL and InnoDB, you can have the best of both worlds.

With MySQL version 5.6 (and above), you have the ability to store and retrieve NoSQL data, …

Be sure to check out my other posts on mysqldump:
– Scripting Backups of MySQL with Perl via mysqldump
– Splitting a MySQL Dump File Into Smaller Files Via Perl
– Creating and restoring database backups with mysqldump and MySQL Enterprise Backup – Part 1 of 2
– Creating and restoring database backups with mysqldump and MySQL Enterprise Backup – Part 2 of 2

I have already written several posts on using mysqldump. This post will be a quick guide to using …