Am 01.12.2011 19:42, schrieb Stdranwl: > Many thanks, I wanted to finalized on RAM requirement for one of my server so > do we > have any calculation method to choose intial RAM?
depends on the used storage engines, buffer-pools, data-size etc. with myISAM you need only all per-connection-buffers*max_connections + key_buffer + query_cache + 20% reserve with innodb the buffer_pool_size SHOULD be as big as the data and so the memory equal but always the moemory as large as buffer_pool_size + above but this only hsould because as example on a dbmail-machine you never need the memory-buffers as large as the database for good performance below our mail-server configuraton (only the memory-relevant parts) this is a virtual machine for dbmail-services with currently 8 GB database and 9 MB virtual memory with 8300 MB real and the rest ballooned 9000 MB memory because the max 500 connections and about 1.5 MB memory per connection, this machine is running perfectly, but as said - theusecase makes the difference, here is a half of the database as buffer_pool enough since you never need all mail-contents at the same time >> MySQLTuner 1.2.0 - Major Hayden <ma...@mhtx.net> -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.5.18-log [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 156M (Tables: 3) [--] Data in InnoDB tables: 8G (Tables: 49) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [!!] Total fragmented tables: 12 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 5d 21h 29m 55s (13M q [26.894 qps], 50K conn, TX: 22B, RX: 5B) [--] Reads / Writes: 64% / 36% [--] Total buffers: 5.1G global + 1.2M per thread (500 max threads) [OK] Maximum possible memory usage: 5.7G (66% of installed RAM) [OK] Slow queries: 0% (7/13M) [OK] Highest usage of available connections: 48% (242/500) [OK] Key buffer size / total MyISAM indexes: 128.0M/114.3M [OK] Key buffer hit rate: 98.5% (56M cached / 831K reads) [OK] Query cache efficiency: 73.2% (8M cached / 11M selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 304K sorts) [OK] Temporary tables created on disk: 0% (537 on disk / 679K total) [OK] Thread cache hit rate: 99% (242 created / 50K connections) [OK] Table cache hit rate: 29% (153 open / 520 opened) [OK] Open file limit used: 0% (75/30K) [OK] Table locks acquired immediately: 99% (6M immediate / 6M locks) [!!] InnoDB data size / buffer pool: 8.9G/4.0G ______________________________ max_connections = 500 query_cache_limit = 385K query_cache_min_res_unit = 1K query_cache_size = 150M query_cache_type = 1 table_cache = 15000 thread_cache = 600 table_definition_cache = 768 tmp_table_size = 512M max_heap_table_size = 512M key_buffer_size = 128M sort_buffer_size = 320K read_rnd_buffer_size = 256K join_buffer_size = 320K read_buffer_size = 128K preload_buffer_size = 128K myisam_sort_buffer_size = 15M innodb_buffer_pool_size = 4096M innodb_buffer_pool_instances = 4 innodb_purge_threads = 1 innodb_max_purge_lag = 200000 innodb_max_dirty_pages_pct = 60 innodb_additional_mem_pool_size = 64M innodb_log_file_size = 512M innodb_log_buffer_size = 256M innodb_thread_concurrency = 32 innodb_thread_sleep_delay = 10 innodb_flush_log_at_trx_commit = 2 innodb_support_xa = 1 innodb_lock_wait_timeout = 50 innodb_table_locks = 0 innodb_checksums = 0 innodb_file_format = barracuda innodb_file_per_table = 1 innodb_open_files = 600 innodb_io_capacity = 800 innodb_read_io_threads = 8 innodb_write_io_threads = 8 transaction-isolation = READ-COMMITTED
signature.asc
Description: OpenPGP digital signature
