List:General Discussion« Previous MessageNext Message »
From:Reindl Harald Date:December 1 2011 7:04pm
Subject:Re: Use of Swap Memroy
View as plain text  

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 <major@stripped>
-------- 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


Attachment: [application/pgp-signature] OpenPGP digital signature signature.asc
Thread
Use of Swap MemroyStdranwl1 Dec
  • Re: Use of Swap MemroyReindl Harald1 Dec
    • Re: Use of Swap MemroyStdranwl1 Dec
      • Re: Use of Swap MemroyReindl Harald1 Dec