Linux IO internals for PostgreSQL administrators
Input-output (IO) performance issues have been on DBAs’ agenda since the beginning of databases. The volume of data grows rapidly and time is of an essence when one needs to get necessary data fast from the disk and, more importantly, to the disk.
For most databases it is relatively easy to find checklist of recommended Linux settings to maximize IO throughput and, in most cases, this checklist is indeed good enough. It is however essential always to understand how the optimisation of those settings actually works, especially, if you run into corner cases.
Другие доклады
-
Dmitry Yuhtimovsky Gilev.ru
Debugging 1С code in PostgreSQL DBMS
- 1C:Enterprise 8 and PostgreSQL 9 interoperability 1.1 Changes in new 1C platform versions 1.2 v81c_data and v81c_index schemas 1.3 Sending 1C queries to SQL 1.4 Using 1C technological log events for PostgreSQL diagnostics
- Analyzing queries that affect PostgreSQL performance 2.1 A free tool for automating log parsing 2.2 Pareto principle in action 2.3 Installation and configuration of the tool 2.4 A case study of query optimization 2.4.1 An issue in a PostgreSQL query 2.4.2 Finding non-optimal operations in a query 2.4.3 Resolving inefficiencies
- PostgreSQL statistics for performance diagnostics 3.1 Comparing Postgres with MS SQL Server 3.2 Troubleshooting locks 3.3 Operating load diagnostics 4 Case studies by the gilev.ru team
-
Aleksei Plotnikov Skype
Database platform architecture and administrating PostgreSQL in Skype
Most of the main Skype services use a database platform based on PostgreSQL and other open-source technologies, such as Skytools, plProxy, pgBouncer, etc. This platform consists of several hundreds of servers with thousands of databases, which process hundreds of thousands of transactions per second. At the same time, the platform architecture allows its users (applications and their developers) to work with "logical" databases, without any worries about their real “physical” structure.
Our Skype Database Platform team is responsible for the database platform infrastructure. We develop automation systems for various processes that help us ensure service reliability and facilitate development, testing, and deployment of code. In this presentation, I will outline the database platform architecture, review its main components, and tell you about the methods we use in our every-day work to ensure high availability, scalability, replication, fault tolerance, and more.
-
Radoslav Glinsky Skype (Microsoft)
Test environment on demand
Do you test your PostgreSQL releases prior to Production in a dedicated test environment? Are you sure that your test environment (shortly Test) is equal to Production and in an appropriate state?
In Skype we were facing multiple challenges associated with database testing:
- Simplifying complex Production architecture of thousands of PostgreSQL instances, interconnected with RPCs and replications, infrastructure servers and external DB scripts, into their Test counterparts.
- Constantly growing hardware requirements, insufficient cleanup of data generated in Test.
- Differences between Test and Production were appearing and accumulating. Recognizing and fixing them required lots of effort.
-
Dmitry Lebedev BestPlace
Researching GIS data with PostGIS and adjacent toolset
Nowadays one can make a decent urban research based simply on public datasets, making interesting and unexpected insights. In the presentation, I'll show examples of these calculations in PostGIS, the industry standard de-facto.
But just PostGIS is not enough. You need tools to import, verify and visualize the data. It's critically important to visualize the data live, to debug your calculations and shorten iterations. I'll describe all these steps:
- Collecting the data: public API, OpenStreetMap; direct user input.
- 3rd party APIs for calculations.
- Visualization of GIS and other sorts of data: QGIS, Matplotlib, Zeppelin integrated with PostGIS.
- Debugging the calculations: live visualization (Arc, QGIS, NextGIS Web)
- Scripting and minimizing the chores: Makefile, Gulp