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 105 Next 30 Older Entries

Displaying posts with tag: DBA (reset)

A CTO Must Never Do This…
+0 Vote Up -4Vote Down

Read the original article at A CTO Must Never Do This…

A couple years back I was contacted to look at a very strange problem.

The firm ran flash sales. An email goes out at noon, the website traffic explodes for a couple of hours, then settles back down to a trickle.

Of course you might imagine where this is going. During that peak, the MySQL database was brought to its knees. I was asked to do analysis during this peak load, and identify and fix problems. Make it go faster, please!

First day on the job I’m working with a team of outsourced DBAs. I was also working

  [Read more...]
Meet The MySQL Experts Podcast: MySQL Utilities
Employee_Team +5 Vote Up -0Vote Down

Managing a MySQL database server can become a full time job. In many occasions, one MySQL DBA needs to manage multiple, even tens of, MySQL servers, and tools that bundle a set of related tasks into a common utility can be a big time saver, allowing you spend more time improving performance and less time executing repeating tasks. While there are several such utility libraries to choose, it is often the case that you need to customize them to your needs. The MySQL Utilities library is the answer to that need. It is open source so you can modify and expand it as you see fit.

In the latest episode of the "Meet the MySQL Experts" podcast series, Chuck Bell, Sr. MySQL Software Developer at Oracle, introduces a variety of recently

  [Read more...]
7 essential tools for MySQL DBA
+2 Vote Up -0Vote Down

1. Percona Toolkit
Percona Toolkit (aka Maatkit and Aspersa) is must have collection of advanced command-line tools which helps in performing tasks that are too difficult or complex to perform manually.

2. Mydumper
Mydumper is a high-performance multi-threaded backup/restore tool for MySQL. It’s up to 10x faster compared to mysqldump, can take consistent snapshots and provides File compression on-the-fly. Though it’s still under active development but is well tested/used in production on some large installations.

3. MySQL Master HA
This tool helps to maintain your Master-Slave

  [Read more...]
Welcome, hastexo and PalominoDB, as SkySQL Partners
+2 Vote Up -0Vote Down

Last week was full of exciting news for all things MySQL.

However, SkySQL also had some great individual news in that it announced 2 new partners:

I just wanted to take a moment and officially welcome both to the SkySQL fold!

PalominoDB and hastexo, it’s great to be partnered with you!

And we’re looking forward to all the exciting things the future has to hold!

For those interested, you can read more about each partnership here:

  [Read more...]
Why You Need a Part-Time Remote MySQL Expert
+0 Vote Up -0Vote Down
When I thought to start to write this post, I was thinking about all the logical reasons why you would consider hiring a part-time remote MySQL database administrator or MySQL expert.
It then occurred to me that most of the readers, who are reading this post now, already know or can imagine the benefits of hiring a DBA to take care of their system.

So what I want to offer is a case based on the emotional benefits and value a part-time DBA can offer you and your business.

1) Peace of Mind

A DBA can offer you peace of mind that one of your core business components is being taken care of. It is like

  [Read more...]
Collaborate 2012 Registration is Now Open!
+3 Vote Up -0Vote Down
http://collaborate12.ioug.org   Double Down at COLLABORATE 12- The IOUG Forum with Two Ways to Save- and a Chance to Win! The user-driven Oracle event of the year is fast-approaching, and IOUG wants you to make youreducational experience a sure bet. Between … Continue reading →
For People That Have Managers
+1 Vote Up -0Vote Down
Interesting take on what managers are thinking: http://quickbase.intuit.com/blog/2012/01/09/10-things-your-boss-isnt-telling-you/
MongoDB for MySQL folks part 3 - More on queries and indexes
+1 Vote Up -0Vote Down
Last time I wrote about MongoDB for MySQL DBAs I described some of the basics of MongoDB querying, and this time I'll follow that up with some more on querying.

As we saw last time, the basic format of a MongoDB query is:
Note that you do NOT replace db with the name of the database you want to query here, you just make the database you want to use the current one and issue the query, such as:
> use test
> db.mycoll.find()
The example above will find all objects in the mycoll collection, and will include all the object attributes and also the key (_id), like this:
{ "_id" : ObjectId("4eb0634807b16556bf46b214"), "c1" : 1 }
{ "_id" : ObjectId("4eb0634a07b16556bf46b215"),

  [Read more...]
MongoDB for MySQL folks part 3 - More on queries and indexes
+0 Vote Up -0Vote Down
Last time I wrote about MongoDB for MySQL DBAs I described some of the basics of MongoDB querying, and this time I'll follow that up with some more on querying.

As we saw last time, the basic format of a MongoDB query is:
Note that you do NOT replace db with the name of the database you want to query here, you just make the database you want to use the current one and issue the query, such as:
> use test
> db.mycoll.find()
The example above will find all objects in the mycoll collection, and will include all the object attributes and also the key (_id), like this:
{ "_id" : ObjectId("4eb0634807b16556bf46b214"), "c1" : 1 }
{ "_id" : ObjectId("4eb0634a07b16556bf46b215"), "c2" : 1 }
{ "_id" :

  [Read more...]
TIL: Lookout For DEFINER
+0 Vote Up -0Vote Down

The Issue

I haven't blogged in a while an I have a long TODO list of things to publish: The repository for the SNMP Agent, video and slides of my OSCON talk and a quick overview of MHA master-master support. In the meantime, here's a little fact that I didn't know from MySQL CREATE VIEW documentation:

Although it is possible to create a view with a nonexistent DEFINER account, an error occurs when the view is referenced if the SQL SECURITY value is DEFINER but the definer account does not exist.
How can this be possible?

The Problem

For a number of reasons we don't have the same user accounts on the master than we have on the slaves (ie: developers shouldn't be querying the

  [Read more...]
MySQL HA Agent Mini HowTo
+1 Vote Up -0Vote Down

Why This Post

While testing Yoshinori Matsunobo's MHA agent I found that although the wiki has a very complete documentation, it was missing a some details. This article intends to close that gap and bring up some issues to keep in mind when you do your own installation. At the end of the article I added a Conclusions section, if you're not interested in the implementation details, but to read my take on the project, feel free to jump straight to the end from here.

My Test Case

Most of our production environments can be simplified to match the MHA's agent most simple use case: 1 master w/ 2 or more slaves and at least one more slave in an additional

  [Read more...]
MySQL Global status difference using MySQL procedures / functions
+2 Vote Up -0Vote Down
As a MySQL DBA, I guess you use the SHOW GLOBAL STATUS command or the corresponding INFORMATION_SCHEMA.GLOBAL STATUS table to show current mySQL status quite often. And many of us use different tools to collect that data and view it.

But sometimes we use same command from the SQL prompt, because we have to, because it's the only option or just because that is the most convenient way. And often you kick of two such command in a row, a few seconds apart, just to see how things are moving:
... DBA picks his or her nose for a few seconds ...

And then you can see how things are advancing. But how much? To figure of how much, you have to calculate the differnce between the values returned by those two statements. And then there is another

  [Read more...]
+1 Vote Up -0Vote Down
Back in February I wrote an article titled A Small Fix For mysql-agent. Since then we did a few more fixes to the agent and included a Bytes Behind Master (or BBM) chart. For those who can't wait to get their hands on the code, here's the current version: MySQL SNMP agent RPM. For those who'd like to learn about it's capabilities and issues, keep reading.

What to Expect From this Version

The article I quoted above pretty much describes the main differences with the original project, but we went further with the changes while still relying on Masterzen's code for the data collection piece.

The first big change is that we transformed

  [Read more...]
MySQL: Using Views as Performance Improvement Tools
+0 Vote Up -0Vote Down
The most basic and most oft-repeated task that a DBA has to accomplish is to look at slow logs and filter out queries that are suboptimal, that consume lots of unnecessary resources and that hence slow down the database server. This post looks at why and how VIEWs can help against such suboptimal operations.
On LVM: How to setup Volume Groups and Logical Volumes.
+0 Vote Up -0Vote Down
LVM (Logical Volume Management) is a very important tool to have in the toolkit of a MySQL DBA. It allows you to create and extend logical volumes on the fly. This allows me to, say, add another disk and extend a partition effortlessly. The other very important feature is the ability to take snapshots, that you can then use for backups. All in all its a must have tool. Hence, this guide will allow you to understand various terminologies associated with LVM, together with setting up LVM volumes and in a later part will also show you how to extend...
MySQL Database cleaner 1.0 released
+2 Vote Up -0Vote Down
If you ever, and I think many of use DBAs have, been in the situation where you are stuck with data in the database that isn't used and isn't accessed, data which may consist of rows that are no longer used, data rows that aren't references, because you don't use FOREIGN KEYs or they weren't applicable in this case. Or data that was once used, but no longer is.

And in many cases, this data is tucked in among your other good rows of data :-( One way of cleaning up the database in a case like this is to run standard DELETE statements, but there are a few issues with this:
  • You may be accessing a lot of data, so this may take a while.
  • You will be locking large amount of data for this.
  • The join statement to get the data that is no longer used and / or no longer referenced is complex.
  • There is no really good way to split

  [Read more...]
Oracle Selects Pearson VUE to Deliver MySQL Certification Exams
Employee_Team +0 Vote Up -0Vote Down
Effective June 1, 2011, MySQL certification exams will be offered exclusively through a new test delivery partner — Pearson VUE — and will no longer be available through Prometric. This will consolidate all Oracle Certification exams within the operations of a single testing vendor so we can provide better service and global testing coverage for these Oracle certification exams. Pearson VUE currently has over 5,000 test centers worldwide in 165 countries.

To help prepare you for this transition, here are some important dates for you to be aware of:
  • If you are currently following a certification path and are planning to take an exam on/after June 1: Registration has began at Pearson VUE on May 16, 2011 for all scheduled exams on or after June 1. Visit pearsonvue.com/oracle to create a new web account

  [Read more...]
Some More Replication Stuff
+0 Vote Up -0Vote Down
Listening to the OurSQL podcast: Repli-cans and Repli-can’ts got me thinking, what are the issues with MySQL replication that Sarah and Sheeri didn’t have the time to include in their episode. Here’s my list:

Replication Capacity Index

This is a concept introduced by Percona in last year’s post: Estimating Replication Capacity which I revisited briefly during my presentation at this year’s MySQL Users Conference. Why is this important? Very simple: If
  [Read more...]
Handling Human Errors
+0 Vote Up -0Vote Down

Interesting question on human mistakes was posted on the DBA Managers Forum discussions today.

As human beings, we are sometimes make mistakes. How do you make sure that your employees won’t make mistakes and cause downtime/data loss/etc on your critical production systems?

I don’t think we can avoid this technically, probably working procedures is the solution.
I’d like to hear your thoughts.

I typed my thoughts and as I was finishing, I thought that it makes sense to post it on the blog too so here we go…

The keys to prevent mistakes are low stress levels, clear communications and established processes. Not a complete list but I think these are the top things to reduce the number of mistakes we make managing data infrastructure or for

  [Read more...]
Better Indexes $ave You Money
+0 Vote Up -0Vote Down

Download PDF

Can database performance improvements be achieved with zero code changes? Learn how to use one simple advanced technique to make better MySQL indexes and improve your queries by 500% or more. Even with a highly indexed schema as shown in our 10 table join example, significant improvements in performance can be achieved.

This presentation introduces the approach for correct identification and verification of problem SQL statements and then describes the means of identifying index choices for optimization. Then discussed is not only how to apply indexes to improve query performance, but how to apply

  [Read more...]
A cool terminal tip for Mac users
+3 Vote Up -0Vote Down
If you use a Mac, and you are dealing with many similar tasks at once, like examining many database servers in different terminals, you may like this one.
I have been using iTerm 2 for a while, and my handling of parallel tasks has improved a lot. (No, I am not talking about Parallel replication, although I have applied this trick while testing that technology as well.)
iTerm2 has some cool features, and probably the most striking one is split panes. That alone would be a good reason for giving iTerm2 a try. But the one that I use the most, often in combination with Split Panes, is called Send Input to all tabs.
Here is how it works.

  [Read more...]
implementing table quotas in MySQL
+6 Vote Up -0Vote Down
I have just seen Limiting table disk quota in MySQL by Shlomi Noach, and I could not resist.
You can actually implement a disk quota using an updatable view with the CHECK OPTION.
Instead of giving the user access to the table, you give access to the view (at least for inserting, see the caveat at the end), and you will get a genuine MySQL error when the limit is reached.

drop table if exists logs;
create table logs (t mediumtext) engine=innodb;

drop function if exists exceeded_logs_quota ;
create function exceeded_logs_quota()
returns boolean
return (
select CASE

  [Read more...]
A hidden options file trick
+7 Vote Up -0Vote Down
I was listening today to the OurSQL Episode 36: It's Not Our (De)fault! Part 1. As usual, Sheeri and Sarah are very informational and entertaining while explaining the innards of MySQL and their best practices.
Being a DBA oriented show, there was an omission in this podcast. There was no mention of custom groups that you can have for your my.cnf. This is mostly useful for developers. If your application requires some specific settings, instead of using a separated configuration file, you can use a different group, and then instruct your client applications to use that group.
By default, all client applications read the "[client]" group.
But you can tell your client to read a group that you can call whatever you like.
For example, with this

  [Read more...]
The Casual MySQL DBA – Operational Basics
+2 Vote Up -0Vote Down

So your not a MySQL DBA, but you have to perform like one. If you have a production environment that’s running now, what are the first things you do when it’s not running or reported as not running?

  • Are the MySQL processes running? (i.e. mysqld and mysqld_safe)
  • Can you connect locally via cli?
  • What’s in the MySQL error log?
  • What are current MySQL threads doing? Locked? long running? how many? idle sources?
  • Can you connect remotely via cli?
  • Verify free diskspace?
  • Verify system physical resources?
  • If this is a slave, is MySQL replication running? Is it up to date?
  • What is the current MySQL load, e.g. reads/writes/throughput/network/disk etc?
  • What is the current InnoDB state and load? (based on if your using InnoDB)
  • After you do this manually more then

      [Read more...]
    A Replication Surprise
    +2 Vote Up -0Vote Down
    While working on a deployment we came across a nasty surprise. In hindsight it was avoidable, but it never crossed our minds it could happen. I'll share the experience so when you face a similar situation, you'll know what to expect.


    To deploy the changes, we used a pair of servers configured to replicate with each other (master-master replication). There are many articles that describe how to perform an ALTER TABLE with minimum or no downtime using MySQL replication. The simple explanation is:
  • Set up a passive master of the database you want to modify the schema. 
  • Run the schema updates on the passive master.
  • Let replication to catch up once the schema modifications are done.
  • Promote the passive master as the new active master.
  • The details to make this work will depend on each individual situation and are too extensive for

      [Read more...]
    I love my new job!
    +4 Vote Up -0Vote Down
    I just have to chime in about how happy I am with my new job. I now work for Blue Gecko, as of August 30th. My role is a Senior Database and Systems Administrator. Blue Gecko is based out of Seattle though I'll be working out of my home in New Hampshire, albeit with my frequent travels to Seattle for family reasons, this will work out quite well.

    Already in the last week, I've engaged in several tasks, all of which have been very interesting problems to solve. Not only that, but I've spoken with several existing and potential customers and never realized I really enjoy consulting with and acquiring customers-- hearing what problems they need to solve and being able to ascertain quickly how to solve those problems, making the customer look forward to engaging with us.

    Who is Blue Gecko?

    Based out of Seattle, their website states (

      [Read more...]
    DBJ – Wonders of Maatkit for MySQL
    +2 Vote Up -3Vote Down

    If you’re new to the MySQL DBA role, you’ll be excited to learn about the Maatkit toolset.  It provides a whole host of valuable functionality and fills many of the DBAs day-to-day needs.

    DatabaseJournal – Wonders of Maatkit

    LOAD DATA: a tricky replication issue
    +7 Vote Up -0Vote Down
    When you are importing large amounts of data from other sources LOAD DATA is a common method of inserting data into a table.
    It is one of the old commands implemented in MySQL. As such it is very fast, and it has been optimized for both MyISAM and InnoDB.
    All is well when you are loading data into a standalone server. All is almost well when you are using replication. LOAD DATA used to be a problem in old versions of MYSQL, prior to 4.1. With recent versions of MySQL, it is replicated correctly, and sometimes efficiently.
    The trouble starts when the data file is big. The definition of big and the amount of trouble that you can get depends on many factors. That's why users may not realize that this problem exists, even with fairly large files, and then being hit by this

      [Read more...]
    Sometimes, even a command line guy likes a GUI
    +5 Vote Up -0Vote Down
    As everyone knows, I am a command line guy. I am very much comfortable with the shell prompt and the command line SQL client. I do most of my work that way, and I am very much productive.
    However, there comes a time when even for a command line enthusiast a GUI can be helpful.
    Here comes the latest MySQL Workbench 5.2.
    There are two areas where I feel that WB can give me a hand:
    The first is when looking at tables that contain BLOB columns. Sure I can deal with them at the command line, but this editor makes my life easier.

    When a column contains a BLOB, you can open the

      [Read more...]
    Exchanging partitions with tables
    +8 Vote Up -2Vote Down
    While I was presenting my partitioning tutorial at the latest MySQL Conference, I announced a new feature that was, as far as I knew, still in the planning stage. Mattias Jonsson, one of the partitions developers, was in attendance, and corrected me, explaining that the feature was actually available in a prototype.
    So, we can have a look at this improvement, which I am sure will make DBAs quite happy. The new feature is an instantaneous exchange between a partition and a table with the same structure. Using this feature, you can transfer the contents of one partition to one table, and vice versa. Since the transition is done only in the
      [Read more...]
    Previous 30 Newer Entries Showing entries 31 to 60 of 105 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.