1. Documentation publique
  2. debugging
  3. Performance et bdd géré par Azure

Performance et bdd géré par Azure

Soumis par admin le ven, 10/27/2023 - 02:18

Contents

AMAZON AWS – 1

Microsoft AZURE 1

Choosing the right version of MySQL/MariaDB 1

Caching 2

DB Monitoring and Profiling: 3

AZURE conclusion to challenge 3

Drupal Application Performance 3

Upgrade Drupal to 10.1.5, skip 10.0.x 3

Latest new core includes some performance enhancements 3

Table Indexes 3

Table indexes could be added in some tables that may benefit. This can significantly speed up query performance. 3

Application Performance Patches 3

Performance profiling to identify pain points 4

Node revision delete 4

AMAZON AWS –

MySQL Performance considerations within an AWS managed db (RDS)

  • To get more iops we had to go to a better class of storage. This vastly increased cost but also performance.

Microsoft AZURE

MySQL Performance considerations within an Azure managed db system.

Choosing the right version of MySQL/MariaDB

  • ⁠**Upgrade MySQL/MariaDB Versions**: Ensure that you are running the latest version of MySQL/MariaDB supported by Azure for potential performance improvements - (upgrade from 10.3 MariaDB to a newer release)

Increase IOPS (Input / Output Per Second)

  • **Choose the Right Service Tier**: Azure offers different service tiers with varying levels of performance. Consider upgrading to a higher tier if you are experiencing performance issues. Higher tiers offer more resources like CPU and memory.  This is often the simplest way to improve performance. You can upgrade to a higher service tier with more CPU, memory, and better storage performance.

  • **Faster and costlier** selecting a higher service tier can increase IOPS, it may also come with a higher cost.

  • **Read Replicas**: For read-heavy workloads, you can consider using read replicas to offload read traffic from the primary database, which can help improve performance.

  • **Monitoring and Tuning**: Continuously monitor your database's performance and make adjustments as needed based on actual usage patterns.

Caching

innodb_buffer_pool_size:

The `innodb_buffer_pool_size` determines the size of the InnoDB buffer pool, which is a critical component of MySQL's InnoDB storage engine.

  • ⁠Increasing `innodb_buffer_pool_size` allocates more memory for caching data and index pages. This means that more of your frequently accessed data can be stored in memory, reducing the need to read from disk. This can lead to significantly faster SELECT queries, as data retrieval from memory is much faster than disk I/O.

  • ⁠Reduced Disk I/O - A larger buffer pool reduces the frequency of disk reads

  • ⁠Improved Concurrency - When data is readily available in memory, there is less contention for disk resources.

some considerations and potential drawbacks

  • ⁠**Memory Usage**: A larger buffer pool consumes more memory.

  • ⁠**Diminishing Returns**: Increasing the buffer pool size beyond a certain point may yield diminishing returns.  The optimal size depends on the workload and the available system resources.

  • ⁠**InnoDB Locking**: A large buffer pool doesn't directly affect write operations, which are influenced more by factors like the InnoDB log and transaction configuration. So, for write-heavy workloads, optimizing other parameters is necessary.

  • ⁠**Configuration Impact**: Modifying the `innodb_buffer_pool_size` requires a server restart to take effect. It's essential to carefully plan and test any changes

To determine the optimal `innodb_buffer_pool_size` for your specific workload, you should monitor your database's performance, analyze its behavior, and consider factors like the available system memory, the size of your dataset, and the nature of your queries. It's a good practice to start with a conservative setting, monitor performance, and gradually increase it as needed, all while keeping an eye on system memory usage. Consulting with a database administrator or MySQL performance expert can also be helpful in making these decisions.

DB Monitoring and Profiling:

  • Use database monitoring tools to keep track of query performance and system resource utilization. Regularly review performance metrics and adjust configurations accordingly.

AZURE conclusion to challenge

  • To get the best cost , performance, do not use Azure managed db

  • Consider switching to Amazon AWS or continue without managed db.

  • Azure managed db currently 3x slower than container based db

Drupal Application Performance

Upgrade Drupal to 10.1.5, skip 10.0.x

  • Latest new core includes some performance enhancements

Table Indexes

  • Table indexes could be added in some tables that may benefit. This can significantly speed up query performance.

Application Performance Patches

Performance profiling to identify pain points

  • Use XHProf for Application performance and logic profiling to target our efforts

Node revision delete