MySQL : What management tools do you use?

A quick question out to the world. What management tools do you use for MySQL?

We currently have:

  • MySQL Workbench : It’s OK, but I don’t really like it. It feels like half a product compared to tools I’ve used for other database engines…
  • phpMyAdmin : I’ve used this on and off for over a decade for my own website. While I’m typing this sentence, they’ve probably released 4 new versions. :) We have an installation of this which we use to access our MySQL databases should the need arise.
  • mysql Command Line : I use the command line and a variety of scripts for the vast majority of the things I do.

When I’m working with Oracle, my first port of call for any situation is to use SQL*Plus along with a variety of scripts I’ve created over the years. The performance stuff in Cloud Control (if you’ve paid for the Diagnostics and Tuning option) is the big exception to that of course.

I still consider myself a newbie MySQL administrator, but I’ve found myself spending more and more time at the command line, to the point where I rarely launch MySQL Workbench or phpMyAdmin these days. I’m wondering if that is common to other MySQL administrators, or if it is a carry over from my Oracle background…

Enquiring minds need to know!

Cheers

Tim…


MySQL : What management tools do you use? was first posted on October 31, 2014 at 9:35 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Index Advanced Compression vs. Bitmap Indexes (Candidate)

A good question from Robert Thorneycroft I thought warranted its own post. He asked: “I have a question regarding bitmapped indexes verses index compression. In your previous blog titled ‘So What Is A Good Cardinality Estimate For A Bitmap Index Column ? (Song 2)’ you came to the conclusion that ‘500,000 distinct values in a 1 […]

Singapore: sending out an SOS

The Oracle community rocks! I mentioned previously that the beginning of the OTN APAC tour will take me to Singapore to meet Doug Burns again. Well, Doug took the opportunity to organize a little meetup of the local Singapore Oracle User community for Free Pizza, Free Beer and Free Oracle Presentations next monday, November 3rd. If you are in Singapore or can get there with reasonable effort, I would love to meet you there. See the flyer and rsvp to the email address if you want to come as seats are limited.
The speaker lineup sounds like a european takeover. You will have to listen to me talk about SQL Plan management first only to be rewarded with presentations by Doug Burns (Scotland) on Real Time SQL Monitoring and Morten Egan (Denmark) on Big Data.

HPC versus HDFS: Scientific versus Social

There have been rumblings from the HPC community indicating a general suspicion of and disdain for Big Data technology which would lead one to believe that whatever Google, Facebook and Twitter do with their supercomputers is not important enough to warrant seriousness—that social supercomputing is simply not worthy.  A little of this emotion seems to […]

Quiz night

Here’s a little puzzle that came up on OTN recently.  (No prizes for following the URL to find the answer) (Actually, no prizes anyway). There’s more in the original code sample than was really needed, so although I’ve done a basic cut and paste from the original I’ve also eliminated a few lines of the text:


execute dbms_random.seed(0)

create table t
as
select rownum as id,
       100+round(ln(rownum/3.25+2)) aS val2,
       dbms_random.string('p',250) aS pad
from dual
connect by level <= 1000
order by dbms_random.value;

begin
  dbms_stats.gather_table_stats(ownname          => user,
                                tabname          => 'T',
                                method_opt       => 'for all columns size 254'
  );
end;
/

column endpoint_value format 9999
column endpoint_number format 999999
column frequency format 999999

select endpoint_value, endpoint_number,
       endpoint_number - lag(endpoint_number,1,0)
                  OVER (ORDER BY endpoint_number) AS frequency
from user_tab_histograms
where table_name = 'T'
and column_name = 'VAL2'
order by endpoint_number
;

alter session set optimizer_mode = first_rows_100;

explain plan set statement_id '101' for select * from t where val2 = 101;
explain plan set statement_id '102' for select * from t where val2 = 102;
explain plan set statement_id '103' for select * from t where val2 = 103;
explain plan set statement_id '104' for select * from t where val2 = 104;
explain plan set statement_id '105' for select * from t where val2 = 105;
explain plan set statement_id '106' for select * from t where val2 = 106;

select statement_id, cardinality from plan_table where id = 0;

The purpose of the method_opt in the gather_table_stats() call is to ensure we get a frequency histogram on val2; and the query against the user_tab_columns view should give the following result:


ENDPOINT_VALUE ENDPOINT_NUMBER FREQUENCY
-------------- --------------- ---------
           101               8         8
           102              33        25
           103             101        68
           104             286       185
           105             788       502
           106            1000       212

Given the perfect frequency histogram, the question then arises why the optimizer seems to calculate incorrect cardinalities for some of the queries; the output from the last query is as follows:


STATEMENT_ID                   CARDINALITY
------------------------------ -----------
101                                      8
102                                     25
103                                     68
104                                    100           -- expected prediction 185
105                                    100           -- expected prediction 502
106                                    100           -- expected prediction 212

I’ve disabled comments so that you can read the answer at OTN if you want to – but see if you can figure out the reason before reading it. (This reproduces on 11g and 12c – and probably on earlier versions all the way back to 9i).

I haven’t done anything extremely cunning with hidden parameters, materialized views, query rewrite, hidden function calls, virtual columns or any other very dirty tricks, by the way.


Announcement: Singapore Oracle Sessions

When I knew that the ACE Director, Bjoern Rost of Portrix Systems was coming to Singapore on his way to begin the OTN APAC tour, I suggested he stay at mine for a few days and sample all that Singapore has to offer.

Then a thought occurred to me. While he was here, why not setup an informal Oracle users meetup, much like the various ones at cities around the world like Sydney, Birmingham and London (to name but three I'm aware of). Morten Egan, my new colleague and Oak Table luminary had already suggested to me months ago that we should get something going in Singapore, so why not start now?

Well, in a matter of a few days, we've put together an agenda, a room, we will be having pizza and beer and other drinks and three hopefully useful sessions from experienced speakers. 

Here is the agenda (SingaporeOracleSessions.pdf) and a map (SOSMap.pdf) to help you get to the venue which is very handily placed near Bugis MRT. All that's required to register is to email me at dougburns at Yahoo. There are currently 21 people registered but the room holds (believe it or not) 42, so spread the word!

Hopefully, it's just the beginning ....

12.1.0.2 Introduction to Zone Maps Part II (Changes)

In Part I, I discussed how Zone Maps are new index like structures, similar to Exadata Storage Indexes, that enables the “pruning” of disk blocks during accesses of the table by storing the min and max values of selected columns for each “zone” of a table. A Zone being a range of contiguous (8M) blocks. I […]

Percona Live London 2014

Percona Live London takes place next week from November 3-4 where Pythian is a platinum sponsor—visit us at our booth during the day on Tuesday, or at the reception in the eveningl. Not only are we attending, but we’re taking part in exciting speaking engagements, so be sure to check out our sessions and hands-on labs. Find those details down below.

 

MySQL Break/Fix Lab by Miklos Szel, Alkin Tezuysal, and Nikolaos Vyzas

Monday November 3 — 9:00AM-12:00PM
Cromwell 3 & 4

Miklos, Alkin, and Nikolaos will be presenting a hands-on lab by demonstrating an evaluation of operations errors and issues in MySQL 5.6, and recovering from them. They will be covering instance crashes and hangs, troublesehooting and recovery, and significant performance issues. Find out more about the speakers below.

About Miklos: Miklos Szel is a Senior Engineer at Pythian, based in Budapest. With greater than 10 years’ experience in system and network administration, he has also worked for Walt Disney International as its main MySQL DBA. Miklos specializes in MySQL-based high availability solutions, performance tuning, and monitoring, and has significant experience working with large-scale websites.

About Alkin: Alkin Tezuysal has extensive experience in enterprise relational databases, working in various sectors for large corporations. With greater than 19 years’ of industry experience, he has been able to work on large projects from the group up to production. In recent years, he has been focusing on eCommerce, SaaS, and MySQL technologies.

About Nikolaos: Nik Vyzas is a Lead Database Consultant at Pythian, and an avid open source engineer. He began his career as a software developer in South Africa, and moved into technology consulting firms for various European and US-based companies. He specializes in MySQL, Galera, Redis, MemcacheD, ad MongoDB on many OS platforms.

 

Setting up Multi-Source Replication in MariaDB 10 by Derek Downey

Monday November 3 — 2:00-5:00PM
Cromwell 3 & 4

For a long time, replication in MySQL was limited to only a single master. When MariaDB 10.0 became generally available, the ability to allow multiple masters became a reality. This has opened up the door to previously impossible architectures. In this hands-on tutorial, Derek will discuss some of the features in MariaDB 10.0, demonstrate establishing a four-node environment running on participants’ computer using Vagrant annd VirtualBox, and even discuss some limitations associated with  10.0. Check out Derek’s blog post for more detailed info about his session.

About Derek:Derek began his career as a PHP application developer, working out of Knoxville, Tennessee. Now a Principal Consultant in Pythian’s MySQL practice, Derek is sought after for his deep knowledge of Galera and diagnosing replication issues.

 

Understanding Performance Through Measurement, Benchmarking, and Profiling by René Cannaò

Monday November 3 — 2:00-5:00PM
Orchard 2

It is essential to understand how your system performs at different workloads to measure the impacts of changes and growth and to understand how those impacts will manifest. Measuring the performance of current workloads is not trivial and the creation of a staging environment where different workloads need to be tested has it’s own set of challenges. Performing capacity planning, exploring concerns about scalability and response time and evaluating new hardware or software configurations are all operations requiring measurement and analysis in an environment appropriate to your production set up. To find bottlenecks, performance needs to be measured both at the OS layer and at the MySQL layer: an analysis of OS and MySQL benchmarking and monitoring/measuring tools will be presented. Various benchmark strategies will be demonstrated for real-life scenarios, as well as tips on how to avoid common mistakes.

About René: René has 10 years of working experience as System, Network and Database Administrator mainly on Linux/Unix platform. In recent years, he has been focused mainly on MySQL, previously working as Senior MySQL Support Engineer at Sun/Oracle and now as Senior Operational DBA at Pythian (formerly Blackbird, acquired by Pythian.)

 


Pythian is a global leader in data consulting and managed services. We specialize in optimizing and managing mission-critical data systems, combining the world’s leading data experts with advanced, secure service delivery. Learn more about Pythian’s MySQL expertise.

Metric Thresholds and the Power to Adapt

Metric thresholds have come a long way since I started working with OEM 10g.  I remember how frustrating it could be if an ETL load impacted the metric values that had to be set for a given IO or CPU load for a database when during business hours, a much lower value would be preferable.  Having to explain to the business why a notification wasn’t sent during the day due to the threshold set for resource usage for night time batch processing often went unaccepted.

With EM12c, release 4, we now have Time-based Static thresholds and Adaptive thresholds.  Both are incredibly valuable to ensuring the administrator is aware of issues before they become a problem and not let environments with askew workloads leave them unaware.

Both of these new features are available once you are logged into a target, then from the left side menu, <Target Type Drop Down Below Target Name>, Monitoring, Metric and Collection Settings.  Under the Metrics tab you will find a drop down that can be changed from the default of Metrics with Thresholds to Time-based Static and Adaptive Thresholds which will allow you to view any current setup for either of these advanced threshold management.

adv_thresh_page2

To access the configuration, look below on the page for the Advanced Threshold Management link-

adv_thresh_page3

Time-Based Static Thresholds

The concept behind Time-based Static thresholds is that you have very specific workloads in a 24hr period and you wish to set thresholds based on the resource cycle.  This will require the administrator to be very familiar with the workload to set this correctly.  I understand this model very well, as most places I’ve been the DBA for, I was known for memorizing EXACTLY the standard flow of resource usage for any given database.

In the Time-based Static Threshold tab from the Metrics tab, we can configure, per target, (host, database, cluster) the thresholds by value and time that makes sense for the target by clicking on Register Metrics.

This will take you to a Metric Selector page that will help you set up the time-based static thresholds for the target and remember, this is target specific.  You can choose to set up as many metrics for a specific target or just one or two.  The search option allows for easy access to the metrics.

adaptive12

Choose which metrics you wish to set the time-based static thresholds for and click OK.

You can then set the values for each metric that was chosen for weekday or weekend, etc.

adaptive13

You will be warned that your metric thresholds will not be set until you hit the Save button.  Note: You won’t be able to click on it until you close this warning, as the Save button is BEHIND the pop-up warning.

If the default threshold changes for weekday day/night and weekend day/night are not adequate to satisfy the demands of the system workload, you can edit and change these to be more definitive-

adv_thrhld5

Once you’ve chosen the frequency change, you can then set up the threshold values for the more comprehensive plan and save the changes.  That’s all there is to it, but I do recommend tweaking as necessary if any “white noise” pages result from the static settings.

Removing Time-based Static Thresholds

To remove a time-based threshold for any metric(s), click on the select for each metric with thresholds that you wish to remove and click the Remove button.  You will be asked to confirm and the metric(s) time-based static threshold settings will be reverted to the default values or to values set in a default monitoring template for the target type.

Adaptive Thresholds

Unlike the Time-based Static Thresholds, which are based off of settings configured manually, Adaptive Thresholds source their threshold settings off of a “collected” baseline.  This is more advanced than static set thresholds as it takes the history of the workload collected in a baseline into consideration when calculating the thresholds.  The most important thing to remember is to ensure to use a baseline that includes a clear example of a standard workload of the system in the snapshot.

There are two types of baselines, static and moving.  A static baseline is for a given snapshot of time and does not change.  A moving baseline is recollected on a regular interval and can be for anywhere from 7-31 days.

The reason to use a moving baseline over a static one is that a moving baseline will incorporate changes to the workload over time, resulting in a system that has metric growth to go with system growth.  The drawback?  If there is a problem that happens on a regular interval, you may not catch it, where the static baseline could be verified and not be impacted by this type of change.

After a baseline of performance metric data has been collected from a target, you can then access the Adaptive Thresholds configuration tab via the Advanced Threshold Management page.

You have the option from the Advanced Threshold Management page to set up the default settings for the baseline type, threshold change frequency and how long the accumulation of baseline data should be used to base the adaptive threshold value on.

adaptive11

Once you choose the adaptive settings you would like to make active, click on the Save button to keep the configuration.

Now let’s add the metrics we want to configure adaptive thresholds for by clicking on Register Metrics-

adaptive14

You will be taken to a similar window that you saw for the Time-based Static Thresholds.  Drill down in the list and choose the metrics that could benefit from an adaptive threshold setting and once you are done choosing all the metrics that you want from the list, click on OK.

Note:  Once you hit OK, there is no other settings that have to be configured.  Cloud Control will then complete the configuration, so ensure you have the correct you wish to have registered for the target.

adaptive15

Advanced Reporting on Adaptive Thresholds

For any adaptive threshold that you have register, you can click on the Select, (on the right side of the Metric list) and view analysis of the threshold data to see how the adaptive thresholds are supporting the metric.

adaptvie16

You can also test out different values and preview how they will support the metric and decide if you want to move away from an adaptive threshold and to a static one.

You can also choose click on the Test All which will look at previous data and see how the adaptive thresholds will support in theory in the future by how data in the baseline has been analyzed for the frequency window.

For my metric, I didn’t have time behind my baseline to give much in the way of a response, but the screenshot gives you an idea of what you will be looking at-

adaptvie16

Removing Adaptive Thresholds

If there is a metric that you wish to no longer have a metric threshold on, simply put a check mark in the metric’s Select box and then click on Deregister-

adaptive17

You will be asked if you want to continue, click Yes and the adaptive threshold will be removed from the target for the metric(s) checked.

Advanced threshold management offers the administrator a few more ways to gain definitive control over monitoring of targets via EM12c.  I haven’t found an environment yet that didn’t have at least one database or host that could benefit from these valuable features.

 

 

 

 



Tags:  

Del.icio.us
Facebook
TweetThis
Digg
StumbleUpon


Copyright © DBA Kevlar [Metric Thresholds and the Power to Adapt], All Right Reserved. 2014.

A World View

I’ve mentioned this before, but I thought I would show something visual…

The majority of my readers come from the USA and India. Since they are in different time zones, it spreads the load throughout the day. When I wake up, India are dominant.

MorningTraffic

In the afternoon the USA come online, by which time Russia have given up, but there is still a hardcore of Indian’s going for it! :)

AfternoonTraffic

I haven’t posted an evening shot as it’s the same as the afternoon one. Don’t you folks in India ever sleep?

I’m sure this is exactly the same with all other technology-related websites, but it does make me pause for thought occasionally. Most aspects of our lives are so localised, like traffic on the journey to work or family issues. It’s interesting to stop and look occasionally at the sort of reach this internet thing has given us. It may be a little rash, but I predict this interwebs thing might just catch on!

Cheers

Tim…


A World View was first posted on October 29, 2014 at 8:53 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.