A naukri.com initiative
Databases
Mysql
2w
242
Image Credit: Mysql
MySQL: information_schema.tables performance regression
- Queries against i_s.tables should be fast and scale to a million tables. In MySQL 8.x, queries against data dictionary tables do not scale well in case of a large number of tables in a single schema e.g. 313k tables. MySQL 8.x uses hidden InnoDB tables to store the data dictionary. Since queries against the data dictionary are run very often and often implicit, it is a matter of great concern when the system behaves so poorly in such cases.
- Queries against the DD do not scale well, in my case against 313k tables in a single database. Even explicit queries against information_schema tables or SHOW TABLES LIKE statements are slow and a drop database on a schema with 313k tables would never terminate.
- This issue arises due to the heavy usage of these queries for almost everything, including internally and implicitly. This slow down is even worse when the number of tables is large in a single schema, rendering it a matter of concern.
- Asking for a table in the million schema is slow even when asking for tables from other schemas with only 6 tables is slow. Even command show tables like is affected by the slowdown.
- Queries against i_s.tables should be fast and scale to a million tables. But, not only are explicit queries against I_S.TABLES and SHOW TABLES LIKE statements slower, it's also a matter of great concern when it fails in cases such as
- MySQL 8.0 uses hidden InnoDB tables to store the data dictionary, and queries are not scaled well in cases with a large number of tables in a single schema; this causes concern as queries against DD are run often and often implicit.
- Drop a database on a schema with 313k tables would never terminate. Several queries against I_S.TABLES are simpler than the query plan shows and experience worse performance than anticipated.
- This issue causes a severe slowdown since these queries are run often and often implicit, and not just when they're physically sent to the server.e.g. events processing, and distributed transactions.
- Even queries with table_schema from a schema with only 6 tables are slow. Queries against i_s.tables should scale well to millions of tables, but MySQL 8.x has hidden InnoDB tables to store a data dictionary and causes slow downs.
- Queries against the data dictionary tables in MySQL7 were mostly CPU-bound and started to be more IO-bound from MySQL 8. As a result, heavy-disk activity with long latencies caused performance regression in MySQL8 when more than a few thousand tables exist in a schema.
Read Full Article
14 Likes
Mysql
2w
394
Image Credit: Mysql
MySQL: dual passwords
- MySQL allows multiple passwords on a single account for regulatory requirement.
- There is no native view or reporting query that can give an overview of all accounts and their password usage.
- Log analysis with elevated log levels is required to track password usage.
- Rotating machine accounts and using mechanisms like vault for login information distribution is recommended.
Read Full Article
23 Likes
Dbi-Services
2w
143
Image Credit: Dbi-Services
PostgreSQL Conference Europe 2024 – Last day
- PGConf.EU 2024, the PostgreSQL Conference in Athens, was a hub of creativity, expertize sharing and community spirit surrounding PostgreSQL.
- Automated Database Change Management with Flyway and CI/CD Pipelines was a talk of practical insights into automating database migrations, ensuring consistency and reducing deployment risks.
- PG_createsubscriber patch Dev Euler Taveira held a session on Speeding Up Logical Replication Setup, addressing initial data synchronization in logical replication, and suggested optimization strategies to expedite replication setup.
- PostgreSQL Executor: Executing Your Execution Plan was a talk that explained how PostgreSQL executes query plans and provided a clearer understanding for those looking to optimize queries.
- HELP!!1! The Database is Slow!! was a treasure trove of actionable advice for database administrators faced with performance challenges.
- Postgres Platform “Best Practices” for the Modern DBA was the talk that explored the intersection of traditional DBA roles and platform engineering and highlighted how modern DBAs can leverage platform engineering principles to enhance efficiency and adaptability.
- The conference ended with a closing talk that encapsulated the spirit of the event, highlighting the incredible work done by the community and the organizers.
- The article also includes personal reflections from the writer and encourages readers to support the PostgreSQL project regardless of their expertise level.
Read Full Article
8 Likes
Dbi-Services
2w
313
Image Credit: Dbi-Services
2024.pgconf.eu – Bigger than ever, the family is growing fast
- The PostgreSQL community is growing every year and the same happens for pgconf.eu, which happened in Athens, Greece, this year.
- Attending this event is one of the best opportunities to get in touch with the people behind PostgreSQL.
- The conference is about people, with no marketing, and a focus on making PostgreSQL the best open source database.
- The conference offers a mix of technical sessions, discussions, and opportunities to connect with experts in PostgreSQL.
Read Full Article
18 Likes
Discover more
- Programming News
- Software News
- Web Design
- Devops News
- Open Source News
- Cloud News
- Product Management News
- Operating Systems News
- Agile Methodology News
- Computer Engineering
- Startup News
- Cryptocurrency News
- Technology News
- Blockchain News
- Data Science News
- AR News
- Apple News
- Cyber Security News
- Leadership News
- Gaming News
- Automobiles News
Dev
2w
13
Image Credit: Dev
How to implement CREATE TABLE IF NOT EXISTS in Oracle
- Unlike PostgreSQL and MySQL, Oracle doesn't provide a direct CREATE TABLE IF NOT EXISTS syntax.
- The most common and reliable approach is to use a PL/SQL block with exception handling.
- A more verbose but explicit approach is to check the data dictionary first.
- For simple scripts where PL/SQL might be overkill, you can use a single-line alternative.
Read Full Article
Like
Medium
2w
126
Spatie Backup Mysqldump Not Recognized: Troubleshooting on Windows for Laravel Projects
- Building powerful and scalable applications requires a well-chosen technology stack.
- PHP, Python, and JavaScript are popular programming languages for web development.
- MySQL is a reliable and flexible database for storing structured data.
- Laravel, a PHP framework, promotes rapid development and code reusability.
- Windows offers a familiar environment and tools for developers.
- The integration of Programming, MySQL, Laravel, and Windows creates a seamless workflow.
- Challenges include performance optimization, security measures, and database scaling.
- Spatie Backup Mysqldump Not Recognized: Troubleshooting on Windows for Laravel Projects is a common issue.
- Combining Programming, MySQL, Laravel, and Windows provides a powerful platform for building applications.
Read Full Article
7 Likes
Dev
2w
174
Image Credit: Dev
SQL-92 in TPC Benchmarks: Are They Still Relevant?
- The author explores whether queries from analytics benchmarks written on SQL-92 are irrelevant and inefficient in 2021, considering the window functions introduced in SQL:2003.
- The original benchmark comparison between PostgreSQL and DuckDB was based on an execution time that exceeded one minute, caused by an inefficient SQL query.
- Efficient SQL queries perform well with PostgreSQL-compatible databases, compared to inefficient ones.
- The author writes an SQL query to address the business question, which uses standard SQL features established 29 years ago and implemented in all databases.
- The author finds it easier to reason through each step and obtain accurate results using structured SQL queries with a WITH clause to define common table expressions.
- The author recommends focusing on queries that avoid inefficient code to effectively compare performance with PostgreSQL-compatible databases.
- PostgreSQL has prioritized simplicity in its code, which means it may perform poorly with poorly designed queries.
- Indexing tables properly could further optimize query performance.
- The author concludes that running benchmarks with queries that ignore the past 30 years of SQL is not recommended.
Read Full Article
10 Likes
Amazon
2w
326
Image Credit: Amazon
Unlock cost savings using compression with Amazon DocumentDB
- Amazon DocumentDB now supports document compression using the LZ4 compression algorithm with compressed documents up to seven times smaller.
- Document compression can reduce storage usage and I/O cost leading to lower storage and I/O costs.
- You can use the Amazon DocumentDB Compression Review Tool to get a sense of how compressible your data is, before enabling compression.
- Using existing APIs, you can monitor compression status and collection size after compression.
- Compressed documents require less storage space and fewer I/O operations during database reads and writes, leading to lower storage and I/O costs.
- Document compression/decompression requires additional CPU as well as increases read/write latency – but the benefits will outweigh the overhead if you have collections with compressible data.
- You can configure document compression for individual Amazon DocumentDB collections based on collection access patterns and storage requirements.
- Document compression is only supported on Amazon DocumentDB version 5.0 and only collection data is compressed in Amazon DocumentDB.
- You can always enable compression on a collection as your workload changes in future.
- Amazon DocumentDB Compression can be a great way to reduce costs and improve performance for your DocumentDB workloads with compressible data.
Read Full Article
19 Likes
Amazon
2w
318
Image Credit: Amazon
Achieve a high-speed InnoDB purge on Amazon RDS for MySQL and Amazon Aurora MySQL
- Purge is essential for a MySQL database to clean up delete-marked table records and undo logs that are no longer needed for multiversion concurrency control (MVCC) or rollback operations.
- The slow advancement of purge in the background compared to numerous data changes causes poor database performance.
- InnoDB uses undo logs as the key data structure to support MVCC and rollback operations and purge to clean them up.
- The most heavy-duty part of the purge operation is identifying delete-marked table records including those in clustered indexes, secondary indexes, and BLOB columns.
- The number of purge threads, the workload characteristics, and database resource utilization can significantly impact the performance of purge operation.
- Choosing the right instance class, partitioning and sharding strategies can improve the efficiency of the purge operation.
- Using the schema_unused_indexes view in SYS schema to identify and drop the unused secondary indexes is a recommended optimization.
- Monitoring and setting up alarms for the rollback segment history list length metric can warn of the need to optimize database capacity and workload characteristics.
- Purge operation can be an essential tool to optimize a MySQL database's performance.
- Optimizing purge operation needs careful consideration of workload optimization, database capacity planning, and configurations.
Read Full Article
19 Likes
The New Stack
2w
4
Image Credit: The New Stack
How to Run Databases on Kubernetes: An 8-Step Guide
- Running databases on Kubernetes requires persistent data storage and consistency.
- Key concepts to consider when running databases in Kubernetes include: database storage, scaling databases, data consistency and backups, and StatefulSets.
- StatefulSets are a Kubernetes resource for managing stateful applications that ensures pods possess persistent storage.
- To create a database StatefulSet application on Kubernetes you will need to create a StorageClass, PersistentVolume (PV), Persistent Volume Claim (PVC), and MySQL StatefulSet.
- To enable the pods to communicate between each other in Kubernetes, a MySQL StatefulSets headless service is created.
- Performing regular backups and routine restore of databases is important to ensure availability of your Kubernetes workloads and maintain database integrity.
- Monitoring tools such as Prometheus, Grafana and Nagios can be used to monitor database health and notify the engineer if there is a degradation in service or issue with the database.
- As Kubernetes evolves, the support for StatefulSets will increase, making running databases on Kubernetes a powerful solution for modern infrastructures.
- To learn more about what Kubernetes can do for your business, you can read more about it on Andela.
- Using Kubernetes to run databases can be achieved with proper planning and attention to StatefulSets, PersistentVolumes, and persistent storage needs.
Read Full Article
Like
Dbi-Services
2w
265
Image Credit: Dbi-Services
PostgreSQL Conference Europe 2024 – Day 2
- The second day of the PostgreSQL Conference Europe 2024 in Athens covered a range of technical sessions on topics like upgrading PostgreSQL, handling statistics, and using DBA skills in the cloud.
- One session focused on upgrading PostgreSQL installations and highlighted the importance of logical replication and careful schema design for smooth upgrades.
- Another session explored the inner workings of PostgreSQL's statistics collection, providing insights on optimizing query performance.
- A talk from Microsoft announced the first iteration of PostgreSQL Query Store in Azure, offering performance tuning tools for PostgreSQL users.
- The final session explained how PostgreSQL operators in Kubernetes can simplify database management in containerized environments, bridging the gap between traditional administration and cloud-native practices.
Read Full Article
15 Likes
Medium
2w
344
The Comprehensive Guide to SQL: Unlocking the Power of Data
- SQL is a standardized language used to manage and manipulate relational databases.
- The SELECT statement allows you to retrieve data from one or more tables.
- The WHERE clause filters records based on specific conditions.
- JOINs allow you to combine rows from multiple tables based on related columns.
Read Full Article
20 Likes
Amazon
2w
139
Image Credit: Amazon
Visualize vector embeddings stored in Amazon Aurora PostgreSQL and explore semantic similarities
- Amazon Aurora PostgreSQL-Compatible Edition supports the pgvector extension that enables manipulating vector embeddings in relational databases.
- Amazon Bedrock is a fully-managed AWS service that offers various foundation models (FMs), including Amazon Titan Text Embeddings.
- Preparing dataset, generating vector embeddings, storing data and embedding in Aurora PostgreSQL database and importing required libraries are the steps to visualize vector embeddings and explore semantic similarities.
- PCA is used to transform high-dimensional data into a lower-dimensional space, which enables visualizing the underlying structure of the data.
- This article provides a step-by-step guide for preparing dataset, using Bedrock to generate vector embeddings, storing data and embedding in PostgreSQL table, performing dimensionality reduction with PCA technique and using the plotly package to plot a 3D Scatter Plot of Categories.
- The resulting three-dimensional scatter plot shows items with similar meanings clustered closely in the embedding space. This proximity ensures that when we perform a semantic search on this dataset for a specific item, it returns products with similar semantics.
- The integration of vector data types in Aurora PostgreSQL-Compatible opens up exciting possibilities for exploring semantic similarities and visualizing complex data structures.
- By using techniques such as PCA, users can gain valuable insights into their data, uncover hidden patterns, and make informed decisions.
- Clean up the resources used after the completion of tasks to avoid incurring charges.
- The author of this article, Ravi Mathur, is a Sr. Solutions Architect at AWS who provides technical assistance and architectural guidance on various AWS services.
Read Full Article
8 Likes
Cloudblog
3w
305
Image Credit: Cloudblog
What’s new in PostgreSQL 17, now available in Cloud SQL
- Cloud SQL now supports PostgreSQL 17, offering a range of new features and enhancements in security, developer experience, performance, tooling, and observability.
- PostgreSQL 17 introduces the MAINTAIN privilege, offering a more granular level of control over database maintenance tasks with predefined role called pg_maintain.
- The MERGE command is a powerful addition that simplifies data manipulation and improves performance.
- JSON_TABLE function introduces a more intuitive way to convert JSON data into a standard table format, unlike earlier methods.
- PostgreSQL 17 introduces new, more efficient TidStore to store tuple IDs during VACUUM operations, reducing significantly memory consumption.
- Improvement to the ReadBuffer API in PostgreSQL 17 introduces reading multiple consecutive blocks from disk into shared buffers with one system call, benefiting workloads that involve reading multiple blocks.
- PostgreSQL 17 introduces the --filter option in pg_dump, pg_dumpall, pg_restore, providing more fine-grained control over what objects are included or excluded in the dump or restore operation.
- The new pg_wait_events system view provides information about events causing processes to wait and identify performance bottlenecks and troubleshooting database issues.
- The pg_stat_checkpointer system view provides information related to the checkpoint process, allowing you to monitor and analyze checkpoint performance.
- Cloud SQL PostgreSQL 17 is available on Google Cloud, with significant advancements that streamline database operations and improve management capabilities.
Read Full Article
18 Likes
Dev
3w
109
Image Credit: Dev
Pragma Autonomous Transaction in Oracle PL/SQL
- A Pragma Autonomous Transaction in Oracle PL/SQL allows you to run a transaction independently of the main transaction.
- You declare a block of code as an autonomous transaction using the following syntax: PRAGMA AUTONOMOUS_TRANSACTION;
- Since it operates independently, you must explicitly commit or roll back the changes inside the autonomous transaction block.
- An autonomous transaction lets you log some actions (like inserting or updating data) even if the main transaction fails.
- Autonomous transactions in Oracle PL/SQL are typically used for logging, auditing, or performing operations that should not be affected by the success or failure of the main transaction.
- Main Transaction and Autonomous Transaction are separate: Changes in the autonomous transaction (logging the message) are committed independently, even if the main transaction rolls back.
- The main transaction attempts to insert a new employee into the employees table. It also logs the action using an autonomous transaction, which commits the log entry immediately, regardless of the main transaction's outcome.
- When the main transaction rolls back due to an issue, the employee record for Michael Brown is not added, but the log entry remains in the audit_log table, illustrating the independent nature of autonomous transactions.
- This is why autonomous transactions are often used for logging so you can record important information regardless of other transaction outcomes.
- PRAGMA AUTONOMOUS_TRANSACTION; declares the procedure as an autonomous transaction.
Read Full Article
6 Likes
For uninterrupted reading, download the app