title: How to improve DB configuration
body: <p>I have a MySQL 5.5 database, with 58 tables, ~14,188,772 rows, 19.9 GB in size. I want to mention that 19.5 GB of those is in one table. </p>
  
  <p>I've never changed the configuration, but after recent performance issues, I stumbled across the variable everyone seems to mention first: <code>innodb_buffer_pool_size</code>. Everywhere I looked I saw its value set to multiple GB's, then I checked my own, and it's (the default) 128 MB.</p>
  
  <p>I found a query in <a href="https://dba.stackexchange.com/questions/27328/how-large-should-be-mysql-innodb-buffer-pool-size">another thread</a> that aims to calculate the right buffer pool size. I ran this query and it told me that 32 GB would be the right amount in my case. On the other hand, that thread calls 20 GB enourmous. I have 16 GB RAM in the server, does this mean I need 32+ for the DB to function optimally?</p>
  
  <p>This worries me I'm running a hugely misconfigured database. Can someone enlighten me about what one usually needs to change in a DB configuration? The settings that need "fiddling" to get right, the things that I should never touch?</p>
  
  <p>Here is my entire "server variables"  page from phpmyadmin:</p>
  
  <pre><code>Variable                                            Session value / Global value    
  auto increment increment                            1   
  auto increment offset                               1   
  autocommit                                          ON  
  automatic sp privileges                             ON  
  back log                                            50  
  basedir                                             /usr    
  big tables                                          OFF     
  binlog cache size                                   32,768  
  binlog direct non transactional updates             OFF     
  binlog format                                       STATEMENT   
  binlog stmt cache size                              32,768  
  bulk insert buffer size                             8,388,608   
  character set client                                utf8    
  (Global value)                                      latin1  
  character set connection                            utf8    
  (Global value)                                      latin1  
  character set database                              latin1  
  character set filesystem                            binary  
  character set results                               utf8    
  (Global value)                                      latin1  
  character set server                                latin1  
  character set system                                utf8    
  character sets dir                                  /usr/share/mysql/charsets/  
  collation connection                                utf8_general_ci     
  (Global value)                                      latin1_swedish_ci   
  collation database                                  latin1_swedish_ci   
  collation server                                    latin1_swedish_ci   
  completion type                                     NO_CHAIN    
  concurrent insert                                   AUTO    
  connect timeout                                     10  
  datadir                                             /var/lib/mysql/     
  date format                                         %Y-%m-%d    
  datetime format                                     %Y-%m-%d %H:%i:%s   
  default storage engine                              InnoDB  
  default week format                                 0   
  delay key write                                     ON  
  delayed insert limit                                100     
  delayed insert timeout                              300     
  delayed queue size                                  1,000   
  div precision increment                             4   
  engine condition pushdown                           ON  
  error count                                         0   
  (Global value)                                      0   
  event scheduler                                     OFF     
  expire logs days                                    10  
  external user           
  (Global value)      
  flush                                               OFF     
  flush time                                          0   
  foreign key checks                                  ON  
  ft boolean syntax                                   + -&gt;&lt;()~*:""&amp;|  
  ft max word len                                     84  
  ft min word len                                     4   
  ft query expansion limit                            20  
  ft stopword file                                    (built-in)  
  general log                                         OFF     
  general log file                                    /var/lib/mysql/xxx.log  
  group concat max len                                1,024   
  have compress                                       YES     
  have crypt                                          YES     
  have csv                                            YES     
  have dynamic loading                                YES     
  have geometry                                       YES     
  have innodb                                         YES     
  have ndbcluster                                     NO  
  have openssl                                        DISABLED    
  have partitioning                                   YES     
  have profiling                                      YES     
  have query cache                                    YES     
  have rtree keys                                     YES     
  have ssl                                            DISABLED    
  have symlink                                        YES     
  hostname                                            xxx
  identity                                            0   
  (Global value)                                      0   
  ignore builtin innodb                               OFF     
  init connect        
  init file       
  init slave      
  innodb adaptive flushing                            ON  
  innodb adaptive hash index                          ON  
  innodb additional mem pool size                     8,388,608   
  innodb autoextend increment                         8   
  innodb autoinc lock mode                            1   
  innodb buffer pool instances                        1   
  innodb buffer pool size                             134,217,728     
  innodb change buffering                             all     
  innodb checksums                                    ON  
  innodb commit concurrency                           0   
  innodb concurrency tickets                          500     
  innodb data file path                               ibdata1:10M:autoextend  
  innodb data home dir        
  innodb doublewrite                                  ON  
  innodb fast shutdown                                1   
  innodb file format                                  Antelope    
  innodb file format check                            ON  
  innodb file format max                              Antelope    
  innodb file per table                               OFF     
  innodb flush log at trx commit                      1   
  innodb flush method         
  innodb force load corrupted                         OFF     
  innodb force recovery                               0   
  innodb io capacity                                  200     
  innodb large prefix                                 OFF     
  innodb lock wait timeout                            50  
  innodb locks unsafe for binlog                      OFF     
  innodb log buffer size                              8,388,608   
  innodb log file size                                5,242,880   
  innodb log files in group                           2   
  innodb log group home dir                           ./  
  innodb max dirty pages pct                          75  
  innodb max purge lag                                0   
  innodb mirrored log groups                          1   
  innodb old blocks pct                               37  
  innodb old blocks time                              0   
  innodb open files                                   300     
  innodb print all deadlocks                          OFF     
  innodb purge batch size                             20  
  innodb purge threads                                0   
  innodb random read ahead                            OFF     
  innodb read ahead threshold                         56  
  innodb read io threads                              4   
  innodb replication delay                            0   
  innodb rollback on timeout                          OFF     
  innodb rollback segments                            128     
  innodb spin wait delay                              6   
  innodb stats method                                 nulls_equal     
  innodb stats on metadata                            ON  
  innodb stats sample pages                           8   
  innodb strict mode                                  OFF     
  innodb support xa                                   ON  
  innodb sync spin loops                              30  
  innodb table locks                                  ON  
  innodb thread concurrency                           0   
  innodb thread sleep delay                           10,000  
  innodb use native aio                               OFF     
  innodb use sys malloc                               ON  
  innodb version                                      5.5.31  
  innodb write io threads                             4   
  insert id                                           0   
  (Global value)                                      0   
  interactive timeout                                 28,800  
  join buffer size                                    131,072     
  keep files on create                                OFF     
  key buffer size                                     16,777,216  
  key cache age threshold                             300     
  key cache block size                                1,024   
  key cache division limit                            100     
  large files support                                 ON  
  large page size                                     0   
  large pages                                         OFF     
  last insert id                                      0   
  (Global value)                                      0   
  lc messages                                         en_US   
  lc messages dir                                     /usr/share/mysql/   
  lc time names                                       en_US   
  license                                             GPL     
  local infile                                        ON  
  lock wait timeout                                   31,536,000  
  locked in memory                                    OFF     
  log                                                 OFF     
  log bin                                             OFF     
  log bin trust function creators                     OFF     
  log error       
  log output                                          FILE    
  log queries not using indexes                       OFF     
  log slave updates                                   OFF     
  log slow queries                                    OFF     
  log warnings                                        1   
  long query time                                     10  
  low priority updates                                OFF     
  lower case file system                              OFF     
  lower case table names                              0   
  max allowed packet                                  16,777,216  
  max binlog cache size                               18446744073709547520    
  max binlog size                                     104,857,600     
  max binlog stmt cache size                          18446744073709547520    
  max connect errors                                  10  
  max connections                                     151     
  max delayed threads                                 20  
  max error count                                     64  
  max heap table size                                 16,777,216  
  max insert delayed threads                          20  
  max join size                                       18446744073709551615    
  max length for sort data                            1,024   
  max long data size                                  16,777,216  
  max prepared stmt count                             16,382  
  max relay log size                                  0   
  max seeks for key                                   18446744073709551615    
  max sort length                                     1,024   
  max sp recursion depth                              0   
  max tmp tables                                      32  
  max user connections                                0   
  max write lock count                                18446744073709551615    
  metadata locks cache size                           1,024   
  min examined row limit                              0   
  multi range count                                   256     
  myisam data pointer size                            6   
  myisam max sort file size                           9223372036853727232     
  myisam mmap size                                    18446744073709551615    
  myisam recover options                              BACKUP  
  myisam repair threads                               1   
  myisam sort buffer size                             8,388,608   
  myisam stats method                                 nulls_unequal   
  myisam use mmap                                     OFF     
  net buffer length                                   16,384  
  net read timeout                                    30  
  net retry count                                     10  
  net write timeout                                   60  
  new                                                 OFF     
  old                                                 OFF     
  old alter table                                     OFF     
  old passwords                                       OFF     
  open files limit                                    1,024   
  optimizer prune level                               1   
  optimizer search depth                              62  
  optimizer switch                                    index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on  
  performance schema                                  OFF     
  performance schema events waits history long size   10,000  
  performance schema events waits history size        10  
  performance schema max cond classes                 80  
  performance schema max cond instances               1,000   
  performance schema max file classes                 50  
  performance schema max file handles                 32,768  
  performance schema max file instances               10,000  
  performance schema max mutex classes                200     
  performance schema max mutex instances              1,000,000   
  performance schema max rwlock classes               30  
  performance schema max rwlock instances             1,000,000   
  performance schema max table handles                100,000     
  performance schema max table instances              50,000  
  performance schema max thread classes               50  
  performance schema max thread instances             1,000   
  pid file                                            /var/run/mysqld/mysqld.pid  
  plugin dir                                          /usr/lib/mysql/plugin/  
  port                                                3,306   
  preload buffer size                                 32,768  
  profiling                                           OFF     
  profiling history size                              15  
  protocol version                                    10  
  proxy user      
  (Global value)      
  pseudo slave mode                                   OFF     
  (Global value)      
  pseudo thread id                                    49,037  
  (Global value)                                      0   
  query alloc block size                              8,192   
  query cache limit                                   1,048,576   
  query cache min res unit                            4,096   
  query cache size                                    16,777,216  
  query cache type                                    ON  
  query cache wlock invalidate                        OFF     
  query prealloc size                                 8,192   
  rand seed1                                          0   
  (Global value)                                      0   
  rand seed2                                          0   
  (Global value)                                      0   
  range alloc block size                              4,096   
  read buffer size                                    131,072     
  read only                                           OFF     
  read rnd buffer size                                262,144     
  relay log       
  relay log index         
  relay log info file                                 relay-log.info  
  relay log purge                                     ON  
  relay log recovery                                  OFF     
  relay log space limit                               0   
  report host         
  report password         
  report port                                         3,306   
  report user         
  rpl recovery rank                                   0   
  secure auth                                         OFF     
  secure file priv        
  server id                                           0   
  skip external locking                               ON  
  skip name resolve                                   OFF     
  skip networking                                     OFF     
  skip show database                                  OFF     
  slave compressed protocol                           OFF     
  slave exec mode                                     STRICT  
  slave load tmpdir                                   /tmp    
  slave max allowed packet                            1,073,741,824   
  slave net timeout                                   3,600   
  slave skip errors                                   OFF     
  slave transaction retries                           10  
  slave type conversions      
  slow launch time                                    2   
  slow query log                                      OFF     
  slow query log file                                 /var/lib/mysql/bl07-slow.log    
  socket                                              /var/run/mysqld/mysqld.sock     
  sort buffer size                                    2,097,152   
  sql auto is null                                    OFF     
  sql big selects                                     ON  
  sql big tables                                      OFF     
  sql buffer result                                   OFF     
  sql log bin                                         ON  
  sql log off                                         OFF     
  sql low priority updates                            OFF     
  sql max join size                                   18446744073709551615    
  sql mode        
  sql notes                                           ON  
  sql quote show create                               ON  
  sql safe updates                                    OFF     
  sql select limit                                    18446744073709551615    
  sql slave skip counter                              0   
  sql warnings                                        OFF     
  ssl ca      
  ssl capath      
  ssl cert        
  ssl cipher      
  ssl key         
  storage engine                                      InnoDB  
  stored program cache                                256     
  sync binlog                                         0   
  sync frm                                            ON  
  sync master info                                    0   
  sync relay log                                      0   
  sync relay log info                                 0   
  system time zone                                    CEST    
  table definition cache                              400     
  table open cache                                    400     
  thread cache size                                   8   
  thread concurrency                                  10  
  thread handling                                     one-thread-per-connection   
  thread stack                                        196,608     
  time format                                         %H:%i:%s    
  time zone                                           SYSTEM  
  timed mutexes                                       OFF     
  timestamp                                           1,399,897,257   
  (Global value)                                      0   
  tmp table size                                      16,777,216  
  tmpdir                                              /tmp    
  transaction alloc block size                        8,192   
  transaction prealloc size                           4,096   
  tx isolation                                        REPEATABLE-READ     
  unique checks                                       ON  
  updatable views with limit                          YES     
  version                                             5.5.31-0ubuntu0.12.04.1     
  version comment                                     (Ubuntu)    
  version compile machine                             x86_64  
  version compile os                                  debian-linux-gnu    
  wait timeout                                        28,800  
  warning count                                       0   
  (Global value)                                      0   
  </code></pre>