问请推荐my.cnf的更改

EN

[mysqld]
local-infile=0
tmpdir = /var/mysqltmp
character-set-server=utf8
bind-address=127.0.0.1
skip-federated
skip-archive
back_log = 75
max_connections = 300
key_buffer_size = 512M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 1024M
join_buffer_size = 64K
read_buffer_size = 64K
sort_buffer_size = 128K
table_definition_cache = 40000
table_open_cache = 40000
thread_cache_size = 64
wait_timeout = 120
connect_timeout = 10
tmp_table_size = 512M
max_heap_table_size = 512M
max_allowed_packet=536870912
max_seeks_for_key = 1000
group_concat_max_len = 102400
max_length_for_sort_data = 1024
net_buffer_length = 16384
max_connect_errors = 100000
concurrent_insert = 2
read_rnd_buffer_size = 256K
bulk_insert_buffer_size = 8M
query_cache_limit = 0
query_cache_size = 0
query_cache_type = 0
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = InnoDB

log_warnings=1
innodb_purge_threads=1
innodb_doublewrite = 1
innodb_file_per_table = 1
innodb_open_files = 1000
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_instances = 16
innodb_buffer_pool_size = 16G

innodb_log_files_in_group = 2
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 0
innodb_lock_wait_timeout=50
innodb_flush_method = O_DIRECT
innodb_support_xa=1

innodb_io_capacity = 4600
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_flush_neighbors = 0
open_files_limit=14000

             total       used       free     shared    buffers     cached
Mem:         64321      63925        395        270       1235      45936
-/+ buffers/cache:      16753      47567
Swap:         4204        451       3753

>>  MySQLTuner 1.6.1 - Major Hayden <major@mhtx.net>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.0.21-MariaDB
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -ARCHIVE +Aria +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MyISAM
[--] Data in MyISAM tables: 1G (Tables: 113)
[--] Data in InnoDB tables: 3G (Tables: 1535)
[--] Data in MEMORY tables: 2M (Tables: 33)
[!!] Total fragmented tables: 238

-------- Security Recommendations  -------------------------------------------
[OK] There is no anonymous account in all database users
[OK] All database users have passwords assigned
[--] There is 605 basic passwords in the list.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 15d 23h 22m 44s (58M q [42.243 qps], 5M conn, TX: 492B, RX: 50B)
[--] Reads / Writes: 65% / 35%
[--] Binary logging is disabled
[--] Total buffers: 17.1G global + 800.0K per thread (300 max threads)
[OK] Maximum reached memory usage: 17.2G (27.31% of installed RAM)
[OK] Maximum possible memory usage: 17.4G (27.65% of installed RAM)
[OK] Slow queries: 0% (78/58M)
[OK] Highest usage of available connections: 7% (22/300)
[OK] Aborted connections: 0.00%  (158/5240320)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (34K temp sorts / 3M sorts)
[!!] Joins performed without indexes: 167178
[OK] Temporary tables created on disk: 8% (196K on disk / 2M total)
[OK] Thread cache hit rate: 99% (22 created / 5M connections)
[!!] Table cache hit rate: 2% (2K open / 66K opened)
[OK] Open file limit used: 0% (295/80K)
[OK] Table locks acquired immediately: 99% (89M immediate / 89M locks)

-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 18.5% (99M used / 536M cache)
[OK] Key buffer size / total MyISAM indexes: 512.0M/545.4M
[OK] Read Key buffer hit rate: 99.9% (51M cached / 51K reads)
[!!] Write Key buffer hit rate: 47.6% (14M cached / 7M writes)

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 16.0G/3.2G
[OK] InnoDB buffer pool instances: 16
[!!] InnoDB Used buffer: 18.64% (195406 used/ 1048560 total)
[OK] InnoDB Read buffer efficiency: 100.00% (25814174259 hits/ 25814306598 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 5363748 writes)

-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.

-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Beware that open_files_limit (80311) variable
    should be greater than table_open_cache ( 40000)
Variables to adjust:
    query_cache_size (>= 8M)
    join_buffer_size (> 64.0K, or always use indexes with joins)
    table_open_cache (> 40000)

        -- MYSQL PERFORMANCE TUNING PRIMER --
             - By: Matthew Montgomery -

MySQL Version 10.0.21-MariaDB x86_64

Uptime = 15 days 23 hrs 23 min 37 sec
Avg. qps = 42
Total Questions = 58307091
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/10.0/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 78 out of 58307112 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/10.0/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 64
Current threads_cached = 21
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 300
Current threads_connected = 1
Historic max_used_connections = 22
The number of used connections is 7% 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 : 16.53 G
Configured Max Per-thread Buffers : 234 M
Configured Max Global Buffers : 16.51 G
Configured Max Memory Limit : 16.74 G
Physical Memory : 62.81 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 545 M
Current key_buffer_size = 512 M
Key cache miss rate is 1 : 1003
Key buffer free ratio = 81 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is supported but not enabled
Perhaps you should set the query_cache_size

SORT OPERATIONS
Current sort_buffer_size = 128 K
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

JOINS
/usr/local/bin/tuning-primer.sh: line 402: export: `2097152': not a valid identifier
Current join_buffer_size = 68.00 K
You have had 167181 queries where a join could not use an index properly
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 = 80311 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 = 40000 tables
Current table_definition_cache = 40000 tables
You have a total of 1767 tables
You have 2015 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 512 M
Current tmp_table_size = 512 M
Of 2228513 temp tables, 8% were created on disk
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 64 K
Current table scan ratio = 114 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 7207
Your table locking seems to be fine