Настройка производительности сервера MySQL

Помимо  параметра mysqltuner – существует также другой метод проверки действующих настроек MySQL с целью получения комментариев на предмет улучшения производительности.

Чтобы получить данную информацию необходимо применение tuning-primer.sh

Настройка производительности сервера MySQL

Итак, производим его загрузку:

# wget https://launchpad.net/mysql-tuning-primer/trunk/1.6-
r1/+download/tuning-primer.sh
# chmod +x tuning-primer.sh

Для корректного функционирования программы, вам понадобится наличие калькулятора bc. Чтобы проверить его наличие, используйте следующую комбинацию:

1 # which bc
2 which: no bc in (/sbin:/bin:/usr/sbin:/usr/bin)

В случае отсутствия, необходимо произвести установку:

1 # yum -y install bc
2 ...
3 Installed:
4 bc.x86_64 0:1.06.95-1.el6
5	 
6 Complete!

Теперь можно осуществлять запуск:

001 # ./tuning-primer.sh
002	 
003 Using login values from ~/.my.cnf
004 - INITIAL LOGIN ATTEMPT FAILED -
005 Testing for stored webmin passwords:
006 None Found
007 Could not auto detect login info!
008 Found potential sockets: /var/lib/mysql/mysql.sock
009 Using: /var/lib/mysql/mysql.sock
010 Would you like to provide a different socket?: [y/N]
011 Do you have your login handy ? [y/N] : y
012 User: root
013 Password: p@ssw0rd
014	 
015 Would you like me to create a ~/.my.cnf file for you? [y/N] : y
016	 
017 ~/.my.cnf already exists!
018	 
019 Replace ? [y/N] : y
020	 
021 -- MYSQL PERFORMANCE TUNING PRIMER --
022 - By: Matthew Montgomery -
023	 
024 MySQL Version 5.5.38 i686
025	 
026 Uptime = 0 days 1 hrs 4 min 54 sec
027 Avg. qps = 0
028 Total Questions = 17
029 Threads Connected = 1
030	 
031 Warning: Server has not been running for at least 48hrs.
032 It may not be safe to use these recommendations
033	 
034 To find out more information on how each of these
035 runtime variables effects performance visit:
036 http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html
037 Visit http://www.mysql.com/products/enterprise/advisors.html
038 for info about MySQL's Enterprise Monitoring and Advisory Service
039	 
040 SLOW QUERIES
041 The slow query log is NOT enabled.
042 Current long_query_time = 10.000000 sec.
043 You have 0 out of 38 that take longer than 10.000000 sec. to complete
044 Your long_query_time seems to be fine
045	 
046 BINARY UPDATE LOG
047 The binary update log is NOT enabled.
048 You will not be able to do point in time recovery
049 See http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html
050	 
051 WORKER THREADS
052 Current thread_cache_size = 0
053 Current threads_cached = 0
054 Current threads_per_sec = 1
055 Historic threads_per_sec = 0
056 Your thread_cache_size is fine
057	 
058 MAX CONNECTIONS
059 Current max_connections = 151
060 Current threads_connected = 1
061 Historic max_used_connections = 1
062 The number of used connections is 0% of the configured maximum.
063 You are using less than 10% of your configured max_connections.
064 Lowering max_connections could help to avoid an over-allocation of memory
065 See "MEMORY USAGE" section to make sure you are not over-allocating
066	 
067 INNODB STATUS
068 Current InnoDB index space = 0 bytes
069 Current InnoDB data space = 0 bytes
070 Current InnoDB buffer pool free = 98 %
071 Current innodb_buffer_pool_size = 128 M
072 Depending on how much space your innodb indexes take up it may be safe
073 to increase this value to up to 2 / 3 of total system memory
074	 
075 MEMORY USAGE
076 Max Memory Ever Allocated : 144 M
077 Configured Max Per-thread Buffers : 122 M
078 Configured Max Global Buffers : 144 M
079 Configured Max Memory Limit : 266 M
080 Physical Memory : 1006 M
081 Max memory limit seem to be within acceptable norms
082	 
083 KEY BUFFER
084 No key reads?!
085 Seriously look into using some indexes
086 Current MyISAM index space = 105 K
087 Current key_buffer_size = 16 K
088 Key cache miss rate is 1 : 0
089 Key buffer free ratio = 87 %
090 Your key_buffer_size seems to be fine
091	 
092 QUERY CACHE
093 Query cache is supported but not enabled
094 Perhaps you should set the query_cache_size
095	 
096 SORT OPERATIONS
097 Current sort_buffer_size = 64 K
098 Current read_rnd_buffer_size = 256 K
099 No sort operations have been performed
100 Sort buffer seems to be fine
101	 
102 JOINS
103 Current join_buffer_size = 132.00 K
104 You have had 0 queries where a join could not use an index properly
105 Your joins seem to be using indexes properly
106	 
107 OPEN FILES LIMIT
108 Current open_files_limit = 1024 files
109 The open_files_limit should typically be set to at least 2x-3x
110 that of table_cache if you have heavy MyISAM usage.
111 Your open_files_limit value seems to be fine
112	 
113 TABLE CACHE
114 Current table_open_cache = 4 tables
115 Current table_definition_cache = 400 tables
116 You have a total of 41 tables
117 You have 4 open tables.
118 Current table_cache hit rate is 2%
119 , while 100% of your table cache is in use
120 You should probably increase your table_cache
121 
122 TEMP TABLES
123 Current max_heap_table_size = 16 M
124 Current tmp_table_size = 16 M
125 Of 259 temp tables, 12% were created on disk
126 Created disk tmp tables ratio seems fine
127	 
128 TABLE SCANS
129 Current read_buffer_size = 256 K
130 Current table scan ratio = 4 : 1
131 read_buffer_size seems to be fine
132	 
133 TABLE LOCKING
134 Current Lock Wait ratio = 0 : 296
135 Your table locking seems to be fine

Поскольку массив выводимой информации большой, для удобства перед блоком:

1 case $mode in
2     all | ALL )

Прописываем {, в свою очередь в конце скрипта:

1 } | tee tuning-primer.log

Также удовлетворительного результата можно добиться путём его моментального перенаправления в файл:

1 # ./tuning-primer.sh &> tuning-primer.log
01 # head tuning-primer.log
02	 
03 -- MYSQL PERFORMANCE TUNING PRIMER --
04 - By: Matthew Montgomery -
05	 
06 MySQL Version 5.1.73 x86_64
07	 
08 Uptime = 21 days 1 hrs 37 min 14 sec
09 Avg. qps = 0
10 Total Questions = 253928
11 Threads Connected = 2

Comments

comments

Ваш e-mail не будет опубликован. Обязательные поля помечены *