Suggestions to consider for your AWS RDS Parameters Group
innodb_lru_scan_depth=100 # from 1024 to reduce 90% of CPU cycles used for function
innodb_io_capacity=1900 # from 200 to use more of SSD capacity
innodb_log_buffer_size=1G # from 8M to support ~ 30 minutes of log activity in RAM
innodb_log_file_size=4G # from 128M to minimize log rotation
Checklist for UTF8MB4 Conversion:
Space should be available
Foriegn_key_check should be disabled
Need to convert Database
Need to convert Routines
Need to convert Triggers
Need to convert Tables
Note: Bulky tables should be converted usingLOAD DATA ... INTO table syntax
For the database:
ALTER DATABASE database123 CHARACTER SET utf8MB4 ...
Single-threading is very likely to be the cause of getting "behind".
A Master can execute multiple transactions at the same time. But a single-threaded Slave is limited to one transaction at a time. This is the old, simple, way of doing Replication.
Newer replication techniques are more complex. Depending on what version of MySQL you are running, multi-...
This could be a serious problem when replicating between MySQL 8.0 and MariaDB 10.x.
The default (for good technical reasons) COLLATION for the 8.0 is utf8mb4_0900_ai_ci. Note the "255" associated with it. MariaDB has not yet adopted the Unicode 9.0 collations.
Furthermore, Oracle (MySQL 8.0) did a major rewrite of the collation code, thereby possibly ...
First step is to VACUUM ANALYZE the table to make sure the table isn't full of dead tuples and the statistics are up to day.
For a btree index to be maximally efficient, all but one of the conditions on it need to be equality. You have one equality, one inequality, and one max, which is pretty much the same as an inequality. Also, all the equality ...
The query has to scan the index until it finds an entry that satisfies the WHERE condition, and it seems like there are not too many rows that satisfy the condition (PostgreSQL estimates 236), so this takes a while.
The index only scan makes that fast, because you don't have to read the table, but the index itself will be big (check with \di+ ...
There is no advantage in having the buffer_pool_size much more than the total amount of data and indexes you have. (Cf, SHOW TABLE STATUS)
The buffer_pool_size should not be so large that it leads to swapping. Swapping is terrible for performance. Note: If you are running other apps on the same box, you need to take their memory usage into account.
here are two ideas that can be tried..
Run a script or other little app in the background that scans through the killing connections that do not match the established rules
it fires off every few seconds ..
Or create a system event trigger that fires off checks the user IP address and any other information that needs to be checked when the connection is ...
Here is a query what will generate all the necessary update statements.
It temporarily disables foreign key checks so that the update statements succeed.
It updates the default character set for each database that needs it.
It updates the default character set of each table:
If it doesn't have the right default character set.
If it contains text columns ...
It's good you don't see a lot of reads, ideally it should be zero if available memory permits.
When InnoDB needs a page it checks the buffer pool. If the page is in the buffer pool - done. If not - InnoDB will read it from the disk and cache it in the buffer pool. So, if the data set fits into the buffer pool there will be eventually no reads from disk.
force manually an autovacumm policy on mergency situations
This is not going to work. hot_standby_feedback doesn't prevent autovacuum from running. It lets it run but prevents it from removing rows that are not "dead enough". No amount of vacuuming is going to help if you don't get the long running queries on the replicas to go away first.
You can use ...