Poorly Tuned Databases Exact a Heavy Toll: 3 MariaDB Tuning Tips Amy Krishnamohan Tue, 06/27/2017 - 17:44

Optimizing your database performance is important, but it often falls by the wayside in the bustle of daily business. How big a deal is it?

Let’s look at potential consequences of a poorly tuned database:

Bad user experience: Obviously you never want customers to complain of bad performance from your application. That’s the first step toward…

Lost sales: Customers may walk away when your application is just too slow. But it gets worse. You’re more likely to run into downtime when your database is overloaded, and if the server that’s down is an ordering or credit card–processing system, you’re guaranteed to forfeit sales. That money talk leads us to…

Unnecessary spending: You can spend more money on hardware (RAM, etc.) to speed up …

Join Percona’s Chief Evangelist, Colin Charles as he presents MariaDB Server 10.2: The Complete Guide on Tuesday, June 27, 2017, at 7:00 am PDT / 10:00 am EDT (UTC-7).

Register Now

The new MariaDB Server 10.2 release is out. It has some interesting new features, but beyond just a list of features we need to understand how to use them. This talk will go over everything new that MariaDB 10.2 has to offer.

In this webinar, we’ll learn about Window functions, common table expressions, finer-grained CREATE USER statements, and more – including getting mysqlbinlog up to parity with MySQL. …

Indexing and the MySQL Document StoreThe MySQL Document Store allows developers who do not know Structured Query Language (SQL) to use MySQL as a high efficient NoSQL document store. It has several great features but databases, NoSQL and SQL, have a problem searching through data efficiently. To help searching, you can add an index on certain fields to go directly to certain records. Traditional databases, like MySQL, allow you to add indexes and NoSQL databases, for example MongoDB, lets you add indexes. The MySQL Document Store also allows indexing.

So lets take a quick look at some simple data and then create an index.

mysql-js> db.foo.find()
[
    {
        "Name": "Carrie",
        "_id": "888881f14651e711940d0800276cdda9",
        "age": 21
    },
    {
        "Name": "Alex",
        "_id": "cc8a81f14651e711940d0800276cdda9",
        "age": 24
    },
    {
 …

This Log Buffer Edition covers Oracle, SQL Server and MySQL.

Oracle:

You may wish to clone an Oracle Home (for example you have all your databases on a single Oracle Home, but you want to separate Development from Test

Removing Outliers using stddev()

Installing Scala and Apache Spark on a Mac

Introduction to Oracle Big Data Cloud Service – Compute Edition (Part V) – Pig

More on …

Schema Sharding with MariaDB MaxScale 2.1 - Part 2 Wagner Bianchi Mon, 06/26/2017 - 09:15

In this second installment of schema sharing with MariaDB MaxScale to combine SchemaRouter and ReadWriteSplit MaxScale routers, we'll go through the details of implementing it in order to shard databases among many pairs of master/slave servers.

Before we move forward, I would like to highlight that you need to remove any duplicate database schema or a schema that is present on both shards. Schemas should exist in just one of the shards as when the SchemaRouter plugin starts, it’s going to read all the database schemas that exists on all the shards to get to know where the query will be routed. Take care to use mysql_secure_instalation and remove test database schema to avoid the below error when sending queries to MaxScale that will resolve them …

When we started working with a customer they asked us to setup for them more reliable and performant MySQL database. At the time they were using Percona XtraDB cluster with ELB to balance read traffic between PXC nodes. There was no writer redundancy – if a writer node was down, the app was down. The app by the way is a Magento backed online shop. All stuff was running in AWS.

We started to design the new database architecture. The first component we threw away was ELB. A simple OLTP sysbench test showed that ELB was a bottleneck. The ELB was capable to deliver somewhat around 6800 qps while with ProxySQL the Percona XtraDB Cluster was a bottleneck. We squeezed 10k qps from ProxySQL + PXC. That basically means we got all ProxySQL feature free of charge.

But one ProxySQL instance is a SPOF. To make ProxySQL highly available we put another instance and managed it with keepalived.

Eventually the architecture looked like …

For the impatient:

# echo -e "deb http://repo.mysql.com/apt/debian/ stretch mysql-5.7\ndeb-src http://repo.mysql.com/apt/debian/ stretch mysql-5.7" > /etc/apt/sources.list.d/mysql.list
# wget -O /tmp/RPM-GPG-KEY-mysql https://repo.mysql.com/RPM-GPG-KEY-mysql
# apt-key add /tmp/RPM-GPG-KEY-mysql
# apt update
# apt install mysql-server

In the latest stable version of Debian, if you ask to install mysql-server, you now get installed mariadb automatically, with no (evident) way of installing Oracle’s MySQL. Any major version upgrade has to be done carefully (not only for MariaDB, but also for MySQL and Postgres), and I bet that a MySQL 5.5 to MariaDB 10.1 will cause a huge confusion. Not only it will fail user expectations, I think this will cause large issues now that MariaDB has chosen to become a “hard” fork, and become incompatible in many ways with MySQL. Not only the server upgrade will cause user struggle, the connector is …

Last Thursday, I was introducing MySQL Document Store in Ghent, BE at Percona University.

I was explaining how great is this technology and how MySQL can replace your NoSQL database but still provides you all the benefits from a RDBMS.

This is the full presentation:

Introduction to MySQL Document Store from Frederic Descamps

Then somebody came with a nice question. Let me put first some context:

• we will create a collection to add people in it
• we will create a virtual column on the age
• we will index that column
• we will query and add records to that collection

Collection creation and add some users

mysql-js> schema = …

This is a "Howto" kind of post, and some speculations and historical references aside, it will show you how to build MariaDB ColumnStore (current version 1.0.9) from GitHub source, how to install and configure it for basic usage, as well as how to resolve some minor problems you may get in the process. Keep reading and eventually you'll get the real Howto below :)

* * *
I try not to care about any software issues besides good old MySQL, InnoDB storage engine internals, query optimization and some MyRocks or a little bit of …

以前の記事では、MySQL 8.0.1で導入された新しい 日本語のutf8bm4のCollation(文字照合順)について ご紹介しました。このcollation (utf8mb4_ja_0900_as_cs) は、CLDR 30で定義されたアクセント記号(清音濁音半濁音)ならびに大文字小文字(拗音促音など)を判別する実装となっています。

今日ご紹介するのはひらがなカタカナを判別できる新しい「かなセンシティブ」なCollation utf8mb4_ja_0900_as_cs_ksです。 …