Fri, 12 Apr 2024 13:36:18 +0000 en-US hourly 1 https://wordpress.org/?v=6.5.2 https://www.percona.com/blog/wp-content/uploads/2023/02/cropped-percona-favicon-32x32.png 32 32 76301791 https://www.percona.com/blog/partially-rolling-back-a-transaction-in-mysql-or-postgresql/ https://www.percona.com/blog/partially-rolling-back-a-transaction-in-mysql-or-postgresql/#respond Fri, 12 Apr 2024 13:36:18 +0000 https://www.percona.com/blog/?p=95737 This short write-up focuses on a different transaction control behavior of databases. Though this is not unusual, I decided to write an article on rolling back transactions to a particular point. I selected this topic because I found many people are not aware of this feature in databases.DescriptionEvery ACID-compliant RDBMS follows the “All or None” […]]]> https://www.percona.com/blog/partially-rolling-back-a-transaction-in-mysql-or-postgresql/feed/ 0 95737 https://www.percona.com/blog/segmentation-fault-a-dba-perspective/ https://www.percona.com/blog/segmentation-fault-a-dba-perspective/#respond Fri, 02 Feb 2024 11:00:43 +0000 https://www.percona.com/blog/?p=88748 Segmentation fault - A DBA perspective INTRODUCTION On occasions, DBAs come across segmentation fault issues while executing some queries. However, this is one of the least explored topics till time. I tried to search for details related to segmentation fault on the internet and found many articles, however it failed to quench my thirst as none of them had an answer I was looking for. So, I decided to gather information and write detailed information about this issue. In order to understand “segmentation fault”, it is inevitable to know the basic idea of segmentation and its implementation in C programming. In this blog, I will also cover a scenario that causes “segmentation fault”. BASIC UNDERSTANDING In order to understand segmentation fault, it is necessary to understand memory management methods for processes. When we need to execute any program, it should be loaded into memory first. Inside the memory, they can be allocated any available space. When a program leaves the memory, space becomes available, however, the OS may or may not be able to allocate vacant memory space to another program or process as it has some issues. As the amount of space required by the new program may be higher than the space available in a fragment; the program should be broken into different chunks before it is loaded into memory, due to which memory management becomes challenging because it leads to fragmentation. In order to overcome these issues, the concept of paging and segmentation was introduced where physical address space and virtual address space were designed. A detailed description of these concepts are as below. Paging This was designed to allow non-contiguous space allocation to processes. Here, memory is divided into equal sizes of partitions where the code of a program resides. The chunks in main memory are called frames, while they are called pages in the secondary(or HDD). In order to handle memory management, a structure called memory management unit(MMU) is built, which divides memory blocks in2 major sections: logical address space and physical address space. Logical address space - it comprises logical addresses that are generated by CPU for program Physical address space - it has physical addresses that are pointers to actual locations in memory. In order to perform actual translation of a logical address to the physical address, MMU needs to perform memory mapping operations, which can be accomplished by another structure called page table. A page table has actual references to relevant physical addresses for logical addresses. The figure below describes the same. Segmentation This scheme was introduced to overcome disadvantages with paging; it works similar to paging. Instead of fixed size pages, it creates different sizes of segments that are based on program code. In this case we do not need physical address space. Here, a segment table manages everything. Here, virtual(logical) to physical address translation is a little easier as segment tables store adequate information. I do not dive into this topic further as it requires a bit more technical understanding. The purpose of adding this section was to have some basic understanding of mapping from logical to physical addresses. WHAT IS SEGMENTATION FAULT As explained above, the CPU first fetches a logical address, and by using a page table or a segment table, it finds/calculates the physical address of the desired memory location. That is how memory management works. In an attempt to access the desired location, we sometimes come across some issues that are as described below. On occasions, after calculating the physical address using a page/segment table, the program comes across the issue that required contents(piece of code, variables or anything else) are not available in the physical memory location. This phenomenon is called “page fault”. This is not unusual and doesn’t affect the course of the execution as it just loads desirable items in memory. Another one is a classical case of inaccessible memory location. When the generated physical address points to a physical location that is not accessible by the program. This is called “segmentation fault”, which terminates the process execution. This happens when a program tries to access a read-only portion of memory or another program’s space. Although the segmentation fault has been maligned as a showstopper, it is still mandatory as it is a mechanism to provide protection against any internal corruption. Note:- segmentation fault has nothing to do with segmentation memory management method. A REPRODUCIBLE SCENARIO While exploring at code-level there are a number of scenarios that result in a segmentation fault, such as buffer overflows, stack overflows and so on. However, This blog is written from the database perspective, hence, I would not prefer to dive into those scenarios as they are very high-level programming concepts. In this section, I will focus on a scenario in PostgreSQL database that causes segmentation fault. This is the one that I came across once where the database gets restarted due to “segmentation fault”. Below is a line of code that result into a segmentation fault on PostgreSQL 13.4 and 12.8 CREATE SCHEMA debug; CREATE TABLE debug.downloaded_images ( itemid text NOT NULL, download_time timestamp, PRIMARY KEY(itemId) ); INSERT INTO debug.downloaded_images (itemid, download_time) VALUES ('1190300','2021-09-07 11:00:10.255831'); BEGIN; CREATE TABLE IF NOT EXISTS "debug"."foo" (itemId TEXT, last_update TIMESTAMP, PRIMARY KEY(itemId) ); DECLARE "test-cursor-crash" CURSOR WITH HOLD FOR SELECT di.itemId FROM "debug".downloaded_images di LEFT JOIN (SELECT itemId, MIN(last_update) as last_update FROM "debug"."foo" GROUP BY itemId) computed ON di.itemId=computed.itemId WHERE COALESCE(last_update, '1970-01-01') < download_time; FETCH 10000 IN "test-cursor-crash"; COMMIT; The above example is taken from the hyperlinked page. By doing some further analysis, it came to the light that it creates issues with LEFT JOIN only. In the case of an equi-join, it works as expected. This error was fixed in later versions of PostgreSQL. CAUSES As described above, the actual cause of this error is trying to access a memory address that is not accessible by the program, and there are various reasons for the same to happen. However, sophisticated users have limited understanding of such concepts, due to that, I will try to explain in the simplest possible terms. The following are possible causes for segmentation fault. Operating system issues Buggy OS kernel Faulty hardware(specifically memory) Bug in a product(e.g. PostgreSQL, MySQL) Database corruption Though the scope of this error is not limited to above mentioned reasons only, these are most probable ones. In order to know the root cause of the issue, one needs to troubleshoot it with the help of programmers. TROUBLESHOOTING To delve into the root cause of segmentation fault, it is imperative to install debug symbols and enable creation of a core dump on failure. This helps analyze the issue and shows what function or a part of code causes the issue. If requirements do not meet, it is not able to generate the core dump and it becomes impossible to trace the issue. Enable core dump generation Every database has different methods to generate core dump files. In order to enable generation of core dump, one needs to set some kernel settings as below. # echo 'kernel.core_pattern=/var/crash/core-%e-%p' >> /etc/sysctl.conf # ulimit -c unlimited Here, any other path can be used instead of /var/crash. Enable debugging Debug symbols enable code-level debugging. It shows details about the file being executed and the line of the code where the execution is happening. It is a responsibility of software developers to build debug symbols. In PostgreSQL, debug symbols can be enabled at the time of installation as below. # ./configure CFLAGS="-O0 -g3" Also, there are certain packages available in PostgreSQL, such as postgresql-12-dbg In case of MySQL, the following command during the source code installation may turn on debugging. # cmake -DWITH_DEBUG=1 Allow database to generate core dumps After enabling the core dump generation and debugging, it is important that databases should also collaborate with the host OS to generate core dump. Hence, the database should be started with an option to create core files. In order to accomplish this, one should start the database with such an option. In the case of PostgreSQL, the pg_ctl command should be started with the -c option as shown below. $ /usr/local/pgsql/bin -D -c start While in MySQL, following lines can be added in my.cnf or my.ini [mysqld] core-file Note :- In an event of a crash, the OS dumps all the contents from memory in the core file. So, before enabling, be sure you have sufficient space to accommodate the core dump. Debugging core files Core files are version specific, and they can be read with the binary of a specific version of the database. Another version’s binary file cannot read the core file generated by the current version of the database. Like, the core file generated by MySQL 8 cannot be read by mysql binary from any other version. The core dump can be traced by Gnu debugger(gdb). The below one is an example of reading the core dump. $ gdb /usr/local/pgsql/bin/postgres /var/crash/core-postgres-64807 GNU gdb (Ubuntu 9.2-0ubuntu1~20.04.1) 9.2 Copyright (C) 2020 Free Software Foundation, Inc. . . . Reading symbols from /usr/local/pgsql/bin/postgres... [New LWP 64807] [Thread debugging using libthread_db enabled] Using host libthread_db library "/lib/x86_64-linux-gnu/libthread_db.so.1". Core was generated by `postgres: postgres postgres [local] COMMIT'. Program terminated with signal SIGSEGV, Segmentation fault. #0 slot_deform_heap_tuple (natts=2, offp=0x5560dcfd1d58, tuple=, slot=0x5560dcfd1d10) at execTuples.c:930 930 execTuples.c: No such file or directory. (gdb) Apart from that, Valgrind is also one of the tools that can be used to debug the issue. To know more about Valgrind, kindly click on the link. PERCONA’S INITIATIVE As it is described, Segmentation fault is caused by various issues that are sometimes not even in control of Programmers. But in many cases, programs themselves are culprits and trigger segmentation faults; however users have least knowledge of the same. Percona is committed to strengthening an open-source community and has acknowledged the issue. The Percona team strongly believes that users should have knowledge of perils associated with some non-standard modules(or PostgreSQL extensions) that are identified as troublemakers. These details are planned to be added in pg_gather reports. At present, this is in a development phase. The next version of the pg_gather will have these details available. SUMMARY Indeed, segmentation fault is a kind of issue that is not widely explored yet. Having said that, it revisits frequently on database systems due to a variety of reasons. Basically, it surfaces due to an attempt to access an unauthorized area or segment of memory where a normal DBA is least aware of the same. The issue can be troubleshooted by enabling core dump generation and installation of debug symbols. ]]> https://www.percona.com/blog/segmentation-fault-a-dba-perspective/feed/ 0 88748 https://www.percona.com/blog/decoding-sequential-scans-in-postgresql/ https://www.percona.com/blog/decoding-sequential-scans-in-postgresql/#respond Thu, 25 Jan 2024 14:37:22 +0000 https://www.percona.com/blog/?p=93606 In every database product, sequential scans or full table scans are often resource consuming, so almost all the developers and DBAs see such scans as a performance killer. In my opinion, this is a false perception or biased view. In many cases, sequential scans are proven to be a performance booster. However, due to a […]]]> https://www.percona.com/blog/decoding-sequential-scans-in-postgresql/feed/ 0 93606 https://www.percona.com/blog/the-ultimate-guide-to-database-corruption-part-2-b-tree-index-corruption/ https://www.percona.com/blog/the-ultimate-guide-to-database-corruption-part-2-b-tree-index-corruption/#respond Tue, 17 Jan 2023 13:34:27 +0000 https://www.percona.com/blog/?p=84617 This blog is in continuation of my previous blog on the basic understanding of corruption with the subject line The Ultimate Guide to Database Corruption: Part 1 – An Overview. If you have not already gone through it, I encourage you to read it to understand data corruption.IntroductionThis blog focuses on B-tree indexes and various […]]]> https://www.percona.com/blog/the-ultimate-guide-to-database-corruption-part-2-b-tree-index-corruption/feed/ 0 84617 https://www.percona.com/blog/database-corruption-an-overview/ https://www.percona.com/blog/database-corruption-an-overview/#comments Thu, 30 Jun 2022 12:48:38 +0000 https://www.percona.com/blog/?p=81382 Though I am writing this post being a PostgreSQL DBA, this page can be read by anyone because the concept of corruption is the same in every database.After reading this blog post, one should understand what database corruption is and how it may occur.Being DBAs, corruption is something we do not want to ever see […]]]> https://www.percona.com/blog/database-corruption-an-overview/feed/ 3 81382