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 91 to 120 of 1137 Next 30 Older Entries

Displaying posts with tag: Databases (reset)

On the threshold
+0 Vote Up -0Vote Down

When you setup a monitoring system for SQL Server, you often use thresholds to determine when an instance is healthy. You might say that you want to be alerted when CPU use is over 90% or when there’s only 10% of disk space left. The trouble with these thresholds is that they will often throw off false positives, or send you an alert when really nothing is wrong. Simple thresholds often have to be tuned to the individual instance, since a server with 10 TB still has 1 TB of space left at 90% disk use.

Baron Schwartz blogged about this issue in

  [Read more...]
Extending Reporting Services
+0 Vote Up -0Vote Down

I am doing a reporting proof-of-concept (POC) for my company. Business intelligence (BI) is often the last thing that gets thought of during an application’s life cycle because it’s only really necessary after you get customers. Before that, the main focus is on application features. Soon after launch, your coworkers and your customers start asking questions about usage and adoption, and customers start to ask for summary information on their data as well as just dumping their data. If you’re the only guy in charge of the database, this is often overwhelming.

Thankfully, we now have many options for what’s called self-service BI. The developer or DBA sets up the basic data models (say Orders) in an automated tool like SQL Server Reporting Services (SSRS) and then allows an

  [Read more...]
From Oracle to 10gen, The MongoDB Company
+2 Vote Up -0Vote Down
Those who are familiar with me know I've a dream.

5 years ago I decided to leave a systems integrator where I was doing great. Why? I wanted to be in a company with the same growth prospects that Oracle had in the 80s. I dreamed to be in the Oracle of 30 years ago and, as time travel wasn't affordable, I decided to join MySQL AB to help expand the business in Europe, the Middle East and Africa.
A few years later my dream came true, but in a slightly different sense. Sun acquired MySQL and was later swallowed by


  [Read more...]
Percona Live 2013, MySQL, Continuent and an ever-healthy Ecosystem
+0 Vote Up -0Vote Down

I’m sitting here in the lounge at SFO thinking back on the last week, the majority of which has been spent meeting my new workmates and attending the Percona MySQL conference.

For me it has been as much of a family reunion as it has been about seeing the wonderful things going on in MySQL.

Having joined Continuent last month after an ‘absence’ in NoSQL land of almost 2.5 years, joining the MySQL community again just felt like coming home after a long absence. And that’s no bad thing. On a very personal level it was great to see so many of my old friends, many of whom were not only pleased to see me, but pleased to see me working back in the MySQL fold. Evidently many people think this is where I belong.

What was great to see is that the MySQL community is alive and

  [Read more...]
Percona Live Has No Code of Conduct
+17 Vote Up -26Vote Down

I am not at Percona Live this week because I opted to stay home after a crazy year of travel (41 talks in 11 different countries on 3 continents in the past year). However, I realized today that Percona Live has no Code of Conduct.

I will not be attending any Percona Live events until there is an acceptable Code of Conduct. MySQL is the world’s most popular open source database; the community deserves a Code of Conduct.

ETA: I have contacted Kortney, the conference organizer for Percona Live, and asked for a Code of Conduct to be put in place ASAP.

ETA: If you want to know why this is an issue, see http://adainitiative.org/what-we-do/conference-policies/

ETA: This is my personal statement, and not a statement of what any of my Mozilla colleagues may feel. Other

  [Read more...]
The 3 Hidden Messages in Tomas Ulin’s Keynote
+10 Vote Up -1Vote Down

This morning I watched Tomas Ulin’s Keynote at Percona Live: MySQL Conference and Expo, delivered yesterday. I missed this live as I am not at Percona Live (I am on a conference hiatus from March through September for personal reasons). As far as the technical content in it, there have been a few posts about the Hadoop Applier and MySQL 5.7, so there’s not much of a need to delve in there.

Message #1: Failure
I was impressed that Ulin spoke of failure. Around 7:27 in the video above, Ulin says, “We really failed with 5.0,” and “even 5.1 we weren’t fully and back on track when we released.” He spoke about the new way MySQL 5.5 and 5.6 were engineered, a hybrid agile/milestone development cycle. There are some hidden

  [Read more...]
On warming up a MySQL 5.6 server
+5 Vote Up -0Vote Down

In the past…

One of the typical problems you have when restarting mysqld is that the InnoDB buffer pool (buffer pool from now on) is empty and consequently access to the database requires reading directly from disk. Performance suffers dramatically as a consequence.

So the common solution is to artificially warm upthe server by doing queries which will fill the buffer pool. Typical solutions might be to do: SELECT COUNT(*) FROM some_table FORCE INDEX (PRIMARY) LIMIT ... on a number of tables to fill up the pool on startup. Fitting this into the standard mysql init start script is somewhat tricky as no hooks are provided for this sort of post-start action. (It would be nice to have this for other tasks too.)

Of course choosing the right parameters here can be tricky as workload changes over time, and as the ratio of the size of the database to the size of the  [Read more...]
MySQL 5.6 is out, so what is next?
+4 Vote Up -0Vote Down

MySQL 5.6 is out now and that is good news. I have already been using pre-GA versions on some production servers with good success and now that the few wrinkles I have seen have been ironed out, I am sure a lot of people will find the new features in 5.6 well worth waiting for.

However, that does lead to the question of: “what next?”

I still have several things that I would like to see in MySQL in no specific order of preference such as:

  • Session transaction state exposed as a variable to allow you to determine if you have started a transaction or not, and thus allowing you to use BEGIN WORK, ROLLBACK or COMMIT as needed.  This information is available via the C API I believe but not via SQL.  Something like @@in_transaction = 1.  Makes modular programming easier.
  • Table space management. The default behaviour now in 5.6 is to move to
  [Read more...]
Joining Continuent
+0 Vote Up -0Vote Down

I’ve just completed my first month here at Continuent, strangely back into the MySQL ecosystem which I have been working in for some time before I joined CouchOne, and then Couchbase, two and half years ago. Making the move back to MySQL is both an experience, and somehow, comfortable…

Continuent produce technology that makes for easier replication between MySQL servers and, more importantly, more flexible solutions when you need to scale out by providing connector and management functionality for your MySQL cluster. That means that you can easily backup, add slaves, and create complex replication scenarios such as multi-master, and even multiple-site, multiple-master topologies. This functionality is split over two products, Continuent Tungsten, which is the cluster management product, and the open source Tungsten Replicator, which provides the basic replication

  [Read more...]
Videos from Open Database Camp
+4 Vote Up -0Vote Down

Open Database Camp was just over a week ago, Mar 16-17th at Harvard University, co-located with Northeast LinuxFest. We had a great lineup of speakers, and we have processed all 11 videos in record time! We got new video cameras at the beginning of the year, so the video quality and resolution is stellar, you can see everything. Here are the videos:

2013 Open Database Camp
and Related Northeast LinuxFest Videos


  [Read more...]
Open Database Camp Schedule Is Up!
+1 Vote Up -0Vote Down

I have had a lot of folks wanting to know when talks would be during Open Database Camp, and we had enough space in the schedule that we did not need to vote on talks, so I present the Open Database Camp Boston Schedule, now online. Lots of MySQL talks, but also a Mongo talk thrown in for good measure! (still hoping to sneak a Postgres talk in…)

As with any conference schedule, this might change. See you at Harvard University in Cambridge on Saturday and Sunday!

Deprecated, Removed and Ignored Variables in MySQL 5.6
+2 Vote Up -0Vote Down

Over at the OurSQL podcast, Gerry and I were inspired by the Percona blog post about MySQL 5.5 and 5.6 default variable values differences. We were going to do a show where we talked about that, but in researching that topic, we found there were lots (around 20 to be exact) of variables and a few features in MySQL 5.6 that are ignored, removed or deprecated.

These are variables that should be removed from your configuration so as not to cause warnings or errors. When I was writing up the show notes I realized that it was a pretty good list of variables, that anyone can just read – whether or not you are willing/able to listen to the 28-minute podcast.

So if you want to see the list of

  [Read more...]
Different MySQL Forks for Different Folks
+2 Vote Up -1Vote Down

At Confoo last week, I tried out a new presentation, called “Different MySQL Forks for Different Folks”. The idea was to explain the differences among all the forks – Drizzle, MariaDB, Percona and of course Oracle’s MySQL. But I did not just go into technical merit of each fork; I also explained the values of each company, as that can be a big decision in deciding what software to choose.

There are PDF slides and a video on youtube. But I wanted to put some of the links I used to gather information here, as an easy place for folks to come and click links if they like, or if they want to research on their own.

At the official Drizzle documentation:
What is

  [Read more...]
Percona Live Conference Recommendations
+2 Vote Up -0Vote Down

While many attendees are repeat offenders, if 2013 is your first MySQL conference and you are relatively new with MySQL (say

Here are my top recommendations that give you a good grounding in the various conference topics and a wealth of experience from known MySQL authorities, on important topics.

  • A backup today saves you tomorrow by Ben Mildred at Pythian. Losing your data is a terrible experience. Learn what is needed to keep your data safe and you system highly available.
  •   [Read more...]
    [updated] Free book February returns – Get a copy of the InnoDB Quick Reference Guide
    +0 Vote Up -0Vote Down

    This month is a special month. It’s not because of President’s Day or even the exciting day where we revel in groundhogs. No, this month is special because the free book give-away is happening again. This is where you, the reader, gets to win something free for doing nothing more than posting a comment saying that you want a copy of my recently published book – The InnoDB Quick Reference Guide from Packt Publishing. The book is a great reference for DBAs, PHP, Python, or Perl programmers that integrate with MySQL and want to learn more about the InnoDB database engine.

    So, all you have to do is post a comment here saying that you want a copy and write out a single (or more) sentence about how you use InnoDB in your

      [Read more...]
    When is the error log filename not the right filename
    +3 Vote Up -0Vote Down

    When evaluating a MySQL system one of the first things to look at is the MySQL error log. This is defined by the log[_-]error variable in the MySQL Configuration file. Generally found like:

    grep log.error /etc/my.cnf
    log_error=/var/lib/mysql/logs/mysql_error_log
    log-error=/var/lib/mysql/logs/mysql_error_log
    

    It is possible to find multiple rows because this could be defined in the [mysqld] and [mysqld_safe] sections. It is also possible it is incorrectly defined twice in any given section.

    Immediately I see a problem here, and the following describes why. If you look at this file name, in this case it’s actually found, but the file is empty.

    $ ls -l /var/lib/mysql/logs/mysql_error_log
    -rw-r----- 1 mysql mysql 0 Feb 19 20:35 /var/lib/mysql/logs/mysql_error_log
    

    An error log should never exist and be

      [Read more...]
    The MySQL symlink trap
    +3 Vote Up -1Vote Down

    Many users of MySQL install and use the standard directories for MySQL data and binary logs. Generally this is /var/lib/mysql.
    As your system grows and you need more disk space on the general OS partition that commonly holds /tmp, /usr and often /home, you create a dedicated partition, for example /mysql. The MySQL data, binary logs etc are then moved to this partition (hopefully in dedicated directories). For example data is placed in /mysql/data.
    Often however, a symbolic link (symlink) is used to so MySQL still refers to the data in /var/lib/mysql.

    When it comes to removing the symlink and correctly configuring MySQL, you first stop MySQL and correctly defining the datadir my.cnf variable to point to the right location. However, MySQL still keeps the legacy directory information around and this will cause MySQL replication to fail in several ways when you



      [Read more...]
    The Curious Case of the Missing Binlogs
    +0 Vote Up -0Vote Down
    When you enable binlogs in the my.cnf file you can either set the log-bin flag to true, or you can set it to a path and file name prefix such as this:
    [mysqld]
    log-bin=/path/to/binlogs/mysql-binlog
    This changes the default location where binlogs are stored.  The problem is that when you connect to mysql there is currently no way to query the server to find out if that path has been changed, and what it currently is.  This means you can't be sure where any server's binlogs are actually stored.
    Ok, so they're not really missing, but it's a known issue that mysql doesn't make them easy to find.  The server obviously knows the path internally, but it doesn't make this information available. Bug #35231 has been open on this issue since 2008 and is currently being ignored.


      [Read more...]
    Percona Repo Conflicts With Itself
    +3 Vote Up -2Vote Down

    On a RHEL 6.3 server, we are running MySQL 5.1 (not MariaDB, not the Percona patched version, plain old regular MySQL):

    $ rpm -qa | egrep -i "percona|maria|mysql" | sort
    mysql-5.1.61-4.el6.x86_64
    mysql-libs-5.1.61-4.el6.x86_64
    mysql-server-5.1.61-4.el6.x86_64
    percona-toolkit-2.1.8-1.noarch
    percona-xtrabackup-2.0.5-499.rhel6.x86_64
    perl-DBD-MySQL-4.013-3.el6.x86_64
    $

    We went to do a system update today, and had a problem with dependencies, because the Percona repository wants to replace mysql-libs with Percona’s shared libraries. How rude!

    # yum update mysql-libs
    Loaded plugins: downloadonly, rhnplugin, security
    Setting up Update Process
    Resolving Dependencies
    --> Running transaction check
    ---> Package Percona-Server-shared-51.x86_64











      [Read more...]
    What OS do you use for MySQL?
    +0 Vote Up -0Vote Down

    In looking at operating systems in use for last year I found a very high concentration of RedHat/CentOS 5, and Ubuntu LTS operating systems. I would like to get a better picture of what is really used for MySQL production systems.
    Please take a moment to help me out. This survey only has one question.

    Thanks

    Poor programming practices
    +3 Vote Up -2Vote Down

    When will it stop. These amateur programmers that simply cut/paste code really affect those good programmers in the ecosystem trying to make a decent living. I was reviewing a developed (but incomplete) PHP/MySQL system using a common framework (which in itself is irrelevant for this post).

    In one source file there were 12 repetitions of the following code:

       //permissions
        $this->security_model->setUserPermissions($id);
        if (!array_key_exists($id,$this->session->userdata['permissions']) OR
    	!array_key_exists('id', $this->session->userdata['permissions'][$id]) OR
    	!array_key_exists('scope', $this->session->userdata['permissions'][$id]['name'])){
          $this->session->set_flashdata('alert', 'You are not authorized to go there.');
          redirect($this->agent->referrer());
        }
    

    It’s bad enough when code

      [Read more...]
    IN Subqueries in MySQL 5.6 Are Optimized Away
    +9 Vote Up -12Vote Down

    In MySQL 5.6, it looks like IN() subqueries are optimized even better than they are in MariaDB 5.5. Here’s a typical IN() subquery, using the sakila sample database (query taken from slide 6 of the presentation about new MySQL 5.6 optimizer statistics):

    SELECT title FROM film WHERE film_id IN (SELECT film_id FROM film_actor)

    Before there were any subquery optimizations, say if you are using MySQL 5.1, you would have to rewrite this query as a JOIN, to avoid the dreaded DEPENDENT SUBQUERY that shows up in the EXPLAIN:

    mysql> explain SELECT title FROM film WHERE film_id IN (SELECT film_id FROM film_actor)\G
    *************************** 1. row ***************************
    id: 1
    select_type: PRIMARY


      [Read more...]
    Do we need a MySQL Cookbook?
    +2 Vote Up -2Vote Down

    The blog title says it all: Do we need a MySQL Cookbook? I tend to think so.

    This seems to be something that is missing with current MySQL documentation. There is lots of information available but finding the appropriate bit can be quite tedious and it often requires looking in multiple places.

    A lot of other software has such books, but for some reason MySQL seems to be missing one.

    A recent example comes from a “documentation feature request” I posted today: http://bugs.mysql.com/bug.php?id=68171. MySQL 5.6 provides a way to “move InnoDB tables” from one server to another. There are many reasons why you may want to do it, but the documentation is currently rather sparse. A simple “example recipe” for this would be good, as would an equivalent recipe for other

      [Read more...]
    New EXPLAIN Features in MySQL 5.6 – FORMAT=JSON actually adds more information!
    +8 Vote Up -0Vote Down

    When I heard that MySQL 5.6 was implementing EXPLAIN for writes (INSERT,UPDATE,DELETE,REPLACE), I was pretty excited. Then I heard that MySQL 5.6 also was implementing a JSON format for EXPLAIN and my thought was “I do not care about that!”

    Boy, was I wrong. The JSON format does not just put the output into JSON format, it also gives extra information that’s actually pretty useful! It can tell you when you are doing an implicit cast, which parts of a composite index are being used, and when index condition pushdown are being used. None of these are shown in regular EXPLAIN (which seems odd, why could they extend the JSON format but not put the information into the regular EXPLAIN format?), so using the JSON format is actually a good idea even if you do not care about what format your output is in.

    As a

      [Read more...]
    Upgrading to MySQL 5.5 on Ubuntu 10.04 LTS
    +4 Vote Up -0Vote Down

    Ubuntu does not provide an apt-get repository package for MySQL 5.5 on this older OS, however this is still a widely used long term support version. The following steps will upgrade an existing MySQL 5.1 apt-get version to a standard MySQL 5.5 binary.

    Step 1. Remove existing MySQL 5.1 retaining data and configuration

    sudo su -
    service mysql stop
    cp -r /etc/mysql /etc/mysql.51
    cp -r /var/lib/mysql /var/lib/mysql.51
    which mysqld
    dpkg -P mysql-server mysql-server-5.1 mysql-server-core-5.1
    which mysqld
    which mysql
    dpkg -P mysql-client-5.1 mysql-client-core-5.1
    which mysql
    dpkg -P libdbd-mysql-perl libmysqlclient16 mysql-common
    # This will not remove /etc/mysql if any other files are in the directory
    dpkg -l | grep mysql
    [ -d /etc/mysql ] && mv /etc/mysql /etc/mysql.uninstall
    cp -r /etc/mysql.51 /etc/mysql
    

    2. Prepare configuration  [Read more...]

    MySQL 5.1 vs. MySQL 5.5: Floats, Doubles, and Scientific Notation
    +8 Vote Up -0Vote Down

    For the past few days, I have been upgrading a few servers. We are going from Percona’s patched MySQL 5.1 to MariaDB 5.5 (the subquery optimization is excellent, and we have lots of subqueries). Our process involves upgrading a slave first, and letting it replicate for a while, and if everything’s good, update more slaves, then the master.

    This has served us well in the past. And now that we are checking data integrity between masters and slaves with pt-table-checksum, the process involves checking before we start that there is data integrity. This is easy, as we checksum twice daily and have a Nagios alert if there are any discrepancies. After the upgrade, we checksum again, to be sure no data has been changed/corrupted in the process of doing a

      [Read more...]
    On MySQL Memory Usage and Configuration
    +1 Vote Up -0Vote Down

    I saw a post on profiling memory usage today and this reminds me of several discussions I have had with different people.

    Why would you want to profile the memory usage? Usually to see where memory is allocated and for what purposes, and usually you only care when memory usage is higher than expected. That is a DBA normally wants to use all available memory on a server for mysqld, whatever that size may be.

    Configuration parameters may be inappropriate and need adjusting, so having a way to determine the range of memory usage based on those parameters would be most helpful.  However, the configuration parameters as a whole put no limit on memory used, so different workloads can quite easily lead to memory being

      [Read more...]
    Open Database Camp at Northeast LinuxFest
    +4 Vote Up -1Vote Down

    I am happy and proud to announce that there will be an Open Database Camp held at this year’s Northeast LinuxFest! The venue is at Harvard University in Cambridge, MA (“our fair city”), and will take place Saturday, March 16 and Sunday, March 17, 2013.

    Northeast LinuxFest and Open Database Camp are both free, but there is no reciprocal membership. To register for Open Database Camp, just sign up with Eventbrite. We are also soliciting session ideas ahead of time, and attendees will choose sessions during the Saturday morning planning session, as usual for Open DB Camp.

    If you are interested in

      [Read more...]
    Not a cool new feature for Master_Host
    +1 Vote Up -1Vote Down

    I was surprised to find on a customer MySQL server this new syntax for Master_host in SHOW SLAVE STATUS.

    mysql> SHOW SLAVE STATUS\G
    *************************** 1. row ***************************
                   Slave_IO_State: Connecting to master
                      Master_Host: testdb1.xxx.com or 10.XXX.XX.XXX
                      Master_User: repl
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: db1-354215-bin-log.000005
              Read_Master_Log_Pos: 1624
                   Relay_Log_File: db2-354214-relay-log.000001
       

    Is this a fancy new Percona Server feature? No. It’s operator error.

    We read a little further to find.

    mysql> SHOW SLAVE STATUS\G
    ...
                 Slave_IO_Running: Connecting
    
    ...
                    Last_IO_Errno: 2005
                    Last_IO_Error:
      [Read more...]
    Installing MySQL MHA with Percona Server
    +0 Vote Up -0Vote Down

    MySQL MHA by Oracle ACE Director Yoshinori Matsunobu is an excellent open source tool to help in providing HA with native MySQL replication. The installation however is dependent on some Perl packages and to the untrained eye this may be an issue if you are using Percona Server as your choice of MySQL implementation.

    The MHA Node page requires the perl-DBD-MySQL package to be installed. The installation on RedHat/CentOS/Oracle Linux look like this:

    $ sudo yum install perl-DBD-MySQL
    Loaded plugins: fastestmirror
    Loading mirror speeds from cached hostfile
    * base: mirror.anl.gov
    * extras: mirror.anl.gov
    * updates: mirror.anl.gov
    Setting up Install Process
    Resolving
      [Read more...]
    Previous 30 Newer Entries Showing entries 91 to 120 of 1137 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.