Centreon Database Management

Some quick SQL statements. This info is unstructured uptil now, got to make a readable article so bear with me.

 

Show database size and free space left:

SELECT table_schema "centstorage",
sum( data_length + index_length ) / 1024 /
1024 "Data Base Size in MB",
sum( data_free )/ 1024 / 1024 "Free Space in MB"
FROM information_schema.TABLES
GROUP BY table_schema ;

SELECT table_schema "centstorage",
sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",
sum( data_free )/ 1024 / 1024 "Free Space in MB"
FROM information_schema.TABLES
WHERE table_schema = 'centstorage'
GROUP BY table_schema ;


Using the Tuning Primer script I got the following suggestion:

TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 4817 : 1
You have a high ratio of sequential access requests to SELECTs
You may benefit from raising read_buffer_size and/or improving your use of indexes.

mysql> SHOW GLOBAL VARIABLES LIKE 'read_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| read_buffer_size | 131072 |
+------------------+--------+
1 row in set (0.00 sec)

mysql> SET GLOBAL read_buffer_size=1024*256;

mysql> SHOW GLOBAL VARIABLES LIKE 'read_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| read_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.00 sec)

So here are some more adjustments I made:

mysql> SET GLOBAL query_cache_size = 2*1024*1024;
mysql> SHOW GLOBAL VARIABLES LIKE ‘query_cache_size’;
+——————+———+
| Variable_name    | Value   |
+——————+———+
| query_cache_size | 2097152 |
+——————+———+
1 row in set (0.00 sec)

mysql> SHOW INNODB STATUS;

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 134137088; in additional pool allocated 1048576
Buffer pool size   6400
Free buffers       0
Database pages     6399
Modified db pages  64
Pending reads 1
Pending writes: LRU 0, flush list 0, single page 0
Pages read 73899637, created 39570, written 438718
509.28 reads/s, 0.17 creates/s, 5.35 writes/s
Buffer pool hit rate 999 / 1000

# InnoDB Key Buffer
# status opvragen onderstaande variabele: SHOW INNODB STATUS;
innodb_buffer_pool_size=100M

# MyISAM Key Buffer
# status opvragen onderstaande variabele: SHOW STATUS LIKE 'key%';
key_buffer_size=100M

 

mysql> show processlist;
+-----+------+-----------+------+---------+------+-------+------------------+
| Id  | User | Host      | db   | Command | Time | State | Info             |
+-----+------+-----------+------+---------+------+-------+------------------+
| 905 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |
+-----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

mysql> show status like 'Conn%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 914   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show status like '%onn%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Aborted_connects         | 0     |
| Connections              | 914   |
| Max_used_connections     | 29    |
| Ssl_client_connects      | 0     |
| Ssl_connect_renegotiates | 0     |
| Ssl_finished_connects    | 0     |
| Threads_connected        | 2     |
+--------------------------+-------+
7 rows in set (0.01 sec)


 

Restore:
gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]

Three interesting links:

http://mysqlopt.blogspot.com/2012/01/mysql-server-tuning.html

http://www.cyberciti.biz/tips/enable-the-query-cache-in-mysql-to-improve-performance.html