Month: November 2015

MariaDB Talks at Percona Live Data Performance Conference 2016

The Percona Live Data Performance Conference 2016 isn’t until 18-21 April 2016, but community voting is now open. So if you’re going, and want to see lots of MariaDB talks, please click on the buttons below and vote!
You have to be logged in to the Percona site to vote. Once logged in, scroll to the bottom of the talk description page, assign the number of stars that represents your interest in the talk (5 is the best) and click on the “Submit Vote” button. That’s all there is to it.
Here are all of the proposed talks I know of that are either specifically MariaDB-related or are by employees of either the MariaDB Foundation or MariaDB Corporation. If there are others, please let me know!

Using and Managing MariaDB by Colin Charles
View & Vote!

MariaDB 10.1 – What’s New? by Colin Charles & Monty Widenius
View & Vote!

MariaDB/MySQL security essentials by Colin Charles
View & Vote!

MariaDB Connectors: Fast and Smart with the new protocol optimizations by Sergei Golubchik
View & Vote!

MariaDB 10.1 Security: Validation, Authentication, Encryption by Sergei Golubchik
View & Vote!

Window functions in MariaDB by Sergei Petrunia
View & Vote!

Exploring MariaDB Query Optimizer with ANALYZE for statements by Sergei Petrunia
View & Vote!

The MariaDB CONNECT Storage Engine to simplify heterogeneous data access by Serge Frezefond
View & Vote!

Databases in the Hosted Cloud by Colin Charles
View & Vote!

Best practices for MySQL High Availability by Colin Charles
View & Vote!

Choosing a MySQL HA solution today by Colin Charles
View & Vote!

The MySQL Server Ecosystem in 2016 by Colin Charles
View & Vote!

Low Impact Monitoring by Gerardo Narvaja
View & Vote!

Characters sets and Collations – All you wanted to know but was afraid to ask by Anders Karlsson
View & Vote!

IBM Power8 – The new powerful open database platform by Anders Karlsson
View & Vote!

Migrating data – Working with datatypes, it might not be what you expect! by Anders Karlsson
View & Vote!

Real-time data streaming to Hadoop using MaxScale by Dipti Joshi
View & Vote!

A Multi-tenancy Use Case for MaxScale by Dipti Joshi & Markus Mäkelä
View & Vote!

My proposals for Percona Live: Window Functions and ANALYZE for statements

I’ve made two session proposals for Percona Live conference:

Window functions in MariaDB covers Window functions, a new feature in MariaDB 10.2. As far as I know, this the first attempt to have a talk about this feature.

Exploring MariaDB Query Optimizer with ANALYZE for statements is about making use of ANALYZE (actually, ANALYZE FORMAT=JSON mostly) from MariaDB 10.1 to narrow down query optimizer problems. I did introductory talks about this feature before. This time, I intend to go a bit deeper and focus not on the ANALYZE command itself, but on its use cases.

if you feel these talks are worth it, please vote!

SQLcl: The new challenger for the SQL Plus crown

I’m that grumpy old guy who comes to your party and sits in a corner with a face like a dog’s backside. You know, that guy who hates everything new: Adele? She’s no Whitney Houston! Bluray? It’s not as good as Betamax! TOAD? It’s not as good as SQL Plus! I’m exaggerating – but only a little. Unless your work… Continue Reading →

RAM is the new disk – and how to measure its performance – Part 3 – CPU Instructions & Cycles

If you haven’t read the previous parts of this series yet, here are the links: [ Part 1 | Part 2 ]. A Refresher In the first part of this series I said that RAM access is the slow component of a modern in-memory … Continue reading

The Power and Ease of Adaptive Fault Detection

Adaptive Fault detection is a prime example of how efficiently VividCortex can help you understand and optimize your system. We define a “fault” as a special kind of system stall: a period during which applications are asking the server to perform a great deal of work, but that work is getting bottlenecked and therefore not completing.

This understanding of faults is based on Queueing Theory (if you need a refresher on QT, be sure to check out our recent and highly accessible ebook, Everything You Need to Know About Queueing Theory); we detect faults by using advanced statistics and machine learning. VividCortex’s Fault Detection is completely adaptive and self-tuning – it doesn’t require any configuration. The program can detect faults as short as one second in duration. Even the most attentive user would likely fail to notice system stalls so small, but with our Adaptive Fault Detection, they’re easily diagnosed and solved.

But why is it important for users to lock onto such small problems? Well, system performance problems almost always start small and, overtime, snowball into much more serious issues. Catching them early is the best way to prevent major performance problems and outages.

When you’re using Adaptive Fault detection, VividCortex displays faults in an easily understood timeline, running from left to right.

In the example here, you can see three stalls – notated as vertical bars along the timeline – that occurred in one of our production database servers. The widgets beneath the timeline (shown in the image below) illustrate what was happening in the server at the moment of each fault. As you select a fault, you can see a red line appear on the myriad charts, to indicate the precise instant when that fault occurred.

Right away, you’ll notice a few telltale signs of a fault. For instance, take a look at the chart tracking MySQL concurrency: when you hover over the fault in the timeline, you can see that at the moment of the fault, the concurrency spiked up to 141 queries, all trying to run simultaneously – much more than this machine can handle.

Now, let’s look at the fault on the far right of the timeline; we see that at the time it occurred, the newdownsample program had just started running, and we can see that there was a notably high amount of Disk Throughput and CPU activity in the server as a result.

And, again, when we hover over the pixel representing the exact instant of the fault, we see that MySQL concurrency spiked dramatically – in this case, to a clogged 154 queries running at a single moment.

Looking further down the collection of summary widgets, we find Top MySQL Queries. Here, we see the third query listed is quit abusive, arising from the newdownsample program as well. That abusive query whisks resources away from other processes.

Clearly, it would be beneficial to examine this query more closely. Doing so is simple: all you need to do is click to drill down into the widget, to see more top running queries during the relevant time range. You can then select that query and continue drilling down into it with the various tools at your disposal.

Also note that we’ve just introduced our new Profiler tool, which takes our older Top Queries and Top Processes tools to a whole new level. You’ll be able to drill down with more precision and customization than ever before. The APIs are faster too! To read more about the Profiler tool, check out our recent announcement here.

And finally, to watch VividCortex’s Adaptive Fault Detection in action, check out Baron Schwartz’s demo in the video below.

Voting for talks at the Percona Live Data Performance Conference 2016

So this year the Percona Live conference has a new name — it is the “Data Performance Conference” (presumably for a much broader appeal and the fact that Percona is now in the MongoDB world as well). And the next new thing to note? You have to go through a process of “community voting”, i.e. the speaker has to promote their talks before via their own channels to see how many votes they can get (we tried this before at the MySQL & Friends Devroom at FOSDEM; in this case, please remember you also need to create a new account and actually vote while logged in).
I hope you vote for Sergei, Monty and my proposals!

Using and Managing MariaDB – a tutorial, which has been referred to as The Complete MariaDB Server tutorial, I thought I will change the name up a little, in addition to the content. The most recent version of this tutorial was given at the Percona Live Conference in Santa Clara in 2015 (slides). Since then we’ve released MariaDB Server 10.1, and there’s much more new things to talk about!
MariaDB 10.1 – What’s New? – a talk that would have Michael “Monty” Widenius (creator of MySQL and MariaDB) and me give it together. I’ve described this as a dance, and the last time we did this was at Percona Live Amsterdam. The content will of course be new, and I am creating the slide deck this time around.
Databases in the Hosted Cloud – this is a pet talk. It costs some money to make, and if accepted I plan to also showcase who has better performing hosted databases. I did this at Percona Live Amsterdam 2015 (slides), but since then we’ve seen Amazon offering MariaDB Server as part of RDS, HPCloud being sunset, and also Rackspace upping their offering with High Availability Databases. More research to be done from now till then!
Best Practices for MySQL High Availability – this would be another tutorial, and at Percona Live Amsterdam 2015 it had the highest registered attendance (Kortney told me the day before and I removed all practicals, since 100+ people with practicals is impossible for one person to manage – slides). I think with the changes in NDBCLUSTER (recently announced at OpenWorld), the addition of tools in the MHA world (mha-helper), this should have a lot of new information (and more importantly a lot of new things to play with).
Choosing a MySQL HA solution today – a talk based on the above tutorial, cut short, to ensure people whom are not at tutorial day, will have solutions to think about and take home for implementation in the future.
MariaDB/MySQL security essentials – a talk which focuses on improvements in MariaDB Server 10.1, and MySQL 5.6/5.7, including encryption at rest, easier SSL setup for replication topologies, and even external authentication plugins (eg. Kerberos is almost ready – see MDEV-4691).
The MySQL Server Ecosystem in 2016 – a talk about MySQL and the forks around it, including the private trees that exist (some like the Twitter tree haven’t been updated in a while, but clearly have made inroads in giving us new features). Learn what to use, and what is the best one for your use case. 
MariaDB Connectors: Fast and Smart with the new protocol optimizations – a talk from Sergei Golubchik, about new protocol optimisations in MariaDB Server as well as how we optimise this from the connectors as well.
MariaDB 10.1 Security: Validation, Authentication, Encryption – a talk from Sergei Golubchik focusing on MariaDB 10.1 security improvements; he’s got some amazing slides on encryption that I saw at Percona Live Amsterdam, and you can see a five-minute lightning version from the meetup.

Here’s to happy voting and I hope to give at least some of these talks (if not all!).

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

 The show goes on. This Log Buffer Edition picks some blogs which are discussing new and old features of Oracle, SQL Server and MySQL.Oracle:Directory Usage Parameters (ldap.ora) list the host names and port number of the primary and alternate LDAP directory servers.Data Visualization Cloud Service (DVCS) is a new Oracle Cloud Service. It is a subset offering of the currently supported Business Intelligence Cloud Service (BICS).ORA-24247: network access denied by access control list (ACL).Latches are low level serialization mechanisms, which protect memory areas inside SGA. They are light wait and less sophesticated than enqueues and can be acquired and released very quickly.handling disks for ASM – when DB, Linux and Storage admins work together.SQL Server:How to use the Performance Counter to measure performance and activity in Microsoft Data Mining.Phil Factor demonstrates a PowerShell-based technique taking the tedium out of testing SQL DML.Sandeep Mittal provides an introduction to the COALESCE function and shows us how to use it.Hadoop many flavors of SQL.Installing and Getting Started With Semantic Search.MySQL:Support for storing and querying JSON within SQL is progressing for the ANSI/ISO SQL Standard, and for MySQL 5.7.Loss-less failover using MySQL semi-syncronous replication and MySQL Fabric!Memory consumption The binary format of the JSON data type should consume more memory.This post compares a B-Tree and LSM for read, write and space amplification. The comparison is done in theory and practice so expect some handwaving mixed with data from iostat and vmstat collected while running the Linkbench workload.If you do not have a reliable network access (i.e. in some remote places) or need something really small to store your data you can now use Intel Edison. Learn more about Pythian’s expertise in Oracle , SQL Server & MySQL.

MySQL Performance: Improved Connect/sec Rate in MySQL 5.7

This article is continuing the MySQL 5.7 Performance story, started from 1.6M
QPS on MySQL 5.7 details post , then 1M
QPS on mixed OLTP_RO with MySQL 5.7 article, and detailed story
about why
the Point-Selects performance is so critical (and why the 1M result
published by MariaDB is not fair)..The current story will be
about Connect/sec (connect/disconnect) performance improvement in
MySQL 5.7 – such kind of metric is very critical for any application
which cannot use persistent connections all the time (and many web apps
are in such a case). Well, I’d say MySQL from the beginning was
extremely good for its lightweight connections, and made in the past the
base of success for many web solutions.. However, time is going, and
we’re no more with 4cores as “commodity hardware” (this is rather a
smart-watch today ;-)) – so, there was a need to speed-up this Connect
rate to match higher workloads. This was already greatly done in MySQL
5.6, and finally yet more improved in MySQL 5.7 – you may read all
details about directly from our developers – I’ll just present here
a short summary about where we’re today..So far, first of all,
how to test the Connect/sec performance of your MySQL server instance? –
the most simple way here is just to use a standard Sysbench kit, load
10M rows into sysbench database (1 table or several tables, no matter —
the main show-stopper here is the Connect code itself), and then run the
following :

#!/bin/bash

# —————————————————————-
# Connect/sec test
# —————————————————————-

for Users in 8 16 32 64 128 256 512 1024
do
LD_PRELOAD=/usr/lib64/libjemalloc.so.1 sysbench –num-threads=$Users \
–test=oltp –oltp-table-size=10000000 \
–db-ps-mode=disable –oltp-dist-type=uniform –oltp-table-name=sbtest_10M \
–max-requests=0 –max-time=300 –mysql-socket=/tmp/mysql.sock \
–mysql-user=dim –mysql-password=dim –mysql-db=sysbench \
–mysql-table-engine=INNODB –db-driver=mysql \
–oltp-point-selects=1 –oltp-simple-ranges=0 –oltp-sum-ranges=0 \
–oltp-order-ranges=0 –oltp-distinct-ranges=0 –oltp-skip-trx=on \
–oltp-read-only=on –oltp-reconnect-mode=query –oltp-connect-delay=0 run
sleep 30
done

# —————————————————————-

means on every point-select query your client session will re-connect, and
the final QPS result will give you the max Connect/sec rate your MySQL
instance is able to reach for a given amount of concurrent users.And
here are the results obtained from older to newer generations Intel-based
Linux servers :12cores-HT @2.9Ghz :32cores-HT
@2.3Ghz :40cores-HT
@2.3Ghz :72cores-HT
@2.5Ghz :

Instead of SUMMARY :

Connect/sec performance is mainly depending on the MySQL
“connect/disconnect” code itself + CPU chip speed

it’s hard to speak about “scalability” here as the max possible
Connect/sec rate limit is reached pretty quickly and depending on IP
stack performance as well..

tuning “thread_cache_size” to something bigger than zero is helping
here, but not too much..

MySQL 5.7 showing the best performance here regardless the HW
platform, and reaching over 100K Connect/sec on the latest
Intel CPU chip

there is a clear better-and-better tendency in MySQL 5.5 => 5.6 => 5.7
results

and we still can do yet more better with MySQL 5.8 ! (question of time
and resources – so, please, send us your feedback/ votes/ wishes if
you want to see it ;-))

MySQL 5.7 rocks! ;-))

Rgds,-Dimitri

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