Rudy rudy-remindme

rudy-remindme / scripts.mysql

Created April 15, 2023 11:30 — forked from HarshadRanganathan/scripts.mysql

MySQL Scripts

	/* Get currently running transactions */
	select * from information_schema.innodb_trx

	/* Get currently running transactions where tables in use */
	select * from information_schema.innodb_trx where trx_tables_in_use = 1

	/* show locked tables */
	show open tables where in_use > 0 ;

	SHOW ENGINE INNODB STATUS;

rudy-remindme / Aurora RDS Performance Optimizations

Created April 15, 2023 11:19 — forked from HarshadRanganathan/Aurora RDS Performance Optimizations

AWS Support - Q&A

	1. I see that most of your connections and queries are running on the master(writer) database instance itself without much load on the reader instance. You can consider dividing your workload such that read-only queries/workloads are directed to the reader instance, and only write queries are handled by your writer instance. This will help alleviate the large undo logs(RollbackSegmentHistoryListLength) due to long running queries and in itself this should mitigate a lot of the performance issues.

	One way to achieve splitting of Reads and Writes is by making use of a third party software Proxy solution which can split reads and writes to the appropriate endpoints. Below are a few example software solutions which you can consider:
	[+] ProxySQL - https://proxysql.com/
	[+] Heimdall Data - https://www.heimdalldata.com/


	2. If and where possible, try to split large transactions into multiple smaller transactions. This will again reduce the growth of the undo log which seems to be the main cause of t