<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Matheus Oliveira on Medium]]></title>
        <description><![CDATA[Stories by Matheus Oliveira on Medium]]></description>
        <link>https://medium.com/@matheus_nazar?source=rss-a408ddee8c68------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*rwNBaLfXuPLkYVOp</url>
            <title>Stories by Matheus Oliveira on Medium</title>
            <link>https://medium.com/@matheus_nazar?source=rss-a408ddee8c68------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Mon, 16 Nov 2026 01:03:48 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@matheus_nazar/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[AWS RDS com lentidão causada por limitação de Throughput da instância RDS]]></title>
            <link>https://medium.com/nazar-io/aws-rds-com-lentid%C3%A3o-causada-por-limita%C3%A7%C3%A3o-de-throughput-da-inst%C3%A2ncia-rds-11950a435bbb?source=rss-a408ddee8c68------2</link>
            <guid isPermaLink="false">https://medium.com/p/11950a435bbb</guid>
            <category><![CDATA[bottleneck]]></category>
            <category><![CDATA[performance]]></category>
            <category><![CDATA[throughput]]></category>
            <category><![CDATA[rds]]></category>
            <dc:creator><![CDATA[Matheus Oliveira]]></dc:creator>
            <pubDate>Wed, 26 Mar 2025 22:36:56 GMT</pubDate>
            <atom:updated>2025-03-27T15:54:45.583Z</atom:updated>
            <content:encoded><![CDATA[<p>Durante um período de maior demanda em um ambiente crítico de um de nossos clientes, que utiliza AWS RDS com Oracle, a performance degradou de maneira súbita, causando indisponibilidade da aplicação.</p><h4><strong>Evidências do problema</strong></h4><p>Após análise, as métricas de <strong>ReadIOPs</strong> e <strong>DiskQueueDepth</strong> chamaram a atenção. A <strong>DiskQueueDepth</strong> havia aumentado, significativamente, enquanto <strong>ReadIOPs</strong> parecia limitado a 5000 IOPS, o que que indicava um possível problema de I/O.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mhsvcSyIiaGU1CuioelsHw.png" /><figcaption>ReadIOPs limited to around 5000 IOPS and high disk queue.</figcaption></figure><p>A instância estava configurada com um disco GP2 com 2700 GB, o que daria um baseline de 8100 IOPs e a métrica <strong>burstBalance</strong> estava em 100%, de maneira que não havia uma limitação de IOPs.</p><p><strong>Então, o que estaria limitando os IOPS em 5000? </strong>A limitação na verdade era de throughput da instância, uma vez que a métrica <em>EBSByteBalance%</em> estava em zero.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/936/1*8mtKLs8UeQT3CGvfwi3XPQ.png" /><figcaption>EBSByteBalance% zeroed</figcaption></figure><h4><strong>Solução do problema</strong></h4><p>Para resolver o problema foi necessário aumentar o tamanho da instância RDS para uma com maior capacidade de throughput. Saindo de uma instância <strong>db.r5.2xlarge</strong> para uma instância <strong>db.r5.4xlarge</strong>, mesmo com consumo baixo de CPU, com IOPs abaixo do limite do disco e nenhum outro recurso com consumo significativo.</p><h4><strong>Conclusão</strong></h4><p>Portanto, se você está utilizando uma instância RDS otimizada para EBS, fique atento às métricas <strong>EBSIOBalance%</strong> e <strong>EBSByteBalance%</strong>, pois valores consistentemente baixos podem indicar um gargalo de I/O que precisa ser tratado.</p><p>Em nosso monitoramento, mantemos um olhar atento sobre essas métricas e os comandos mais onerosos, permitindo identificar problemas antes que eles impactem a performance do seu banco de dados, garantindo performance, escalabilidade e redução de custos.</p><h4>Sobre a Nazar</h4><p>Fundada em <strong>2014</strong>, no Brasil, por profissionais de Tecnologia da Informação com mais de <strong>25 anos de experiência</strong>, a <a href="https://www.nazar.ai/"><strong>NAZAR</strong></a> é especializada em <strong>monitoramento, diagnóstico e otimização de desempenho de bancos de dados</strong>. Nossa expertise em <strong>computação em nuvem, escalabilidade e manutenção de aplicações complexas</strong> já ajudou mais de <strong>300 empresas</strong>, com mais de <strong>5.000 bancos de dados otimizados</strong>.</p><p>Nosso princípio fundamental é simples: <strong>Performance Wins</strong> — ou seja, bancos de dados eficientes geram melhores resultados e reduzem custos.</p><p>Banco de Dados tem sido um dos seus principais custos? Entre em contato conosco: <a href="https://cal.com/nazar-ai/30min">https://cal.com/nazar-ai/30min</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=11950a435bbb" width="1" height="1" alt=""><hr><p><a href="https://medium.com/nazar-io/aws-rds-com-lentid%C3%A3o-causada-por-limita%C3%A7%C3%A3o-de-throughput-da-inst%C3%A2ncia-rds-11950a435bbb">AWS RDS com lentidão causada por limitação de Throughput da instância RDS</a> was originally published in <a href="https://medium.com/nazar-io">NAZAR</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Partial index]]></title>
            <link>https://medium.com/nazar-io/partial-index-186e42c4207f?source=rss-a408ddee8c68------2</link>
            <guid isPermaLink="false">https://medium.com/p/186e42c4207f</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[sql-tuning]]></category>
            <category><![CDATA[database-tuning]]></category>
            <category><![CDATA[database-performance]]></category>
            <category><![CDATA[partial-index]]></category>
            <dc:creator><![CDATA[Matheus Oliveira]]></dc:creator>
            <pubDate>Mon, 13 Nov 2023 23:32:38 GMT</pubDate>
            <atom:updated>2023-11-14T16:05:58.581Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*IkHIjxLEk9w8zrPRk2OP7Q.jpeg" /><figcaption>SQL Tuning — Partial index</figcaption></figure><h4>A partial index is a valuable feature supported by various relational databases but is not commonly used. In this post, we will explore some use cases where you can benefit from using partial indexes.</h4><p>Partial indexes can be useful in various scenarios where you want to improve query performance by reducing the size of an index or filtering which rows are included in the index. Here are some examples of when to use partial indexes:</p><ol><li><strong>Selective Indexing:</strong> When you have a large table with many rows but want to index only a subset of those rows that meet specific criteria. For example, you may have a table of customer orders, and you want to create an index only for orders that are not canceled (status != ‘canceled’).</li><li><strong>Data Range Filtering:</strong> When you have a table with a date or timestamp column and want to create an index for a specific time range. This can be helpful for time-based data, like logs or sensor readings.</li><li><strong>Boolean or Enum Columns:</strong> When you have boolean or enum columns and want to create an index on specific values. For instance, you might have a table of products and want to index only the active products.</li><li><strong>Sparse Data:</strong> When you have a column with a low cardinality and want to create an index for a specific value that is not present in most rows.</li><li><strong>Query Optimization: </strong>When you have queries that frequently filter by a specific condition, a partial index can significantly speed up those queries. For example, if you often search for unshipped orders.</li><li><strong>Storage Optimization:</strong> Partial indexes can reduce the storage requirements of your database because they are smaller due to indexing a subset of rows. This can be especially valuable in scenarios with limited storage capacity.</li><li><strong>Concurrency:</strong> In databases with high levels of concurrent write operations, partial indexes can reduce contention and locking conflicts by only indexing a subset of rows.</li></ol><p>Let’s consider a scenario where you have a table for user accounts, and it includes a boolean column called active to track whether a user account is active (true) or inactive (false). In this case, most of the user accounts are inactive. We want to compare the use of a partial index and a non-partial index for this scenario.</p><p>Non-Partial Index Example:</p><p>Suppose you have a user_accounts table with 8,4 million rows and the following schema:</p><pre>CREATE TABLE tb_user_accounts (<br>    user_id serial PRIMARY KEY,<br>    username varchar(255) NOT NULL,<br>    active boolean NOT NULL<br>);</pre><p>To create a non-partial index on the active column, you would typically create an index that covers all rows, including both active and inactive users:</p><pre>CREATE INDEX idx_all_users_active ON tb_user_accounts (active);</pre><p>This index will include all user accounts, regardless of their active status.</p><p>Partial Index Example:</p><p>Now, let’s consider the scenario where you want to optimize queries that involve active user accounts (where active = true). To do this, you can create a partial index as follows:</p><pre>CREATE INDEX idx_active_users_active ON tb_user_accounts (user_id) <br>WHERE active = true;</pre><p>In this case, the WHERE clause specifies that only rows where active is true will be included in the index. This results in a smaller, more focused index that includes only active user accounts, leading to better query performance.</p><pre>select pg_size_pretty(pg_table_size(&#39;idx_all_users_active&#39;));<br><br>pg_size_pretty|<br>--------------+<br>56 MB         |<br><br>select pg_size_pretty(pg_table_size(&#39;idx_active_users_active&#39;));<br><br>pg_size_pretty|<br>--------------+<br>240 kB        |</pre><p>The above syntax is compatible with PostgreSQL. In Oracle Database and MySQL 8, you can create an function based index using a CASE expression to effectively achieve a partial index-like behavior. Here&#39;s an example:</p><pre>CREATE INDEX idx_active_users_active ON user_accounts <br>(CASE WHEN active = 1 THEN user_id ELSE NULL END);</pre><p>In summary, a partial index is advantageous when you have specific criteria for indexing a subset of rows within a table, such as optimizing queries for active user accounts in a scenario where most rows have the value active = false. It can lead to improved query performance, reduced storage requirements, and better concurrency handling compared to non-partial indexes, which index all rows indiscriminately.</p><blockquote><em>“It is not lack of hardware,<br>It is not network traffic,<br>It is not slow front ends,<br>the main performance problem in the huge majority of database applications is bad SQL code.”<br></em>Joe Celko</blockquote><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=186e42c4207f" width="1" height="1" alt=""><hr><p><a href="https://medium.com/nazar-io/partial-index-186e42c4207f">Partial index</a> was originally published in <a href="https://medium.com/nazar-io">NAZAR</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[[ SQL Performance Killers] — Individual inserts vs. Bulk inserts.]]></title>
            <link>https://medium.com/nazar-io/sql-performance-killers-individual-inserts-vs-bulk-inserts-14176c201673?source=rss-a408ddee8c68------2</link>
            <guid isPermaLink="false">https://medium.com/p/14176c201673</guid>
            <category><![CDATA[tuning]]></category>
            <category><![CDATA[bulk-insert]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[sql-tuning]]></category>
            <category><![CDATA[database-performance]]></category>
            <dc:creator><![CDATA[Matheus Oliveira]]></dc:creator>
            <pubDate>Tue, 07 Nov 2023 13:25:09 GMT</pubDate>
            <atom:updated>2023-11-07T22:49:20.513Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OBwEe7KMHuG9whsWp3JzzA.jpeg" /></figure><h3>Individual inserts vs. Bulk inserts.</h3><h4>An important part of my daily work is to help developers, database administrators, devops, sysadmins and project managers to identify (using <a href="https://www.nazar.io/">Nazar.io</a>) and fix bad SQL code. And the idea with the [SQL Performance Killers] series is to share some practical sql tuning examples.</h4><p>Continuing (after a very long time) the [SQL performance killers series] in this post I’ll explain why bulk insert operations are generally faster than many individual insert operations.</p><p>Bulk inserts are significantly faster than individual inserts when working with a database for several reasons:</p><ol><li><strong>Reduced network traffic:</strong> Bulk inserts reduce the amount of data transferred between the application and the database server. In many cases, network latency can be a bottleneck for database performance. By sending a single batch of data, you can reduce the impact of network latency and improve efficiency.</li><li><strong>Reduced transaction overhead:</strong> Each individual insert operation is typically wrapped in a transaction, which can lead to increased overhead due to transaction management. Bulk inserts can be enclosed in a single transaction, reducing the overhead associated with transaction management and ensuring data consistency.</li><li><strong>Locking and concurrency: </strong>When you perform many individual inserts, each insert may require locks on the affected rows, leading to potential contention and concurrency issues in a multi-user environment. Bulk inserts often lock the entire table or a specific set of rows, reducing contention and improving concurrency.</li><li><strong>Logging and indexing: </strong>Databases often maintain transaction logs and indexes to ensure data consistency and query performance. Bulk inserts are more efficient in terms of logging and indexing because they involve fewer transactions and updates to indexes.</li></ol><p>In the example below I inserted 40,000 rows in a sample table, first using individual inserts and then using bulk insert.</p><pre>INSERT INTO TB_INSERT(NAME) VALUES (&#39;AF9CB08DF4F7B71F033CC857ECF30C21&#39;);<br>INSERT INTO TB_INSERT(NAME) VALUES (&#39;B16D3C99C04F223E362BC0E1B4FFE7CD&#39;);<br>...<br>INSERT INTO TB_INSERT(NAME) VALUES (&#39;BEDB35BC448FD5F32F37B86BECFDF225&#39;);<br>INSERT INTO TB_INSERT(NAME) VALUES (&#39;4436A24C954EA17AEE9E92D4F16FAD20&#39;);<br><br>UPDATED ROWS 40000<br>START TIME MON NOV 06 14:52:02 PST 2023<br>FINISH TIME MON NOV 06 14:52:22 PST 2023</pre><pre>INSERT INTO TB_INSERT(NAME) VALUES<br>(&#39;AF9CB08DF4F7B71F033CC857ECF30C21&#39;),<br>(&#39;B16D3C99C04F223E362BC0E1B4FFE7CD&#39;),<br>...<br>(&#39;BEDB35BC448FD5F32F37B86BECFDF225&#39;),<br>(&#39;4436A24C954EA17AEE9E92D4F16FAD20&#39;);<br><br>   <br>UPDATED ROWS 40000<br>START TIME MON NOV 06 14:56:32 PST 2023<br>FINISH TIME MON NOV 06 14:56:34 PST 2023</pre><p>As we can see, inserting 40,000 rows into a sample table using bulk load was 10 times faster than using individual inserts. While individual inserts took 20 seconds to complete, bulk inserts were done in only 2 seconds.</p><p>However, it’s essential to note that the suitability of bulk inserts depends on the database management system (DBMS) you are using and the specific use case. Always consider the characteristics of your database and the requirements of your application when deciding whether to use bulk inserts or individual inserts.</p><blockquote>“It is not lack of hardware,<br>It is not network traffic,<br>It is not slow front ends,<br>the main performance problem in the huge majority of database applications is bad SQL code.”<br><em>Joe Celko</em></blockquote><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=14176c201673" width="1" height="1" alt=""><hr><p><a href="https://medium.com/nazar-io/sql-performance-killers-individual-inserts-vs-bulk-inserts-14176c201673">[ SQL Performance Killers] — Individual inserts vs. Bulk inserts.</a> was originally published in <a href="https://medium.com/nazar-io">NAZAR</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Azure Database for PostgreSQL Single Server intermittent restart and recovery due to undocumented…]]></title>
            <link>https://medium.com/nazar-io/azure-database-for-postgresql-single-server-intermittent-restart-and-recovery-due-to-undocumented-c5a1dc4bdb48?source=rss-a408ddee8c68------2</link>
            <guid isPermaLink="false">https://medium.com/p/c5a1dc4bdb48</guid>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[azure]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Matheus Oliveira]]></dc:creator>
            <pubDate>Mon, 13 Jul 2020 20:14:12 GMT</pubDate>
            <atom:updated>2020-07-14T11:46:30.949Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1uqhOEjNp_yruKn5Zq_IrA.png" /></figure><h3>Azure Database for PostgreSQL Single Server intermittent restart and recovery due to undocumented behavior.</h3><p>If you are experiencing Azure Database for PostgreSQL intermittent restart and recovery, it may be due to internal Log file reaching 1 GB.</p><p>Recently, I was involved in a troubleshooting of a production Azure Database for PostgreSQL that was intermittently unavailable. As in all intermittent problems, I first tried to find metrics that could indicate the root cause. This time, there was no metric with any behavior change that could be correlated to the occurrences of the problem, so I opened a support ticket for the Microsoft team.</p><p>I shared every information I had about the problem and the application that used that database. After confirming all the evidences, the support attendant said that the issue needed to be escalated to the product group for further investigation.</p><p>After a few days, I received the answer from the support team saying:</p><p><strong>“By Design, if the internal Log file (size determined by the level of Logging set in Portal) consistently reaches 1 GB, it degrades the Performance of the Postgres Single Server DB and can lead to Restart And Recovery to self-stablize the database.&quot;</strong></p><p>The root cause of the problem was enlightened but this behavior is not mentioned in the documentation (<a href="https://docs.microsoft.com/en-us/azure/postgresql/concepts-server-logs">https://docs.microsoft.com/en-us/azure/postgresql/concepts-server-logs</a>). What the documentation says is that, once you reach 1 GB of log files, the oldest files will be deleted regardless of the retention period set.</p><p><strong>&quot;The short-term storage location can hold up to 1 GB of log files. After 1 GB, the oldest files, regardless of retention period, will be deleted to make room for new logs&quot;</strong></p><p>First, I think that this behavior should be explicit in the documentation. Second and most important, <strong>it is an important limitation of the Azure Database for PostgreSQL Single Server as, if you set the maximum retention period which is 7 days, it will only allow approximately 6 MB of log per hour.</strong></p><p>With this limitation in mind, be careful with logging level and set an alert for &quot;Sever Log Storage used&quot;. Also, reducing logging retention period to 1 day (default is 3 days) would reduce the probability of reaching the 1 GB limit.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c5a1dc4bdb48" width="1" height="1" alt=""><hr><p><a href="https://medium.com/nazar-io/azure-database-for-postgresql-single-server-intermittent-restart-and-recovery-due-to-undocumented-c5a1dc4bdb48">Azure Database for PostgreSQL Single Server intermittent restart and recovery due to undocumented…</a> was originally published in <a href="https://medium.com/nazar-io">NAZAR</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Migrating from Oracle RAC to AWS RDS with 15 minutes downtime.]]></title>
            <link>https://medium.com/nazar-io/migrating-from-oracle-rac-to-aws-rds-with-15-minutes-downtime-f6ba90ab574a?source=rss-a408ddee8c68------2</link>
            <guid isPermaLink="false">https://medium.com/p/f6ba90ab574a</guid>
            <category><![CDATA[dms]]></category>
            <category><![CDATA[aws]]></category>
            <category><![CDATA[migration]]></category>
            <category><![CDATA[oracle-database]]></category>
            <category><![CDATA[rds]]></category>
            <dc:creator><![CDATA[Matheus Oliveira]]></dc:creator>
            <pubDate>Mon, 28 Oct 2019 12:25:19 GMT</pubDate>
            <atom:updated>2021-10-27T18:24:35.171Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pEMm4vaBwwnbwZOq6Vst1A.png" /></figure><p>A few days ago, we were challenged to reduce the database downtime for an Oracle RAC (on-premisses) migration to AWS RDS.<br>Other teams had already tried to optimize the migration process and reduce database downtime ( which makes it even more challenging ) but their best estimation was a 4 hours downtime, which was yet too high for the customer expectations and criticality of the business.<br>Don’t have to mention that we also had a tight deadline of a few days to define the new migration strategy.</p><p>Scenario:</p><ul><li>Version migration: From Oracle 11g on RAC (with ASM) to Oracle 12g on AWS RDS;</li><li>Network latency: From an on-premisses environment located at São Paulo region to AWS RDS at US East (N. Virginia);</li><li>LOB objects: ~40% of the the database size in a single table;</li></ul><p>We knew, based on past migrations projects, that AWS Database Migration Service (DMS) would help us to solve this challenge.</p><p>First we tried loading the target database and creating a single CDC task, but the combination of large LOBs and high latency between the two environments made the task to run slower then transactions on the source database and consequently this approach did not work.</p><p>After failing on the first approach, we analysed the database model and identified no database object was dependent on the table with the LOB columns, so we decided to processe the table with LOB columns separately.</p><p>On the second approach, we loaded the the target database and, this time, we created two CDC task, one for all database objects except the table with LOBs and a second task exclusively with the table with LOB columns.</p><p>This way, the first task, with all the objects except the table with lobs, started running faster then the transactions on the source database and quickly we had the target database in-sync with the source database.</p><p>For the table with LOB columns, our strategy was to load all records until one day before and during the dawn, when transactions volume decreased significantly on the source database, we loaded only the records of the day and started the second task. This time the task was able to run faster then the source database and keep the target database in-sync with the source database.</p><p>As soon as we had both tasks synced, we stopped the source database and redirected the application to the AWS RDS database.</p><p>&quot;With this approach, we achieved a downtime of just 15 minutes which greatly reduced the impact on our business.&quot; — <strong>Eugênio Barbaresco, Diretoria de TI do Grupo Comporte S/A.</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f6ba90ab574a" width="1" height="1" alt=""><hr><p><a href="https://medium.com/nazar-io/migrating-from-oracle-rac-to-aws-rds-with-15-minutes-downtime-f6ba90ab574a">Migrating from Oracle RAC to AWS RDS with 15 minutes downtime.</a> was originally published in <a href="https://medium.com/nazar-io">NAZAR</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Oracle RDS Read Replica with DMS CDC.]]></title>
            <link>https://medium.com/nazar-io/oracle-rds-read-replica-with-dms-cdc-b16bb018e1fe?source=rss-a408ddee8c68------2</link>
            <guid isPermaLink="false">https://medium.com/p/b16bb018e1fe</guid>
            <category><![CDATA[rds]]></category>
            <category><![CDATA[oracle]]></category>
            <category><![CDATA[read-replica]]></category>
            <category><![CDATA[aws]]></category>
            <category><![CDATA[dms]]></category>
            <dc:creator><![CDATA[Matheus Oliveira]]></dc:creator>
            <pubDate>Wed, 23 Jan 2019 23:20:57 GMT</pubDate>
            <atom:updated>2019-01-24T13:41:04.881Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qmkuLI9NefEKV0z-on2iIw.png" /></figure><p>Even though Read Replica (RR) feature is not natively available for Amazon RDS for Oracle, you can implement it using Database Migration Service (DMS) with change data capture (CDC).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/495/1*cfBM8TV5kOWfhzQJ7MPi3g.png" /><figcaption><a href="https://docs.aws.amazon.com/pt_br/dms/latest/userguide/CHAP_ReplicationInstance.html">https://docs.aws.amazon.com/pt_br/dms/latest/userguide/CHAP_ReplicationInstance.html</a></figcaption></figure><blockquote>Amazon RDS Read Replicas provide enhanced performance and durability for database (DB) instances. This feature makes it easy to elastically scale out beyond the capacity constraints of a single DB instance for read-heavy database workloads. You can create one or more replicas of a given source DB Instance and serve high-volume application read traffic from multiple copies of your data, thereby increasing aggregate read throughput. Read replicas can also be promoted when needed to become standalone DB instances. Read replicas are available in Amazon RDS for MySQL, MariaDB, and PostgreSQL as well as Amazon Aurora.<br>https://aws.amazon.com/rds/details/read-replicas</blockquote><p>Basically, you will need to setup “Supplemental logging” on your source database and then create a snapshot that will be used for your RR instance creation. <br> After creating your RR instance from your source database snapshot, you will setup you DMS replication by creating a replication instance, source and target endpoints, and a task.</p><p>Create a “Replicate data changes only” task with the “Target table preparation mode” option set to “Do nothing”.</p><p>The trick here is to set “CDC start mode” with the “Specify start time” option and specifying the Snapshot Creation Time or setting “CDC start mode” with the “Specify log sequence number” and specifying the SCN of the time of your source database snapshot. By doing this, DMS will be able to replicate every data changed since your source database snapshot, creating your Read Replica instance.</p><p>The idea of this post is to explore a different use of the AWS DMS service more than going through every implementation details. Another use of DMS CDC would be creating a disaster recovery plan in different AWS Regions. What other use can you think?</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b16bb018e1fe" width="1" height="1" alt=""><hr><p><a href="https://medium.com/nazar-io/oracle-rds-read-replica-with-dms-cdc-b16bb018e1fe">Oracle RDS Read Replica with DMS CDC.</a> was originally published in <a href="https://medium.com/nazar-io">NAZAR</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[[ SQL performance Killers ] — STALE STATISTICS]]></title>
            <link>https://medium.com/nazar-io/sql-performance-killers-stale-statistics-f735411facc8?source=rss-a408ddee8c68------2</link>
            <guid isPermaLink="false">https://medium.com/p/f735411facc8</guid>
            <category><![CDATA[tuning]]></category>
            <category><![CDATA[apm]]></category>
            <category><![CDATA[performance]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Matheus Oliveira]]></dc:creator>
            <pubDate>Tue, 21 Aug 2018 20:04:13 GMT</pubDate>
            <atom:updated>2018-08-21T20:04:13.199Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bJYigucw6x-b6D0Sic4_tA.png" /></figure><h4>An important part of my daily work is to help developers, database administrators, devops, sysadmins and project managers to identify (using <a href="https://www.nazar.io/">Nazar.io</a>) and fix bad SQL code. And the idea with the [SQL performance Killers] series is to share some practical sql tuning examples.</h4><p>Continuing the [SQL performance killers series post <a href="https://medium.com/nazar-io/sql-performance-killers-functions-to-column-data-8efff6582ba4">#1</a>, post <a href="https://medium.com/nazar-io/sql-performance-killers-in-vs-exists-c19da0a99ce1">#2</a> and post <a href="https://medium.com/nazar-io/sql-performance-killers-implicit-conversions-37245a91b51b">#3</a>, <a href="https://medium.com/nazar-io/sql-performance-killers-views-c03b88a0fdfd">#4</a>], in this fifth post I’ll explain why you should keep database statistics updated.</p><p>When a valid SQL statement is sent to the database server for the first time, the query optimizer chooses the best execution plan for the SQL statement. There are basically two types of optimizer, Rule Based Optimizer (RBO) and Cost-based Optimizer (CBO).</p><p>RBO was the first optimization method and as the name suggests, was essentially a list of rules the database should follow to generate an execution plan.</p><p>The CBO is an evolution of the RBO optimizer and examines all of the possible plans for a SQL statement and picks the one with the lowest cost, where cost represents the estimated resource usage for a given plan. To estimate the resources needed for each execution plan, the CBO must have information about all the objects accessed in the SQL statement. This information is the database statistics.</p><p>Last week we started a consulting project where the challenge was to identify the root cause of poor performance and instability of an Oracle RDS in a very critical application. The symptoms were high memory consumption, increased write to disk , increased number of database connections and several sessions on hold. Every time the problem occurred, the team had a few minutes to start killing the holding sessions, otherwise the database instance would be &quot;abnormally terminated&quot;.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZQmnecUNUMtP_oMqLwWPSw.png" /><figcaption>Database instance abnormally terminated.</figcaption></figure><p>By monitoring with Nazar, we identified that queries from one application functionality was responsible for 63% of the total time spent on the database and we started analyzing these queries.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SxYBfm5YjjX0vy_dNirSEA.png" /><figcaption>Top 10 most time consuming commands.</figcaption></figure><p>By monitoring the problem as it occurred, we could see that all the holding sessions where executing the same queries and all of them were waiting for the &quot;DIRECT PATH WRITE TEMP&quot; event with &quot;HASH&quot; segments on TEMP tablespace.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1016/1*cWs_yZST0COTynO-qcoL6g.png" /><figcaption>“HASH” segments on TEMP tablespace.</figcaption></figure><p>Analyzing the queries plans we confirmed that they were using HASH JOINS which is a memory based JOIN that is very effective when the data set fits in memory. If the data sets do not fit in memory, then it uses a lot of sort area memory and I/O to the temporary tablespace.</p><p>Digging deeper in our analysis we found that the statistics on the database were stale. One of the main tables had around 1.5 M rows while the statistics showed only 55 K rows. Then we gathered statistics for the main tables used by the queries and all HASH JOIN were substituted by NESTED LOOPS.</p><p>The optimizer was making wrong decisions based on stale statistics. It decided that the data used by the HASH JOIN would fit in memory but actually it did not fit, causing a high usage of sort area memory and temporary tablespace.</p><p>Maintaining statistics accurate ensures that the Optimizer will have the best possible source of information to determine the best execution plan.</p><blockquote>“It is not lack of hardware,<br>It is not network traffic,<br>It is not slow front ends,<br>the main performance problem in the huge majority of database applications is bad SQL code.”<br><em>Joe Celko</em></blockquote><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f735411facc8" width="1" height="1" alt=""><hr><p><a href="https://medium.com/nazar-io/sql-performance-killers-stale-statistics-f735411facc8">[ SQL performance Killers ] — STALE STATISTICS</a> was originally published in <a href="https://medium.com/nazar-io">NAZAR</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[[ SQL performance Killers ] — VIEWS]]></title>
            <link>https://medium.com/nazar-io/sql-performance-killers-views-c03b88a0fdfd?source=rss-a408ddee8c68------2</link>
            <guid isPermaLink="false">https://medium.com/p/c03b88a0fdfd</guid>
            <category><![CDATA[tuning]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[apm]]></category>
            <category><![CDATA[application-performance]]></category>
            <dc:creator><![CDATA[Matheus Oliveira]]></dc:creator>
            <pubDate>Mon, 30 Jul 2018 13:25:40 GMT</pubDate>
            <atom:updated>2018-07-31T12:09:34.088Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qxa-BdND9cVgIVp7LC2I0g.png" /></figure><h4>An important part of my daily work is to help developers, database administrators, devops, sysadmins and project managers to identify (using <a href="https://www.nazar.io/">Nazar.io</a>) and fix bad SQL code. And the idea with the [SQL performance Killers] series is to share some practical sql tuning examples.</h4><p>Continuing the [SQL performance killers series post <a href="https://medium.com/nazar-io/sql-performance-killers-functions-to-column-data-8efff6582ba4">#1</a>, post <a href="https://medium.com/nazar-io/sql-performance-killers-in-vs-exists-c19da0a99ce1">#2</a> and post <a href="https://medium.com/nazar-io/sql-performance-killers-implicit-conversions-37245a91b51b">#3</a>], in this fourth post I’ll go through how database VIEWS are processed and its impact on application’s performance.</p><p><strong>Basically, VIEWS are processed using either <em>MERGE </em>or <em>TEMPTABLE</em> algorithms.</strong></p><p>When the VIEW is processed with the <strong>MERGE algorithm,</strong> the optimizer rewrites the query by merging the view code with code from the query. In most cases, the resulting query plan will be the same as if the query had accessed the tables directly, without using a view. <strong>In this case, the use of views should not cause any performance impact.</strong></p><p>The problem is when the <em>TEMPTABLE</em> algorithm is used to process the VIEW. <strong>When using the <em>TEMPTABLE</em> algorithm the optimizer first executes the view code and creates a temporary table with the resultset from the view and just then it executes the query on the temporary table.</strong></p><p>Suppose you have an <em>address</em> table with approximately 30 million rows and you create a view <em>vw_address_brazil</em> with addresses only from Brazil.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*r0oD5Tn-TENVWsJ5OeR8nQ.png" /><figcaption>Create view <em>vw_address_brazil</em></figcaption></figure><p>Then you write a query to count how many addresses from Brazil are in the state of PERNAMBUCO using the view <em>vw_address_brazil</em>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*E3JWm29g687y4kulgt1ajA.png" /><figcaption>Query on vw_address_brazil view</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Yaib8i2Y9jM5UVE33W36KQ.png" /></figure><p>For the above query, the optimizer uses the MERGE algorithm and the query will have the same execution time and execution plan as if it had accessed the <em>address </em>table<em> </em>directly, without using a view.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*BeNpI3yeqO7prHALST7Uxw.png" /><figcaption>Query accessing the table address directly</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*I4hHC-VhftyiBFq-RVN16Q.png" /></figure><p>Now, lets create a view <em>vw_address_country_state</em> with the total number of addresses from each state in each country.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hDVw5PfDzyKGWQd0cYfO_g.png" /></figure><p>Then we will use the view <em>vw_address_country_state </em>to query the total number of addresses from Brazil which<strong> </strong>are in the state of PERNAMBUCO.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*b_WMLzd7u6pW9DXAGx6eXQ.png" /><figcaption><em>Query on vw_address_country_state view</em></figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OYTPaICTgT-wJ162zMywhA.png" /></figure><p>This time the optimizer used the TEMPTABLE algorithm, causing the query to be executed in 8 seconds compared to the 0.03 seconds using the MERGE algorithm. The reason of all this slowness is that this time the database had to first execute the code from the view ( calculating the total number of addresses from each state in each country ) and saving it on a temporary table and only then apply the filter <em>country = ‘Brazil’ and state = ‘PE’.</em></p><p>The MERGE algorithm could not be used because of the &quot;<em>count(*)&quot; </em>in the code of the view. In most relational databases, if the view contains any of the following constructs, the MERGE algorithm cannot be used:</p><p>- Aggregate functions (SUM(), MIN(), MAX(), COUNT()…)<br>- DISTINCT<br>- GROUP BY<br>- HAVING<br>- LIMIT<br>- UNION or UNION ALL<br>- Subquery in the select list<br>- Set operations (Iintersect, Minus…)<br>- Outer joins</p><blockquote>“80% of the performance issues are caused by improperly coded database applications.” – <strong>Craig S. Mullins</strong></blockquote><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c03b88a0fdfd" width="1" height="1" alt=""><hr><p><a href="https://medium.com/nazar-io/sql-performance-killers-views-c03b88a0fdfd">[ SQL performance Killers ] — VIEWS</a> was originally published in <a href="https://medium.com/nazar-io">NAZAR</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[[ SQL performance Killers ] — Implicit Conversions]]></title>
            <link>https://medium.com/nazar-io/sql-performance-killers-implicit-conversions-37245a91b51b?source=rss-a408ddee8c68------2</link>
            <guid isPermaLink="false">https://medium.com/p/37245a91b51b</guid>
            <category><![CDATA[performance-tuning]]></category>
            <category><![CDATA[database-query]]></category>
            <category><![CDATA[application-performance]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Matheus Oliveira]]></dc:creator>
            <pubDate>Mon, 23 Jul 2018 16:51:23 GMT</pubDate>
            <atom:updated>2018-07-23T16:58:24.200Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*RFODjYyWlwMakCQctXUKPA.png" /></figure><h4><strong>An important part of my daily work is to help developers, database administrators, devops, sysadmins and project managers to identify (using </strong><a href="https://www.nazar.io/"><strong>Nazar.io</strong></a><strong>) and fix bad SQL code. And the idea with the [SQL performance Killers] series is to share some practical sql tuning examples.</strong></h4><p>This is the third post of the [SQL performance killers series], the first two posts were [<a href="https://medium.com/nazar-io/sql-performance-killers-functions-to-column-data-8efff6582ba4">Applying functions to column data</a>] and [<a href="https://medium.com/nazar-io/sql-performance-killers-in-vs-exists-c19da0a99ce1">IN vs EXISTS</a>]. <br>This time I want to show you how leaving the responsibility of data type conversions to the database — implicit conversion — can impact your database/application performance.</p><p>When your parameter has a different type from the column you are comparing to, the database tries to make an implicit conversion so that the command won&#39;t return an error. For example, when comparing a number with a string, it will try to convert the string to a number implicitly<em>.</em></p><p>Although it makes things convenient for the developers, it can bring important performance impact. One of this performance issues happened with one of our customers who had just partitioned a huge table. The table was a central part of his database and accessed by most of the &quot;slow queries&quot;. After partitioning the table it was expected to have a big performance improvement but it did&#39;t happen.</p><p>After analyzing the queries and its execution plan, we noticed that there was no partition elimination and the queries were accessing every table partitions. The problem was that the column used for the partition key was a &quot;<em>timestamp </em><strong><em>with</em></strong><em> time zone&quot;</em> and the parameter received by the query was a <em>string.</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2OILriUUqSxehKoC6S16WQ.png" /><figcaption>Query with implicit convertion</figcaption></figure><p>In this scenario PostgreSQL was converting the string to a “<em>timestamp </em><strong><em>without</em></strong><em> time zone” what was </em>preventing partition elimination.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*jq2aN4vEIjBFnVsGlya3Fw.png" /><figcaption>Query with explicit conversiton</figcaption></figure><p>After applying explicit conversion to the queries, it started doing partition elimination and the expected performance gains finally came!</p><blockquote>“80% of the performance issues are caused by improperly coded database applications.” — <strong>Craig S. Mullins</strong></blockquote><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=37245a91b51b" width="1" height="1" alt=""><hr><p><a href="https://medium.com/nazar-io/sql-performance-killers-implicit-conversions-37245a91b51b">[ SQL performance Killers ] — Implicit Conversions</a> was originally published in <a href="https://medium.com/nazar-io">NAZAR</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[[ SQL performance Killers ] — IN vs EXISTS]]></title>
            <link>https://medium.com/nazar-io/sql-performance-killers-in-vs-exists-c19da0a99ce1?source=rss-a408ddee8c68------2</link>
            <guid isPermaLink="false">https://medium.com/p/c19da0a99ce1</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[performance]]></category>
            <category><![CDATA[apm]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[tuning]]></category>
            <dc:creator><![CDATA[Matheus Oliveira]]></dc:creator>
            <pubDate>Fri, 13 Jul 2018 14:26:51 GMT</pubDate>
            <atom:updated>2018-07-24T15:04:44.053Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*eJGRpub518ETdEwVqNyQOg.png" /></figure><p><strong>An important part of my daily work is to help developers, database administrators, devops, sysadmins and project managers to identify (using </strong><a href="https://www.nazar.io/"><strong>Nazar.io</strong></a><strong>) and fix bad SQL code. And the idea with the [SQL performance Killers] series is to share some practical sql tuning examples.</strong></p><p>Besides <a href="https://medium.com/nazar-io/sql-performance-killers-functions-to-column-data-8efff6582ba4">applying functions to column data</a><strong>,</strong> the wrong use of the IN and EXISTS operators is another very common “SQL performance killer”. Although, in many cases, using either one or the other will give the same query result, they are processed very differently and knowing this difference is the key to avoid killing query performance.</p><p>The IN operator is typically processed as:</p><p>1 — The subquery is executed and result is distinct’ed;<br>3 — Then joined to the external table;</p><p>IN operator i.e:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/926/1*XfOS-7S1ZSKcjyArXL-T0Q.png" /></figure><p>Will typically be processed as:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/960/1*i1HUXJp8sO2nW2IwkBKa-A.png" /></figure><p>While the EXISTS operator is typically processed as:</p><p>1 — For each row of the external table [ Full table scan ] the subquery will be executed once;</p><p>EXISTS operator i.e:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/966/1*-YUpnyikjmQn8UMKDtdppQ.png" /></figure><p>Will typically be processed as:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*00n0siMM3fRTkAqEgdAaKg.png" /></figure><p>So, when using IN is better than EXISTS, and vice versa?</p><p><strong>When the subquery [ SELECT column_c FROM table_y ] is slow and the external table [ table_x ] is relatively small, then using EXISTS will be faster,</strong> as the time to full scan the external table and execute the subquery will be less than the time to execute the subquery and to distinct on and join with the external table.</p><p><strong>When the subquery [ SELECT column_c FROM table_y ] is fast and the external table [ table_x ] has many rows, then IN is better, </strong>as the time to execute the subquery, distinct on and join with the external table will be less than the time to full scan the external table and execute the subquery.</p><p><strong>If both the subquery and the outer table are small, IN and EXISTS will have the same performance.</strong></p><p><strong>If both the subquery and the outer table are big, either might work as well as the other, it will depend basically on the existing indexes.</strong></p><blockquote><em>“It is not lack of hardware,<br>It is not network traffic,<br>It is not slow front ends,<br>the main performance problem in the huge majority of database applications is bad SQL code.”<br></em>Joe Celko</blockquote><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c19da0a99ce1" width="1" height="1" alt=""><hr><p><a href="https://medium.com/nazar-io/sql-performance-killers-in-vs-exists-c19da0a99ce1">[ SQL performance Killers ] — IN vs EXISTS</a> was originally published in <a href="https://medium.com/nazar-io">NAZAR</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>