sethu sethu - 5 months ago 27
MySQL Question

Increasing the caching capability of MySQL

My mysql only caters read requests. I thought, it will be a good idea to make use of the cache completely. I am running MySQL in a VM and that is the only application running inside the VM. I am allocating 2GB memory for that VM. I am using a 64 bit centos on the VM. If you think already it is using the maximum memory that can be used, I can also allocate more to that VM. I am not very good in understanding the mysql settings and finding out the memory footprint used by a process but I am interested in learning how to. Thanks a lot for the help.

These are some information regarding my MYSQL :

mysql> show global variables like "%cache%";
+------------------------------+----------------------+
| Variable_name | Value |
+------------------------------+----------------------+
| binlog_cache_size | 32768 |
| have_query_cache | YES |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| max_binlog_cache_size | 18446744073709547520 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| table_cache | 64 |
| thread_cache_size | 0 |
+------------------------------+----------------------+
13 rows in set (0.00 sec)


mysql> show global variables like "%buffer%";
+-------------------------------+---------+
| Variable_name | Value |
+-------------------------------+---------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_log_buffer_size | 1048576 |
| join_buffer_size | 131072 |
| key_buffer_size | 8384512 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 2097144 |
+-------------------------------+---------+
12 rows in set (0.00 sec)

mysql> show table status where name="items"
-> ;
+-------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| items | MyISAM | 10 | Dynamic | 42667 | 346 | 14775916 | 281474976710655 | 1970176 | 0 | 341337 | 2009-07-22 13:31:00 | 2010-10-20 15:37:18 | NULL | latin1_swedish_ci | NULL | | |
+-------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+


This is the output of my ulimit -a

[sethu@work13 root]$ ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 8191
max locked memory (kbytes, -l) unlimited
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 10240
cpu time (seconds, -t) unlimited
max user processes (-u) 8191
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited


Please let me know if you need more information.

lik lik
Answer

For basic statistics and recommendations you can start with mysqltuner.pl script. Do not apply recommendations blindly, as it might decrease performance.

MySQLTuner Github page

One-liner to fetch latest version of script and run it:

curl -sSL mysqltuner.pl | perl
Comments