Tag: collation

Feed: Planet PostgreSQL. The PostgreSQL caching system has always been a bit of a miracle to many people and many have asked me during consulting or training sessions: How can I figure out what the PostgreSQL I/O cache really contains? What is in shared buffers and how can one figure out? This post will answer this kind of question and we will dive into the PostgreSQL cache.Creating a simple sample databaseBefore we can inspect shared buffers we have to create a little database. Without data the stuff we are going to do is not too useful: [email protected] ~ % createdb ... Read More

Feed: Planet PostgreSQL. PostgreSQL 10 introduced native partitioning and more recent versions have continued to improve upon this feature. However, many people set up partition sets before native partitioning was available and would greatly benefit from migrating to it. This article will cover how to migrate a partition set using the old method of triggers/inheritance/constraints to a partition set using the native features found in PostgreSQL 11+. Note these instructions do not cover migrating to PG10 since some key features that make this migration easier were not yet implemented. It is highly recommended to move to PG11 or higher if ... Read More

Feed: Planet PostgreSQL. FreshPorts had a cache clearing issue recently. It lingered. For a long time. It took me a while to figure it out. It turned out to be a Python 3 compatibility issue. In this post: PostgreSQL 12.1 FreeBSD 12.1 I will outline how database changes invoke external scripts asynchronously which then clear the cache. I will go into the debugging of the issue and how the bug was fixed. FreshPorts caches data on disk. When you go to visit a page, the website checks the cache on disk, and if not found, it runs the queries, creates ... Read More

Feed: Planet MySQL; Author: Severalnines; With MySQL 8.0 Oracle adopted a new approach to development. Instead of pushing features with major versions, almost every minor MySQL 8.0 version comes with new features or improvements. One of these new features is what we would like to focus on in this blog post.  Historically MySQL did not come with good tools for provisioning. Sure, you had mysqldump, but it is just a logical backup tool, not really suitable for larger environments. MySQL enterprise users could benefit from MySQL Enterprise Backup while community users could use xtrabackup. Neither of those came with a ... Read More

Feed: Planet MySQL; Author: MySQL Performance Blog; In MySQL 8, Information Schema was basically re-written to utilize the all-New Data Dictionary which made it faster and better in many ways. Yet it also introduces some very counterintuitive behaviors when it comes to how actual and consistent data is.Here is a simple test.  Let’s create a table and insert a bunch of rows in it… and then check table size: mysql> show table status G *************************** 1. row *************************** Name: t Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL ... Read More

Feed: Planet MySQL; Author: MinervaDB; Table of Contents Introduction We have several customers on ClickHouse now for both columnar database analytics and archiving MySQL data, You can access data from ClickHouse with clickhouse-client but this involves some learning  and also limitations technically. Our customers are very comfortable using MySQL so they always preferred a MySQL client for ClickHouse query analysis and reporting, Thankfully ProxySQL works as a optimal bridge between ClickHouse and MySQL client, This indeed was a great news for us and our customers worldwide. This blog post is about how we can use MySQL client with ClickHouse. Installation ... Read More

Feed: Planet MySQL; Author: Severalnines; This is the second part of a two-part series blog for Maximizing Database Query Efficiency In MySQL. You can read part one here. Using Single-Column, Composite, Prefix, and Covering Index Tables that are frequently receiving high traffic must be properly indexed. It's not only important to index your table, but you also need to determine and analyze what are the types of queries or types of retrieval that you need for the specific table. It is strongly recommended that you analyze what type of queries or retrieval of data you need on a specific table ... Read More

Feed: Planet MySQL; Author: Sveta Smirnova; First I want to thank everyone who attended my December 5, 2019 webinar “Introduction to MySQL Query Tuning for DevOps“. Recording and slides are available on the webinar page.Here are answers to the questions from participants which I was not able to provide during the webinar.Q: How to find stored execution plans and optimizer metadata stored in mysql data dictionary (i.e. PS, IS, sys schema)?A: The Optimizer creates the query execution plan each time when MySQL Server executes the query. These plans are never stored.However, some information, used by the optimizer, to create the ... Read More

Feed: Planet MySQL; Author: Jesper Krogh; If you want to back up your table and views, stored procedures, or stored function definitions, you can use mysqldump or mysqlpump to export the schema without the data. However, if you just want the views you need to look for another option. This blog shows how MySQL Shell comes to the rescue. Backup the view definition using MySQL ShellThere are a couple of approaches to get the view definitions. One option is to consider the information_schema.VIEWS view which has the following columns: mysql> SELECT COLUMN_NAME AS Field, COLUMN_TYPE AS Type FROM information_schema.COLUMNS WHERE ... Read More

Feed: Planet PostgreSQL. SQL is query a language that is usually pretty easy to read. However, if people don’t format their queries properly even SQL turn out to be a nightmare. That’s why developers often turn to an SQL beautifier to turn an ugly query into a nicely formatted string. Various tools are available on the web to achieve exactly that.Can the same thing be achieved using only PostgreSQL onboard tools? The answer is yes. This post will show you how to achieve that.How PostgreSQL handles viewsIn PostgreSQL a view is not stored as plain text. Instead, it is stored ... Read More