For tuning the mysql server you could use the MySQL Tuning Primer Script to tune you cache.
get the script from here.
then run the script with:
|
1 |
./tuning-primer.sh |
the result could look like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 |
-- MYSQL PERFORMANCE TUNING PRIMER -- - By: Matthew Montgomery - MySQL Version 5.5.40-0+wheezy1 x86_64 Uptime = 13 days 23 hrs 44 min 21 sec Avg. qps = 2 Total Questions = 3355704 Threads Connected = 1 Server has been running for over 48hrs. It should be safe to follow these recommendations To find out more information on how each of these runtime variables effects performance visit: http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html Visit http://www.mysql.com/products/enterprise/advisors.html for info about MySQL's Enterprise Monitoring and Advisory Service SLOW QUERIES The slow query log is NOT enabled. Current long_query_time = 10.000000 sec. You have 0 out of 3355725 that take longer than 10.000000 sec. to complete Your long_query_time seems to be fine BINARY UPDATE LOG The binary update log is NOT enabled. You will not be able to do point in time recovery See http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html WORKER THREADS Current thread_cache_size = 8 Current threads_cached = 7 Current threads_per_sec = 0 Historic threads_per_sec = 0 Your thread_cache_size is fine MAX CONNECTIONS Current max_connections = 24 Current threads_connected = 1 Historic max_used_connections = 12 The number of used connections is 50% of the configured maximum. Your max_connections variable seems to be fine. INNODB STATUS Current InnoDB index space = 240 K Current InnoDB data space = 352 K Current InnoDB buffer pool free = 94 % Current innodb_buffer_pool_size = 128 M Depending on how much space your innodb indexes take up it may be safe to increase this value to up to 2 / 3 of total system memory MEMORY USAGE Max Memory Ever Allocated : 2.39 G Configured Max Per-thread Buffers : 3.38 G Configured Max Global Buffers : 720 M Configured Max Memory Limit : 4.09 G Physical Memory : 7.22 G Max memory limit seem to be within acceptable norms KEY BUFFER Current MyISAM index space = 561 M Current key_buffer_size = 512 M Key cache miss rate is 1 : 7 Key buffer free ratio = 64 % Your key_buffer_size seems to be fine QUERY CACHE Query cache is enabled Current query_cache_size = 64 M Current query_cache_used = 2 M Current query_cache_limit = 1 M Current Query cache Memory fill ratio = 3.27 % Current query_cache_min_res_unit = 4 K Your query_cache_size seems to be too high. Perhaps you can use these resources elsewhere MySQL won't cache query results that are larger than query_cache_limit in size SORT OPERATIONS Current sort_buffer_size = 128 M Current read_rnd_buffer_size = 256 K Sort buffer seems to be fine JOINS Current join_buffer_size = 132.00 K You have had 0 queries where a join could not use an index properly Your joins seem to be using indexes properly OPEN FILES LIMIT Current open_files_limit = 1024 files The open_files_limit should typically be set to at least 2x-3x that of table_cache if you have heavy MyISAM usage. Your open_files_limit value seems to be fine TABLE CACHE Current table_open_cache = 495 tables Current table_definition_cache = 400 tables You have a total of 64 tables You have 72 open tables. The table_cache value seems to be fine TEMP TABLES Current max_heap_table_size = 32 M Current tmp_table_size = 32 M Of 812 temp tables, 13% were created on disk Created disk tmp tables ratio seems fine TABLE SCANS Current read_buffer_size = 16 M Current table scan ratio = 5 : 1 read_buffer_size is over 8 MB there is probably no need for such a large read_buffer TABLE LOCKING Current Lock Wait ratio = 0 : 3355993 Your table locking seems to be fine |
and edit your config:
|
1 |
nano /etc/mysql/my.cnf |
