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.

I am worrying about my memory usage as it is reaching more than 90% if I will not restart php-fpm.

I have the following configuration on my MySQL:

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8

myisam-recover         = BACKUP
max_connections        = 250
table_cache            = 4K
wait_timeout            = 1200

query_cache_limit       = 1M
query_cache_size        = 128M
join_buffer_size        = 2M

log_error = /var/log/mysql/error.log

log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time = 1

expire_logs_days        = 10
max_binlog_size         = 100M

innodb_buffer_pool_size = 5G
[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[isamchk]
key_buffer              = 16M

!includedir /etc/mysql/conf.d/

I am running nginx & php-fpm.

Two of my sites with lots of traffic are configured with this settings:

PHP-FPM pm.max_children: 64
PHP-FPM pm.start_servers: 20
PHP-FPM pm.min_spare_servers: 15
PHP-FPM pm.max_spare_servers: 64
PHP-FPM pm.max_requests: 500

The rest of my sites are configured using the following settings:

PHP-FPM pm.max_children: 64
PHP-FPM pm.start_servers: 10
PHP-FPM pm.min_spare_servers: 5
PHP-FPM pm.max_spare_servers: 15
PHP-FPM pm.max_requests: 500

When I restart mysql server and php-fpm, the initial size of memory consumed is 85%. I created a script to monitor the consumption of the memory and if it reaches 90% I automatically restarted php-fpm. I don't know if this is a good idea.

When the number of php-fpm server running increase, the memory usage also increased rapidly.

I used the following command to monitor the number of php-fpm server running:

smem -u -t -k

The example output are:

User     Count     Swap      USS      PSS      RSS
web8        22        0  1017.7M     1.0G     1.4G
web2        20        0     1.1G     1.1G     1.3G

The output above is good when the number of server/count does not increase. But when it increase up to 64, which is the highest value of PHP-FPM pm.max_spare_servers. There come's the problem. The memory increased rapidly.

BTW, here's the specs of my server:

Intel® Xeon® E3-1270 v2 Single Processor - Quad Core Dedicated Server
CPU Speed: 4 x 3.5 Ghz w/ 8MB Smart Cache
Motherboard: SuperMicro X9SCM-F
Total Cores: 4 Cores + 8 Threads
RAM: 32 GB DDR3 1333 ECC
Hard Drive: 120GB
Smart Cache: 8MB

Can anybody suggest the best settings for my server?

share|improve this question
1  
Can you update this with some further details about how much memory MySQL and php-fpm are using, and how you're determining memory usage? Also, from what you posted, it sounds like you think php-fpm is using nearly all of the memory on the server, so I'm not sure what this has to do with MySQL. If there's free memory on the server, you might benefit from configuring MySQL to use more memory, but this won't solve your problem with php-fpm. –  James Jan 1 at 15:31

2 Answers 2

up vote 2 down vote accepted

Configuration items

  • You should adjust the innodb_buffer_pool_size parameter for better memory usage. This is where all the memory of large DB machines should go. For example, on a 32 GB RAM machine, this can go up to 24 GB.

  • On bigger installations you should use innodb_file_per_table = 1, which is creating single files instead of one big blob. If you change this parameter after the database initialization you have to recreate (like dump/drop and re-import) the tables.

  • Some benchmarks indicate that actually switching off the query cache helps performance. You may want to experiment with this. To switch it off, use query_cache_size=0, query_cache_type=0.
  • It can help to put different parts of the mysql datadir (iblog, ibdata) on different filesystems / storage devices. This depends on your infrastructure. Settings herefore are datadir, innodb_data_home_dir, innodb_log_group_home_dir.
  • If your storage is fast, thus it can handle a lot of IOPS, you may want to adjust the innodb_io_capacity setting, which defines a limit for the IOPS MySQL will create. The default is 200, which is sensible for single spindle disks. But if you have storage appliances with a lot of fast SAS drives, or even SSDs, this limit can be increased greatly.

Sample my.cnf file : This file does not contain all the configuration items. Use this as a starting point and adjust it, particularly considering the configuration items discussed in the previous section.

    #
    [client]
    port            = 3306
    socket          = /var/run/mysqld/mysqld.sock

    [mysqld]
    socket          = /var/run/mysqld/mysqld.sock
    port            = 3306
    user                           = mysql
    # applies only when running as root
    #memlock                        = 1

    table_open_cache               = 3072
    table_definition_cache         = 4096
    max_heap_table_size            = 64M
    tmp_table_size                 = 64M
    max_connections                = 505
    max_user_connections           = 500
    max_allowed_packet             = 16M
    thread_cache_size              = 32
    query_cache_size               = 64M

    # InnoDB
    default_table_type             = InnoDB

    # 80% of ram that is dedicated for the database (this needs to be adjusted to your system)
    innodb_buffer_pool_size        = 14G
    # number of CPU cores dedicated to the MySQL InnoDB backend 
    innodb_buffer_pool_instances = 16 

    innodb_data_file_path          = ibdata1:128M:autoextend
    innodb_file_per_table          = 1
    innodb_log_file_size           = 512M
    innodb_log_files_in_group      = 2

    # MyISAM
    myisam_recover                 = backup,force

    # Logging
    log_warnings                   = 2
    log_error                      = /var/log/mysql/error.log

    slow_query_log                 = 1
    slow_query_log_file            = /var/log/mysql/mysql-slow.log
    long_query_time                = 1
    log_queries_not_using_indexes  = 1
    min_examined_row_limit         = 20

    # Binary Log / Replication
    server_id                      = 1
    log-bin                        = mysql-bin
    binlog_cache_size              = 1M 
    sync_binlog                    = 8
    binlog_format                  = row
    expire_logs_days               = 7
    max_binlog_size                = 128M 
    relay-log                      = /var/log/mysql/slave-relay.log
    relay-log-index                = /var/log/mysql/slave-relay-log.index 

    [mysqldump]
    quick
    single-transaction
    max_allowed_packet             = 16M

    [mysql]
    no_auto_rehash

    [myisamchk]
    key_buffer                     = 512M
    sort_buffer_size               = 512M
    read_buffer                    = 8M
    write_buffer                   = 8M

    [mysqld_safe]
    open-files-limit               = 8192
    log-error                      = /var/log/mysql/error.log
share|improve this answer

Configure some important variables in my.cnf file and restart MySQL service :

max_connections=500

max_connect_errors=100

innodb_buffer_pool_size=value should be 70% of total Memory

innodb_log_buffer_size=less than 4 MB if there are TEXT/BLOB then set to more value between 8-128 MB

innodb_file_per_table=ON

thread_cache_size=if threads_connected value varies from 75 to 100, set thread_cache_size to 25.

table_cache=more than default

share|improve this answer
1  
Hi, What do you mean on the size of value should be 0% of total Memory on the value of innodb_buffer_pool_size? And also how do I know the number of threads_connected? –  jaypabs Oct 3 '13 at 11:52
1  
innodb_buffer_pool_size should be 70% of your total memory if you are having mostly InnoDB tables. (20GB of 32 GB RAM would be better) –  Peter Venderberghe Oct 4 '13 at 7:23
    
execute a query on server show status like 'threads_connected'; to get threads_connected value –  Peter Venderberghe Oct 4 '13 at 7:25
    
the value is 17. What should I do with this value? –  jaypabs Oct 4 '13 at 10:29
    
For example: You can't do anything for threads_connected but if this value varies between 17 to say 50 then you can change thread_cache_size to 30. –  Peter Venderberghe Oct 4 '13 at 12:10

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.