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.
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.
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.
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]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 Skeema. Skeema is a new open source CLI tool for managing MySQL schemas and …
[Read more]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]
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]
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!
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]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]