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

I have finally recovered from my trip to Santa Clara enough that I can scribble down some notes from this year's MySQL Conference. Writing a travel report is part of the deal where my employer covers the travel expense, so even if many people have written about the conference, I need to do it too. And judging from the many posts for instance from Pythian's direction, Nokia is perhaps not the only company with such a policy?

Baron's keynote

There has usually always been something that can be called a "soft keynote". Pirate Party founder Rick Falckvinge speaking at a database conference is a memorable example (I still keep in touch with him, having met him at the Hyatt Santa Clara). This year there was one less day, and therefore less keynotes. The soft keynote was therefore taken care of by Baron using some time out of Peter's opening keynote. Baron's talk was an ode to the conference itself, underscoring the meaning of the conference beyond just learning about technology. Sharing his own journey from a numb ASP.NET coder ("a good day at the office was when I changed a table based layout to pure CSS ...but nobody else seemed to care.") to his role today, he challenged people to network, make new friends and new revolutionary ideas. To me, it was a great opening keynote (and quite obviously would have made less sense on the last day of the conference). The talk, including Peter's part, is available on Percona.TV.

read more

PlanetMySQL Voting: Vote UP / Vote DOWN

I wanted to evaluate MariaDB's virtual column and see if it could store business rules next to table data. With virtual columns this could be done and if you specify as 'virtual', would not even take up space on disk.

The imaginary use case is that there is a sales team in a company and you would like to evaluate if a particular salesperson is eligible for a bonus. In order to get a bonus, you need to sell above the average for the day and be in the top 5 amongst the salespeople.

So here is what I did:

MariaDB [test]> create table salespeople (id int unsigned not null auto_increment primary key, salesperson_id int unsigned not null , `date` datetime not null default 0, sold decimal(15,2) not null default 0, day_avg decimal(15,2) not null default 0,  above_avg char(1) as (if(sold>day_avg,'Y','N')) virtual);
Query OK, 0 rows affected (0.02 sec)

So far so good.

 MariaDB [test]> insert into salespeople(salesperson_id, `date`, sold, day_avg) values (1,now(),300,150);
Query OK, 1 row affected (0.01 sec)
MariaDB [test]> select * from salespeople;
+----+----------------+---------------------+--------+---------+-----------+
| id | salesperson_id | date                | sold   | day_avg | above_avg |
+----+----------------+---------------------+--------+---------+-----------+
|  1 |              1 | 2012-04-19 13:16:32 | 300.00 |  150.00 | Y         |
+----+----------------+---------------------+--------+---------+-----------+
1 row in set (0.00 sec)

Cool, just like mashing a view into a table.

MariaDB [test]> update salespeople set sold = 149 where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
MariaDB [test]> select * from salespeople;
+----+----------------+---------------------+--------+---------+-----------+
| id | salesperson_id | date                | sold   | day_avg | above_avg |
+----+----------------+---------------------+--------+---------+-----------+
|  1 |              1 | 2012-04-19 13:59:57 | 149.00 |  150.00 | N         |
+----+----------------+---------------------+--------+---------+-----------+
1 row in set (0.00 sec)

Works again. No triggers for UPDATE needed.
Now lets try to add another business rule:

MariaDB [test]> drop table salespeople;
Query OK, 0 rows affected (0.03 sec)

MariaDB [test]> create table salespeople (
    -> id int unsigned not null auto_increment primary key,
    -> salesperson_id int unsigned not null,
    -> `date` datetime not null default 0,
    -> sold decimal(15,2) not null default 0,
    -> day_avg decimal(15,2) not null default 0,
    -> above_avg char(1) as (if(sold>day_avg,'Y','N')) virtual,
    -> day_position int not null default 0,
    -> give_bonus char(1) as (if(above_avg='Y' and day_position between 1 and 5,'Y','N') )virtual
    -> );
ERROR 1900 (HY000): A computed column cannot be based on a computed column
MariaDB [test]>

DOH! >.<

ADDITION:

I have found a way to store more business rules with the limitation of not being able to use another virtual column to "build" a more complex rule set:

MariaDB [test]> create table salespeople (
    -> id int unsigned not null auto_increment primary key,
    -> salesperson_id int unsigned not null,
    -> `date` datetime not null default 0,
    -> sold decimal(15,2) not null default 0,
    -> day_avg decimal(15,2) not null default 0,
    -> day_position int not null default 0,
    -> bonus_amount decimal(15,2) as (
    -> case when sold>day_avg and day_position between 1 and 5
    ->  then 0.2 * (sold - day_avg) #20% bonus
    -> when sold>day_avg and day_position >5
    ->  then 0.05 * (sold - day_avg) #5% bonus
    -> else 0
    -> end )virtual
    -> );
Query OK, 0 rows affected (0.75 sec) 

MariaDB [test]> insert into salespeople(salesperson_id, `date`, sold, day_avg, day_position) values (1,curdate(),300,150,3);
Query OK, 1 row affected (0.01 sec) 

MariaDB [test]> select * from salespeople;
+----+----------------+---------------------+--------+---------+--------------+--------------+
| id | salesperson_id | date                | sold   | day_avg | day_position | bonus_amount |
+----+----------------+---------------------+--------+---------+--------------+--------------+
|  1 |              1 | 2012-04-20 00:00:00 | 300.00 |  150.00 |            3 |        30.00 |
+----+----------------+---------------------+--------+---------+--------------+--------------+
1 row in set (0.00 sec)

PlanetMySQL Voting: Vote UP / Vote DOWN

I’d had some difficulty manually creating my own windows service for MariaDB (worked fine from the installer), but it was due to the way I was creating it, so I wanted to share the proper approach:

Old Way:

sc create "maria55" binpath= "\"C:/Program Files/MySQL/MariaDB 5.5/bin/mysqld\"
\"--defaults-file=C:/Program Files/MySQL/MariaDB 5.5/data/my.ini\""
DisplayName= "Maria55" start= "auto"

New Way:

sc create "maria55" binpath= "\"C:/Program Files/MySQL/MariaDB 5.5/bin/mysqld\"
\"--defaults-file=C:/Program Files/MySQL/MariaDB 5.5/data/my.ini\" maria55"
DisplayName= "Maria55" start= "auto"

The key is adding the name, maria55, after the –defaults-file=.. option, but still within the “” that belong to “binpath”.

This extra parameter exists so that mysqld knows whether or not it was started as a service or not.

Without it, the server does not know, and therefore didn’t realize it was running as a service, and thus since the service manager got no response from mysqld, it terminated the service after 30 seconds (though I could connect and issue any MySQL command or query within that 30 seconds).

Many thanks to Wlad for helping me to track this down!

For reference, here is my terminal output:

C:\>sc create "maria55" binpath= "\"C:/Program Files/MySQL/MariaDB 5.5/bin/mysqld\"
\"--defaults-file=C:/Program Files/MySQL/MariaDB 5.5/data/my.ini\" maria55"
DisplayName= "Maria55" start= "auto"
[SC] CreateService SUCCESS

C:\Users\Chris>net start maria55
The maria55 service is starting.
The maria55 service was started successfully.

With the initial service attempt, the service creates fine, but fails after 30 seconds:

C:\Users\Chris>net start maria55
The service is not responding to the control function.

More help is available by typing NET HELPMSG 2186.

Hope this helps.

 

PlanetMySQL Voting: Vote UP / Vote DOWN

On Friday last week, after the intensive days of the conference, Ars Technica wrote and published a nice article about MariaDB including many of the messages we had been delivering during the conference, http://arstechnica.com/business/news/2012/04/mysql-founders-latest-mariadb-release-takes-enterprise-features-open-source.ars.

Last year, when it became clear that O’Reilly wasn’t going to arrange the MySQL user conference in the future, there was a lot of discussion on who should arrange it. In the end Percona was pretty fast informing everyone that they had booked the convention center in Santa Clara to arrange the conference this year. Now with the results to hand it’s easy to say that the conference was very well arranged. Great work Percona!

The MariaDB booth was located in the .Org section of the expo hall and we experienced a huge crowd, especially on the first day (Wednesday) of the conference. Our t-shirts were really popular and we could probably have handed out even double the amount of what we had with us. Unfortunately for those in attendance, we had to put some aside for our next upcoming event in Bellingham, WA, USA 28-29th of April. It’s the LinuxFest Northwest 2012, http://linuxfestnorthwest.org. We hope to see some of you there!

We released MariaDB 5.5.23 GA on Tuesday of the conference. Apparently people just loved this news and we’ve enjoyed double our usual download rates since then.

On the SkySQL MariaDB Solutions Day on Friday the 13th, the MySQL founders Monty and David started the day with a panel and the day continued with sessions on all kinds of MariaDB and MySQL related topics. Make sure you read SkySQL’s summary, http://www.skysql.com/blogs/jenwilbur/seal-you-next-year-successful-mysql-friday-13th-santa-clara.
SkySQL has also posted pictures of the event on https://www.facebook.com/skysql.

During the conference we had many interesting conversations with people and businesses that we haven’t had a chance to meet before who had migrated to MariaDB. I’m certain there will be even more of these discussions this year and next.

To stay up to date with MariaDB, add yourself to the MariaDB announce list, which informs mainly about new releases. Also add yourself to the MariaDB Facebook page to get even more MariaDB news. Sign up at http://mariadb.org.

PlanetMySQL Voting: Vote UP / Vote DOWN

I am very pleased to say that earlier today, SkySQL announced it has raised $4 Million in Series A Round Funding.

Let me post the main part of the press release here:

SAN JOSE – April 18, 2012 – SkySQL, the first choice in affordable database solutions for the MySQL® and MariaDB® databases in the enterprise and the cloud, today announces that the company has raised $4 million in Series A funding from a number of investors, including OnCorps, an elite peer-based community of veteran technology investors and advisors committed to bringing better, cost-disruptive technologies into the mainstream. Also funding the round are European investors including Finnish Industry Investment Ltd., Spintop Ventures and Open Ocean Capital.

SkySQL will primarily use the investment to fund growth in its new product development, including adding critical positions. This is the company’s most recent move in expanding beyond a MySQL and MariaDB services-only company into a fully-fledged database products and services provider. SkySQL made its debut in October 2010 and has been funded to date with seed money from OnCorps, as well as Open Ocean Capital. Today, SkySQL is currently operating in 13 countries.

“The SkySQL strategy aligns well with OnCorps’s technology leadership vision,” said Bob Suh of OnCorps. “SkySQL will capitalize on the growing database and cloud services market. We are delighted and look forward to working with the SkySQL team to contribute to their continued success.”

“This investment will let us focus on ramping up development and efforts behind both enterprise and cloud database products that address a very real need in the marketplace,” said Ulf Sandberg, chief executive officer of SkySQL. “We also plan to build on our existing MySQL and MariaDB services offering, which offers customers unrivaled support, consulting and training.”

You can read the full press release here:

http://www.skysql.com/news-and-events/press-releases/skysql-raises-4-million-series-round

Very exciting times!

 

PlanetMySQL Voting: Vote UP / Vote DOWN

Thanks to all of those who came by our booth and to see Leif’s presentation on Read Optimization, and to my Lightning Talk on OLTP and OLAP at the Percona MySQL Conference and Expo. It was an incredible week and a great place to launch TokuDB v6.0 from! A big thanks to Percona for a great event, to Pythian for a fantastic dinner, and to SkySQL for a worthwhile follow on. We are also very grateful to Network World for giving us a product of the week award, and to Bloor Research for an insightful review of TokuDB v6.0.

Mr. Bill Gets Hammered by Big Data

For those who missed it, here is a copy of Leif’s presentation with a good photo from Percona. Thanks to Sheeri for her tweet as well. In addition, here is a copy of my Lightning Talk (in case you were too distracted by Mr.Bill). There were some great photos taken by Mark Lehmann (including the one shown above, and those in the “Scanner Wars“) as well as Percona. Thanks to Erin,  Sheeri, Amrith and Ernie for their tweets too!

I considered a detailed conference review, but others have already captured the event so well that there was little to add. In case you missed it, there are great write-ups by O’Reilly, Percona, Shlomi, and several others.

Thanks again to those who came by!

 

PlanetMySQL Voting: Vote UP / Vote DOWN

Well, as you or may not have heard, MariaDB 5.5 (5.5.23) was declared GA last week!

It was only about 6-ish weeks ago that MariaDB 5.5 had been released as alpha, so the fact it’s already GA is excellent news for all MariaDB users (and MySQL 5.5 users looking to migrate).

Some of the 5.5 enhancements include:

• Significantly more efficient thread pool, comparable in functionality to the closed source feature in MySQL Enterprise.
• Non-blocking client API Library (MWL#192)
• @@skip_replication option (MWL#234)
• SphinxSE updated to version 2.0.4.
• “extended keys” support for XtraDB and InnoDB
• New INSTALL SONAME statement
• New LIMIT ROWS EXAMINED optimization.
• mysql_real_connect() Changes
  In MySQL, and in MariaDB versions before 5.5.21, mysql_real_connect() removes from the MYSQL object any options set with mysql_option() when it fails. Beginning with MariaDB 5.5.21, options are preserved by a failing mysql_real_connect(); use mysql_close(), as normal, to clear them.
  This only has effect if the MYSQL object is reused after a mysql_real_connect() failure (which is unusual). No real-life incompatibilities are expected from this change (it is unlikely that an application would rely on options being automatically removed between connection attempts).

• The variables replicate_do_*, replicate_ignore_*, and replicate_wild_* have been made dynamic, so they can be changed without requiring a server restart. See Dynamic Replication Variables for more information.
• Updates to performance schema tables are not stored in binary log and thus not replicated to slaves. This is to ensure that monitoring of the master will not cause a slower performance on all slaves. This also fixes a crash on the slaves.

Here is the official “What is MariaDB 5.5″ page:

http://kb.askmonty.org/en/what-is-mariadb-55

Also of interest:

Release Notes
Changelog
General Info

And as always, if you’d like full support for MariaDB, just contact us at SkySQL.

Hope this helps.

 

PlanetMySQL Voting: Vote UP / Vote DOWN

Today I tried to make incremental backups of a MariaDB instance in a MySQL sandbox with Percona XtraBackup.
I used the recently released XtraBackup 2.0. And of course there is documentation about making incremental backups. 

MySQL sandbox makes it easy to run many different MySQL versions on one machine. It does this by changing the port number, data directory, UNIX socket location and a whole lot more.

So I first started with a full backup and after that I used that backup as a base for the incremental backups. To do that I had to specify the port number which is 5522 and the username and password for the msandbox account. As MySQL uses a UNIX socket instead of a TCP connection if the hostname is localhost I specified 127.0.0.1 as hostname to force a TCP connection. That worked!

Then I created the incremental backup by using the --incremental option and the --incremental-basedir option to specify the location of the full backup. That also worked!

Then I tried to make a backup while putting some load on my database. I did use  "INSERT INTO test1(col1) SELECT col1 FROM test1" to do this.

The full and incremental backups still worked, or at least that's what the backup script told me. But the size of the incremental backups was quite small. And I noticed that the LSN was very small and not increasing. The xtrabackup_checkpoints file also told me that the backups where all for exactly the same LSN. As the LSN is only for InnoDB, I verified the table type for my test tables. And my test tables were in fact InnoDB. A "SHOW ENGINE INNODB STATUS\G" told me that the LSN was in fact increasing.

It turned out that XtraBackup was making backups of /var/lib/mysql instead of ~/sandboxes/msb_5_5_22-mariadb/data/. Adding "--defaults-file=~/sandboxes/msb_5_5_22-mariadb/my.sandbox.cnf" to the innobackupex command did correct this.

After specifying the correct config file I did try to make backups under load again. It failed due to the logfiles being too small. So I stopped the database, removed the ib_logfile's and started the database with a larger InnoDB logfile size.

Then It all worked flawlessly!

So you should make sure that your backup completes without errors AND that your backups is from the right database. Of course testing restores regularly would also detect this.
PlanetMySQL Voting: Vote UP / Vote DOWN

The MySQL UC this past week was the best in years.   Percona did an outstanding job of organizing the main Percona Live event that ran Tuesday through Thursday.  About 1000 people attended, which is up from the 800 or so at the O'Reilly-run conference in 2011.  There were also excellent follow-on events on Friday for MariaDB/SkySQL, Drizzle, and Sphinx.

PlanetMySQL Voting: Vote UP / Vote DOWN

If you are following news regarding openSUSE and MySQL, you probably already know, that we have both MySQL and MariaDB in openSUSE to allow users to choose what they want to use. And if these two options are not enough, we’ve got server:database repository with newest and greatest development versions of both and MySQL Cluster on to of that. I think all this is great and awesome, that we have all of that.

Now to the not so great part. Unfortunately I’m bare human, I have to eat, sleep and I have some work, some bugs that takes a lot more time that I expected, some school duties to take care of and of course openSUSE Conference to organize! So as a result of all that, I can’t polish MySQL and MariaDB as much I would love to. And on top of that, I’m not expert in MySQL configuration. Part of that is that it just works and I never had any server where MySQL was slowing things down. But there is plenty of skilled MySQL administrators out there in community! So I’m calling out for help. You skilled MySQL admins probably have a lot of interesting tweaks you are applying to default configuration file. So take a look at them and think what could be useful for everybody, not just in your specific use-case. And either send me snippet with short explanation in the comments, or create .cnf file add it to the flavor directory and send me pull request on github! I’ll keep credits and your explanation of the snippet inside, so people will know, who came with this cool option and what does it do

Oh, and don’t get discouraged if I don’t include it right away, it may take me some time to get to it, but I’ll appreciate every suggestion

PlanetMySQL Voting: Vote UP / Vote DOWN