Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Hereunder my hardware config:

CPU: 1x Intel Xeon X3470 Quad core @ 2.93GHz
RAM: 4x 8GB DDR3
Disks: 2 x SATA 320GB for the system & 4 x SAS 300GB for the DB

I've upgraded from 12Gb to 32Gb and switched the Java application server on another server in order to have a dedicated MySQL server.

Hereunder my MySQL config:

MySQL 5.5.28 with InnoDB plugin on RedHat 6. Database size is 130Gb.

  max_allowed_packet = 50M
  max_connections = 100
  back_log = 2048
  skip-external-locking
  tmp_table_size = 300M
  max_heap_table_size = 300M
  sort_buffer_size = 40M
  read_buffer_size = 10M
  binlog_cache_size = 1M
  key_buffer_size = 10M
  table_open_cache = 4096
  table_definition_cache = 500
  open_files_limit = 65535
  thread_cache_size = 100
  query_cache_size = 500M
  innodb_additional_mem_pool_size = 50M
  innodb_buffer_pool_size = 28000M
  innodb_buffer_pool_instances = 5
  innodb_commit_concurrency = 0
  innodb_concurrency_tickets = 0
  innodb_doublewrite
  innodb_fast_shutdown = 0
  innodb_flush_log_at_trx_commit = 2
  innodb_flush_method = O_DIRECT
  innodb_lock_wait_timeout = 50
  innodb_log_buffer_size = 128M
  innodb_log_file_size = 256M
  innodb_thread_concurrency = 0
  innodb_read_io_threads = 64
  innodb_write_io_threads = 64
  optimizer_switch='index_merge=off'
  innodb_stats_on_metadata = 0
  performance_schema = 0

Since my RAM upgrade, I've updated "innodb_buffer_pool_size" from 8000M to 28000M. Since this upgrade, the response time for my SQL requests are a disaster (7x much slower).

What do you think ? Another options to update or to add ?

Many thanks in advance,

Bob'

share|improve this question
    
How bad is "7x slower"? 2ms to 14ms, or 1 second to 7 seconds? An example of the queries that are worst affected might be useful. Also, 28GB is almost 90% of your system memory and it's possible that you're experiencing paging. Could you run "vmstat 5" for 20-30 seconds when your server is busy, and post the output? –  Nathan Jolly May 16 '13 at 0:17
    
I'm really sorry, we can close this topic. Indeed, the slow was due to the SSH tunnel. –  Bob Sauvage May 16 '13 at 12:18
    
You can delete your own question. There should be a delete link right under it next to edit and flag –  Tom V May 19 at 14:49

1 Answer 1

Actually, at a first sight, you may run out of physical memory and run into swappiness.

Minimum memory per-thread, as in your configuration, is 51MB. All threads may use 51x100=5100MB (btw, what is the value of Max_used_connections?).

Adding to 28000MB as the InnoDB_buffer is and the Query_cache_size of 500 MB (is it really needed??), you have 33600MB.

Consider some in-memory temporary tables (maximum 300 MB each) and the fact that most of your joins will be disk-based temporary tables (as you didn't specify join_buffer_size), and you have a clear picture that your server run out of memory solely because of MySQL and Linux starts swapping processes.

Look to top command and and if it shows use of swapping then you might need to instruct MySQL to use lesser memory.

This is from a 32 GB machine, highly transactional with 180 connected threads:

KiB Mem:  32933224 total, 28418952 used,  4514272 free,   526836 buffers
KiB Swap: 31249404 total,        0 used, 31249404 free.  8787796 cached Mem

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
23082 mysql     20   0 21.369g 0.014t  11660 S   2.7 44.3 178:01.13 mysqld

As shown, the use of swap is zero. The rest of output is specific to that machine, can't be compared with yours.

To reduce use of RAM and improve MySQL's temporary table creation you may start reducing sort_buffer_size, set join_buffer_size and reduce innodb_buffer_pool_size.
I give these values as a start point:

sort_buffer_size = 8M
join_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
innodb_buffer_pool_size = 16G

On top of that, is query cache really needed? As I know, it is disabled by default in MySQL 5.6.
As I see, innodb_read_io_threads = 64 and innodb_write_io_threads = 64 denote a highly concurrent transactional server, but you have innodb_concurrency_tickets = 0 and thread limit to 100, so, somehow, you're sure that your queries are sequential only.

As a conclusion, reduce swapping first and then increase per-thread memory allocation if needed.

PS: may I see the output of df -h and the relevant part of the execution of the following script, please?

#!/bin/bash
# ./swap
# Get current swap usage for all running processes
# Erik Ljungstrom 27/05/2011
# Modified by Mikko Rantalainen 2012-08-09
# Pipe the output to "sort -nk3" to get sorted output
# Modified by Marc Methot 2014-09-18
# removed the need for sudo

SUM=0
OVERALL=0
for DIR in `find /proc/ -maxdepth 1 -type d -regex "^/proc/[0-9]+"`
do
    PID=`echo $DIR | cut -d / -f 3`
    PROGNAME=`ps -p $PID -o command --no-headers`
    for SWAP in `grep VmSwap $DIR/status 2>/dev/null | awk '{ print $2 }'`
    do
        let SUM=$SUM+$SWAP
    done
    if (( $SUM > 0 )); then
        echo "PID=$PID swapped $SUM KB ($PROGNAME)"
    fi
    let OVERALL=$OVERALL+$SUM
    SUM=0
done
echo "Overall swap used: $OVERALL KB"

Because some GNU/Linux distros tend to allocate 10% of available RAM for /run {tmpfs} partition and there are methods to reduce it as the system will not use it that much.
(I've reduce it myself to 256 MB and it is used 1%, making almost 3 GB available for MySQL).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.