DEV Community: Syed Umer Tariq

Window Functions In Mariadb (part 2)

Syed Umer Tariq — Thu, 25 May 2023 08:52:34 +0000

AVAILABLE WINDOW FUNCTIONS

MariaDB offers a wide range of built-in window functions to cater to various analytical requirements. These functions include popular ones such as SUM(), AVG(), COUNT(), MIN(), MAX(), and more. Additionally, specialized functions like RANK(), DENSE_RANK(), ROW_NUMBER(), LAG(), and LEAD() enable advanced analytical computations.

USE CASES

Window functions find utility in a multitude of scenarios. This section explores some practical use cases where window functions can be applied effectively. Examples include calculating moving averages, generating cumulative sums, identifying top-n records, calculating percentage shares, and detecting trends over time.

PERFORMANCE

While window functions provide immense analytical capabilities, it is essential to consider their performance implications. As window functions operate on a set of rows, their execution requires careful optimization to ensure efficient query processing

CONCLUSION

Window functions in MariaDB empower users to perform complex analytical computations while maintaining row-level details. With its robustness and performance, MariaDB continues to be an excellent choice for utilizing window functions in relational database environments.

Window Functions In Mariadb (part 1)

Syed Umer Tariq — Thu, 25 May 2023 08:49:01 +0000

One of the most important features which make dealing with complex computations easy are window functions. The article goes in depth about the window functions in Mariadb (concept, syntax and use cases).

A window function in MariaDB allows users to perform calculations on a specific set of rows known as a "window." Unlike traditional aggregate functions that collapse rows into a single result, window functions maintain the individual row structure while performing calculations across a defined window frame.

SYNTAX OF WINDOW FUNCTIONS

To use window functions in MariaDB, the OVER() clause is employed in conjunction with the desired function. The OVER() clause specifies the window frame and partitioning criteria. The syntax for window functions in MariaDB is as follows:

function_name() OVER (
    [PARTITION BY partition_expression]
    [ORDER BY order_expression [{ASC | DESC}]]
    [frame_clause]
)

PARTITIONING AND ORDER BY IN WINDOW FUNCTIONS

Partitioning allows the data to be divided into distinct groups based on one or more columns. By using the PARTITION BY clause within the window function, users can perform calculations separately for each partition. Ordering, on the other hand, allows rows to be arranged in a specific sequence within each partition. This is accomplished using the ORDER BY clause in conjunction with the window function.

FRAME CLAUSES

Frame clauses define the window frame, i.e., the subset of rows used in the calculation. They specify the boundaries of the window in relation to the current row. MariaDB supports three types of frame clauses: ROWS, RANGE, and GROUPS. Each has its own significance and usage scenarios.

The article is continued in next part which discusses about currently available window functions, their use cases and performance. link_part2

Dynamic Columns in Mariadb (Part 2)

Syed Umer Tariq — Mon, 15 May 2023 00:38:10 +0000

In this part we will see why we use dynamic columns in mariadb basically its advantages along with the limitations of using dynamic columns

Benefits of Dynamic Columns

Flexibility: Dynamic columns allow for the storage of complex and variable data structures in a single column. This can simplify the database schema and reduce the number of tables and columns required.
Scalability: Dynamic columns can scale to handle large and complex datasets, as they can store a wide range of data structures and formats.
Simplified Data Management: Dynamic columns can simplify data management by allowing for the creation of more flexible and adaptable database schemas. This can reduce the need for complex ETL processes and data transformations

Limitations of Using Dynamic Columns

Like every feature dynamic columns also comes up with some trade offs.

Complexity: Dynamic columns can be more complex to work with than traditional columns, as they require the use of specialized functions and operators to manipulate and retrieve data.
Storage Overhead: Dynamic columns require additional storage overhead to store the binary data associated with the column. This can increase the storage requirements of the database.
Limited Compatibility: Dynamic columns are a feature specific to MariaDB, and may not be compatible with other database systems or tools. This can limit the portability and interoperability of data stored in dynamic columns.

Conclusion

Dynamic columns are very important and useful feature of mariadb which can help create flexible schemas through which variable data structures can be stored in a single columns. They can help in simplifying database design and reduce the need for complex ETL processes and data transformations. But at the same time it is really important to realize that dynamic columns should be used by first analyzing and understanding what type of data would be stored and using dynamic columns is beneficial or not as it also comes up with trade-offs.

Dynamic Columns in Mariadb (Part 1)

Syed Umer Tariq — Mon, 15 May 2023 00:18:53 +0000

In this article, we will explore the concept of dynamic columns in MariaDB, including how they work, how to create and use them, and their benefits and limitations.

Dynamic Columns

Dynamic columns are a feature in MariaDB that allow the creation of columns that can store data in a flexible and scalable manner. Unlike traditional columns, which have a fixed data type and length, dynamic columns can store data structures of varying complexity, including arrays, maps, and even entire JSON documents.

Dynamic columns work by storing the data in a binary format that can be parsed and manipulated by MariaDB using a set of functions and operators. This allows developers to create and store complex data structures in a single column, without the need for multiple columns or tables.

Creation and Use of Dynamic Columns

Creating a dynamic column in MariaDB is relatively straightforward. To create a dynamic column, you need to specify the column type as DYNAMIC when creating the table. For example, the following SQL statement creates a table with a dynamic column:

CREATE TABLE my_table (
  id INT NOT NULL PRIMARY KEY,
  dynamic_column DYNAMIC
);

Once you have created a table with a dynamic column, you can insert data into the column using the INSERT statement. The data should be in the binary format expected by the dynamic column.

To retrieve data from a dynamic column, you can use the dynamic column functions and operators provided by MariaDB. These functions and operators allow you to extract and manipulate data stored in the dynamic column, and can be used in SQL queries and stored procedures.

Here is an example of how to use the dynamic column functions to extract data from a dynamic column:

SELECT id, DYNAMIC_COLUMN_GET(dynamic_column, 'key1') AS value1, DYNAMIC_COLUMN_GET(dynamic_column, 'key2') AS value2
FROM my_table;

This query retrieves the id, value1, and value2 fields from the my_table table. The DYNAMIC_COLUMN_GET function is used to extract the values associated with the key1 and key2 keys in the dynamic column.

Benefits and Limits of Dynamic columns are discussed in next part part-2

Views In Mariadb (part 2)

Syed Umer Tariq — Wed, 26 Apr 2023 14:57:17 +0000

Creating and using views in mariadb

Views can be created in a following simple manner in mariadb using CREATE VIEW keyword followed by SELECT statement as done below

CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;
The above statement creates a view with view_name name which retrieves column1, column2 from table table_name

Now we can query the view as following

SELECT * FROM view_name

Updating Views

Views can also be updatable based on the certain criteria which has to be met (like no subqueries or aggregate functions). If the view is updatable then only the data can be inserted, deleted and updated in a view. Updating the data in a view doesn't actually update the data in a view since view is only the container for select statement instead it updates the data in underlying original tables.

Dropping Views

Finally the views can be dropped as tables, indexes and other objects in databases using DROP statement.
Following is an example of dropping a view

DROP VIEW view_name

Views In Mariadb (part 1)

Syed Umer Tariq — Wed, 26 Apr 2023 14:43:10 +0000

Views in mariadb are used for simplicity, access management and consistency. Views are basically virtual tables that do not store the data themselves instead take the data from base tables and show them as a single virtual table. We will see below the importance of views and how to create and use them.

Views are simply the saved select statements which help us obtain data from one or more tables. These do not speed up the execution as these are only the saved select queries and do not save the retrieved data so they have to be run again and again each time the view is executed.

Advantages Of Views in Mariadb

Views have following advantages in databases

Simplicity

Views allow you to encapsulate complex queries with joins to simplify the table access. Instead of typing a long and complex query in multiple applications, you can create a view based on the query and use the view name in the applications.

Security

In terms of security, you can grant a user account to access partial data of the base tables through a view. By doing this, you can hide the sensitive data from the users who do not have the privilege to view it.

Consistency

Views can be used to encapsulate business logic and formulas and make them consistent across applications.

In the next part we will see how to create and use the views link for part2

Execution Plan In Mariadb (2nd Part)

Syed Umer Tariq — Sat, 15 Apr 2023 18:22:11 +0000

Interpreting the Execution Plan

The execution plan can be difficult to read at first, but with some practice, you can learn to interpret it effectively. Here are some of the most important columns in the execution plan table:

id: This is a unique identifier for each step in the execution plan. The id column is used to show the order in which the steps will be executed.

select_type: This column describes the type of SELECT statement that is being executed. There are several possible values for this column, including SIMPLE, PRIMARY, SUBQUERY, and UNION.

table: This column shows the name of the table that is being accessed.

type: This column shows the type of access that is being used to access the table. There are several possible values for this column, including ALL, index, range, and ref.

possible_keys: This column shows the indexes that could potentially be used to access the table.

key: This column shows the index that is actually being used to access the table.

rows: This column shows the number of rows that will be examined for each step in the execution plan.

Using the information in the execution plan, you can identify areas where you can optimize your query. For example, if you notice that the optimizer is performing a full table scan instead of using an index, you may want to consider adding an index to the table.

CONCLUSION

The execution plan is a powerful tool that can help you optimize the performance of your MariaDB queries. By analyzing the plan, you can identify areas where you can improve the efficiency of your queries, resulting in faster and more responsive database applications. If you are new to MariaDB, it is highly recommended that you spend some time learning how to use the execution plan, as it can be an invaluable resource for optimizing your database queries.

Execution Plan In Mariadb (1st Part)

Syed Umer Tariq — Sat, 15 Apr 2023 18:19:35 +0000

MariaDB is an open-source database management system that is a fork of MySQL. It is widely used by developers for its reliability, scalability, and ease of use. In this article, we will discuss the execution plan in MariaDB, which is a powerful tool that helps developers optimize the performance of their database queries.

What is an Execution Plan?

An execution plan is a detailed description of how MariaDB will execute a specific SQL query. It contains information about the order in which tables are accessed, the indexes used, and the operations performed on the data. The execution plan is generated by the query optimizer, which is a component of the MariaDB server that is responsible for determining the most efficient way to execute a query.

The query optimizer considers a variety of factors when generating an execution plan, including the size of the tables involved, the complexity of the query, and the available indexes. By analyzing these factors, the optimizer can choose the best strategy for executing the query, which can result in significant performance improvements.

How to Generate an Execution Plan

MariaDB provides several ways to generate an execution plan for a SQL query. The most common way is to use the EXPLAIN keyword, which displays information about the execution plan in a tabular format. To use the EXPLAIN keyword, simply prefix your SQL query with it, like this:

EXPLAIN SELECT * FROM my_table WHERE my_column = 'value';

When you run this query, MariaDB will display a table that describes the execution plan for the query. The table will contain information about the tables that are accessed, the indexes used, and the operations performed on the data.

NOTE:

Interpretation of Execution Plan is explained in 2nd part of the article. (2nd part)

Configure Size of Innodb Buffer Pool

Syed Umer Tariq — Sat, 25 Mar 2023 21:55:51 +0000

The size of the InnoDB Buffer Pool can be configured by setting the innodb_buffer_pool_size system variable. the InnoDB Buffer Pool should usually be between 50%-75% of the memory available.

There are two ways two configure innodb buffer pool size which are given as following

1) The size of the InnoDB buffer pool can be changed dynamically by setting the innodb_buffer_pool_size system variable using the SET GLOBAL statement which requires SUPER privilege.

To configure the InnoDB Buffer Pool with the SET GLOBAL statement, use the following procedure:

Connect to the server using MariaDB Client as the root@localhost user account or another user account with the SUPER privilege
Set the innodb_buffer_pool_size system variable to the new size using the SET GLOBAL statement.

For example, to set the size to 4 GB:

SET GLOBAL innodb_buffer_pool_size=(4 * 1024 * 1024 * 1024);

Confirm that the resize operation has been completed by querying the Innodb_buffer_pool_resize_status status variable using the SHOW GLOBAL STATUS statement

innodb_buffer_pool_size needs to be set in a group that will be read by MariaDB Server, such as [mariadb] or [server]. When set in a configuration file, the value supports units such as "M" (Megabyte), "G" (Gigabyte), etc. For this method server restart is needed to reflect the changes.

Important Concepts Related To Buffer Pool

Syed Umer Tariq — Sat, 25 Mar 2023 21:44:30 +0000

Buffer Pool
Buffer pool is an in-memory cache used for faster access to frequently accessed data. It stores data and indexes reducing disk I/O.
Working of Buffer Pool

Buffer pool mechanism works on the the two sublist concepts the new sublist and the old sublist where every item when accessed first time gets on the top of old sublist and on being called while in the old sublist moves to the top of new sublist. By default 37% of space is reserved for old-block.

Size of Buffer Pool

The size of the buffer pool is determined by innodb_buffer_pool_size system variable. The size of buffer pool should be adjusted per your needs to see the best performance. In order to configure the size of buffer pool set innodb_buffer_pool_size system variable, the InnoDB Buffer Pool should usually be between 50%-75% of the memory available.

2 ways to configure size of buffer pool

1) The size of the InnoDB buffer pool can be changed dynamically by setting the innodb_buffer_pool_size system variable using the SET GLOBAL statement which requires SUPER privilege.

2)Changing the size of buffer pool by setting innodb_buffer_pool_size system variable in configuration file. Ensure that your custom configuration file is read last by using the z- prefix in the file name so that changes made by you are not overwritten.
innodb_buffer_pool_size system variable in the configuration file.
innodb_buffer_pool_size needs to be set in a group that will be read by MariaDB Server, such as [mariadb] or [server]. When set in a configuration file, the value supports units such as "M" (Megabyte), "G" (Gigabyte), etc. For this method server restart is needed to reflect the changes.

** Saving And Restoring Buffer Pool State**

Innodb stores some percentage of most recently used pages from buffer pool at server shutdown and restores them at server restart. This is managed by innodb_buffer_pool_dump_pct configuration option which is used to reduce warmup period.
innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup system variables should be enabled to allow buffer pool dump at shutdown and restore at server restart.

Some Important Points Related to Buffer Pool

Pages are evicted using a least recently used (LRU) algorithm.
InnoDB reserves additional memory for buffers and control structures, so that the total allocated space is approximately 10% greater than the specified buffer pool size.
The size of each page in the Buffer Pool depends on the value of the innodb_page_size system variable.

Buffer Pool in Innodb

Syed Umer Tariq — Sun, 19 Mar 2023 21:59:34 +0000

Buffer pool like in every storage engine plays an important and pivotal role in Innodb engine. Buffer pool stands out as one of the most important component in storage engine's architecture.

Buffer pool is an area where frequently accessed data from tables and indexes is kept. When data is read from disk, it's loaded into the buffer pool, and subsequent reads can be served from memory instead of from disk which is the whole concept of creating a buffer pool. This can greatly improve performance because memory accesses are much faster than disk accesses.

The buffer pool is configurable, and its size is determined by the innodb_buffer_pool_size configuration option. The default value for this option is 128MB, but it's often recommended to increase it to several gigabytes for high-performance workloads. Out of many configurations the user can do according to its requirements are resizing the buffer pool, splitting the buffer pool into multiple parts to minimize memory contention, when and how to perform read-ahead requests to prefetch pages and when to perform flushing.

Buffer pool metrics can be monitored via Innodb standard monitor from which user can analyze the performance of buffer pool.

WORKING OF BUFFER POOL

When InnoDB needs to read data from a table or index, it first checks if the data is already in the buffer pool. If it is, the data can be read directly from memory. If not, InnoDB reads the data from disk and loads it into the buffer pool. If the buffer pool is full, InnoDB will evict some data to make room for the new data.

LRU (Least Recently Used) Algorithm is the backbone of eviction process through which data is stored in buffer pool.InnoDB keeps track of which pages in the buffer pool were accessed most recently and least recently, and when it needs to evict data, it chooses the least recently used pages to evict.

InnoDB also uses a technique called "adaptive flushing" to manage the buffer pool. When the buffer pool is full and new data needs to be loaded, InnoDB will first try to flush some dirty pages to disk. Dirty pages are pages that have been modified in memory but haven't yet been written to disk. By flushing these pages to disk, InnoDB can free up space in the buffer pool for new data.

Conclusion

The buffer pool is a critical component of InnoDB's architecture, and it's designed to improve performance by caching frequently accessed data in memory. By keeping data in memory, InnoDB can avoid disk accesses and serve requests much faster. The buffer pool is configurable, and its size should be set based on the workload and available memory. With a properly sized buffer pool, InnoDB can deliver excellent performance for transactional processing workloads.

Innodb Storage Engine (part 2)

Syed Umer Tariq — Tue, 28 Feb 2023 13:51:43 +0000

Performance and Scalability

InnoDB is designed for high-performance and high-concurrency environments. It provides good performance for both read-intensive and write-intensive workloads.

InnoDB uses a buffer pool to cache frequently-accessed data in memory, which reduces disk I/O and improves performance. InnoDB also supports adaptive hash indexes, which can be used for frequently accessed small tables.

InnoDB also supports multi-core CPUs and can take advantage of parallelism for certain operations, such as index creation and bulk data loading.

InnoDB is also scalable and can handle large datasets. InnoDB supports tablespaces, which can be used to partition data across multiple physical storage devices. InnoDB also supports online schema changes, which allows for changes to the database schema without blocking concurrent transactions.

InnoDB in MariaDB And different from other Storage Engines in Mariadb

MariaDB is a community-developed fork of MySQL that aims to be a drop-in replacement for MySQL with additional features and improvements. MariaDB uses the same storage engines as MySQL, including InnoDB.

Mariadb has multiple storage engines apart from Innodb and some of them are following MyIsam, Aria, Xtradb but we need to see the differences of innodb from the rest of storage engine which has made it the default storage engine in mariadb from version 10.2.

InnoDB is a popular storage engine in MariaDB, and it differs significantly from other storage engines such as MyISAM, Aria, and XtraDB. InnoDB provides transactional processing, MVCC, and row-level locking, which allows for high concurrency and data consistency. InnoDB also provides scalability features such as adaptive hash indexes, parallelism, and online schema changes.

MyISAM, on the other hand, is faster for read-intensive workloads and has a smaller disk footprint for small tables. Aria is a simpler and more lightweight alternative to InnoDB that provides similar transactional features. XtraDB is a fork of InnoDB that includes performance and scalability improvements over the standard InnoDB.

Choosing the right storage engine depends on the specific needs of the application. InnoDB is a good choice for applications that require high concurrency, data consistency, and scalability, while MyISAM may be more suitable for read-intensive workloads with small tables. Aria is a good alternative to InnoDB for simpler applications, while XtraDB is a good choice for high-performance and high-concurrency environments