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
Leave a Reply