Showing entries 1 to 10 of 24
10 Older Entries »
Displaying posts with tag: schema (reset)
schemadiff: Vitess In-memory Schema Diffing, Normalization, Validation and Manipulation

Introducing schemadiff, an internal library in Vitess that has been one of its best-kept secrets until now. At its core, schemadiff is a declarative, programmatic library that can produce a diff in SQL format of two entities: tables, views, or full blown database schemas. But it then goes beyond that to normalize, validate, export, and even apply schema changes, all declaratively and without having to use a MySQL server. Let's dive in to understand its functionality and capabilities.

Vitess Schema Tracking

What is Schema Tracking? # In a distributed relational database system, like Vitess, a central component is responsible for serving queries across multiple shards. For Vitess, it is VTGate. One of the challenges this component faces is being aware of the underlying SQL schema being used. This awareness facilitates query planning. Table schemas are stored in MySQL’s information_schema, meaning that they are located in a VTTablet’s MySQL instance and not in VTGate.

A beginner’s guide to database multitenancy

Introduction In software terminology, multitenancy is an architectural pattern which allows you to isolate customers even if they are using the same hardware or software components. Multitenancy has become even more attractive with the widespread adoption of cloud computing. A relational database system provides a hierarchy structure of objects which, typically, looks like this: catalog … Continue reading A beginner’s guide to database multitenancy →

The post A beginner’s guide to database multitenancy appeared first on Vlad Mihalcea.

ClickHouse in a General Analytical Workload (Based on a Star Schema Benchmark)

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.

We have mentioned ClickHouse in some recent posts (ClickHouse: New Open Source Columnar Database, Column Store Database Benchmarks: MariaDB ColumnStore vs. Clickhouse vs. Apache Spark), where it showed excellent results. ClickHouse by itself seems to be event-oriented RDBMS, as its name suggests (clicks). Its primary purpose, using Yandex Metrica (the system similar to Google Analytics), also points to an event-based nature. We also can see there is …

[Read more]
Percona Live Featured Session with Evan Elias: Automatic MySQL Schema Management with Skeema

Welcome to another post in the series of Percona Live featured session blogs! In these blogs, we’ll highlight some of the session speakers that will be at this year’s Percona Live conference. We’ll also discuss how these sessions can help you improve your database environment. Make sure to read to the end to get a special Percona Live 2017 registration bonus!

In this Percona Live featured session, we’ll meet Evan Elias, Director of Engineering, Tumblr. His session is Automatic MySQL Schema Management with SkeemaSkeema is a new open source CLI tool for managing MySQL schemas and …

[Read more]
MySQL Character encoding – part 2

In MySQL Character encoding – part 1 we stated that the myriad of ways in which character encoding can be controlled can lead to many situations where your data may not be available as expected.

UTF8 was designed on a placemat in a New Jersey diner one night in September or so 1992.

Setting MySQL Client and Server Character encoding.

Lets restart MySQL with the correct setting for our purpose, UTF8. Here we can see the setting in the MySQL configuration file, in this case /etc/mysql/my.cnf.

character-set-server = utf8

This change is then reflected in the session and global variables once the instance is restarted with the new configuration parameter.

mysql> SELECT …
[Read more]
Getting started with Performance Schema and MySQL Workbench 6.1.2 Beta

MySQL’s Performance schema is a relatively new tool for measuring performance and MySQL Workbecn 6.1.2 is the latest beta of that software. I have not had a lot of time to play with performance schema but now I am taking my first steps with the help of Workbench. Startup Workbench and you will find under the Navigator an item labeled Performance Schema Setup. Flip the toggle from OFF to ON and then start exploring.

InnoDB Buffer stats by Schema are show here — one of more than twenty pre-established metrics available.

Now you can run queries and see what the costs are, where the server is waiting, or what indexes remain unused. You can even use workbench to alter the options file to setup other Performance Schema instruments. Trying various settings for optimizer_search_depth is simple with the …

[Read more]
Translated slides from my seminar about using Performance Schema for MySQL troubleshooting at Devconf 2013

Few weeks ago I asked my friends who speak both English and Russian if it is worth translating slides about Performance Schema which I prepared for a seminar at Devconf 2013. They said it is. Today I finished translation and uploaded slides to SlideShare.

Strictly speaking simple translation of slides is not enough, because they were created for the seminar where I was going to explain what they mean. I think I need to repeat same seminar, this time in English language. But if you have rough imagination about what Performance Schema is and need hints for practical use you will find such suggestions in the slides. You will also find ready-to-use queries which you can use to troubleshoot most frequent performance issues.

Enjoy!

Can MySQL use primary key values from a secondary index?

In the article about the role of a primary key, I mentioned that a secondary index in an InnoDB table consists not only of the values of its member columns, but also values of the table’s primary key are concatenated to the index. I.e. the primary key contents is part of every other index.

Assuming the following table structure:

CREATE TABLE `bets` (
  `id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `game_id` int(10) unsigned NOT NULL,
...
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB

Here is the visualization:

If MySQL could use in queries these implicitly added values, it would maybe allow to save some space on listing the primary key columns at the end of an index explicitly. Let’s check various cases.

Row filtering

[Read more]
Optimizing MySQL performance with accurate keys

MySQL performance is largely defined by keys and how efficiently queries can use them. As you scale, at certain point it isn’t enough anymore to just have any indexes and still get a good performance in return. You have to really figure them out and allow your queries to do less work, as little work as possible.

The approach presented in this article can sometimes help designing such good, efficient indexes. As a consultant, I have to rely on it myself from time to time, having to optimize a query that works in a database I know nothing about.

Let’s assume there is an application, which collects user activity in various places. The application uses a poorly indexed database, so there are plenty of examples to choose from. Our example query performs a full table scan, which means it reads all rows from the table it uses. It is also among the most popular statements executed by application.

mysql> EXPLAIN …
[Read more]
Showing entries 1 to 10 of 24
10 Older Entries »