Merhaba, my.cnf optimizasyonu yapmak istiyorum ancak bazı verilerin ne anlama geldiğini bilmediğim için yardıma ihtiyacım var. tuning-prime.sh ile sonucları aldım. detayları aşağıda paylaşıyor olacagım. optimizasyon önerisini uyguladığım arkadaşa 50 TL hemen göndereceğim
Server Bilgileri:
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 8
On-line CPU(s) list: 0-7
Thread(s) per core: 2
Core(s) per socket: 4
Socket(s): 1
NUMA node(s): 1
Vendor ID: GenuineIntel
CPU family: 6
Model: 60
Model name: Intel(R) Xeon(R) CPU E3-1230 v3 @ 3.30GHz
Stepping: 3
CPU MHz: 3300.000
CPU max MHz: 3300,0000
CPU min MHz: 1064,0000
BogoMIPS: 6584.98
Virtualization: VT-x
L1d cache: 32K
L1i cache: 32K
L2 cache: 256K
L3 cache: 8192K
Current CPU usage : 0.512665%.
Current RAM usage : 3257/15840MB (20.56%).
Current Disk usage : 121/215GB (57%).
System uptime : 684 days, 6 hours, 41 minutes.
Current CPU usage : 0.512665%.
UPTIME
Current Load average: 0,02, 0,07, 0,06
Current CPU usage : 0.512665%.
Current RAM usage : 4489/15840MB (28.34%).
Current Disk usage : 121/215GB (57%).
System uptime : 684 days, 7 hours, 21 minutes.
TUNING-PRIME SONUCU
-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -
MySQL Version 10.4.18-MariaDB x86_64
Uptime = 329 days 8 hrs 47 min 48 sec
Avg. qps = 8
Total Questions = 231605719
Threads Connected = 2
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/10.4/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 = 6.000000 sec.
You have 14 out of 231605733 that take longer than 6.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/10.4/en/point-in-time-recovery.html
WORKER THREADS
Current thread_cache_size = 50
Current threads_cached = 11
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine
MAX CONNECTIONS
Current max_connections = 750
Current threads_connected = 2
Historic max_used_connections = 36
The number of used connections is 4% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See "MEMORY USAGE" section to make sure you are not over-allocating
No InnoDB Support Enabled!
MEMORY USAGE
Max Memory Ever Allocated : 12.45 G
Configured Max Per-thread Buffers : 6.22 G
Configured Max Global Buffers : 12.15 G
Configured Max Memory Limit : 18.38 G
Physical Memory : 15.46 G
Max memory limit exceeds 90% of physical memory
KEY BUFFER
Current MyISAM index space = 607 K
Current key_buffer_size = 2 M
Key cache miss rate is 1 : 760267
Key buffer free ratio = 68 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere
QUERY CACHE
Query cache is enabled
Current query_cache_size = 32 M
Current query_cache_used = 12 M
Current query_cache_limit = 512 K
Current Query cache Memory fill ratio = 40.34 %
Current query_cache_min_res_unit = 4 K
Query Cache is 22 % fragmented
Run "FLUSH QUERY CACHE" periodically to defragment the query cache memory
If you have many small queries lower 'query_cache_min_res_unit' to reduce fragmentation.
MySQL won't cache query results that are larger than query_cache_limit in size
SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 2 M
Sort buffer seems to be fine
JOINS
./tuning-primer.sh: line 402: export: `2097152': geçerli bir belirteç değil
Current join_buffer_size = 2.00 M
You have had 260295 queries where a join could not use an index properly
You have had 244 joins without keys that check for key usage after each row
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.
Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.
OPEN FILES LIMIT
Current open_files_limit = 16384 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 = 2000 tables
Current table_definition_cache = 400 tables
You have a total of 368 tables
You have 659 open tables.
The table_cache value seems to be fine
TEMP TABLES
Current max_heap_table_size = 1.00 G
Current tmp_table_size = 1.00 G
Of 10212578 temp tables, 18% were created on disk
Created disk tmp tables ratio seems fine
TABLE SCANS
Current read_buffer_size = 2 M
Current table scan ratio = 38513 : 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.
TABLE LOCKING
Current Lock Wait ratio = 1 : 497
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'