Archive for the ‘mem’ Category

Disabling MySQL Enterprise Monitor Graph

Апрель 23rd, 2012


We have setup our database servers to log all slow queries > 2 seconds. This can be done by enabling below from your configuration file or via runtime using SET GLOBAL command.

Append to configuration (my.cnf) under [mysqld]

slow_query_log = on
long_query_time = 2

Using query

SET GLOBAL slow_query_log = on;
SET GLOBAL long_query_time = 2

With this current setup, we filtered all queries from our application running more than 2 seconds. This is a good way to identify slow queries and optimise them.

However, there are also queries from the MySQL Enterprise monitor that were also being logged. In this case, i found out a (2) graph metrics checking every minute for the total backup time (total_time) and total lock time (lock_time). It takes about 3-6 seconds with only about 70 records on table.

To give you exactly the query, here it is:

# Time: 120420 16:27:01
# User@Host: agent_user[agent_user] @ [127.0.0.1]
# Query_time: 3.141840 Lock_time: 0.000134 Rows_sent: 1 Rows_examined: 71
SET timestamp=1334910421;
SELECT IF(end_time >= (NOW() - INTERVAL 60 SECOND), TIMESTAMPDIFF(SECOND, start_time, end_time), 0) AS total_time, IF((end_time >= (NOW() - INTERVAL 60 SECOND)), lock_time, 0) $

This query checks every minute on the table mysql.backup_history which is a CSV table with no indexes.

In short, I like to disable these 2 graphs so I end up googling and posting to mysql forums but found no answer so I scrutinize every directory in my agent folder until finally found where the graph items are being hidden.

The graph items in MySQL Enterprise Monitor was stored in an XML file in the agent directory where I installed the MEM agent.

/opt/mysql/enterprise/agent/share/mysql-monitor-agent/items/items-mysql-monitor.xml

I edited the XML file and commented out the whole class name that triggers that Backup Run Time and Backup Locked Time graphs.

The disadvantage of disabling these metrics is that you will no longer monitor it from the Enterprise Monitor dashboard. You have to manually query it from the mysql.backup_history table to see the results of your backup.

PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in mem, MySQL Enterprise Monitor

Комментарии отключены

 

Monitoring Your MySQL Backup

Сентябрь 20th, 2011

In California we are always thinking about backups.   Living near an earthquake fault line makes this necessary.  For me, it is the Hayward Fault (it runs from goal post to goal post  in University of Californa Berkeley stadium).  We are strongly advised to have backup systems for water, food, and medical emergencies.  It’s necessary to monitor your food and water emergency supplies so if the worst happens, you don’t have spoiled food or water as your backup (who knew water expires?) .   Plan for the worst, hope for the best, but keep an eye on those supplies and replenish them when necessary!  And most of all, make sure your good intentions end up as actual physical supplies in the garage!

Backups are also an incredibly critical part of the enterprise  environment.  It’s all about being able to successfully restore your database when needed.  There are many ways to backup your database, but are you monitoring your MySQL backups to make sure they are going to be there when you need them?  In this post, we’ll cover the new features in MySQL Enterprise Monitor (MEM) 2.3.5  that help monitor backups.

In the last post  I covered some of the new features in MySQL Enterprise Backup (MEB) that allow you to write single file backups, stream these backups  to  remote servers or other devices, stream to media management software like Oracle Secure Backup, and take advantage of tape encryption capabilities (Steps 1-4 from the previous post ) . This is the final post  in this series which describes the new Backup Advisor in MEM and the underlying mysql tables (Step 5). 

MySQL Enterprise Monitor (MEM) is the monitoring software supplied with the Enterprise Edition subscription of MySQL  and if you are interested in trying it, you can download it  for a trial at https:edelivery.oracle.com.  I find MEM very helpful because it provides  proactive monitoring for your MySQL databases.  This allows you to increase your productivity because you automate monitoring and help speed up diagnosis of potential issues. 

MEB is a backup tool included with an enterprise subscription from Oracle/MySQL, also available for trial from http://edelivery.oracle.com.  MEB was previously known as InnoDB Hot Backup, and provides hot, non-blocking backups for InnoDB tables, and “warm” backups for MyISAM tables. 

Monitor Your Backup
MEM 2.3.5  (and above) has a new Backup Advisor that can be used to monitor backups (note, MEM 2.3.6 was released in early September).  The Backup Advisor alerts you to backup success or failure, excessive backup lock time, backups that are too old, and failure to use incremental backups.  Here are some screen shots.  The first is  from the Details and Advanced Tabs for the “Backup Succeeded” rule  in MEM:

This rule will let you know how long the backup took to complete and how long locks were held. 

Full backups that are older than a threshold number of days (default is 7) are reported since out of date backups will only cause delays or problems if you ever need to restore from them:

You will also see if any excessive lock time in your backup.  :


Backups are always a balancing act between performance, storage space, and restoral time.  Incremental backups only backup the data that has changed since the last backup, save on storage space, and are faster than a full backup.  I encourage the use of incremental backups in your backup strategy.  MEM will notify you if  incremental backups are not enabled:

 

You can customize any of the thresholds to suit your environment.

Tables Behind the Curtain

MEM uses the backup progress information written into the mysql.backup_progress table, and status information from the mysql.backup_history table.  You can query these tables to get backup status information if you are not using MEM (but you will not receive the alerts and notifications that MEM provides).

Here I’ve queried from the backup_history table, which keeps a hstory of the backups I’ve completed:

The backup_progress table shows the state of the backup as it progresses from start to finish:

MySQL Enterprise Backup and Enterprise Monitor new features bring us one step closer to a true enterprise backup environment. Streaming, integration with media management systems, and the ability to take advantage of tape encryption features coupled with the new Backup Advisor in MEM will help achieve that state we all need to plan for but hope to never see – the ability to quickly restore a database when needed.



PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in MEB, mem, mysql, MySQL Enterprise Backup, MySQL Enterprise Monitor

Комментарии отключены

 

Starting a new job!

Май 6th, 2010

I’ve had a wild ride over the past ~4.5 years, starting with MySQL AB as a “Support Engineer”, and working through to “Senior Support Engineer”, and then “Regional Support Manager, Americas” with the MySQL Support Team - truly one of the best product support teams I’ve ever known in the IT industry, even if I am biased.

I’ve always had a passion for helping people, which is why I think I did “OK” in Support. However I’ve always also had a second passion which has been bubbling away for me too - building solutions for diagnosing database issues. I started in the database world in the Oracle market, working on monitoring and management tools. MySQL “poached” me from there whilst I was building a MySQL monitoring module for the cross database monitoring tool that we had, as well as working in a supporting/consulting role for our customers.

Given my background when I joined, I was an obvious person to be put in to an internal coordinator role to manage interaction between the team that develops MySQL Enterprise Monitor and Support back then (before MEM reached it’s first GA), so I also had the pleasure of working with that team pretty closely for my time in Support.

It satisfied my second passion for a long time. Now it’s time to take the leap back to that full time!

At the start of this week I started in my new role, as a “Software Development Manager” for the Enterprise Tools team, still within MySQL/Oracle, working full time on MySQL Enterprise Monitor and MySQL Proxy.

I’m pretty excited about the new role - I want to help make monitoring and management of MySQL easy, both by getting more of the “right” data from the server, and by representing it all in coherent ways for DBAs in our tools, so that they too can be more productive in making the right choices when managing their systems. I feel pretty well placed to do that now. :)

If you’re a customer that has used MEM and wants to give any feedback, or even just an interested user in the community - I’m all ears, either leave your comments here, or ping me by email (first_name.last_name@oracle.com) - I’d love to have some discussions with you about your needs, likes, or dislikes.

I thank every person in Support for my time with them, however I owe all of that time to one fairly anonymous guy in the “external” MySQL world, but a man mountain “internally” - Dean Ellis, the man who convinced me to join MySQL AB all those years ago, and has been a fantastic mentor and manager over the years to me. Mr Ellis - I salute you. Thanks for bringing me on the ride, I still don’t regret it, no matter how much you tried to make me! ;)

Enterprise Tools team - Look Out, you have me to whine at you full time now!


PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in mem, mysql, Support Team

Комментарии отключены

 

Drizzle and the Gearman logging plug-in

Август 28th, 2009
Disclaimer:
This blog post is about things I did on my own free time, not endorsed by my employer.

I have been meaning to look at Gearman for a long time, but I just couldn't find any project where I could use it.

Well, that was true until last week a couple of weeks ago, when I started to put together Drizzle, the Gearman logging plug-in, Perl and the Enterprise Monitor.

As I was finishing writing the agent.pl script, I thought that it would be a good idea to split the script in at least two components: one that would just collect the queries, and another component that would do the processing of the log entries (replacing the literals for "?", grouping queries by query text, etc).

It was right there when I realized that this was my chance to look at Gearman! The first thought was to still use the regular query logging plug-in.
But there is already a Gearman logging plugin, and I was curious about how that worked.

A quick Google search returned very little information, but I did find the doxygen docs, and reading the code was fairly straight forward.

By reading the code, I found out that the plug-in registers the function drizzlelog with the Gearman Job server, and that it passes the same string that the query logging plug-in sends to the log file.

Next step was to find a hello world Perl + Gearman example. And I found a sample for the client and the worker. That almost worked out of the box, but I got this error:

Can't call method "syswrite" on an undefined value at /Library/Perl/5.8.8/Gearman/Taskset.pm line 201.

A little google search and I found an example where the port was appended to the host. I then added the port 4730 to worker.pl and client.pl and it all worked as expected.

Once I got the simple example working, I added most of the agent.pl code to the worker.pl script, made a few small changes, and added comments. I was done!

The Gearman logging plugin sends query logs to the job server, and the job server asks the worker to do the actual job.
In the end, the service manager ends up with all the information related to the queries that go to the Drizzle server.

Layout.
For this initial version, one worker cannot handle jobs for more than one drizzle server, this is not a Gearman limitation. When I wrote this script, there was no way to tell the worker, which Drizzle server was sending the log entry.

And that was an excellent excuse to add a few more fields to the Gearman logging plugin. (That patch was already approved and will soon be part of the main Drizzle source.)



worker-1 handles requests for drizzled-1 and worker-2 handles jobs for drizzled-2. I am already looking into ways to change this.

Where is the code?
As usual, I posted the worker.pl script on the MySQL Forge.

How do I start the worker?
Like this:

$ DEBUG=1 perl worker.pl  --serveruuid="22222222-5555-5555-5555-222222222211"\
--serverhostuuid="ssh:{11:11:11:11:11:11:11:11:11:11:11:11:11:11:11:21}" \
--serverdisplayname="Main-Drizzle-web2"



How do I start the client?
In this case, the Gearman client is the drizzle plug-in, so, all you need to do is add these lines to your drizzle.cnf

$ cat /etc/drizzle/drizzled.cnf
[drizzled]
logging_gearman_host = 127.0.0.1
logging_gearman_enable = true


Restart the Drizzle server and you are ready to go (well, you also need the MySQL Enterprise Monitor)

Final Note.
I was amazed at how easy it was to have it all working, I will keep looking for other projects where I could use Gearman.


PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in Drizzle, Gearman, mem, perl, plugin

Комментарии отключены

 

Drizzle query monitoring

Август 28th, 2009
Disclaimer:
This blog post is about things I did on my own free time, not endorsed by my employer.

A little over a month ago, Ronald posted a blog about the different query logging plug-ins that are available for Drizzle. This was pretty exciting news, especially when I saw the details that were included in the logs.

Meanwhile, a few weeks ago, I started looking at the REST API that comes with the MySQL Enterprise Monitor.

The result is that we can now see most of the information returned by the plug-in, on the Dashboard.




How?
A colleague at work, wrote a little Perl script that interacts with the REST API, and I took his work as the foundation for my agent.pl script.

The next problem was to find a way to call this script as soon as there was a new entry on the log. After a little Google search, I went ahead and decided to ask my friend Adriano Di Paulo (who among other things, introduced me to MySQL).
A few minutes later, he showed me a working example of the Tail Perl module.
That was exactly what I needed, as soon as there is a new entry, I call the function assemble_queries() and I pass the new log entry as the parameter.


sub tail_log {
 my $file=File::Tail->new(name=>$log_file, maxinterval=>1, reset_tail=>0);
 while( defined (my $line=$file->read ) ) {
   print "\n" . $line . "\n" if $DEBUG > 3;
   assemble_queries( $line );
 }
}



The assemble_queries() function is mostly based on what MC blogged about some time ago. On his blog post, he shows how to collect query related data using Dtrace and Perl.

Then, every n number of queries, I use send_json_data() to send the query information to the Dashboard, delete the sent data and it is ready to process more queries.

Now that I'm writing this, I realized that if send_json_data() fails, the information related to the queries are lost :|. (Note to self, fix it).

There are other functions in there, but they are mostly for housekeeping.

How do I use it?
Very simple, get the agent.pl script from the MySQL Forge website, edit the credentials, hosts, and ports to fit your needs (Future versions would include some kind of config file).

And then you call the script like this:

$ DEBUG=1 perl agent.pl  --serveruuid="22222222-5555-5555-5555-222222222211" \
--serverhostuuid="ssh:{11:11:11:11:11:11:11:11:11:11:11:11:11:11:11:21}"\
--serverdisplayname="Main-Drizzle-web2" \
--log-file=/path/to/log/file

As soon as the scripts starts, it will add the drizzle server to the service manager, and once you start sending queries to drizzle, those queries will end up on the UI.

Next?
Next is already done :). I modified the agent.pl script to use the gearman logging plugin. I'll write a blog about it very soon.

Thanks for reading and enjoy!



PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in Drizzle, Gearman, json, mem, mysql, perl, plugin

Комментарии отключены