This blog post discusses migrating from on-prem or IaaS database to an Azure Database for MySQL and Azure Database for PostgreSQL PaaS databases.
When migrating from an on-prem database server or an IaaS server to the PaaS service few things needs to be in mind in terms of performance expectations. Azure Database for PostgreSQL and Azure database for MySQL (PaaS) comes with a great features that reduce the manual management effort on the database servers, such as High availability, Automated Backups, Server Monitoring for resource utilization, Security such as encryption, Firewall and SSL, Performance insights and recommendations, Portal UI for the server management, etc.
All these features are maintained by background processes that have cost in terms of performance which is not the case in the Azure VM and the on-prem solution, This will require to setup the expectations that your PaaS server should have more resources horse power to perform at the same level as the on-prem or the IaaS server.
On-prem servers will generally have a better performance compared to cloud based database servers for the above mentioned reason and for the fact that the on-prem servers have way less geographical distance between the application and the database servers which means less network latency.
Solution: Please consider the following performance recommendations and best practices while building your environment:
1) Server resources specifications:
Set the specifications (number of vCores and Disk size) for your PaaS server to accommodate your workload and avoid high resource utilization.
Please see performance basic troubleshooting here:
a- Performance Troubleshooting Basics on Azure Database for PostgreSQL
b- Azure Database for MySQL Performance Troubleshooting Basics
2) connection pooling
Make sure that the application has connection pooling implemented as a best practice to communicate with a PaaS database server.
Let’s take Azure Database for PostgreSQL as an example: servers establishing a connection is an expensive operation. This is attributed to the fact that each new connection to the PostgreSQL requires forking of the OS process and a new memory allocation for the connection. As a result, transnational applications frequently opening and closing the connections at the end of transactions can experience higher connection latency, resulting in lower database throughput (transactions per second) and overall higher application latency. It is therefore recommended to leverage connection pooling when designing applications using Azure Database for PostgreSQL. This significantly reduces connection latency by reusing existing connections and enables higher database throughput (transactions per second) on the server. With connection pooling, a fixed set of connections are established at the startup time and maintained. This also helps reduce the memory fragmentation on the server that is caused by the dynamic new connections established on the database server.
See the following for reference:
a- Not all Postgres connection pooling is equal
b- Connection handling best practice with PostgreSQL
c- Connect efficiently to Azure Database for MySQL
3) VMs with accelerated networking:
Accelerated Networking enables single root I/O virtualization (SR-IOV) to a VM, greatly improving its networking performance. This high-performance path bypasses the host from the datapath, reducing latency, jitter, and CPU utilization, for use with the most demanding network workloads on supported VM types. The following picture shows communication between two VMs with and without accelerated networking:
Without accelerated networking, all networking traffic in and out of the VM must traverse the host and the virtual switch. The virtual switch provides all policy enforcement, such as network security groups, access control lists, isolation, and other network virtualized services to network traffic. To learn more about virtual switches, read the Hyper-V network virtualization and virtual switch article.
4) Application and Database servers in the same Data Center:
Make sure your Application and database are in the same data center, applications that access data by using high-volume, frequent, ad hoc querying, a substantial amount of response time is spent on network communication between the application tier and the Azure Database for MySQL and Azure Database for PostgreSQL. Even when both the application and Azure Database are in the same data center, the network latency between the two might be magnified by a large number of data access operations, to reduce the network round trips for the data access operations between the application server and the database servers, consider using the option to either batch the ad hoc queries, or to compile them as stored procedures.
References:
- Performance best practices for using Azure Database for PostgreSQL
- Performance updates and tuning best practices for using Azure Database for PostgreSQL
- Performance troubleshooting best practices using Azure Database for PostgreSQL features
- Steps to install and setup PgBouncer connection pooling proxy with Azure DB for PostgreSQL