Troubleshooting MySQL Performance
10:00 - 19:00
Growth. High volumes. The amounts of users, views, data are increasing. Database queries run slower? Clients are hanging in the process list waiting for something? The server is unresponsive? What to do?
MySQL has tools that allow to figure out which changes lead to a loss in performance.
During this tutorial based on my experience of technical support I will show simple-to-use methods that allow to discover and eliminate causes of unwanted behavior. I will describe the order which they should be used in, what method to start with, how to delve deep into a problem and test safely.
We will look at the following subjects:
- creation and usage of a test server;
- nuances of slow query debugging;
- specifics of the locking system;
- influence that hardware and configuration makes on performance;
- data aggregation with minimal strain on the server.
I will describe MySQL server architecture and the specifics of its various parts such as the optimizer, the table engines, the locking system.
We are going to look both at embedded tools that are always available as well as command line utilities that make the job easier. We will pay separate attention to the debugging capabilities that have appeared in the latest versions.
Monitor PostgreSQL with the Elastic Stack
15:00 - 19:00
How to use the Elastic Stack (previously called ELK Stack) to monitor logs is widely known. But it can also give you a complete picture of your PostgreSQL installation:
* System metrics: Keep track of network traffic and system load.
* Logs: Collect and parse PostgreSQL logs.
* PostgreSQL metrics: Gather the most relevant attributes with the dedicated Metricbeat module.
* Queries: Monitor your queries on the wire without instrumenting PostgreSQL with Packetbeat.
And we will do all of that live since it is so easy and much more interactive that way.
10:00 - 19:00
Microsoft SQL Server made a huge leap in development during the last 10 years, establishing itself as a solid choice between "expensive" Oracle and "free" PostgreSQL. The upcoming release of SQL Server VNext expands its sphere of influence and tries to enter a completely new field of Linux-based software. Not to mention the multitude of possibilities that have appeared with the release of SQL Server 2016 SP1 such as the support of the R language, In-Memory OLTP tables and procedures, column indexes, equalization of functionality of the Standard and Enterprise editions, and many more.
If you want to know what SQL Server is capable of compared to its main opponents, what hidden icebergs are to be on the lookout for then this tutorial is for you.
Plan of the tutorial:
1. Why SQL Server? Comparison with Oracle and PostgreSQL.
1.1. Main capabilities of SQL Server.
1.2. Comparing to Oracle and PostrgeSQL: cost, functionality, tools.
1.3. SQL Server certification — is it worth it? How difficult it is?
2. Selecting a SQL Server version and edition based upon your needs and tasks you intend to solve.
2.1. SQL Server 2016, SQL Server Vnext or SQL Server 2014.
2.2. Enterprise, Developer, Standard, Express — which one do you need?
2.3. SQL Server installation on Windows: GUI and Command Line.
2.4. Installing on Linux, Docker.
3. Tuning and configuring SQL Server to your needs.
3.1. Configuration parameters and recommended values.
3.2. System databases in SQL Server and their purpose.
3.3. Selecting sort parameters for SQL Server.
3.4. Setting up user databases: compatibility levels, recovery mode, default values.
4. SQL Server: first steps.
4.1. SQL Server Community — where and how to locate a solutiong for you SQL Server problem in the fastest possible manner.
4.2. Educational databases for SQL Server — source of best practices.
4.3. In-depth introduction to the latest version of SSMS, configuration and hidden possibilities.
5. Data types in SQL Server and why they matter.
5.1. Comparison to Oracle, PostgreSQL, MySQL, SQLite.
5.2. Selecting data types that suit your needs.
6. TSQL — best practices for writing good code.
6.1. Name agreement and why it matters in general, not only in SQL Server.
6.2. Trace flags in SQL Server: which ones are important.
6.3. TSQL — rules of programming.
7. Free and commercial development and administration tools for SQL Server.
7.1. First Responder KIT — 5 stored procedures that should be present at each server.
7.2. sp_whoisactive — almost free and almost ideal stored procedure to monitor activity.
7.3. Ola Hallengren SQL Server Maintenance Solution — a free toolkit for SQL Server maintenance.
7.4. dbatools.io — a Powershell module for SQL Server administration.
8. Query optimization in SQL Server.
9. Using R in SQL Server.
InnoDB Architecture and Performance Optimization
10:00 - 14:00
InnoDB is the most commonly used storage engine for MySQL and Percona Server and is the focus for majority of storage engine development by MySQL and Percona Server teams.
In this tutorial, we will look at InnoDB architecture, including new developments in MySQL 5.7 as well as Percona Server. We will explain how it should be used to get the the best performance from your application.
We will provide specific advice on server configuration, schema design, application architecture, and hardware choices.
This tutorial is updated from its previous version, to cover new MySQL 5.7 and Percona Server InnoDB features in more details.
How to Shrink from 5 Tiers to 2 in a Multitier Microservices Architecture
System administration and data storages
15:00 - 19:00
A typical microservice architecture has five tiers:
- you need a web proxy like NGINX to handle slow clients and to serve static files,
- you need an application web server like Apache or Django to run your application logic,
- you need a caching layer like Memcached or Redis to cache frequently accessed data,
- you need a database proxy to handle sharding and replication, abstracting that stuff away from the application,
- and you need a database management system to store your data.
Five tiers – isn't it too many? Yeah, that's what we think too. So we've come up with an idea to shrink the whole bunch of tiers down to just a couple. The web proxy and database proxy are combined into one tier, now just a proxy. The application server, the cache, and the DBMS are packed into the other tier. Fewer tiers mean less problems and more bonuses like data consistency and better performance. In this session, I'll explain how to do this magic and how it works under the hood.
Turning to Postgres for 1C: overcoming the fears
15:00 - 19:00
During the tutorial we will explore the following cases:
- discovering troublesome areas using 3rd party extensions made by the PG community;
- debugging algorithms in Vagrant environments prior to placing them in production;
- using Jenkins to run performance degradation checks on the existing algorithms;
- exploring various extensions for the out-of-the-box PostgreSQL that facilitate productivity of 1C systems, including search for "hypothetical" indexes;
- handling backup & recovery with Barman;
- troubleshooting problematic queries and formulating approaches toward refactoring;
- studying how proper mount points in the structure of a PG cluster influence performance of 1C.
And for dessert — how to use Docker and Flocker for 1C-based information systems.
The tutorial consits of 4 sections:
- review of the necessary software that has to be installed on computers of 1C maintenance engineers and performance experts, and the order in which it should be applied;
- classic 1C behavioural patterns and their visualisation with pgBadger;
- troubleshooting of problematic queries in order to formulate approaches toward refactoring;
- influence that versions of PG and the platform cast on durability of the production environment.
Postgres Pro Enterprise for developers
10:00 - 14:00
Plan of the tutorial.
1. Postgres Pro EE — what's difference to the vanilla one?
2. 64-bit XID — no need to have mandatory vacuum very 2 billions of transactions.
3. Temporary tables — now faster.
4. Autonomous transactions — logging and irrevocable operations. How to transfer bitcoins safely?
5. Running tasks on schedule and once in a lifetime. What's another way to transfer bitcoins safely?
6. Table partitioning — we know connections and parameters!
7. Multimaster — in an honest manner. Coordinated copies, Согласованные копии, resistance to catastrophic failures, transparent scaling of read operations. JDBC & haproxy.
8. Table transfer — no more speed limitations on import. Terabytes per second are possible.
9. Scheduler hints — the plans will never go wrong.
10. Adaptive Query Planning — how to teach a database server that all women who operate heavy machinery weight more than 100 kg..
11. pg_variables — simply session variables.
And much more!
Call of Postgres: Advanced Operations
10:00 - 14:00
Handling Postgres is not easy. The task is further complicated by the fact that maintaining Postgres also means that you have to take care of the operating system and the hardware. These complicated systems that Postgres relies upon keep constantly evolving. In order to be universal, hardware and OS are set up to guarantee acceptable performance for any sort of workload by default, and this does not necessarily mean to be an optimal solution for running an RDBMS. Therefore, it is considered to be a best practice to recheck hardware, OS and PostgreSQL components' settings before introducing a database into a production environment. However, which of those need to be tuned exactly and how? Unfortunately, the answers to these questions are scattered in pieces all over the hardware vendors' Web sites, OS maintainers' Wiki pages and various how-to's that could be found on the Internet.
This tutorial is designed to unite numerous bits of information on how to configure Postgres, OS and hardware for optimal performance. We will look at different components and their settings. A distinct attention will be paid to methods for diagnosing and troubleshooting issues found in the OS and in PostgreSQL. Maintenance of Postgres on Linux is the main focus of the tutorial.
Thus, the goal of the tutorial is to present a complete picture of how to maintain Postgres, locate sources of the problems and eliminate them.
The tutorial will be of most use to system and database administrators.
PostgreSQL для системных администраторов и DevOps: интенсивный вводный курс
10:00 - 19:00
Системные администраторы и DevOps часто сталкиваются с проблемой — на проекте нет выделенного администратора баз данных, а PostgreSQL все чаще "чудит" по мере роста нагрузки. На то чтобы систематически разбираться в объемной документации времени нет, да это и не ваша задача — когда система интенсивно развивается, админу тоже есть чем заняться.
Этот курс для вас — за один учебный день я максимально сжато расскажу большой объем знаний о СУБД PostgreSQL, необходимый и достаточный для того чтобы начать ее использовать уже завтра и не повторять общеизвестных ошибок. В этом курсе НЕ будет избытка теории — мало какому админу действительно нужно знать тонкости устройства B+-tree или ориентироваться в разнообразии нормальных форм.
Но совсем без теории нельзя: мы пройдемся по тому как в общих чертах устроен PostgreSQL, как происходит обработка транзакций, зачем нужен WAL и autovacuum. Все остальное будет сугубо практическим: как установить, как настроить Linux для работы с Postgres, какие параметры и как нужно обязательно поменять в postgresql.conf, как настроить брокер соеденений и какой именно выбрать, как обеспечить бэкап и отказоустойчивость, что и как мониторить, как и когда настраивать автовакуум и оптимизировать запись на диск.
В завершение мы пройдем по наиболее типовым эксплуатационным проблемам и выясним как их диагносцировать и устранить.
Oracle Database performance daignostics: crash course
10:00 - 14:00
The following topics are covered in the presentation:
- overview of performance issues, where to look at the outside and the inside of database. How to overcome the most popular slowdown reasons;
- session concept in Oracle Database, session states, wait events, event classes;
- built-in diagnostic tools — dynamic performance views (v$ views), real world examples of what data can you get from them;
- Active Session History (ASH) — load capturing mechanism and its implications;
- Automatic Workload Repository (AWR) as an extension to ASH, AWR performance reports;
- Integrated automatic advisors — ADDM and SQL Advisor;
- Real-time SQL execution monitoring.
1. Introduction – overview of basic concepts:
- response time and factors that matter;
- symptoms based analysis – most typical bottlenecks;
- v$ views – a comprehensive multipurpose tool;
- oracle session concept.
2. Practice: using v$sess_time_model to understand time spent by application in different states. Diagnosing underperforming application, extensive SQL parsing issues, time taken by SQL execution.
3. Practice: collecting and understanding cumulative session and system statistics – v$sessstat / v$sysstat. Understanding various statistics used for identifying session key metrics.
4. Theory: oracle session states explained, wait event and wait classes.
5. Practice: v$session_event. Investigating what a session was waiting for in database. Cases: badly configured network, over-commiting, data racing issues.
6. Practice: v$session – all database sessions at a glance. Understanding the importance of instrumenting your code properly. Sneaking into system sessions.
7. Theory: past analysis v$ views limitation. Active session sampling mechanism explained. Automatic workload repository. StatPack – a free alternative.
8. Practice: investigating historical session activity. Generating and studying an AWR report.
9. Theory: self managing database features – ADDM, advisory framework.
10. Practice: running ADDM report and implementing its findings. Enchaining SQL with SQL Tuning Adviser.
11. Practice: using v$sql_monitor, v$session_longops, report_sql_monitor to understand the progress of SQL execution.
Hands on Labs: High Performance Java with PostgreSQL
10:00 - 19:00
Join this full-day hands-on-labs on your favorite tools: PostgreSQL and Java. BYOL (Bring Your Own Laptop), coding session ahead!
Dave Cramer, founder of PostgreSQL JDBC Driver; Vladimir, Sitnikov one of the most recent prolific contributors to pgjdbc; and Álvaro, Java expert and contributor to JDBC's SCRAM functionality, will guide this session.
Initially some theoretical concepts and high performance Java and Postgres will be presented (1h 30). The rest of the session will be guided labs, where you, as an attendee, will have the opportunity to learn and improve your Java performance with PostgreSQL through self-paced labs, with different levels of difficulty, and with the expert guidance and help from the three instructors.
Among others, the following topics would be covered:
- High Performance JDBC
- pgjdbc tips & trics
- Debugging performance with flame graphs
- Connection pools and performance
- Java versions performance -> Benchmarking from Java6 to Java9
- Computation to the data -> pljava and/or other java in the JVM
Basic to medium Java expertise is required. And a working laptop with Java and PostgreSQL running :)