mysql 如何加速_如何配置mysql服务器(加速)

我的数据库大小是13.4Gb。服务器有32GB的RAM。

但是查询类型选择仍然很慢。

-------- Performance Metrics -------------------------------------------------

[--] Up for: 5s (9 q [1.800 qps], 7 conn, TX: 17K, RX: 1K)

[--] Reads / Writes: 100% / 0%

[--] Binary logging is disabled

[--] Total buffers: 24.5G global + 392.4M per thread (64 max threads)

[OK] Maximum reached memory usage: 24.9G (79.27% of installed RAM)

[!!] Maximum possible memory usage: 49.0G (156.10% of installed RAM)

[OK] Slow queries: 0% (0/9)

[OK] Highest usage of available connections: 1% (1/64)

[!!] Aborted connections: 14.29% (1/7)

[!!] Query cache efficiency: 0.0% (0 cached / 4 selects)

[OK] Query cache prunes per day: 0

[OK] Temporary tables created on disk: 0% (0 on disk / 2 total)

[OK] Thread cache hit rate: 85% (1 created / 7 connections)

[OK] Table cache hit rate: 93% (100 open / 107 opened)

[OK] Open file limit used: 0% (15/1M)

[OK] Table locks acquired immediately: 100% (100 immediate / 100 locks)

-------- InnoDB Metrics -----------------------------------------------------

[--] InnoDB is enabled.

[OK] InnoDB buffer pool / data size: 23.4G/13.4G

[!!] InnoDB buffer pool instances: 24

[!!] InnoDB Used buffer: 0.03% (414 used/ 1535976 total)

[!!] InnoDB Read buffer efficiency: 79.34% (1463 hits/ 1844 total)

[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)

[OK] InnoDB log waits: 0.00% (0 waits / 2 writes)

我有以下服务器mysql配置:

key_buffer_size = 1024M

sort_buffer_size = 256M

max_allowed_packet = 1024M

read_buffer_size = 8M

read_rnd_buffer_size = 128M

thread_stack = 128K

query_cache_limit = 1M

query_cache_size = 8M

query_cache_type = 1

thread_cache_size = 16

max_heap_table_size = 128M

tmp_table_size = 64M

net_read_timeout = 3600

net_write_timeout = 3600

wait_timeout = 120

interactive_timeout = 120

table_open_cache = 1024M

#join_buffer_size=2048M

max_connections = 64

innodb_open_files = 4096

innodb_file_per_table = 1

innodb_flush_method=O_DIRECT

innodb_flush_log_at_trx_commit=2

innodb_log_file_size = 512M

innodb_log_buffer_size = 16M

innodb_buffer_pool_size=24000M

innodb_buffer_pool_instances=24

innodb_buffer_pool_chunk_size = 500M

#innodb_additional_mem_pool_size = 20M

#innodb_thread_concurrency = 16

innodb_read_io_threads = 8

innodb_write_io_threads = 8

innodb_stats_on_metadata = 0

#innodb_io_capacity=2000

innodb_data_file_path=ibdata1:10M:autoextend

您能告诉我如何正确地纠正以下错误并提高从数据库中采样的速度吗?

[!!] InnoDB buffer pool instances: 24

[!!] InnoDB Used buffer: 0.03% (414 used/ 1535976 total)

[!!] InnoDB Read buffer efficiency: 79.34% (1463 hits/ 1844 total)

[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)

[!!] Maximum possible memory usage: 49.0G (156.10% of installed RAM)

[!!] Aborted connections: 14.29% (1/7)

P、 我不使用索引,因为它增加了查询超时和数据库的大小。