# Mysql innodb cache info

Vérifier taille Buffer Pool Size:

```bash
mysql
```

```mysql
SELECT FORMAT (BufferPoolPages*PageSize/POWER(1024,3),2) BufferPoolDataGB FROM (SELECT variable_value BufferPoolPages FROM information_schema.global_status WHERE variable_name ='Innodb_buffer_pool_pages_total') A, (SELECT variable_value PageSize FROM information_schema.global_status WHERE variable_name ='Innodb_page_size') B;
```

[![image.png](https://cavallone.fr/uploads/images/gallery/2024-10/scaled-1680-/u6n5gzYWIlUQmbRP-image.png)](https://cavallone.fr/uploads/images/gallery/2024-10/u6n5gzYWIlUQmbRP-image.png)

Vérifier données en cache:

```bash
Mysql
```

```mysql
SELECT FORMAT (BufferPoolPages*PageSize/POWER(1024,3),2) BufferPoolDataGB FROM (SELECT variable_value BufferPoolPages FROM information_schema.global_status WHERE variable_name ='Innodb_buffer_pool_pages_data') A, (SELECT variable_value PageSize FROM information_schema.global_status WHERE variable_name ='Innodb_page_size') B;
```

[![image.png](https://cavallone.fr/uploads/images/gallery/2024-10/scaled-1680-/o5sKwIapCzjnA7nP-image.png)](https://cavallone.fr/uploads/images/gallery/2024-10/o5sKwIapCzjnA7nP-image.png)

Vérifier % d'utilisation en cache:

```mysql
SELECT CONCAT (FORMAT (DataPages*100.0/TotalPages,2),' %') BufferPoolDataPercentage FROM (SELECT variable_value DataPages FROM information_schema.global_status WHERE variable_name ='Innodb_buffer_pool_pages_data') A, (SELECT variable_value TotalPages FROM information_schema.global_status WHERE variable_name ='Innodb_buffer_pool_pages_total') B;
```

[![image.png](https://cavallone.fr/uploads/images/gallery/2024-10/scaled-1680-/6VgxvLA3PMYv2cQ8-image.png)](https://cavallone.fr/uploads/images/gallery/2024-10/6VgxvLA3PMYv2cQ8-image.png)

  
Données supplémentaires sur le cache:

```mysql
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
```

[![image.png](https://cavallone.fr/uploads/images/gallery/2024-10/scaled-1680-/MWX54sJYOtZlx84j-image.png)](https://cavallone.fr/uploads/images/gallery/2024-10/MWX54sJYOtZlx84j-image.png)

Statut Innodb:

```mysql
SHOW ENGINE INNODB STATUS;
```

Buffer hit ratio:

```mysql
SELECT round ((P2.variable_value /P1.variable_value),4),  P2.variable_value, P1.variable_value FROM information_schema.GLOBAL_STATUS P1, information_schema.GLOBAL_STATUS P2 WHERE P1. variable_name ='innodb_buffer_pool_read_requests'ANDP2. variable_name ='innodb_buffer_pool_reads'; 
```

[![image.png](https://cavallone.fr/uploads/images/gallery/2024-10/scaled-1680-/2a0rFqK3Vtk0W097-image.png)](https://cavallone.fr/uploads/images/gallery/2024-10/2a0rFqK3Vtk0W097-image.png)