Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Previous 30 Newer Entries Showing entries 31 to 60 of 1136 Next 30 Older Entries

Displaying posts with tag: Databases (reset)

MongoDB in 2013 -- A Year in Review
+0 Vote Up -1Vote Down
It's again that time of the year. Analysts are spending oceans of words to predict the future, companies are making plans for the next year and people are resting and enjoying the break with their families. To me, this is the perfect time to reflect on my choices, the direction I'm headed to and consider if I still love what I do.

At the beginning of the year I decided to join MongoDB (formerly 10gen). The more I think about it, the more I realize I've been wrong. Yes, it's been the worst decision in my life not to join MongoDB when I was first offered the opportunity years ago. At that time an

  [Read more...]
Upgrading from MySQL 5.1 to MariaDB 5.5
+2 Vote Up -0Vote Down

In my last post, a tale of two MySQL upgrades, a few folks asked if I would outline the process we used to upgrade, and what kind of downtime we had.

Well, the processes were different for each upgrade, so I will tackle them in separate blog posts. The first step was to upgrade all our MySQL 5.1 machines to MariaDB 5.5. As mentioned in the previous post, MariaDB’s superior performance for subqueries is why we switched – and we switched back to MySQL for 5.6 to take full advantage of the performance_schema.

It is not difficult to blog about our procedure, as we have documentation on each process. My first tip would be to do that in your own environment. This also enables other folks to help, even if they are sysadmins and not normally DBAs. You may notice the steps

  [Read more...]
A Tale of Two MySQL Upgrades
+4 Vote Up -0Vote Down

At the beginning of 2013, Mozilla’s MySQL databases were a mix of MySQL 5.0, Percona’s patched MySQL 5.1, Percona’s patched MySQL 5.5 and MariaDB 5.5. MySQL 5.1 was released in November 2008 – so at the beginning of the year, we still had databases with no new major features in 4 years. Currently we have almost all our databases at Oracle’s MySQL 5.6 – the only stragglers are our cluster running TokuDB and a few machines that are no longer in use. Here’s a graph showing the state of our machines – you can see that in the first half of the year we concentrated on upgrading our 5.0 and 5.1 servers to 5.5, and then in the second half of the year we upgraded everything to MySQL 5.6 (click on the image to get a larger

  [Read more...]
Simple Settings for a Saner MySQL – InnoDB
+0 Vote Up -0Vote Down

Within MySQL, there’s a piece called a storage engine that reads and writes to disk on your behalf when you execute a query. It controls the way that your data is stored on disk. With MySQL, you can change what storage engine you use, which is helpful since every engine has different advantages and downsides, and you can select which engine to use based on your workload. There are two main engines: MyISAM and InnoDB. MyISAM was the default engine before MySQL 5.5 and it’s been there since the beginning. It’s also not crash-proof, it doesn’t have foreign keys, and it’s not transactional. InnoDB, on the other hand, has all these features.

  [Read more...]
Unexplained halts using mysql command line client
+0 Vote Up -0Vote Down

I recently came across an issue trying to connect to a MySQL server using the mysql client. It appeared as through the connection was hanging.

A subsequent connection using the -A option highlighted the problem with the previous connection stuck in the state “Waiting for table metadata lock”.

mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
     Id: 37
   User: root
   Host: localhost
     db: tmp
Command: Query
   Time: 90
  State: preparing
   Info: create table missing as select id from AK where id not in (select id ..
*************************** 2. row ***************************
     Id: 38
   User: root
   Host: localhost
     db: tmp
Command: Field List
   Time: 50
  State: Waiting for table metadata lock
   Info:
*************************** 3. row ***************************
  [Read more...]
Next Madrid MySQL Users Group meeting to take place on 16th January 2014
+1 Vote Up -0Vote Down

Yesterday we had our third Madrid MySQL users group meeting. That was quite interesting.  Thanks go to Juan for his presentation.

We plan the next meeting on January 16th after the New Year is out of the way. If you are interested in MySQL and happen to be in Madrid please consider coming to see us.

More information about the next meeting can be found on the group’s web page. Note: The meeting will be in Spanish. I look forward to seeing you.

Giving thanks to MySQL authors challenge
+5 Vote Up -1Vote Down

Next week the US celebrates Thanksgiving Day. For those that are American or live here, this is a significant event. Three different experiences recently have lead me to write this request for ALL MySQL community members to give thanks to those that have contributed to the MySQL ecosystem. I have made a commitment to myself, and I would like to challenge others to write one book review per week in December, that’s 4 book reviews to the MySQL books that I have on my bookshelf that have made an impact in some way. I ask others to give it a go too.

It only takes a few minutes to pen a comment on Amazon, or a publishers site, but to authors it means so much more. I can only speak for myself, but any comment; good, bad or ugly; helps to know you are out there and you took the time to acknowledge somebody’s work of art (in this case a

  [Read more...]
Kick all the tires before you buy the product
+2 Vote Up -0Vote Down

Translating theory to practice is never easy. Morgan gives us the right steps in a play environment to move from dev.mysql.com native MySQL rpm’s to the new MySQL yum repository. I thought I would try it out.

1. Confirming existing packages

A necessary step, however immediately I have more dependencies including Perl DBD (used in several utilities) including MHA.

$ sudo su -
$ rpm -qa | grep -i mysql
MySQL-devel-5.6.13-1.el6.x86_64
MySQL-test-5.6.13-1.el6.x86_64
MySQL-shared-compat-5.6.13-1.el6.x86_64
MySQL-server-5.6.13-1.el6.x86_64
perl-DBD-MySQL-4.013-3.el6.x86_64
MySQL-client-5.6.13-1.el6.x86_64
MySQL-embedded-5.6.13-1.el6.x86_64
MySQL-shared-5.6.13-1.el6.x86_64
mha4mysql-node-0.54-1.el5.noarch

A further trap

  [Read more...]
What SQL is running in MySQL
+3 Vote Up -0Vote Down

Using the MySQL 5.6 Performance Schema it is very easy to see what is actually running on your MySQL instance. No more sampling or installing software or worrying about disk I/O performance with techniques like SHOW PROCESSLIST, enabling the general query log or sniffing the TCP/IP stack.

The following SQL is used to give me a quick 60 second view on a running MySQL system of ALL statements executed.

use performance_schema;
update setup_consumers set enabled='YES' where name IN ('events_statements_history','events_statements_current','statements_digest');
truncate table events_statements_current; truncate table events_statements_history; truncate table events_statements_summary_by_digest;
do sleep(60);
select now(),(count_star/(select sum(count_star) FROM events_statements_summary_by_digest) * 100) as pct, count_star, left(digest_text,150) as stmt, digest from
  [Read more...]
Monitoring an online MySQL ALTER TABLE using Performance Schema
+2 Vote Up -0Vote Down

Recently a client asked me how long it would take for an ALTER TABLE to complete. Generally the answer is “it depends”. While this was running on a production system I tried with the Performance Schema in MySQL 5.6 to work out some answer to this question. While I never got to investigate various tests using INPLACE and COPY for comparison, Morgan Tocker made the request for experiences with online ALTER in A closer look at Online DDL in MySQL 5.6. Hopefully somebody with more time can expand on my preliminary observations.

Using Mark Leith’s ps_helper (older version) I monitored the File I/O to see if I could determine when using innodb_file_per_table the percentage of

  [Read more...]
MySQL RPMS and the new yum repository
+1 Vote Up -0Vote Down

I was really pleased to see the announcement by Oracle MySQL yum repositories that they have now produced a yum repository from where the MySQL RPMs they provide can be downloaded. This makes keeping up to date much easier. Many companies setup internal yum repositories with the software they need as then updating servers is much easier and can be done with a simple command. For many people at home that means you set this up once and don’t need to check for updates and do manual downloads, but can do a quick yum update xxxx and you get the latest version. Great!  This new yum repository only covers RHEL6 did not include RHEL5 which is not yet end of life and still used by me and probably quite a lot of other people. I filed

  [Read more...]
MySQL shutdown via service reporting ERROR
+0 Vote Up -0Vote Down

Working with MySQL 5.6 under CentOS 6.4 I came across the following problem with MySQL reporting it did not shutdown successfully.

$ sudo su -
$ service mysql stop
Shutting down MySQL................................................................................................................................................................................................................................................................................................
.........................................................................................................................................................................................................................................................................................................
  [Read more...]
Updating MySQL using official repositories
+0 Vote Up -0Vote Down

The announcement of the MySQL Yum repositories was a long overdue request on my wish list. While it was possible to find MySQL at http://public-yum.oracle.com/ it was not the GA version MySQL 5.6. (As I write this post, I check and find that indeed it now appears this may be possible http://public-yum.oracle.com/repo/OracleLinux/OL6/MySQL56/).

I have previously installed MySQL 5.6.13 via rpms downloaded from MySQL Downloads, so I expected the upgrade to be straightforward. Following Chapter 3. Upgrading MySQL with the MySQL Yum

  [Read more...]
MySQL performance schema threads
+3 Vote Up -0Vote Down

A trap for those new to the MySQL Performance Schema is the expectation that thread_id in tables such as events_statements_current matches the id you find in the MySQL processlist. This is NOT TRUE.

If we look at the INFORMATION_SCHEMA.PROCESSLIST table we will find information like:

mysql> select id,db,command,state from information_schema.processlist order by id;
-----------+--------------------+---------+------------------------------------------------------------------+
| id        | db                 | command | state                                                            |
+-----------+--------------------+---------+------------------------------------------------------------------+
|         1 | NULL               | Connect | Slave has read all relay
  [Read more...]
What causes LOST_EVENTS error in the MySQL binary log?
+2 Vote Up -0Vote Down

Using MySQL 5.6.13 under CentOS 6.4, I had a replication failure on one master/slave topology because the master binary log had the following entry that was intentionally written by the MySQL server.

$ mysqlbinlog --start-position=244670849 mysql-bin.000029
...
# at 244670849
#131028 19:31:38 server id 39  end_log_pos 244670906 CRC32 0xc5d084ec
# Incident: LOST_EVENTS
RELOAD DATABASE; # Shall generate syntax error
# at 244670906
...

The question is why? I do not know the answer. Searching online indicates this can occur in a MySQL Cluster environment, and can occur around the use of GRANT statements. Neither of these situations are applicable here. This site runs 4 different master/slave topologies all running the same version, and this has been the only occurrence.

The message, if taken literally could indicate that the

  [Read more...]
MySQL Workbench “Clean up SQL” Feature
+1 Vote Up -0Vote Down

I was playing around with MySQL Workbench earlier in the week, and ran across the “clean up SQL” feature, which I thought was neat. Here’s a picture-based demonstration – you can click on the pictures to make them bigger, so they are more readable.

Here is a typical complex query that looks pretty good formatted in the results from a performance schema query:

Simply click the “broom” icon and watch as your SQL is cleaned up, with one field in the SELECT per line and the JOINs indented and formatted prettily:

Pretty cool, for just the click of a button!

MySQL/NoSQL/Cloud Conference Latin America
+1 Vote Up -0Vote Down

Last week I was a guest speaker at the second annual MySQL/NoSQL/Cloud Conference held in Buenos Aires, Argentina. Thanks to Santiago Lertora from Binlogic who has taken on the responsibility of organizing a event for the MySQL community in South America.

My presentations slides for my 3 talks.

While the MySQL community and ecosystem has changed

  [Read more...]
Simple Settings for a Saner MySQL – Character Sets
+0 Vote Up -0Vote Down

Character sets are like the force: they surround us and penetrate us, binding all our digital world together. A character set is how we convert the 1’s and 0’s that the computer understands into human-readable characters like ABC. In one of the first character sets, ASCII, the number 97 is translated to “a” and 63 is the question mark (?).


“Are there other languages besides English?”
“Don’t think so, Bob.”

English-Only Please

The trouble with ASCII is that it was created back in the ‘60s by a bunch of Americans and they were not thinking about French or German, they were thinking about English. Guess what? ASCII works great for American



  [Read more...]
Simple Settings for a Saner MySQL – Part I
+0 Vote Up -0Vote Down

One day, after taking a job using MySQL, I was writing a query quietly at my desk. It was around quittin’ time, and I was whipping up a new report on the monthly sales figures that the CEO needed for his board meeting in the morning. I, in my ignorance, wrote a nice query with a simple little sum() function. What I did not realize is this caused all my rows to sum up to one row when it really should’ve show several rows. This made my report completely inaccurate. Blindly trusting me as I had blindly trusted MySQL, the CEO presented my report to the board who decided that the company was grossly unprofitable and voted to shut it down. Only when I was waiting in the bread line at my local workhouse did I realize my mistake.

  [Read more...]
MySQL Connect presentations on MySQL Fabric available on SlideShare
Employee +1 Vote Up -0Vote Down
Going to MySQL Connect was truly a blast. We got a lot of good questions and feedback in the sessions and there were a lot of interest in both MySQL Fabric and the MySQL Applier for Hadoop.

A big thank you to all that attended the talks, I got a lot of good questions and comments that will help us build good solutions.

The talks are available on SlideShare:





  [Read more...]
An unexplained connection experience
+0 Vote Up -0Vote Down

The “Too many connections” problem is a common issue with applications using excessive permissions (and those that grant said global permissions). MySQL will always grant a user with SUPER privileges access to a DB to investigate the problem with a SHOW PROCESSLIST and where you can check the limits. I however found the following.

mysql> show global variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 2000  |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> show global status like 'max%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 6637  |
+----------------------+-------+
1 row in set (0.00 sec)

How can the max_used_connection exceed max_connections? This is

  [Read more...]
Learn MySQL for Free with MySQL Marinate, Season 3!
+1 Vote Up -0Vote Down

The 3rd season of MySQL Marinate begins October 1st, 2013*. Join the meetup group and RSVP at season 3 to join! You can do the work on MySQL, or if you prefer, MariaDB or Percona.

If you do not have the book yet, you can still do the first week by using the online material from “Browse Contents” on the O’Reilly book page for Learning MySQL. There is homework for week 1, see the master list for all the information.

If you would like to learn MySQL from the ground up, consider joining us. This is for beginners – If you have no

  [Read more...]
A friday MongoDB funny
+1 Vote Up -0Vote Down

I had to laugh (just a bit) at this on the exhibitor floor at Oracle Open World 2013. There was a large MongoDB presence at the Slot 301. There are a few reasons.
First, the identity crisis remains. There is no MongoDB in the list of exhibitors, it’s 10gen, but where is the 10gen representation in the sign. 99.99% of attendees would not know this.
Second, the first and only slide I saw (as shown below), tries to directly compare implementing a solution to Oracle. The speaker made some comment but I really zoned out quickly. Having worked with MongoDB, even on one of my own projects, contemplated the ROI of being proficient in this for consulting, even discussing at length with the CEO and CTO, and hearing only issues with MongoDB with existing MySQL clients, I have come

  [Read more...]
SQL to Hadoop and back again, Part 1: Basic data interchange techniques
+0 Vote Up -0Vote Down

I’ve got a new article, which is part of a new three-part series, on moving data between SQL and Hadoop, both the export to Hadoop and importing processed content back into an SQL store.

In this first one, we look at the basic mechanics and considerations before you start the migration of data, such as the data format, content, and export techniques.

Read: SQL to Hadoop and back again, Part 1: Basic data interchange techniques


MySQL Puppet Module and Slides
+1 Vote Up -0Vote Down

During yesterday’s MySQL Connect conference, Brandon Johnson and I gave a joint talk about how Mozilla uses puppet to manage hundreds of MySQL servers efficiently. We also released our mysql puppet module on github, so that all can benefit. The slides for the talk are at http://bit.ly/puppet-mysql-slides.

Upgrading to MySQL 5.6 Slides
+1 Vote Up -0Vote Down

I am about to give a talk at MySQL Connect about what you need to know before upgrading to MySQL 5.6.

The PDF slides are online at http://bit.ly/upgrade56. I will be posting a video in the next few weeks!

A Brief Introduction to MySQL Fabric
Employee +2 Vote Up -0Vote Down
As you saw on the keynote, we are introducing an integrated framework for managing farms of MySQL servers with support for both high-availability and sharding. It should be noted that this is a very early alpha and that it at this point is not ready for production use.

MySQL Fabric is an integrated system for managing a collection of MySQL servers and is the framework on which high-availability and sharding is built. MySQL Fabric is open-source and is intended to be extensible, easy to use, and support procedure execution even in the presence of failure, an execution model we call resilient execution.

To ensure high-availability, it is necessary to have redundancy in the system. For database systems, the redundancy traditionally takes the form of having a primary server acting as a master and using replication to keep secondaries



  [Read more...]
Second Madrid MySQL Users Group taking place tomorrow Thursday, 12th September
+0 Vote Up -0Vote Down

If you happen to have some free time tomorrow and are in Madrid please come along to the second Madrid MySQL Users Group.

Details can be found here. The meeting will be in Spanish. I look forward to seeing you.

Video: Getting Started with Performance Schema
+2 Vote Up -0Vote Down

Recently I gave a new talk to both the Tokyo and Boston MySQL User Groups about how to get started using performance schema. I have put some resources online for those interested:

Performance Schema talk video

PDF slides

The feedback has been excellent, so I hope that you find this video useful when trying to learn how to use performance schema and dive into the depth of the information it provides you.

Measuring MySQL Query Performance
+0 Vote Up -0Vote Down

Measuring how well a MySQL is doing can be tough – most metrics are simply proxies for actual performance. Thankfully, the Percona build of MySQL allows you to see exactly how long your queries are taking. It provides a diagnostic view that shows how long queries take, allowing you to gauge its real-world performance. I’ll get to that in a little bit. Before we do that, I want to talk about some other tools you can use.

You can dump the status of the system by running “show global status;”. This will give you how many connections have aborted, rows written, and

  [Read more...]
Previous 30 Newer Entries Showing entries 31 to 60 of 1136 Next 30 Older Entries

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.