Scribd
Upload
16 views1 page

MySQL Revisión InnoDB

The document contains a series of SQL queries aimed at monitoring and analyzing memory usage, InnoDB performance metrics, and table sizes in a database. It includes commands to check global variables, status of threads, and buffer pool statistics, as well as identifying tables with free space and performance issues. The queries are designed to help optimize database performance by assessing read/write operations and memory allocation.

Uploaded by

japaeza
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
16 views1 page

MySQL Revisión InnoDB

The document contains a series of SQL queries aimed at monitoring and analyzing memory usage, InnoDB performance metrics, and table sizes in a database. It includes commands to check global variables, status of threads, and buffer pool statistics, as well as identifying tables with free space and performance issues. The queries are designed to help optimize database performance by assessing read/write operations and memory allocation.

Uploaded by

japaeza
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 1

select sys.

format_bytes(SUM(current_alloc)) AS current_alloc FROM


sys.x$memory_global_by_current_bytes

SHOW GLOBAL VARIABLES LIKE '%length%';

SHOW STATUS LIKE 'Threads_connected';

SHOW GLOBAL STATUS LIKE 'Innodb_data_reads';


SHOW GLOBAL STATUS LIKE 'Innodb_data_writes';

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_%';

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

-- revisemos si hay muchas lecturas desde disco


SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';
-- ratio = (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100 si
es mayor a 5% las lecturas se hacen en Disco (aumentar buffer_pool_size)

SELECT table_schema, table_name, SUM(data_length + index_length) / 1024 / 1024 AS


size_MB
FROM information_schema.tables
WHERE engine='InnoDB'
GROUP BY table_schema, table_name
ORDER BY size_MB DESC
LIMIT 10;

SELECT table_schema, table_name, data_free


FROM information_schema.tables
WHERE engine='InnoDB' AND data_free > 0;

SELECT * FROM performance_schema.events_statements_summary_by_digest


ORDER BY SUM_NO_INDEX_USED DESC
LIMIT 10;

You might also like