MySQL Database Optimization on GCP

Our MySQL database on GCP is experiencing significant performance issues, leading to long wait times and system locks. While I have not undertaken any optimization efforts yet, it's clear that our database is struggling due to a range of factors, including complex queries with outer joins, high process concurrency, and large volumes of inserts. The ideal candidate for this project should have the following skills and experience: - Proficient in MySQL database performance optimization - Prior experience with Google Cloud Platform (GCP) databases - Skilled in handling complex SQL queries effectively - Ability to manage high process concurrency and large volume inserts Key Responsibilities: - Diagnose the root cause of long wait times and system locks - Optimize the database performance through careful tuning of queries and indexes - Implement strategies to manage high process concurrency and large volume inserts The scale of our data processing needs is small to medium-sized datasets, and our priority is to ensure that the system is responsive and reliable. Your role will be crucial in streamlining our database performance and preventing future issues, allowing our business processes to run smoothly. Problem Description: We operate a MySQL 8.0 Enterprise database hosted on the GCP SQL service. This service is configured with 2 vCPUs, 8 GB memory and 15 GB SSD storage. We currently have 56 tables, including dimension, fact, relationship tables, and tables that simulate a materialized view. The bank settings are as follows: autocommit = 1 binlog_transaction_dependency_tracking = WRITESET innodb_buffer_pool_size = 4.25 GB innodb_table_locks = ON max_heap_table_size = 16 MB transaction_isolation = REPEATABLE-READ max_connections = 4030 sort_buffer_size = 256 KB join_buffer_size = 256 KB thread_cache_size = 48 tmp_table_size = 16 MB We face performance issues in some queries in the application, especially those that require outer joins. To optimize these queries, we create tables that simulate materialized views. These views use several tables, including one that contains user data (name and email), in addition to three user relationship tables with a product: r_end_user_direito_uso, r_recommendation and r_scenario. These tables store information about what the user currently has, what is recommended for them and what they will end up with, respectively. Two of these relationship tables are historical, but we only need the most recent data for the view. Our objective is to create a record containing the user's name and email, a product (id_ppn), and whether they already have it, whether it is recommended for them, and whether they will continue with the product, in addition to other data related to the application. However, when executing this query, parts of it enter system lock for several minutes, resulting in very high response times. To ensure that the view is always up to date, we create triggers that send a message to a table monitored by an event. This event, upon detecting the message, triggers the view update procedure. However, due to the large volume of data insertions (which can exceed 50,000 records at once in blocks of 1,000), the bank has suffered from expired waiting times for query execution. Furthermore, when the database receives a large number of read queries simultaneously, it becomes slower, generating unexpected process competition for the size of the application.