-
Notifications
You must be signed in to change notification settings - Fork 9.3k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Magento 2.2.1 mysql hight memory usage #12341
Comments
# Additional informations: php ini file "/etc/php/php7.1/apache2/php.ini":[PHP] ;;;;;;;;;;;;;;;;;;; ; PHP attempts to find and load this configuration from a number of locations. ; The syntax of the file is extremely simple. Whitespace and lines ; Directives following the section heading [PATH=/www/mysite] only ; Directives are specified using the following syntax: ; The value can be a string, a number, a PHP constant (e.g. E_ALL or M_PI), one ; Expressions in the INI file are limited to bitwise operators and parentheses: ; Boolean flags can be turned on using the values 1, On, True or Yes. ; An empty string can be denoted by simply not writing anything after the equal ; foo = ; sets foo to an empty string ; If you use constants in your value, and these constants belong to a ;;;;;;;;;;;;;;;;;;; ; php.ini-production contains settings which hold security, performance and ; php.ini-development is very similar to its production variant, except it is ; This is php.ini-production INI file. ;;;;;;;;;;;;;;;;;;; ; display_errors ; display_startup_errors ; error_reporting ; html_errors ; log_errors ; max_input_time ; output_buffering ; register_argc_argv ; request_order ; session.gc_divisor ; session.sid_bits_per_character ; short_open_tag ; track_errors ; variables_order ;;;;;;;;;;;;;;;;;;;; ; To disable this feature set this option to empty value ; TTL for user-defined php.ini files (time-to-live) in seconds. Default is 300 seconds (5 minutes) ;;;;;;;;;;;;;;;;;;;; ; Enable the PHP scripting language engine under Apache. ; This directive determines whether or not PHP will recognize code between ; The number of significant digits displayed in floating point numbers. ; Output buffering is a mechanism for controlling how much output data ; You can redirect all of the output of your scripts to a function. For ; URL rewriter function rewrites URL on the fly by using ; URL rewriter will not rewrites absolute URL nor form by default. To enable ; Transparent output compression using the zlib library ; http://php.net/zlib.output-compression-level ; You cannot specify additional output handlers if zlib.output_compression ; Implicit flush tells PHP to tell the output layer to flush itself ; The unserialize callback function will be called (with the undefined class' ; When floats & doubles are serialized store serialize_precision significant ; open_basedir, if set, limits all file operations to the defined directory ; This directive allows you to disable certain functions for security reasons. ; This directive allows you to disable certain classes for security reasons. ; Colors for Syntax Highlighting mode. Anything that's acceptable in ; If enabled, the request will be allowed to complete even if the user aborts ; Determines the size of the realpath cache to be used by PHP. This value should ; Duration of time, in seconds for which to cache realpath information for a given ; Enables or disables the circular reference collector. ; If enabled, scripts may be written in encodings that are incompatible with ; Allows to set the default encoding for the scripts. This value will be used ;;;;;;;;;;;;;;;;; ; Decides whether PHP may expose the fact that it is installed on the server ;;;;;;;;;;;;;;;;;;; ; Maximum execution time of each script, in seconds ; Maximum amount of time each script may spend parsing request data. It's a good ; Maximum input variable nesting level ; How many GET/POST/COOKIE input variables may be accepted ; Maximum amount of memory a script may consume (128MB) ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; ; This directive informs PHP of which errors, warnings and notices you would like ; This directive controls whether or not and where PHP will output errors, ; The display of errors which occur during PHP's startup sequence are handled ; Besides displaying errors, PHP can also log errors to locations such as a ; Set maximum length of log_errors. In error_log information about the source is ; Do not log repeated messages. Repeated errors must occur in same file on same ; Ignore source of message when ignoring repeated messages. When this setting ; If this parameter is set to Off, then memory leaks will not be shown (on ; This setting is on by default. ; Store the last error/warning message in $php_errormsg (boolean). Setting this value ; Turn off normal error reporting and emit XML-RPC error XML ; An XML-RPC faultCode ; When PHP displays or logs an error, it has the capability of formatting the ; If html_errors is set to On and docref_root is not empty, then PHP ; http://php.net/docref-ext ; String to output before an error message. PHP's default behavior is to leave ; String to output after an error message. PHP's default behavior is to leave ; Log errors to specified file. PHP's default behavior is to leave this value ;windows.show_crt_warning ;;;;;;;;;;;;;;;;; ; The separator used in PHP generated URLs to separate arguments. ; List of separator(s) used by PHP to parse input URLs into variables. ; This directive determines which super global arrays are registered when PHP ; This directive determines which super global data (G,P & C) should be ; This directive determines whether PHP registers $argv & $argc each time it ; When enabled, the ENV, REQUEST and SERVER variables are created when they're ; Whether PHP will read the POST data. ; Maximum size of POST data that PHP will accept. ; Automatically add files before PHP document. ; Automatically add files after PHP document. ; By default, PHP will output a media type using the Content-Type header. To ; PHP's default character set is set to UTF-8. ; PHP internal character encoding is set to empty. ; PHP input character encoding is set to empty. ; PHP output character encoding is set to empty. ;;;;;;;;;;;;;;;;;;;;;;;;; ; UNIX: "/path1:/path2" ; The root of the PHP pages, used only if nonempty. ; The directory under which PHP opens the script using /~username used only ; Directory in which the loadable extensions (modules) reside. ; Directory where the temporary files should be placed. ; Whether or not to enable the dl() function. The dl() function does NOT work ; cgi.force_redirect is necessary to provide security running PHP as a CGI under ; if cgi.nph is enabled it will force cgi to always sent Status: 200 with ; if cgi.force_redirect is turned on, and you are not running under Apache or Netscape ; cgi.fix_pathinfo provides real PATH_INFO/PATH_TRANSLATED support for CGI. PHP's ; if cgi.discard_path is enabled, the PHP CGI binary can safely be placed outside ; FastCGI under IIS (on WINNT based OS) supports the ability to impersonate ; Disable logging through FastCGI connection. PHP's default behavior is to enable ; cgi.rfc2616_headers configuration option tells PHP what type of headers to ; cgi.check_shebang_line controls whether CGI PHP checks for line starting with #! ;;;;;;;;;;;;;;;; ; Whether to allow HTTP file uploads. ; Temporary directory for HTTP uploaded files (will use system default if not ; Maximum allowed size for uploaded files. ; Maximum number of files that can be uploaded via a single request ;;;;;;;;;;;;;;;;;; ; Whether to allow the treatment of URLs (like http:// or ftp://) as files. ; Whether to allow include/require to open URLs (like http:// or ftp://) as files. ; Define the anonymous ftp password (your email address). PHP's default setting ; Define the User-Agent string. PHP's default setting for this is empty. ; Default timeout for socket based streams (seconds) ; If your scripts have to deal with files from Macintosh systems, ;;;;;;;;;;;;;;;;;;;;;; ; If you wish to have an extension loaded automatically, use the following ; The MIBS data available in the PHP distribution must be installed. ;extension=php_soap.dll ;;;;;;;;;;;;;;;;;;; [CLI Server] [Date] ; http://php.net/date.default-latitude ; http://php.net/date.default-longitude ; http://php.net/date.sunrise-zenith ; http://php.net/date.sunset-zenith [filter] ; http://php.net/filter.default-flags [iconv] ; Use of this INI entry is deprecated, use global internal_encoding instead. ; Use of this INI entry is deprecated, use global output_encoding instead. [intl] [sqlite3] [Pcre] ;PCRE library recursion limit. ;Enables or disables JIT compilation of patterns. This requires the PCRE [Pdo] ;pdo_odbc.db2_instance_name [Pdo_mysql] ; Default socket name for local MySQL connects. If empty, uses the built-in [Phar] ; http://php.net/phar.require-hash ;phar.cache_list = [mail function] ; For Win32 only. ; For Unix only. You may supply arguments as well (default: "sendmail -t -i"). ; Force the addition of the specified parameters to be passed as extra parameters ; Add X-PHP-Originating-Script: that will include uid of the script followed by the filename ; The path to a log file that will log all mail() calls. Log entries include [SQL] [ODBC] ; http://php.net/odbc.default-user ; http://php.net/odbc.default-pw ; Controls the ODBC cursor model. ; Allow or prevent persistent links. ; Check that a connection is still valid before reuse. ; Maximum number of persistent links. -1 means no limit. ; Maximum number of links (persistent + non-persistent). -1 means no limit. ; Handling of LONG fields. Returns number of bytes to variables. 0 means ; Handling of binary data. 0 means passthru, 1 return as is, 2 convert to char. ;birdstep.max_links = -1 [Interbase] ; Maximum number of persistent links. -1 means no limit. ; Maximum number of links (persistent + non-persistent). -1 means no limit. ; Default database name for ibase_connect(). ; Default username for ibase_connect(). ; Default password for ibase_connect(). ; Default charset for ibase_connect(). ; Default timestamp format. ; Default date format. ; Default time format. [MySQLi] ; Maximum number of persistent links. -1 means no limit. ; Allow accessing, from PHP's perspective, local files with LOAD DATA statements ; Allow or prevent persistent links. ; Maximum number of links. -1 means no limit. ; If mysqlnd is used: Number of cache slots for the internal result set cache ; Default port number for mysqli_connect(). If unset, mysqli_connect() will use ; Default socket name for local MySQL connects. If empty, uses the built-in ; Default host for mysql_connect() (doesn't apply in safe mode). ; Default user for mysql_connect() (doesn't apply in safe mode). ; Default password for mysqli_connect() (doesn't apply in safe mode). ; Allow or prevent reconnect [mysqlnd] ; Enable / Disable collection of memory usage statistics by mysqlnd which can be ; Records communication from all extensions using mysqlnd to the specified log ; Defines which queries will be logged. ; Default size of the mysqlnd memory pool, which is used by result sets. ; Size of a pre-allocated buffer used when sending commands to MySQL in bytes. ; Size of a pre-allocated buffer used for reading data sent by the server in ; Timeout for network requests in seconds. ; SHA-256 Authentication Plugin related. File with the MySQL server public RSA [OCI8] ; Connection: Enables privileged connections using external ; Connection: The maximum number of persistent OCI8 connections per ; Connection: The maximum number of seconds a process is allowed to ; Connection: The number of seconds that must pass before issuing a ; Connection: Set this to a user chosen connection class to be used ; High Availability: Using On lets PHP receive Fast Application ; Tuning: This option enables statement caching, and specifies how ; Tuning: Enables statement prefetching and sets the default number of ; Compatibility. Using On means oci_close() will not close [PostgreSQL] ; Detect broken persistent links always with pg_pconnect(). ; Maximum number of persistent links. -1 means no limit. ; Maximum number of links (persistent+non persistent). -1 means no limit. ; Ignore PostgreSQL backends Notice message or not. ; Log PostgreSQL backends Notice message or not. [bcmath] [browscap] [Session] ; Argument passed to save_handler. In the case of files, this is the path ; Whether to use strict session mode. ; Whether to use cookies. ; http://php.net/session.cookie-secure ; This option forces PHP to fetch and use a cookie for storing and maintaining ; Name of the session (used as cookie name). ; Initialize session on request startup. ; Lifetime in seconds of cookie or, if 0, until browser is restarted. ; The path for which the cookie is valid. ; The domain for which the cookie is valid. ; Whether or not to add the httpOnly flag to the cookie, which makes it inaccessible to browser scripting languages such as JavaScript. ; Handler used to serialize data. php is the standard serializer of PHP. ; Defines the probability that the 'garbage collection' process is started ; Defines the probability that the 'garbage collection' process is started on every ; After this number of seconds, stored data will be seen as 'garbage' and ; NOTE: If you are using the subdirectory option for storing session files ; Check HTTP Referer to invalidate externally stored URLs containing ids. ; Set to {nocache,private,public,} to determine HTTP caching aspects ; Document expires after n minutes. ; trans sid support is disabled by default. ; Set session ID character length. This value could be between 22 to 256. ; The URL rewriter will look for URLs in a defined set of HTML tags. ; add a hidden field with the info which is otherwise appended ; to URLs. tag's action attribute URL will not be modified ; unless it is specified. ; Note that all valid entries require a "=", even if no value follows. ; Default Value: "a=href,area=href,frame=src,form=" ; Development Value: "a=href,area=href,frame=src,form=" ; Production Value: "a=href,area=href,frame=src,form=" ; http://php.net/url-rewriter.tags session.trans_sid_tags = "a=href,area=href,frame=src,form=" ; URL rewriter does not rewrite absolute URLs by default. ; Define how many bits are stored in each character when converting ; Enable upload progress tracking in $_SESSION ; Cleanup the progress information as soon as all POST data has been read ; A prefix used for the upload progress key in $SESSION ; The index name (concatenated with the prefix) in $_SESSION ; How frequently the upload progress should be updated. ; The minimum delay between updates, in seconds ; Only write session data when session data is changed. Enabled by default. [Assertion] ; Assert(expr); active by default. ; Throw an AssertationException on failed assertions ; Issue a PHP warning for each failed assertion. (Overridden by assert.exception if active) ; Don't bail out by default. ; User-function to be called if an assertion fails. ; Eval the expression with current error_reporting(). Set to true if you want [COM] ; allow Distributed-COM calls ; autoregister constants of a components typlib on com_load() ; register constants casesensitive ; show warnings on duplicate constant registrations ; The default character set code-page to use when passing strings to and from COM objects. [mbstring] ; Use of this INI entry is deprecated, use global internal_encoding instead. ; Use of this INI entry is deprecated, use global input_encoding instead. ; Use of this INI entry is deprecated, use global output_encoding instead. ; enable automatic encoding translation according to ; automatic encoding detection order. ; substitute_character used when character cannot be converted ; overload(replace) single byte functions by mbstring functions. ; enable strict encoding detection. ; This directive specifies the regex pattern of content types for which mb_output_handler() [gd] [exif] ; http://php.net/exif.decode-unicode-motorola ; http://php.net/exif.decode-unicode-intel ; http://php.net/exif.encode-jis ; http://php.net/exif.decode-jis-motorola ; http://php.net/exif.decode-jis-intel [Tidy] ; Should tidy clean and repair output automatically? [soap] ; Sets the directory name where SOAP extension will put cache files. ; (time to live) Sets the number of second while cached file will be used ; Sets the size of the cache limit. (Max. number of WSDL files to cache) [sysvshm] [ldap] [mcrypt] ; Directory where to load mcrypt algorithms ; Directory where to load mcrypt modes [dba] [opcache] ; Determines if Zend OPCache is enabled for the CLI version of PHP ; The OPcache shared memory storage size. ; The amount of memory for interned strings in Mbytes. ; The maximum number of keys (scripts) in the OPcache hash table. ; The maximum percentage of "wasted" memory until a restart is scheduled. ; When this directive is enabled, the OPcache appends the current working ; When disabled, you must reset the OPcache manually or restart the ; How often (in seconds) to check file timestamps for changes to the shared ; Enables or disables file search in include_path optimization ; If disabled, all PHPDoc comments are dropped from the code to reduce the ; If enabled, a fast shutdown sequence is used for the accelerated code ; Allow file existence override (file_exists, etc.) performance feature. ; A bitmask, where each bit enables or disables the appropriate OPcache ;opcache.inherited_hack=1 ; The location of the OPcache blacklist file (wildcards allowed). ; Allows exclusion of large files from being cached. By default all files ; Check the cache checksum each N requests. ; How long to wait (in seconds) for a scheduled restart to begin if the cache ; OPcache error_log file name. Empty string assumes "stderr". ; All OPcache errors go to the Web server log. ; Preferred Shared Memory back-end. Leave empty and let the system decide. ; Protect the shared memory from unexpected writing during script execution. ; Allows calling OPcache API functions only from PHP scripts which path is ; Mapping base of shared memory segments (for Windows only). All the PHP ; Enables and sets the second level cache directory. ; Enables or disables opcode caching in shared memory. ; Enables or disables checksum validation when script loaded from file cache. ; Implies opcache.file_cache_only=1 for a certain process that failed to ; Enables or disables copying of PHP code (text segment) into HUGE PAGES. ; Validate cached file permissions. ; Prevent name collisions in chroot'ed environment. [curl] [openssl] ; If openssl.cafile is not specified or if the CA file is not found, the ; Local Variables: .htacces file in magento 2 root folder:############################################ overrides deployment configuration mode valueuse command bin/magento deploy:mode:set to switch modesSetEnv MAGE_MODE developer############################################ uncomment these lines for CGI modemake sure to specify the correct cgi php binary file nameit might be /cgi-bin/php-cgiAction php5-cgi /cgi-bin/php5-cgiAddHandler php5-cgi .php############################################ GoDaddy specific optionsOptions -MultiViewsyou might also need to add this line to php.inicgi.fix_pathinfo = 1if it still doesn't work, rename php.ini to php5.ini############################################ this line is specific for 1and1 hosting
############################################ default index file
############################################ disable automatic session startbefore autoload was initialized
############################################ enable resulting html compression
########################################### disable user agent verification to not break multiple image upload
############################################ disable automatic session startbefore autoload was initialized
############################################ enable resulting html compression
########################################### disable user agent verification to not break multiple image upload
############################################ enable apache served files compressionhttp://developer.yahoo.com/performance/rules.html#gzip
############################################ make HTTPS env vars available for CGI mode
############################################ workaround for Apache 2.4.6 CentOS build when working via ProxyPassMatch with HHVM (or any other)Please, set it on virtual host configuration levelSetEnvIf Authorization "(.*)" HTTP_AUTHORIZATION=$1############################################ ############################################ enable rewrites
############################################ you can put here your magento root folderpath relative to web root
############################################ workaround for HTTP authorizationin CGI environment
############################################ TRACE and TRACK HTTP methods disabled to prevent XSS attacks
############################################ redirect for mobile user agents
############################################ never rewrite for existing files, directories and links
############################################ rewrite everything else to index.php
############################################ Prevent character encoding issues from server overridesIf you still have problems, use the second line instead
############################################ Add default Expires headerhttp://developer.yahoo.com/performance/rules.html#expires
########################################### Deny access to root files to hide sensitive application information
For 404s and 403s that aren't handled by the application, show plain 404 responseErrorDocument 404 /pub/errors/404.php ################################ If running in cluster environment, uncomment thishttp://developer.yahoo.com/performance/rules.html#etags
# INTERNET EXPLORER----------------------------------------------------------------------| Document modes |----------------------------------------------------------------------Force Internet Explorer 8/9/10 to render pages in the highest modeavailable in the various cases when it may not.https://hsivonen.fi/doctype/#ie8(!) Starting with Internet Explorer 11, document modes are deprecated.If your business still relies on older web apps and services that weredesigned for older versions of Internet Explorer, you might want toconsider enabling
|
your cron jobs are getting stuck eating all your ram! check your mail or cron.log to see what exactly they are doing! I'd disable the crontab until you know how to correct your problem to bring system stability back! |
Thank you for your help. I think that my MySQL cnf file is not correctly set. Can you help me to improve it with my hardware configuration specified above. Thank you in advance |
Thank you i will try to use it. |
be sure to check /var/spool/mail/ and /var/log/cron for your crontab woes first! |
Hello |
I have change table_open_cache value from 6000 to 512. I think that was the problem. Thank you for your help. mysqltuner help me to solve it. |
Preconditions
- CPU: Intel Xeon E5-1650 V3 @ 3.50GHz X 8
- RAM: 64 GO
Steps to reproduce
or
I have to regularly (several times a day) use the command sudo service mysql restart in order to free some memory. If I do not do this, the server stops responding.
Expected result
Actual result
My MySQL cnf file "/etc/mysql/conf.d/my.cnf":
The MySQL database server configuration file.
You can copy this to one of:
- "/etc/mysql/my.cnf" to set global options,
- "~/.my.cnf" to set user-specific options.
One can use all long options that the program supports.
Run program with --help to get a list of available options and with
--print-defaults to see which it would actually understand and use.
For explanations see
http://dev.mysql.com/doc/mysql/en/server-system-variables.html
This will be passed to all mysql clients
It has been reported that passwords should be enclosed with ticks/quotes
escpecially if they contain "#" chars...
Remember to edit /etc/mysql/debian.cnf when changing the socket location.
Here is entries for some specific programs
The following values assume you have at least 32M ram
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
* Basic Settings
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
Instead of skip-networking the default is now to listen only on
localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
* Fine Tuning
key_buffer_size = 64M
max_allowed_packet = 64M
thread_stack = 192K
thread_cache_size = 8
wait_timeout = 28800
connect_timeout = 120
This replaces the startup script and checks MyISAM tables if needed
the first time they are touched
myisam-recover-options = BACKUP
#max_connections = 100
#table_cache = 64
#thread_concurrency = 10
* Query Cache Configuration
query_cache_limit = 16M
query_cache_size = 256M
table_open_cache = 6000
key_buffer_size = 1024M
* 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 log - should be very few entries.
log_error = /var/log/mysql/error.log
Here you can see queries with especially long duration
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
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
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
* InnoDB
innodb_buffer_pool_size = 4G
innodb_log_file_size = 16M
InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
Read the manual for more InnoDB related options. There are many!
* 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
The text was updated successfully, but these errors were encountered: