Mysql:内存使用量不到一半。负载高

Mysql:内存使用量不到一半。负载高

我有一个 Ubuntu 8.04 LTS,有 8Gb 内存,有双[电子邮件保护],SATA II 上的软件 raid。几个月来,我一直在尝试调整服务器,但似乎还远远没有完成。服务器仅用于 mysql 数据库(mysql Ver 14.12 Distrib 5.0.51a,适用于 debian-linux-gnu (x86_64),使用 readline 5.2)。当负载上升到 15 时(平均约为 6),服务器平均每秒查询 200 次,最大每秒查询 800 次。

现在我至少想利用整个内存,希望获得更好的性能,现在只使用了一半。欢迎任何提示。

以下是 my.cnf 中的部分设置(如果您想要更多值,我可以粘贴它们):

key_buffer = 600M
max_allowed_packet = 1M
table_cache = 8000
sort_buffer_size = 8M
max_connections = 170
query_cache_type = 1
query_cache_limit = 2M
query_cache_size = 128M
read_buffer_size = 8M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 256M
thread_cache_size = 8
wait_timeout=15
connect_timeout=10
interactive_timeout=40
join_buffer_size=6M
query_cache_limit=2M
old_passwords=1
max_heap_table_size = 128M
tmp_table_size = 128M
open_files_limit = 8192

vmstat 输出5秒:

procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa
 1  0   8648 3448312 387860 1273572    0    0     0   178 2161 2526 42 24 34  0
 2  0   8648 3448552 387860 1273572    0    0     0    85 1962 2219 48 40 12  0
 7  0   8648 3448116 387860 1273572    0    0     0   106 1575 1809 52 46  1  0
 6  0   8648 3448132 387860 1273572    0    0     0   194 1423 1481 57 44  0  0
12  0   8648 3423296 387860 1273572    0    0     0    67 2184 2525 61 40  0  0
 7  0   8648 3371656 387860 1273576    0    0     0   118 1648 1859 60 40  0  0
 2  0   8648 3423604 387860 1273576    0    0     0    69 1700 1882 59 41  0  0
 8  0   8648 3423052 387860 1273576    0    0     0    95 2047 2240 62 38  0  0
 3  0   8648 3423184 387860 1273576    0    0     0    54 1588 1839 57 43  0  0
 8  0   8648 3423468 387860 1273576    0    0     0   107 1592 1875 58 42  1  0
10  0   8648 3389928 387860 1273528    0    0     0    59 1810 2054 59 41  0  0
13  0   8648 3422836 387860 1273576    0    0     0    93 1691 1835 60 40  0  0
10  0   8648 3367176 387860 1273576    0    0     0    79 1809 2122 65 35  0  0
 6  0   8648 3358868 387860 1273576    0    0     0    95 1638 1847 56 34 10  0
 7  0   8648 3421612 387860 1273576    0    0     0    75 2127 2441 61 39  0  0
 9  0   8648 3391568 387860 1273576    0    0     0    96 1942 2182 63 37  0  0
19  0   8648 3249100 387860 1273576    0    0     0    82 1627 1853 63 37  0  0
 8  0   8648 3405004 387860 1273580    0    0     0   143 2075 2360 64 36  0  0
11  0   8648 3410264 387860 1273580    0    0     0    60 2025 2341 62 38  0  0
 6  0   8648 3389752 387860 1273580    0    0     0    48 1690 1917 58 42  0  0
 4  0   8648 3388100 387860 1273580    0    0     0    81 2191 2598 64 35  1  0
14  0   8648 3389944 387860 1273576    0    0     0   102 1825 2054 55 45  0  0
19  0   8648 3296832 387860 1273576    0    0     0    99 1799 2092 59 41  0  0
15  0   8648 3267500 387860 1273576    0    0     0    85 1466 1705 58 42  0  0
11  0   8648 3414908 387860 1273576    0    0     0    85 1627 1837 52 48  0  0
15  0   8648 3374624 387860 1273576    0    0     0    69 1947 2230 62 39  0  0
 8  0   8648 3405948 387860 1273576    0    0     0    98 1558 1845 60 40  0  0
 9  0   8648 3380652 387860 1273576    0    0     0   122 1845 2175 65 35  0  0
 9  0   8648 3370580 387860 1273576    0    0     0    50 2075 2436 62 37  1  0

感谢您分享您的想法。自从我将大型表从 myisam 移至 innodb 以来,我已经有 20 小时了。性能没有提高,但默认延迟(服务器空闲时)确实提高了。所以我正在考虑切换回 myisam(因为大多数查询都是读取)。关于将 myisam 索引增加到 4Gb,我不知道这是否能解决问题,因为它无论如何都没有完全使用。我在下面粘贴了一些使用 MySQLTuner 脚本进行的分析。

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.51a-3ubuntu5.8
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 389M (Tables: 821)
[--] Data in InnoDB tables: 112M (Tables: 38)
[!!] Total fragmented tables: 35

-------- Performance Metrics -------------------------------------------------
[--] Up for: 17h 47m 22s (44M q [689.243 qps], 1M conn, TX: 18B, RX: 3B)
[--] Reads / Writes: 94% / 6%
[--] Total buffers: 4.8G global + 12.2M per thread (150 max threads)
[OK] Maximum possible memory usage: 6.6G (84% of installed RAM)
[OK] Slow queries: 0% (1K/44M)
[!!] Highest connection usage: 100%  (151/150)
[OK] Key buffer size / total MyISAM indexes: 600.0M/216.9M
[OK] Key buffer hit rate: 100.0% (11B cached / 148K reads)
[OK] Query cache efficiency: 39.7% (7M cached / 19M selects)
[!!] Query cache prunes per day: 5593858
[OK] Sorts requiring temporary tables: 2% (148K temp sorts / 6M sorts)
[!!] Joins performed without indexes: 25416
[OK] Temporary tables created on disk: 9% (296K on disk / 3M total)
[OK] Thread cache hit rate: 96% (35K created / 1M connections)
[OK] Table cache hit rate: 78% (2K open / 2K opened)
[OK] Open file limit used: 17% (2K/16K)
[OK] Table locks acquired immediately: 99% (16M immediate / 16M locks)
[OK] InnoDB data size / buffer pool: 112.9M/4.0G

所以我要切换回初始配置,效果会好一些。如果您看到任何可以通过增加内存消耗来提高性能的设置,那就太好了。项目可以在结构/编码方面得到很大改进(在 jois 上损失很多),但现在我必须找到一个解决方案来控制损害。

# The MySQL server
[mysqld]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
skip-locking
skip-name-resolv
key_buffer = 600M
max_allowed_packet = 1M
table_cache = 8000
sort_buffer_size = 1M
max_connections = 150
query_cache_type = 1
query_cache_limit = 2M
query_cache_size = 128M
read_buffer_size = 2M
read_rnd_buffer_size = 3M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
wait_timeout=15
connect_timeout=10
interactive_timeout=40
join_buffer_size=6M
query_cache_limit=2M
old_passwords=1
max_heap_table_size = 128M
tmp_table_size = 128M
open_files_limit = 8192
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4
#
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
#log-bin=/home/mysqlbin/mysql-bin
#log-bin=mysql-bin
expire_logs_days=10
max_binlog_size=100M

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 1

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 256M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 64M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 300M
sort_buffer_size = 120M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 300M
sort_buffer_size = 120M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

答案1

我假设您正在为您的数据库使用 myisam 引擎,如果是这种情况,您需要将 myisam 索引设置为 4GB,因为这是 myisam 支持的最大索引大小。

您还应该尝试考虑哪种引擎最适合您的数据库。Myisam 更适合读取,但写入效率不如 innodb。此外,myisam 锁定整个表,而 innodb 仅锁定行。您可以运行混合引擎环境,但 8GB 不足以完全实现这一点。

您能详细说明一下您使用的是什么引擎吗?

答案2

将 innodb_buffer_pool_size 设置为总内存的 80% 左右。

http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size

innodb_buffer_pool_size:InnoDB 用于缓存其表的数据和索引的内存缓冲区的大小(以字节为单位)。默认值为 8MB。此值设置的越大,访问表中数据所需的磁盘 I/O 就越少。在专用数据库服务器上,您可以将其设置为机器物理内存大小的 80%。但是,不要将其设置得太大,因为物理内存竞争可能会导致操作系统分页。此外,初始化缓冲池的时间大致与其大小成正比。在大型安装中,此初始化时间可能很长。例如,在现代 Linux x86_64 服务器上,初始化 10GB 缓冲池大约需要 6 秒。请参见第 7.6.2 节“InnoDB 缓冲池”

相关内容