Showing entries 91 to 100 of 36662
« 10 Newer Entries | 10 Older Entries »
Exposing Innodb Internals via System Variables: Part 5, Consistency / Statistics handling

Introduction

This is the final installment of a five part blog series to explore InnoDB internals by looking at the related tunable system variables. In this section we’re going to cover variables that relate to enforcing data consistency, and how index statistics are handled and stored.

Just like previous sections, I would like to emphasize something that was written in part one of this blog post series.

I should note that while tuning recommendations are provided, this objective of this blog post series was NOT meant to be a tuning primer, but instead to explore the mechanics that each variable interacts with. As such I would like to advise against reading this guide and trying to fine tune all of the available InnoDB variables. System variable tuning is an exercise in diminishing returns, the most benefit you’ll get out of tuning your MySQL server will occur within the first 15 minutes of configuration. In …

[Read more]
gh-ost 1.0.17: Hooks, Sub-second lag control, Amazon RDS and more

gh-ost version 1.0.17 is now released, with various additions and fixes. Here are some notes of interest:

Hooks

gh-ost now supports hooks. These are your own executables that gh-ost will invoke at particular points of interest (validation pass, about to cut-over, success, failure, status, etc.)

gh-ost will set various environment variables for your executables to pick up, passing along such information as migrated/ghost table name, elapsed time, processed rows, migrated host etc.

Sub-second lag control

At GitHub we're very strict about replication lag. We keep it well under 1 second at most times. …

[Read more]
How to set up read-write split in Galera Cluster using ProxySQL

Edited on Sep 12, 2016 to correct the description of how ProxySQL handles session variables. Many thanks to Francisco Miguel for pointing this out.

ProxySQL is becoming more and more popular as SQL-aware load balancer for MySQL and MariaDB. In previous blog posts, we covered installation of ProxySQL and its configuration in a MySQL replication environment. We’ve covered how to set up ProxySQL to perform failovers executed from ClusterControl. At that time, Galera support in ProxySQL was a bit limited - you could configure Galera Cluster and split traffic across all nodes but …

[Read more]
Real-time Data Streaming to Kafka with MaxScale CDC

Mon, 2016-09-05 00:00

Show differences found by pt-table-checksum

pt-table-checksum perfectly solves problem of checking if master and its slaves are in-sync. It answers the question “Are the slaves consistent with the master?”. However if the answer is “No” pt-table-checksum doesn’t actually tell what exactly is different.

[root@master vagrant]# pt-table-checksum -q
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
09-03T22:21:10 0 1 2 1 0 0.013 mysql.proxies_priv
09-03T22:21:10 0 1 9 1 0 0.010 mysql.user

pt-table-sync may give a partial answer. It can print SQL statements to sync the replication cluster. Reading the SQL code you may guess what records were missing/extra or differ.

[root@master vagrant]# pt-table-sync --print --replicate percona.checksums localhost
DELETE FROM `mysql`.`proxies_priv` WHERE `host`='slave.box' AND `user`='root' AND `proxied_host`='' AND `proxied_user`='' LIMIT 1 /*percona-toolkit src_db:mysql src_tbl:proxies_priv …
[Read more]
Types in PHP and MySQL

Since PHP 7.0 has been released there's more attention on scalar types. Keeping types for data from within your application is relatively simple. But when talking to external systems, like a database things aren't always as one eventually might initially expect.

For MySQL the type we see -- in the first approximation -- is defined by the network protocol. The MySQL network protocol by default converts all data into strings. So if we fetch an integer from the database and use PHP 7's typing feature we get an error:

<?php
declare(strict_types=1);

function getInteger() : int {
  $mysqli = new mysqli(...);
  return $mysqli->query("SELECT 1")->fetch_row()[0];
}

var_dump(getInteger());
?>

Fatal error: Uncaught TypeError: Return value of getInteger() must be of the type integer, string returned in t.php:6

Of course the solution is easy: Either we cast ourselves or we disable the strict mode and PHP will …

[Read more]
InnoDB Troubleshooting: Q & A

In this blog, I will provide answers to the Q & A for the InnoDB Troubleshooting webinar.

First, I want to thank everybody for attending the August 11 webinar. The recording and slides for the webinar are available here. Below is the list of your questions that I wasn’t able to answer during the webinar, with responses:

Q: What’s a good speed for buffer pool speed/size for maximum query performance?

A: I am sorry, I don’t quite understand the question. InnoDB buffer pool is an in-memory buffer. In an ideal case, your whole active dataset (rows that are accessed by application regularly) should be in the buffer pool. There is a good …

[Read more]
MHA Quick Start Guide

MHA (Master High Availability Manager and tools for MySQL) is one of the most important pieces of our managed services. When properly set up, it can check replication health, move writer and reader virtual IPs, perform failovers, and have its output constantly monitored by Nagios. Is it easy to deploy and follows the KISS (Keep It Simple, Stupid) philosophy that I love so much.

This blog post is a quick start guide to try it out and play with it in your own testing environment. I assume that you already know how to install software, deal with SSH keys and setup replication in MySQL. The post just covers MHA configuration.

Testing environment

Taken from /etc/hosts

192.168.1.116      mysql-server1
192.168.1.117   mysql-server2
192.168.1.118   mysql-server3
192.168.1.119   mha-manager

mysql-server1: Our master …

[Read more]
How to Stream Change Data through MariaDB MaxScale using CDC API

Fri, 2016-09-02 15:12

Exposing Innodb Internals via System Variables: Part 4, Concurrency

Introduction

This is part four of a five part blog series to explore InnoDB internals by looking at the related tunable system variables. In this section we’re going to explore the mechanics that impact CPU resourcing and how InnoDB handles concurrent threads. You’ll notice that a lot of the variables covered in section relate to features that are now disabled but were a lot more prevalent in previous versions of MySQL, particularly those that were released at a time where system context switching had a greater cost than it does today. These variables are still worth discussing as you may run into older systems that utilize these mechanics, and there are even modern systems I’ve worked with that have implemented these features to tune performance to its highest potential.

Just like previous sections, I would like to emphasize something that was written in part one of this blog post series.

“I should note that …

[Read more]
Showing entries 91 to 100 of 36662
« 10 Newer Entries | 10 Older Entries »