Question posted in
Some documentation to get you started can be found here.

CPU and RAM full when admin create a post – Apache

My wordpress site has a traffic 3 million users / month, everything was fine until our admin create a post / page. When our admin create post / page, our CPU and RAM usage increase until 100% and the website is down. I think after some process is done, the usage return into normal state and the website is live again.

I already used Varnish & Wp- Rocket to cache content.

This is our server configuration
Wordpress (PHP 7.2 & Apache/2.4.38) running on 6 CPU 6GB RAM

My Apache config

<VirtualHost *:8080>
   <files xmlrpc.php>
      order allow,deny
      deny from all
    </files>
    ServerAdmin [email]
    ServerName [domain]
    ServerAlias [domain]
    DocumentRoot /var/www/html/[domain]/public_html
    SetEnvIf Remote_Addr "::1" dontlog
        SetEnvIf Remote_Addr "127.0.0.1" dontlog
        CustomLog /var/log/apache2/access.log combined env=!dontlog
    ErrorLog ${APACHE_LOG_DIR}/error.log
    CustomLog ${APACHE_LOG_DIR}/access.log combined

</VirtualHost>

#LoadModule ssl_module modules/mod_ssl.so
#Listen 443
<VirtualHost *:443>
    Protocols h2 http/1.1
    <files xmlrpc.php>
      order allow,deny
      deny from all
    </files>

    ServerAdmin [email]
    ServerName [domain]
    ServerAlias [domain]
    DocumentRoot /var/www/html/[domain]/public_html
    SSLEngine on
    SSLProtocol ALL -SSLv2 -SSLv3
    SSLHonorCipherOrder On
    SSLCipherSuite [chiper]
    SSLCertificateFile [cert]
    SSLCertificateKeyFile [cert]
    SSLCertificateChainFile [cert]
</VirtualHost>
<Directory /var/www/html/[domain]/public_html>
                Options Indexes FollowSymLinks MultiViews
                AllowOverride All
                Order allow,deny
                Require all granted
</Directory>

Apache Config

Mutex file:${APACHE_LOCK_DIR} default
PidFile ${APACHE_PID_FILE}
Timeout 240
KeepAlive On
MaxKeepAliveRequests 500
KeepAliveTimeout 6

# These need to be set in /etc/apache2/envvars
User ${APACHE_RUN_USER}
Group ${APACHE_RUN_GROUP}

HostnameLookups Off
ErrorLog ${APACHE_LOG_DIR}/error.log

LogLevel warn

# Include module configuration:
IncludeOptional mods-enabled/*.load
IncludeOptional mods-enabled/*.conf

# Include list of ports to listen on
Include ports.conf

<Directory />
    Options FollowSymLinks
    AllowOverride None
    Require all denied
</Directory>

<Directory /usr/share>
    AllowOverride None
    Require all granted
</Directory>

<Directory /var/www/>
    Options Indexes FollowSymLinks
    AllowOverride None
    Require all granted
</Directory>

AccessFileName .htaccess

<FilesMatch "^.ht">
    Require all denied
</FilesMatch>

LogFormat "%v:%p %h %l %u %t "%r" %>s %O "%{Referer}i" "%{User-Agent}i"" vhost_combined
LogFormat "%h %l %u %t "%r" %>s %O "%{Referer}i" "%{User-Agent}i"" combined
LogFormat "%h %l %u %t "%r" %>s %O" common
LogFormat "%{Referer}i -> %U" referer
LogFormat "%{User-agent}i" agent

# Include generic snippets of statements
IncludeOptional conf-enabled/*.conf

# Include the virtual host configurations:
IncludeOptional sites-enabled/*.conf

MPM Prefork config : 

# prefork MPM
# StartServers: number of server processes to start
# MinSpareServers: minimum number of server processes which are kept spare
# MaxSpareServers: maximum number of server processes which are kept spare
# MaxRequestWorkers: maximum number of server processes allowed to start
# MaxConnectionsPerChild: maximum number of requests a server process serves

<IfModule mpm_prefork_module>
        StartServers            5
        MinSpareServers         5
        MaxSpareServers         10
        MaxRequestWorkers       500
        MaxConnectionsPerChild  100
        ServerLimit 1024
</IfModule>

SECOND UPDATE
I increased my DB server to 6 CPU and 10GB RAM. Still got that issue This is my my.cnf config :

#
# * Fine Tuning
#
max_connections     = 300
connect_timeout     = 200
wait_timeout        = 300
max_allowed_packet  = 16M
thread_cache_size       = 50
sort_buffer_size    = 2M
bulk_insert_buffer_size = 160M
tmp_table_size      = 256M
max_heap_table_size = 256M
thread_stack            = 50K
#
# * MyISAM
#
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched. On error, make copy and try a repair.
myisam_recover_options = BACKUP
key_buffer_size     = 16M
#open-files-limit   = 2000
table_open_cache    = 400
myisam_sort_buffer_size = 512M
concurrent_insert   = 2
read_buffer_size    = 512K
read_rnd_buffer_size    = 192K
#
# * Query Cache Configuration
#
# Cache only tiny result sets, so we can fit more in the query cache.
query_cache_limit       = 128K
#query_cache_size       = 50M
query_cache_size = 0
# for more write intensive setups, set to DEMAND or OFF
query_cache_type        = 0
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# we do want to know about network errors and such
log_warnings        = 2
#
# Enable the slow query log to see queries with especially long duration
#slow_query_log[={0|1}]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 1
log_queries_not_using_indexes
#log_slow_rate_limit    = 1000
log_slow_verbosity  = query_plan

#log-queries-not-using-indexes
#log_slow_admin_statements
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#report_host        = master1
#auto_increment_increment = 2
#auto_increment_offset  = 1
log_bin         = /var/log/mysql/mariadb-bin
log_bin_index       = /var/log/mysql/mariadb-bin.index
# not fab for performance, but safer
#sync_binlog        = 1
expire_logs_days    = 10
max_binlog_size         = 100M
# slaves
#relay_log      = /var/log/mysql/relay-bin
#relay_log_index    = /var/log/mysql/relay-bin.index
#relay_log_info_file    = /var/log/mysql/relay-bin.info
#log_slave_updates
#read_only
#
# If applications support it, this stricter sql_mode prevents some
# mistakes like inserting invalid dates etc.
#sql_mode       = NO_ENGINE_SUBSTITUTION,TRADITIONAL
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
default_storage_engine  = InnoDB
# you can't just change log file size, requires special procedure
#innodb_log_file_size   = 50M
innodb_buffer_pool_instances = 7
innodb_buffer_pool_size = 7G
innodb_log_file_size    = 512M
innodb_log_buffer_size  = 16M
innodb_file_per_table   = 1
innodb_open_files   = 400
innodb_io_capacity  = 1000
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_stats_on_metadata = 0
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_lru_scan_depth = 100
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

[mysqldump]
quick
quote-names
max_allowed_packet  = 512M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completion

[isamchk]
key_buffer      = 2G

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

I am using mysqltuner, this is the result

 >>  MySQLTuner 1.7.4 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 10.4.6-MariaDB-1:10.4.6+maria~bionic-log
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/lib/mysql/web-db-instance-new.err(0B)
[!!] Log file /var/lib/mysql/web-db-instance-new.err doesn't exist
[!!] Log file /var/lib/mysql/web-db-instance-new.err isn't readable.

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in InnoDB tables: 1G (Tables: 360)
[--] Data in MyISAM tables: 1G (Tables: 269)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User 'finansialku@%' hasn't specific host restriction.
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 49s (43K q [887.163 qps], 749 conn, TX: 3G, RX: 16M)
[--] Reads / Writes: 97% / 3%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory     : 9.8G
[--] Max MySQL memory    : 8.3G
[--] Other process memory: 152.7M
[--] Total buffers: 7.4G global + 3.1M per thread (300 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 7.8G (80.01% of installed RAM)
[!!] Maximum possible memory usage: 8.3G (85.09% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 2% (1K/43K)
[OK] Highest usage of available connections: 44% (134/300)
[OK] Aborted connections: 0.00%  (0/749)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (1 temp sorts / 9K sorts)
[!!] Joins performed without indexes: 2
[!!] Temporary tables created on disk: 71% (1K on disk / 2K total)
[OK] Thread cache hit rate: 82% (134 created / 749 connections)
[OK] Table cache hit rate: 79% (400 open / 506 opened)
[OK] Open file limit used: 39% (587/1K)
[!!] Table locks acquired immediately: 69%
[OK] Binlog cache memory access: 100.00% (23 Memory / 23 Total)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema isn't installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 6 thread(s).
[--] Using default value is good enough for your version (10.4.6-MariaDB-1:10.4.6+maria~bionic-log)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[OK] Key buffer used: 100.0% (16M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/135.8M
[OK] Read Key buffer hit rate: 98.8% (5M cached / 62K reads)
[!!] Write Key buffer hit rate: 83.5% (1K cached / 1K writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 7.0G/2.0G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (14.2857142857143 %): 512.0M * 2/7.0G should be equal 25%
[OK] InnoDB buffer pool instances: 7
[--] Number of InnoDB Buffer Pool Chunk : 56 for 7 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 95.68% (1415595 hits/ 1479512 total)
[!!] InnoDB Write Log efficiency: 15% (6 hits/ 40 total)
[OK] InnoDB log waits: 0.00% (0 waits / 34 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/296.0K
[OK] Aria pagecache hit rate: 99.4% (235K cached / 1K reads)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.

-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.

-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Restrict Host for user@% to user@SpecificDNSorIp
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Adjust your join queries to always utilize indexes
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Optimize queries and/or use InnoDB to reduce lock wait
    Performance should be activated for better diagnostics
    Consider installing Sys schema from https://github.com/mysql/mysql-sys
    Read this before changing innodb_log_file_size and/or innodb_log_files_in_group
Variables to adjust:
    join_buffer_size (> 256.0K, or always use indexes with joins)
    performance_schema = ON enable PFS
    innodb_log_file_size should be (=896M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

What do i need to fix first?

Tags:

2

Answers


  1. 0

    When the config settings are too high, RAM fills up, then Swap fills up. This slows down MySQL terribly. The fix is to undo the settings.

    But first, are the table MyISAM? Or InnoDB?

    Here are the values that look especially dangerous:

    max_allowed_packet  = 512M    -- go back to 16M
thread_cache_size       = 256K  -- This is threads! not bytes!  change to 50
tmp_table_size      = 1024M   -- change to 1% of RAM
max_heap_table_size = 1024M   -- change to 1% of RAM

myisam_sort_buffer_size = 512M   --> 8M
read_buffer_size    = 2M      --> 128K
read_rnd_buffer_size    = 1M  --> 256K

query_cache_limit       = 256K
query_cache_size        = 250M  -- Drop to 50M; 250M slows things down

# Enable the slow query log to see queries with especially long duration
#slow_query_log[={0|1}]    -- yes, enable it
long_query_time = 1
log_queries_not_using_indexes  -- unnecessarily clutters the slowlog

innodb_io_capacity  = 1000   -- Do you have SSD?

    As for these:

    key_buffer_size     = 128M    -->
innodb_buffer_pool_size = 4G  -->

    If using mostly MyISAM:

    key_buffer_size     = 128M    -->  OK
innodb_buffer_pool_size = 4G  -->  16M

    If using mostly InnoDB:

    key_buffer_size     = 128M    -->  20M
innodb_buffer_pool_size = 4G  -->  3500M
    Login or Signup to reply.
  2. 0

    Rate Per Second=RPS – Suggestions to consider for your my.cnf [mysqld] section

    sort_buffer_size=2M  # from 40M for a more reasonable per connection requirement
read_rnd_buffer_size=192K  # from 1M to reduce handler_read_rnd_next RPS
read_buffer_size=512K  # from 2M to increase handler_read_next RPS
innodb_lru_scan_depth=100  # from 1024 to reduce CPU cycles used for function

    Disclaimer: I am the content author of website mentioned at my profile, Network profile for Utility Scripts to improve performance and additional suggestions see our contact info.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search