// you’re reading...

MySQL

MySQL memory usage stored procedure

Want to know a close approximation of how much RAM MySQL is consuming right now? There’s a simple formula posted at MySQL.com.

Want to 3 add a function to your MySQL database that will allow you to access this value without having to remember the formula?
Answer: Create a Stored Procedure

I’m assuming your using a MySQL database > version 5.1.x and you’re a privileged user. wholesale nba jerseys

Consider this SQL code:

DELIMITER //
CREATE PROCEDURE sproc_show_max_memory ( OUT max_memory DECIMAL(7,4))
BEGIN
SELECT ( @@key_buffer_size + @@query_cache_size + @@tmp_table_size + @@innodb_buffer_pool_size + @@innodb_additional_mem_pool_size + @@innodb_log_buffer_size + @@max_connections * ( @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@join_buffer_size + @@binlog_cache_size + @@thread_stack ) ) / 1073741824 AS MAX_MEMORY_GB INTO max_memory;
END//
DELIMITER ;
CALL sproc_show_max_memory(@show_max_memory);
SELECT @show_max_memory;

Your MySQL client should look like this:

mysql> DELIMITER //
mysql> CREATE PROCEDURE sproc_show_max_memory ( OUT max_memory DECIMAL(7,4))
-> BEGIN
-> SELECT ( @@key_buffer_size  cheap nba jerseys  + @@query_cache_size + @@tmp_table_size  wholesale mlb jerseys  + @@innodb_buffer_pool_size + @@innodb_additional_mem_pool_size + @@innodb_log_buffer_size + @@max_connections * ( @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@join_buffer_size + @@binlog_cache_size + @@thread_stack ) ) / 1073741824 AS MAX_MEMORY_GB INTO max_memory;
-> END//
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> CALL sproc_show_max_memory(@show_max_memory);
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SELECT @show_max_memory;
+------------------+
| @show_max_memory |
+------------------+
|           0.3247 |
+------------------+
1 row in  wholesale mlb jerseys  set (0.00 sec)

*Note: if you notice, there’s a warning after calling the new procedure. This is produced as a result of the decimal(7,4) truncating the data. If you un-suppress warnings (\W), you will find this output:

mysql> CALL sproc_show_max_memory(@show_max_memory);
Query OK, 0 rows affected, 1  PHP  warning (0.00 sec)
Note (Code 1265): Data truncated for column 'max_memory' at row 1

Removing this function is Linked easy.

mysql> DROP PROCEDURE sproc_show_max_memory ;
Query OK, 0 rows affected (0.00 sec)

Discussion

No comments for “MySQL memory usage stored procedure”

Post a comment

Help support my site and buy a domain name at http://domainsemailhosting.com/

%d bloggers like this: