Month: July 2015

What’s New With Oracle Certification This Week? Week of July 27, 2015

Are you waiting for a new Oracle Certification exam to be released? Interested in beta testing, or waiting for your beta exam score? 

See what’s in beta now, view exams just released into production, get information on current promotions or learn about new certifications below.

Current Promotions

Oracle Database Foundations | 1Z0-006 – Beta by invite only; ends August 1, 2015

Post Beta Review – Available Soon

Java Foundations | 1Z0-811

Beta Scores Available in CertView

Log Buffer #434: A Carnival of the Vanities for DBAs

This Log Buffer Edition throws spotlight on some of the salient blog posts from Oracle, SQL Server and MySQL.
Oracle:

STANDARD date considerations in Oracle SQL and PL/SQL
My good friend, Oracle icon Karen Morton passed away.
Multiple invisible indexes on the same column in #Oracle 12c
Little things worth knowing: Data Guard Broker Setup changes in 12c
Things that are there but you cannot use

SQL Server:

Dynamic Grouping in SSRS Reports
SQL 2014 Clustered Columnstore index rebuild and maintenance considerations
SQL Server 2016 CTP2
Azure SQL Database Security Features
Visualize the timeline of your SQL jobs using Google graph and email

MySQL:

Shinguz: Max_used_connections per user/account
Generally in MySQL we send queries massaged to a point where optimizer doesn’t have to think about anything.
Replication is the process that transfers data from an active master to a slave server, which reproduces the data stream to achieve, as best as possible, a faithful copy of the data in the master.
Unknown column ‘smth’ in ‘field list’ -> Oldie but goodie error
Why base64-output=DECODE-ROWS does not print row events in MySQL binary logs

Learn more about Pythian’s expertise in Oracle , SQL Server & MySQL.
The post Log Buffer #434: A Carnival of the Vanities for DBAs appeared first on Pythian – Data Experts Blog.

MariaDB automatic failover with MaxScale and MariaDB Replication Manager

Fri, 2015-07-31 12:14guillaumelefrancMandatory disclaimer: the techniques described in this blog post are experimental, so use at your own risk. Neither me nor MariaDB Corporation will be held responsible if anything bad happens to your servers.
Context
MaxScale 1.2.0 and above can call external scripts on monitor events. In the case of a classic Master-Slave setup, this can be used for automatic failover and promotion using MariaDB Replication Manager. The following use case is exposed using three MariaDB servers (one master, two slaves) and a MaxScale server. Please refer to my Vagrant files if you want to jumpstart such a testing platform.
Requirements
A mariadb-repmgr binary, version 0.4.0 or above. Grab it from the github Releases page, and extract in /usr/local/bin/ on your MaxScale server.
A working MaxScale installation with MySQL Monitor setup and whatever router you like. Please refer to the MaxScale docs for more information on how to configure it correctly.
MaxScale installation and configuration
The MySQL Monitor has to be configured to send scripts. Add the following three lines to your [MySQL Monitor] section:

monitor_interval=1000
script=/usr/local/bin/failover.sh
events=master_down
Failover script
As of the current MaxScale development branch, custom options are not supported, so we have to use a wrapper script to call MariaDB Replication Manager. Create the following script in /usr/local/bin/failover.sh:

#!/bin/bash
# failover.sh
# wrapper script to repmgr

# user:password pair, must have administrative privileges.
user=root:admin
# user:password pair, must have REPLICATION SLAVE privileges.
repluser=repluser:replpass

ARGS=$(getopt -o ” –long ‘event:,initiator:,nodelist:’ — “$@”)

eval set — “$ARGS”

while true; do
case “$1″ in
–event)
shift;
event=$1
shift;
;;
–initiator)
shift;
initiator=$1
shift;
;;
–nodelist)
shift;
nodelist=$1
shift;
;;
–)
shift;
break;
;;
esac
done
cmd=”mariadb-repmgr -user $user -rpluser $repluser -hosts $nodelist -failover=dead”
eval $cmd
Make sure to configure user and repluser script variables to whatever your user:password pairs are for administrative user and replication user. Also make sure to make the script executable (chown +x) as it’s very easy to forget that step.
Testing that failover works
Let’s check the current status, where I have configured server3 as a master and server1-2 as slaves:

$ maxadmin -pmariadb “show servers”
Server 0x1b1f440 (server1)
Server: 192.168.56.111
Status: Slave, Running
Protocol: MySQLBackend
Port: 3306
Server Version: 10.0.19-MariaDB-1~trusty-log
Node Id: 1
Master Id: 3
Slave Ids:
Repl Depth: 1
Number of connections: 0
Current no. of conns: 0
Current no. of operations: 0
Server 0x1b1f330 (server2)
Server: 192.168.56.112
Status: Slave, Running
Protocol: MySQLBackend
Port: 3306
Server Version: 10.0.19-MariaDB-1~trusty-log
Node Id: 2
Master Id: 3
Slave Ids:
Repl Depth: 1
Number of connections: 8
Current no. of conns: 1
Current no. of operations: 0
Server 0x1a7b2c0 (server3)
Server: 192.168.56.113
Status: Master, Running
Protocol: MySQLBackend
Port: 3306
Server Version: 10.0.19-MariaDB-1~trusty-log
Node Id: 3
Master Id: -1
Slave Ids: 1, 2
Repl Depth: 0
Number of connections: 2
Current no. of conns: 0
Current no. of operations: 0
Everything looks normal. Let’s try failover by shutting down server3.

server3# service mysql stop
* Stopping MariaDB database server mysqld [ OK ]
Let’s check the server status again:

$ maxadmin -pmariadb “show servers”
Server 0x1b1f440 (server1)
Server: 192.168.56.111
Status: Slave, Running
Protocol: MySQLBackend
Port: 3306
Server Version: 10.0.19-MariaDB-1~trusty-log
Node Id: 1
Master Id: 2
Slave Ids:
Repl Depth: 1
Number of connections: 0
Current no. of conns: 0
Current no. of operations: 0
Server 0x1b1f330 (server2)
Server: 192.168.56.112
Status: Master, Running
Protocol: MySQLBackend
Port: 3306
Server Version: 10.0.19-MariaDB-1~trusty-log
Node Id: 2
Master Id: -1
Slave Ids: 1
Repl Depth: 0
Number of connections: 8
Current no. of conns: 1
Current no. of operations: 0
Server 0x1a7b2c0 (server3)
Server: 192.168.56.113
Status: Down
Protocol: MySQLBackend
Port: 3306
Server Version: 10.0.19-MariaDB-1~trusty-log
Node Id: 3
Master Id: -1
Slave Ids:
Repl Depth: 0
Number of connections: 2
Current no. of conns: 0
Current no. of operations: 0
MariaDB Replication Manager has promoted server2 to be the new master, and server1 has been reslaved to server2. server3 is now marked as down. If you restart server3, it will be marked as “Running” but not as slave – to put it back in the cluster, you just need to repoint replication with GTID with this command: CHANGE MASTER TO MASTER_HOST=’server1′, MASTER_USE_GTID=CURRENT_POS; The failover script could handle this case as well, although it remains to be tested.
Tags: High AvailabilityMaxScaleReplication
About the Author

Guillaume Lefranc is managing the MariaDB Remote DBA Services Team, delivering performance tuning and high availability services worldwide. He’s a believer in DevOps culture, Agile software development, and Craft Brewing.

MySQL QA Episode 10: Reproducing and Simplifying: How to get it Right

Welcome to the 10th episode in the MySQL QA series! Today we’ll talk about reproducing and simplifying: How to get it Right.Note that unless you are a QA engineer stuck on a remote, and additionally difficult-to-reproduce or difficult-to-reduce bug, this episode will largely be non-interesting for you.However, what you may like to see – especially if you watched episodes 7 (and possibly 8 and 9) – is how reducer automatically generates handy start/stop/client (cl) etc. scripts, all packed into a handy bug tarball, in combination with the reduced SQL testcase.This somewhat separate part is covered directly after the introduction (ends at 11:17), as well as with an example towards the end of the video (starts at time index 30:35).The “in between part” (11:17 to 30:35) is all about reproducing and simplifying, which – unless you are working on a remote case – can likely be skipped by most; remember that 85-95% of bugs reproduce & reduce very easily – and for this – episode 7, episode 8 (especially the FORCE_SKIPV/FORCE_SPORADIC parts), and the script-related parts of this episode (start to 11:17 and 30:35 to end) would suffice.As per the above, the topics covered in this video are: 1. percona-qa/reproducing_and_simplification.txt 2. Automatically generated scripts (produced by Reducer)========= Example bug excerpt for copy/paste – as per the video Though the testcase above should suffice for reproducing the bug, the attached tarball gives the testcase as an exact match of our system, including some handy utilities $ vi {epoch}_mybase # Update base path in this file (the only change required!) $ ./{epoch}_init # Initializes the data dir $ ./{epoch}_start # Starts mysqld (MYEXRA –option) $ ./{epoch}_stop # Stops mysqld $ ./{epoch}_cl # To check mysqld is up $ ./{epoch}_run # Run the testcase (produces output) using mysql CLI $ ./{epoch}_run_pquery # Run the testcase (produces output) using pquery $ vi /dev/shm/{epoch}/error.log.out # Verify the error log $ ./{epoch}_gdb # Brings you to a gdb prompt $ ./{epoch}_parse_core # Create {epoch}_STD.gdb and {epoch}_FULL.gdb; standard and full var gdb stack tracesFull-screen viewing @ 720p resolution recommendedThe post MySQL QA Episode 10: Reproducing and Simplifying: How to get it Right appeared first on Percona Data Performance Blog.

Shinguz: Max_used_connections per user/account

Taxonomy upgrade extras: max_used_connectionsuseraccountconnectionconfigurationHow many connections can be opened concurrently against my MySQL or MariaDB database can be configured and checked with the following command:

SHOW GLOBAL VARIABLES LIKE ‘max_connections’;
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| max_connections | 505 |
+—————–+——-+If this limit was ever reached in the past can be checked with:

SHOW GLOBAL STATUS LIKE ‘max_use%’;
+———————-+——-+
| Variable_name | Value |
+———————-+——-+
| Max_used_connections | 23 |
+———————-+——-+But on MySQL instances with many different applications (= databases/schemas) and thus many different users it is a bit more complicated to find out which of these users have connected how many times concurrently. We can configure how many connections one specific user can have at maximum at the same time with:

SHOW GLOBAL VARIABLES LIKE ‘max_user_connections’;
+———————-+——-+
| Variable_name | Value |
+———————-+——-+
| max_user_connections | 500 |
+———————-+——-+Further we can limit one specific user with:

GRANT USAGE ON *.* TO ‘repl’@’%’
WITH MAX_CONNECTIONS_PER_HOUR 100 MAX_USER_CONNECTIONS 10;and check with:

SELECT User, Host, max_connections, max_user_connections
FROM mysql.user;
+——+—————+—————–+———————-+
| User | Host | max_connections | max_user_connections |
+——+—————+—————–+———————-+
| root | localhost | 0 | 0 |
| repl | % | 100 | 10 |
| repl | 192.168.1.139 | 0 | 0 |
+——+—————+—————–+———————-+But we have currently no chance to check if this limit was reached or nearly reached in the past…

A feature request for this was opened at MySQL wit bug #77888

Solution

If you cannot wait for the implementation here we have a little workaround:

DROP TABLE IF EXISTS mysql.`max_used_connections`;

CREATE TABLE mysql.`max_used_connections` (
`USER` char(16) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`HOST` char(60) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`MAX_USED_CONNECTIONS` bigint(20) NOT NULL,
PRIMARY KEY (`USER`, `HOST`) USING HASH
) ENGINE=MEMORY DEFAULT CHARSET=utf8
;

DROP EVENT IF EXISTS mysql.gather_max_used_connections;

— event_scheduler = on
CREATE DEFINER=root@localhost EVENT mysql.gather_max_used_connections
ON SCHEDULE EVERY 10 SECOND
DO
INSERT INTO mysql.max_used_connections
SELECT user, host, current_connections
FROM performance_schema.accounts
WHERE user IS NOT NULL
AND host IS NOT NULL
ON DUPLICATE KEY
UPDATE max_used_connections = IF(current_connections > max_used_connections, current_connections, max_used_connections)
;

SELECT * FROM mysql.max_used_connections;

+——–+———–+———————-+
| USER | HOST | MAX_USED_CONNECTIONS |
+——–+———–+———————-+
| root | localhost | 4 |
| zabbix | localhost | 21 |
+——–+———–+———————-+Caution: Because we used a MEMORY table those values are reset at every MySQL restart (as it happens with the PERFORMANCE_SCHEMA or the INFORMATION_SCHEMA).

on ORDER BY optimization

Generally in MySQL we send queries massaged to a point where optimizer doesn’t have to think about anything. In our major user database environment 99.9% of queries don’t have alternative query plans available (either because of forced indexes or just straightforward Primary Key read). We have various other systems and from time to time we have to do SQL work there and chase optimizer errors.
There’re multiple places where optimizer can make a choice in very basic queries, for example:

Which index returns less rows
Which index can be used for ORDER BY

A query that I was looking asked a very basic question, on a job instances table, show state and status for latest-by-ID entry for job name=’Ship Christmas Presents’ (real name was a bit different ;-). So, it was SELECT c,d FROM t WHERE b=X ORDER BY a DESC LIMIT 1, where PK is (a) and a possible index is on (b,c).
What we were observing was a massive table scan on PK instead of using (b, …) indexing. Table in question was in hundreds of gigabytes, so that did hurt, a bit. If one forced the (b,c) index, queries became really fast. This wasn’t an issue with some intermittent statistics flapping.
The first thing that immediately caught my attention was that LIMIT 2 produced a proper query plan, whereas LIMIT 1 did not. I shipped a few-gigabyte sized subset onto my test machine and carefully went through what was happening.
EXPLAIN was just telling me that it will be picking bad query plan, EXPLAIN FORMAT=JSON was still telling the same, so I needed to look at some detailed execution data. MySQL has this extremely promising facility called ‘optimizer trace’, so I got the trace for my test-case. Unfortunately, the trace gave everything that I knew – that only one index made sense for reducing the dataset and that it changed to PK to order things better. It told me about number of rows and some “cost” of the plan – whatever those numbers mean, and it gave super-high numbers for table scan.
My optimizer trace did not tell why it decided to switch from decent query plan to absolutely horrible one, it just had a block telling that “reconsidering_access_paths_for_index_ordering” and that “plan_changed”: true, which I already knew. On the other hand, that provided me with quick pointer into the source code – the six thousand lines of sql_select.cc. I could find above trace pointer somewhere in test_if_skip_sort_order(), which then calls this:
test_if_cheaper_ordering(…, order, table, usable_keys, ref_key, select_limit, &best_key, …);
Essentially, this function will look at all the indexes that can be used instead of “filesort” and see what would happen if we used them. This function would say that the ref_key (b,c) – the index that returns least rows – is not the best key, and the best key is one on (a). How did it come up with such conclusion? Short answer – optimizer is very naïve.
That logic is explained in this comment:

/*
We assume that each of the tested indexes is not correlated
with ref_key. Thus, to select first N records we have to scan
N/selectivity(ref_key) index entries.

*/

It makes the naïve and somewhat optimistic calculation on (a) and the most pessimistic possible calculation on (b,c). I’ll start with the pessimistic one. Optimizer assumes that there’re 20000 instances for our job, and for each of these jobs we have to do a separate seek into PK, so our cost is 20000 (~300MB) for PK reads + few more reads on index itself.
Now the optimism (and bad query plan) comes from the idea that if we’re only selecting only 1 out of 20000 rows, that means only 0.005% of table scan is enough to satisfy LIMIT 1. If we would provide LIMIT 10, it would be only 0.05%. On a 100GB table, that means 5MB of data read, and that seems to be cheaper than the very pessimistic calculation of more than 300MB above.
Unfortunately, optimizer doesn’t understand, that there’re humans who are building these systems, and humans have their own rationale and thinking. One of the ideas that a human would have is that if you have 100GB table, you better understand things like data locality and other sorts of efficiencies. That means that in real world most of large tables have various degrees of correlation of data. In our fictitious example we know that “Christmas” happen, well, at Christmas. Looking through our window we know that it isn’t anywhere near Christmas.
So, database assumes that our data is distributed like this:
—-W—-H—-E—-E—-E—–
When it is more like this:
———–W-H-EE-EE———-
With this data distribution the pessimistic decision on (b,c) becomes way too dark and miserable – there’s a chance that “random” seeks into PK will all hit same pages, so we will need very few megabytes read. Our best case ends up being at ~5MB, our worst case is somewhere at above mentioned 300MB. Now optimistic decision can vary from “oh hey, I just started reading and found a row immediately” win of a lottery to “hey, I calculated an average for you” naïveté of 5MB to “oh snap, I was wrong” of say… 30GB.
Though we all agree that optimizer cannot everything, it errs into the direction of chasing much wider range of possibilities and being way more opportunistic rather than being somewhat more reliable. Obviously, it is very hard to tell whether changing some of these heuristics is possible in a way that would not affect million other places, but in this exact case we can look at what could be done better with information at our hand, either by rewriting queries or implementing somewhat procedural access.
One way is materializing the maximum ID first simply because it is already hidden in the (b,c) index – internally inside InnoDB that index is (b,c,a). So if we
SELECT MAX(a) FROM t WHERE b=X
we can get most of the data for the read just by scanning few index pages. We can use that data then to dive into primary key for that single row. Very similar approach can be taken with different limits.
We should also know that optimizer has most of this logic back from MyISAM times and it doesn’t know that it knows multiple values of (a) just by doing records-in-range estimation for (b,c) index – it does two random dives and it already observes primary key values. Simply by knowing that both of these values represent the range nowhere close the table scan head or tail it can discard the truly expensive query plan.
Of course, that is messy and can have broken sampling, but hey, it may be better than nothing, although it will again assume something opposite – that data is somehow correlated. Which we humans think it is and computer is on the opposite side of the fence.
So, in summary, you have to understand that database assumes things you don’t and sometimes you have to force your query plans or write your queries in a way that there’s no optimization space for optimizer left.
See also:

a case for FORCE INDEX

Webinar-on-demand: Learn how to add HA and DR to MySQL operating on-prem and in VMware’s vCloud Air public cloud

Learn how VMware Continuent adds HA, DR and real-time data warehouse loading to off-the-shelf MySQL operating on-prem and in VMware vCloud Air public cloud. 

We introduce vCloud Air basics, then do a deep dive into the VMware Continuent system architecture covering important issues like fail-over, zero-downtime maintenance, and load scaling. We will conclude with a demonstration of using

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569