Centreon Database Management

Centreon Database Management

Centreon Database Management 150 150 Roderick Derks

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

Roderick Derks

Liefhebber van fietsen, van het oplossen van IT puzzels, en van het delen van informatie om anderen te helpen.

All stories by:Roderick Derks

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Your Name (required)

Your Email (required)

Subject

Your Message

Your Name (required)

Your Email (required)

Subject

Your Message