Prestashop problem on centminmod, database mariadb use CPU 100%

My mysql config.
Is there anything to correct.

I was trying to recompile php from 7.3 to 7.2 but there is the same CPU problem.

Code (Text):

[client]
default-character-set=utf8
socket=/var/lib/mysql/mysql.sock

[mysql]
max_allowed_packet = 128M

[mysqld]
character-set-server=utf8mb4
ignore_db_dirs=cmsetiofiotest
local-infile=0
ignore_db_dirs=lost+found
character-set-server=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

#bind-address=127.0.0.1
# optimized my.cnf for MariaDB 5.5.x
# by eva2000
# centminmod.com

tmpdir=/home/mysqltmp

innodb=ON
#skip-federated
##skip-pbxt
##skip-pbxt_statistics
#skip-archive
#skip-name-resolve
#old_passwords
back_log = 768
max_connections = 900
key_buffer_size = 512M
myisam_sort_buffer_size = 512M
myisam_max_sort_file_size = 4096M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_definition_cache = 10240
table_open_cache = 10240
thread_cache_size = 384
wait_timeout = 1800
connect_timeout = 10
tmp_table_size = 768M
max_heap_table_size = 768M
max_allowed_packet = 128M
#max_seeks_for_key = 4294967295
#group_concat_max_len = 1024
max_length_for_sort_data = 1024
net_buffer_length = 16384
max_connect_errors = 100000
concurrent_insert = 2
read_rnd_buffer_size = 512K
bulk_insert_buffer_size = 8M
# query_cache boost for MariaDB >10.1.2+
# https://community.centminmod.com/posts/30811/
query_cache_limit = 1024K
query_cache_size = 160M
query_cache_type = 1
query_cache_min_res_unit = 2K
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = InnoDB

log_warnings=1
slow_query_log=0
long_query_time=1
slow_query_log_file=/var/lib/mysql/slowq.log
#log-error=/var/log/mysqld.log

# innodb settings
innodb_purge_threads = 4
innodb_file_per_table = 1
innodb_open_files = 10000
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_size = 4096M

## https://mariadb.com/kb/en/mariadb/xtradbinnodb-server-system-variables/#innodb_buffer_pool_instances
innodb_buffer_pool_instances=4

innodb_log_files_in_group = 2
innodb_log_file_size = 640M
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 20
innodb_lock_wait_timeout=50
innodb_flush_method = O_DIRECT

# 200 * # DISKS
innodb_io_capacity = 2100
innodb_io_capacity_max = 4200
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_flush_neighbors = 0

# mariadb settings
[mariadb]
#thread-handling = pool-of-threads
#thread-pool-size= 20
#mysql --port=3307 --protocol=tcp
#extra-port=3307
#extra-max-connections=1

userstat = 0
key_cache_segments = 1
aria_group_commit = none
aria_group_commit_interval = 0
aria_log_file_size = 1024M
aria_log_purge_type = immediate
aria_pagecache_buffer_size = 1024M
aria_sort_buffer_size = 256M

[mariadb-5.5]
innodb_file_per_table = 1

#ignore_db_dirs=
query_cache_strip_comments=0

innodb_read_ahead = linear
innodb_adaptive_flushing_method = estimate
innodb_flush_neighbor_pages = 1
innodb_stats_update_need_lock = 0
innodb_log_block_size = 512

log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

[mysqld_safe]
socket=/var/lib/mysql/mysql.sock
#log-error=/var/log/mysqld.log
#nice = -5
open-files-limit = 8192

[mysqldump]
quick
max_allowed_packet = 128M

[myisamchk]
tmpdir=/home/mysqltmp
key_buffer = 768M
sort_buffer = 128M
read_buffer = 128M
write_buffer = 128M

[mysqlhotcopy]
interactive-timeout

[mariadb-10.0]
innodb_file_per_table = 1

# 2 variables needed to switch from XtraDB to InnoDB plugins
#plugin-load=ha_innodb
#ignore_builtin_innodb

## MariaDB 10 only save and restore buffer pool pages
## warm up InnoDB buffer pool on server restarts
#innodb_buffer_pool_dump_at_shutdown=1
#innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_populate=0
## Disabled settings
performance_schema=OFF
innodb_stats_on_metadata=OFF
innodb_sort_buffer_size=2M
innodb_online_alter_log_max_size=128M
query_cache_strip_comments=0
log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

[mariadb-10.3]
#innodb_file_format = Barracuda
innodb_default_row_format = dynamic
innodb_file_per_table = 1

## wsrep specific
# wsrep_on=OFF
# wsrep_provider
# wsrep_cluster_address
# binlog_format=ROW
# default_storage_engine=InnoDB
# innodb_autoinc_lock_mode=2
# innodb_doublewrite=1
# query_cache_size=0

# 2 variables needed to switch from XtraDB to InnoDB plugins
#plugin-load=ha_innodb
#ignore_builtin_innodb

## MariaDB 10 only save and restore buffer pool pages
## warm up InnoDB buffer pool on server restarts
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
## Disabled settings
performance_schema=OFF
innodb_stats_on_metadata=OFF
innodb_sort_buffer_size=2M
innodb_online_alter_log_max_size=128M
query_cache_strip_comments=0
log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

# Defragmenting unused space on InnoDB tablespace
innodb_defragment=1
innodb_defragment_n_pages=7
innodb_defragment_stats_accuracy=0
innodb_defragment_fill_factor_n_recs=20
innodb_defragment_fill_factor=0.9
innodb_defragment_frequency=40

# page compression
# 10.1+ https://mariadb.com/kb/en/mariadb/compression/
#innodb_compression_algorithm=zlib
#innodb_use_fallocate=0
#innodb_use_mtflush=0
#innodb_mtflush_threads=4

 

Code (Text):

./mysqlreport
Use of uninitialized value in formline at ./mysqlreport line 1293.
MariaDB 10.3.20-MariaDB     uptime 0 23:0:17    Wed Nov 20 23:18:05 2019

__ Key _________________________________________________________________
Buffer used    39.21M of  512.00M   %Used:   7.66
  Current     132.58M              %Usage:  25.89
Write hit       5.59%
Read hit      100.00%

__ Questions ___________________________________________________________
Total           5.33M      64.4/s
  DMS           5.23M      63.1/s  %Total:  98.11
  -Unknown      4.56M      55.0/s           85.48
  QC Hits       4.52M      54.6/s           84.79
  Com_        111.48k       1.3/s            2.09
  COM_QUIT     25.92k       0.3/s            0.49
Slow 1 s          278       0.0/s            0.01  %DMS:   0.01 Log:
DMS             5.23M      63.1/s           98.11
  SELECT        5.06M      61.1/s           94.97         96.80
  INSERT      121.46k       1.5/s            2.28          2.32
  UPDATE       31.02k       0.4/s            0.58          0.59
  DELETE       14.76k       0.2/s            0.28          0.28
  REPLACE           0         0/s            0.00          0.00
Com_          111.48k       1.3/s            2.09
  set_option   57.79k       0.7/s            1.08
  stmt_prepar  12.20k       0.1/s            0.23
  stmt_close   12.20k       0.1/s            0.23

__ Rows ________________________________________________________________
Rows           22.73G    274.5k/s
  Using idx    22.65G    273.5k/s  %Index:  99.62
Rows/question   4.27k

__ SELECT and Sort _____________________________________________________
Scan           13.12k       0.2/s %SELECT:   0.26
Range          12.72k       0.2/s            0.25
Full join       1.46k       0.0/s            0.03
Range check         0         0/s            0.00
Full rng join     362       0.0/s            0.01
Sort scan      28.42k       0.3/s
Sort range     28.20k       0.3/s
Sort mrg pass      11       0.0/s

__ Query Cache _________________________________________________________
Memory usage  150.94M of  160.00M  %Usage:  94.33
Block Fragmnt   8.44%
Hits            4.52M      54.6/s
Inserts       505.23k       6.1/s
Insrt:Prune    4.32:1       4.7/s
Hit:Insert     8.94:1

__ Table Locks _________________________________________________________
Waited          9.18k       0.1/s  %Total:   0.98
Immediate     929.84k      11.2/s

__ Tables ______________________________________________________________
Open             1427 of  10240    %Cache:  13.94
Opened          2.15k       0.0/s

__ Connections _________________________________________________________
Max used           34 of    900      %Max:   3.78
Total          25.93k       0.3/s

__ Created Temp ________________________________________________________
Disk table      9.47k       0.1/s   %Disk:  24.79
Table          38.20k       0.5/s    Size: 768.0M
File               10       0.0/s

__ Threads _____________________________________________________________
Running             7 of      2
Created            34       0.0/s
Slow                0         0/s
Cached             32 of    384      %Hit:  99.87

__ Aborted _____________________________________________________________
Clients             4       0.0/s
Connects            1       0.0/s

__ Bytes _______________________________________________________________
Sent           19.45G    234.8k/s
Received        1.00G     12.1k/s

__ InnoDB Buffer Pool __________________________________________________
Usage         307.80M of    4.00G  %Usage:   7.51
Read hit       99.98%
Pages
  Free        242.44k              %Total:  92.49
  Data         19.42k                        7.41  %Drty:   0.00
  Misc            283                        0.11
  Latched           0                        0.00
Reads          92.19M      1.1k/s
  From disk    19.21k       0.2/s   %Disk:   0.02
  Ahead Rnd         0         0/s
Writes        137.14k       1.7/s
Flushes        78.78k       1.0/s
Wait Free           0         0/s   %Wait:   0.00

__ InnoDB Lock _________________________________________________________
Waits               0         0/s
Current             0
Time acquiring
  Total             0 ms
  Average           0 ms
  Max               0 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
  Reads        19.95k       0.2/s
  Writes      112.62k       1.4/s
  fsync        42.46k       0.5/s
  Pending
    Reads           0
    Writes          0
    fsync           0

Pages
  Created         173       0.0/s
  Read         19.24k       0.2/s
  Written      78.78k       1.0/s

Rows
  Deleted       3.33k       0.0/s
  Inserted      9.01k       0.1/s
  Read        123.28M      1.5k/s
  Updated       3.45k       0.0/s

__ Aria Pagecache ______________________________________________________
Buffer used   576.00k of    1.00G   %Used:   0.05
  Current      42.30M              %Usage:   4.13
Write hit      93.93%
Read hit       99.99%

__ Subquery Cache ______________________________________________________
Hit ratio       0.45%
Hits                6      0.0/s
Miss            1.32k      0.0/s

 

@eva2000 current stats, is there anything wrong?

 

After many hours of fighting I have installed a clean Centos 7, on similar VPS.

I have installed rsync Prestashop, to have a copy and till now new server (VPS) is OK.

It is little bit slower, there is no optimizations or node is more busy.

Anyway there is no CPU100% problem.

Something generate problem in Centminmode but I can not determine what is the problem.

There is no hardware problem, because for tests i bought much stronger dedicated server and i was able to reproduce Centminmode problems.

It is related to configs but i don't know how to correct it.


Tomorrow I will try to optimize the new server and I will check when I will get a problem or not.
Then I will decide what to do. Stay or leave Centminmode community.

@eva2000 George thank you for your help and time

 

At Centminmode:

I was trying:

1a. Use different version of php 7.2 and few version of 7.3
1b. Try to do not install Zendopcache
1c. Do not USE PDO optimization.
2. Try to use default mariadb config
3. Try to change config of php-fmp
5. disable memcached
service memcached stop
chkconfig memcached off

My currect configs:

/etc/centminmod/custom_config.inc

Code (Text):

PHPFINFO='y'
NGINX_SSLCACHE_ALLOWOVERRIDE='y'
SET_DEFAULT_MYSQLCHARSET='utf8mb4'
AUTOHARDTUNE_NGINXBACKLOG='y'
ZSTD_LOGROTATE_NGINX='y'
ZSTD_LOGROTATE_PHPFPM='y'
NGINX_LIBBROTLI='y'
NGXDYNAMIC_BROTLI='y'
PHP_PGO='n'
PHP_BROTLI='y'
PHP_LZFOUR='y'
PHP_LZF='y'
PHP_ZSTD='y'
PHPREDIS='n'
LETSENCRYPT_DETECT='y'
DUALCERTS='y'
AUDITD_ENABLE='y'
LETSENCRYPT_DETECT='y'
DUALCERTS='y'
AUDITD_ENABLE='y'

/etc/centminmod/php.d/a_customphp.ini

Code (Text):

date.timezone = UTC
max_execution_time = 60
short_open_tag = On
realpath_cache_size = 4096k
realpath_cache_ttl = 43200
upload_max_filesize = 800M
memory_limit = 800M
post_max_size = 800M
expose_php = Off
mail.add_x_header = Off
max_input_nesting_level = 128
max_input_vars = 10000
mysqlnd.net_cmd_buffer_size = 16384
mysqlnd.collect_memory_statistics = Off
mysqlnd.mempool_default_size = 16000
always_populate_raw_post_data=-1

/etc/centminmod/php.d/memcached.ini

Code (Text):

extension=/usr/local/lib/php/extensions/no-debug-non-zts-20180731/memcached.so
memcached.sess_connect_timeout=3000
; http://php.net/manual/en/memcached.configuration.php
memcached.sess_consistent_hash=0
memcached.sess_number_of_replicas=0
memcached.sess_randomize_replica_read=0
memcached.sess_remove_failed=0
memcached.sess_binary=0

/etc/my.cnf

Code (Text):

[client]
default-character-set=utf8
socket=/var/lib/mysql/mysql.sock

[mysql]
max_allowed_packet = 128M

[mysqld]
character-set-server=utf8mb4
ignore_db_dirs=cmsetiofiotest
local-infile=0
ignore_db_dirs=lost+found
character-set-server=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

#bind-address=127.0.0.1
# optimized my.cnf for MariaDB 5.5.x
# by eva2000
# centminmod.com

tmpdir=/home/mysqltmp

innodb=ON
#skip-federated
##skip-pbxt
##skip-pbxt_statistics
#skip-archive
#skip-name-resolve
#old_passwords
back_log = 768
max_connections = 900
key_buffer_size = 512M
myisam_sort_buffer_size = 512M
myisam_max_sort_file_size = 4096M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_definition_cache = 10240
table_open_cache = 10240
thread_cache_size = 384
wait_timeout = 1800
connect_timeout = 10
tmp_table_size = 768M
max_heap_table_size = 768M
max_allowed_packet = 128M
#max_seeks_for_key = 4294967295
#group_concat_max_len = 1024
max_length_for_sort_data = 1024
net_buffer_length = 16384
max_connect_errors = 100000
concurrent_insert = 2
read_rnd_buffer_size = 512K
bulk_insert_buffer_size = 8M
# query_cache boost for MariaDB >10.1.2+
# https://community.centminmod.com/posts/30811/
query_cache_limit = 1024K
query_cache_size = 160M
query_cache_type = 1
query_cache_min_res_unit = 2K
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = InnoDB

log_warnings=1
slow_query_log=0
long_query_time=1
slow_query_log_file=/var/lib/mysql/slowq.log
#log-error=/var/log/mysqld.log

# innodb settings
innodb_purge_threads = 4
innodb_file_per_table = 1
innodb_open_files = 10000
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_size = 4096M

## https://mariadb.com/kb/en/mariadb/xtradbinnodb-server-system-variables/#innodb_buffer_pool_instances
innodb_buffer_pool_instances=4

innodb_log_files_in_group = 2
innodb_log_file_size = 640M
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 20
innodb_lock_wait_timeout=50
innodb_flush_method = O_DIRECT

# 200 * # DISKS
innodb_io_capacity = 2100
innodb_io_capacity_max = 4200
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_flush_neighbors = 0

# mariadb settings
[mariadb]
#thread-handling = pool-of-threads
#thread-pool-size= 20
#mysql --port=3307 --protocol=tcp
#extra-port=3307
#extra-max-connections=1

userstat = 0
key_cache_segments = 1
aria_group_commit = none
aria_group_commit_interval = 0
aria_log_file_size = 1024M
aria_log_purge_type = immediate
aria_pagecache_buffer_size = 1024M
aria_sort_buffer_size = 256M

[mariadb-5.5]
innodb_file_per_table = 1

#ignore_db_dirs=
query_cache_strip_comments=0

innodb_read_ahead = linear
innodb_adaptive_flushing_method = estimate
innodb_flush_neighbor_pages = 1
innodb_stats_update_need_lock = 0
innodb_log_block_size = 512

log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

[mysqld_safe]
socket=/var/lib/mysql/mysql.sock
#log-error=/var/log/mysqld.log
#nice = -5
open-files-limit = 8192

[mysqldump]
quick
max_allowed_packet = 128M

[myisamchk]
tmpdir=/home/mysqltmp
key_buffer = 768M
sort_buffer = 128M
read_buffer = 128M
write_buffer = 128M

[mysqlhotcopy]
interactive-timeout

[mariadb-10.0]
innodb_file_per_table = 1

# 2 variables needed to switch from XtraDB to InnoDB plugins
#plugin-load=ha_innodb
#ignore_builtin_innodb

## MariaDB 10 only save and restore buffer pool pages
## warm up InnoDB buffer pool on server restarts
#innodb_buffer_pool_dump_at_shutdown=1
#innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_populate=0
## Disabled settings
performance_schema=OFF
innodb_stats_on_metadata=OFF
innodb_sort_buffer_size=2M
innodb_online_alter_log_max_size=128M
query_cache_strip_comments=0
log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

[mariadb-10.3]
#innodb_file_format = Barracuda
innodb_default_row_format = dynamic
innodb_file_per_table = 1

## wsrep specific
# wsrep_on=OFF
# wsrep_provider
# wsrep_cluster_address
# binlog_format=ROW
# default_storage_engine=InnoDB
# innodb_autoinc_lock_mode=2
# innodb_doublewrite=1
# query_cache_size=0

# 2 variables needed to switch from XtraDB to InnoDB plugins
#plugin-load=ha_innodb
#ignore_builtin_innodb

## MariaDB 10 only save and restore buffer pool pages
## warm up InnoDB buffer pool on server restarts
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
## Disabled settings
performance_schema=OFF
innodb_stats_on_metadata=OFF
innodb_sort_buffer_size=2M
innodb_online_alter_log_max_size=128M
query_cache_strip_comments=0
log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

# Defragmenting unused space on InnoDB tablespace
innodb_defragment=1
innodb_defragment_n_pages=7
innodb_defragment_stats_accuracy=0
innodb_defragment_fill_factor_n_recs=20
innodb_defragment_fill_factor=0.9
innodb_defragment_frequency=40

# page compression
# 10.1+ https://mariadb.com/kb/en/mariadb/compression/
#innodb_compression_algorithm=zlib
#innodb_use_fallocate=0
#innodb_use_mtflush=0
#innodb_mtflush_threads=4

/usr/local/nginx/conf/conf.d/mydomain.com.ssl.conf

Code (Text):

# Centmin Mod Getting Started Guide
# must read http://centminmod.com/getstarted.html
# For HTTP/2 SSL Setup
# read http://centminmod.com/nginx_configure_https_ssl_spdy.html

# redirect from www to non-www  forced SSL
# uncomment, save file and restart Nginx to enable
# if unsure use return 302 before using return 301
# server {
#       listen   80;
#       server_name mydomain.com www.mydomain.com;
#       return 302 https://$server_name$request_uri;
# }

server {
  listen 443 ssl http2 reuseport;
  listen 80;
  server_name mydomain.com www.mydomain.com;

  ssl_dhparam /usr/local/nginx/conf/ssl/mydomain.com/dhparam.pem;
  ssl_certificate      /etc/letsencrypt/live/mydomain.com/fullchain.pem;
  ssl_certificate_key  /etc/letsencrypt/live/mydomain.com/privkey.pem;
  include /usr/local/nginx/conf/ssl_include.conf;

  # cloudflare authenticated origin pull cert community.centminmod.com/threads/13847/
  #ssl_client_certificate /usr/local/nginx/conf/ssl/cloudflare/mydomain.com/origin.crt;
  #ssl_verify_client on;
  http2_max_field_size 16k;
  http2_max_header_size 32k;
  # mozilla recommended
  ssl_ciphers TLS13-AES-128-GCM-SHA256:TLS13-AES-256-GCM-SHA384:TLS13-CHACHA20-POLY1305-SHA256:TLS13-AES-128-CCM-8-SHA256:TLS13-AES-128-CCM-SHA256:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:DHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES128-SHA256:ECDHE-ECDSA-AES128-SHA:ECDHE-RSA-AES256-SHA384:ECDHE-RSA-AES128-SHA:ECDHE-ECDSA-AES256-SHA384:ECDHE-ECDSA-AES256-SHA:ECDHE-RSA-AES256-SHA:DHE-RSA-AES128-SHA256:DHE-RSA-AES128-SHA:DHE-RSA-AES256-SHA256:DHE-RSA-AES256-SHA:ECDHE-ECDSA-DES-CBC3-SHA:ECDHE-RSA-DES-CBC3-SHA:EDH-RSA-DES-CBC3-SHA:AES128-GCM-SHA256:AES256-GCM-SHA384:AES128-SHA256:AES256-SHA256:AES128-SHA:AES256-SHA:DES-CBC3-SHA:!DSS;
  ssl_prefer_server_ciphers   on;
  #add_header Alternate-Protocol  443:npn-spdy/3;

  # before enabling HSTS line below read centminmod.com/nginx_domain_dns_setup.html#hsts
  #add_header Strict-Transport-Security "max-age=31536000; includeSubdomains;";
  #add_header X-Frame-Options SAMEORIGIN;
  add_header X-Xss-Protection "1; mode=block" always;
  add_header X-Content-Type-Options "nosniff" always;
  #add_header Referrer-Policy "strict-origin-when-cross-origin";
  #spdy_headers_comp 5;
  ssl_buffer_size 1369;
  ssl_session_tickets on;
 
  # enable ocsp stapling
  #resolver 8.8.8.8 8.8.4.4 1.1.1.1 1.0.0.1 valid=10m;
  #resolver_timeout 10s;
  #ssl_stapling on;
  #ssl_stapling_verify on;
  #ssl_trusted_certificate /usr/local/nginx/conf/ssl/mydomain.com/mydomain.com-trusted.crt;

# ngx_pagespeed & ngx_pagespeed handler
#include /usr/local/nginx/conf/pagespeed.conf;
#include /usr/local/nginx/conf/pagespeedhandler.conf;
#include /usr/local/nginx/conf/pagespeedstatslog.conf;

  # limit_conn limit_per_ip 16;
  # ssi  on;

  access_log /home/mydomain/domains/log/ls.access.log combined buffer=256k flush=5m;
  error_log /home/mydomain/domains/log/ls.error.log;

  include /usr/local/nginx/conf/autoprotect/mydomain.com/autoprotect-mydomain.com.conf;
  root /home/mydomain/domains/mydomain.com;
  # uncomment cloudflare.conf include if using cloudflare for
  # server and/or vhost site
  #include /usr/local/nginx/conf/cloudflare.conf;
  include /usr/local/nginx/conf/503include-main.conf;

  #location / {
  #include /usr/local/nginx/conf/503include-only.conf;

# block common exploits, sql injections etc
#include /usr/local/nginx/conf/block.conf;

  # Enables directory listings when index file not found
  #autoindex  on;

  # Shows file listing times as local time
  #autoindex_localtime on;

  # Wordpress Permalinks example
  #try_files $uri $uri/ /index.php?q=$uri&$args;

  #}

  include /usr/local/nginx/conf/pre-staticfiles-local-mydomain.com.conf;
  include /usr/local/nginx/conf/pre-staticfiles-global.conf;
  include /usr/local/nginx/conf/staticfiles.conf;
  include /usr/local/nginx/conf/php.conf;
 
  include /usr/local/nginx/conf/drop.conf;
  #include /usr/local/nginx/conf/errorpage.conf;
  include /usr/local/nginx/conf/vts_server.conf;
  include /usr/local/nginx/conf/prestashop.conf;
}

/usr/local/etc/php-fpm.conf

Code (Text):

[global]
; Log level
; Possible Values: alert, error, warning, notice, debug
; Default Value: notice
log_level = warning
pid = /var/run/php-fpm/php-fpm.pid
error_log = /var/log/php-fpm/www-error.log
emergency_restart_threshold = 10
emergency_restart_interval = 1m
process_control_timeout = 10s
;include=/usr/local/nginx/conf/phpfpmd/*.conf

[www]
user = nginx
group = nginx

listen = 127.0.0.1:9000
listen.allowed_clients = 127.0.0.1
listen.backlog = 65535

;listen = /tmp/php5-fpm.sock
listen.owner = nginx
listen.group = nginx
listen.mode = 0660

pm = ondemand
pm.max_children = 30
; Default Value: min_spare_servers + (max_spare_servers - min_spare_servers) / 2
pm.start_servers = 8
pm.min_spare_servers = 4
pm.max_spare_servers = 12
pm.max_requests = 5000

; PHP 5.3.9 setting
; The number of seconds after which an idle process will be killed.
; Note: Used only when pm is set to 'ondemand'
; Default Value: 10s
pm.process_idle_timeout = 10s;

rlimit_files = 65536
rlimit_core = 0

; The timeout for serving a single request after which the worker process will
; be killed. This option should be used when the 'max_execution_time' ini option
; does not stop script execution for some reason. A value of '0' means 'off'.
; Available units: s(econds)(default), m(inutes), h(ours), or d(ays)
; Default Value: 0
;request_terminate_timeout = 0
; Default Value: 0
;request_slowlog_timeout = 0
slowlog = /var/log/php-fpm/www-slow.log

pm.status_path = /phpstatus
ping.path = /phpping
ping.response = pong

; Limits the extensions of the main script FPM will allow to parse. This can
; prevent configuration mistakes on the web server side. You should only limit
; FPM to .php extensions to prevent malicious users to use other extensions to
; exectute php code.
; Note: set an empty value to allow all extensions.
; Default Value: .php
security.limit_extensions = .php

; catch_workers_output = yes
php_admin_value[error_log] = /var/log/php-fpm/www-php.error.log
php_admin_value[disable_functions] = shell_exec

I was looking at this files but it seams ok, but maybe someone will notice something wrong which make this problem.

2. Is there any other config that I should have a look and may be related to my problem (mariadb CPU100%)?

 

Is there anything else that I should to check?

 

@eva2000 Thank you for your respond and for ideas. I not an expert but does MySQL slow queries are more related to application (Prestashop) troubleshooting?

The same database and Prestashop on Centos without centminmode (maria, php-fmp, nginx) is fine. The problem is when I use centminmode. Aplication and the database is the same i even change confing (to default one) in centminmod but it did not helped.

I guess that is more related to php not to mariadb.

I was changing config in mariadb and nginx but no changes.
Only thing that I did make is that I did not reinstall php on centminmode without recompile it to check it will make a change or not.
I have a problem to do that.

Is there any way to install default php from repository in centminmod to check if the problem is not php?

It do not looks that is a nginx or mariadb config, so it left php or something else, but what....

 

any other things could be then php?

 

Ok after another x hours I think that finally i have found that is the problem.
I was disabling one by one and finally i disabled module cross selling and it seems that problem is solved.

Thank you for help.

I will make extensive testing.

to use prestashop with centmin it need to me turn on:

Code (Text):

PHPFINFO='y'
PHPINTL='y'

my /etc/centminmod/custom_config.inc looks:

Code (Text):

NGINX_SSLCACHE_ALLOWOVERRIDE='y'
SET_DEFAULT_MYSQLCHARSET='utf8mb4'
AUTOHARDTUNE_NGINXBACKLOG='y'
ZSTD_LOGROTATE_NGINX='y'
ZSTD_LOGROTATE_PHPFPM='y'
NGINX_LIBBROTLI='y'
NGXDYNAMIC_BROTLI='y'
PHP_PGO='y'
PHP_BROTLI='y'
PHP_LZFOUR='y'
PHP_LZF='y'
PHP_ZSTD='y'
LETSENCRYPT_DETECT='y'
DUALCERTS='y'
AUDITD_ENABLE='y'
PHPREDIS='n'
PHPINTL='y'
PHPFINFO='y'

for any update I recommend to follow:
https://community.centminmod.com/threads/timeout-for-a-proccess.8394/

 

I have Prestashop 1.6 but I use php 7.3.

please check System requirements for PrestaShop 1.7
php compatibility matrix.

I had a lot of problems and finally I have contacted with one company who helpem me to install and configure everything.