Mysql：less于一半的内存使用。 高负荷

我有一个Ubuntu 8.04 LTS，8Gb内存，双[email protected]，软件袭击sata II。 现在，我尝试调整服务器的几个monts，似乎还远远没有完成。 服务器仅用于mysql数据库（对于使用readline 5.2的debian-linux-gnu（x86_64），使用mysql Ver 14.12 Distrib 5.0.51a）。 当负载达到15（平均值约为6）时，服务器平均为200查询/秒，最大为800查询/秒。

现在我想至less要利用整个记忆，希望有更好的performance，现在只用了一半。 任何提示，欢迎。

下面是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秒钟：

• 增加Virtual Box客户内存分配至75％
• 解锁PC导致所有应用程序崩溃？
• 在Debian挤压Xen内核使1GB内存'缺席'
• 分叉和内存分配问题
• 笔记本电脑上开发人员的最佳apacheconfiguration

 procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu---- rb 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 

所以我要回到最初的configuration文件，工作稍微好一些。 如果您看到任何可以通过增加内存消耗来提高性能的设置，那就太好了。 这些项目可以在结构/编码方面得到很大的改进（在jois上丢失了很多），但是现在我必须find一个解决scheme来作为损害控制。

 # 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