Learn about Centmin Mod LEMP Stack today
Register Now

Prestashop problem on centminmod, database mariadb use CPU 100%

Discussion in 'Install & Upgrades or Pre-Install Questions' started by adamus007p, Oct 15, 2019.

Tags:
  1. eva2000

    eva2000 Administrator Staff Member

    46,238
    10,510
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +16,293
    Local Time:
    11:17 PM
    Nginx 1.19.x
    MariaDB 5.5/10.x
    only 4 mins 22 seconds of activity monitored so not really indicative of your load - give it 24-72hrs to accumulate

    setio.sh disk I/O looks good
     
  2. adamus007p

    adamus007p Member

    254
    17
    18
    Feb 8, 2019
    Ratings:
    +28
    Local Time:
    2:17 PM
    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
    
     
  3. adamus007p

    adamus007p Member

    254
    17
    18
    Feb 8, 2019
    Ratings:
    +28
    Local Time:
    2:17 PM
    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
    
     
  4. adamus007p

    adamus007p Member

    254
    17
    18
    Feb 8, 2019
    Ratings:
    +28
    Local Time:
    2:17 PM
    @eva2000 current stats, is there anything wrong?
     
  5. eva2000

    eva2000 Administrator Staff Member

    46,238
    10,510
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +16,293
    Local Time:
    11:17 PM
    Nginx 1.19.x
    MariaDB 5.5/10.x
    Stats look good with high read hit rate percentage wise and high rows using indexe percentage and optimal buffer memory usage where 96.8% of queries are reads/selects.

    Nothing wrong from what I can see.
     
  6. adamus007p

    adamus007p Member

    254
    17
    18
    Feb 8, 2019
    Ratings:
    +28
    Local Time:
    2:17 PM
    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
     
  7. adamus007p

    adamus007p Member

    254
    17
    18
    Feb 8, 2019
    Ratings:
    +28
    Local Time:
    2:17 PM
    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%)?
     
    Last edited: Nov 23, 2019
  8. adamus007p

    adamus007p Member

    254
    17
    18
    Feb 8, 2019
    Ratings:
    +28
    Local Time:
    2:17 PM
    Is there anything else that I should to check?
     
  9. eva2000

    eva2000 Administrator Staff Member

    46,238
    10,510
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +16,293
    Local Time:
    11:17 PM
    Nginx 1.19.x
    MariaDB 5.5/10.x
    Read up on how to check for MySQL slow queries etc. I wouldn't be posting any of it on public forum as it exposes MySQL data/info to the internet so if unsure, hire someone to look into the issue as only a person with access to the server and the know-how, would be able to get to the bottom of the issue(s).
     
  10. adamus007p

    adamus007p Member

    254
    17
    18
    Feb 8, 2019
    Ratings:
    +28
    Local Time:
    2:17 PM
    @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....
     
    Last edited: Nov 27, 2019
  11. eva2000

    eva2000 Administrator Staff Member

    46,238
    10,510
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +16,293
    Local Time:
    11:17 PM
    Nginx 1.19.x
    MariaDB 5.5/10.x
    no there isn't right now
     
  12. adamus007p

    adamus007p Member

    254
    17
    18
    Feb 8, 2019
    Ratings:
    +28
    Local Time:
    2:17 PM
    any other things could be then php?
     
  13. adamus007p

    adamus007p Member

    254
    17
    18
    Feb 8, 2019
    Ratings:
    +28
    Local Time:
    2:17 PM
    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/
     
    Last edited: Nov 30, 2019
  14. eva2000

    eva2000 Administrator Staff Member

    46,238
    10,510
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +16,293
    Local Time:
    11:17 PM
    Nginx 1.19.x
    MariaDB 5.5/10.x
    interesting troubleshooting (y)
     
  15. dandumit

    dandumit New Member

    19
    1
    3
    Jan 9, 2021
    Ratings:
    +1
    Local Time:
    3:17 PM
    @adamus007p could you please tell me what version of php are you using ? I am struggling to install presta and I am stucked on fileinfo extension. I have tried to update/ compile with different versions but no luck
     
  16. adamus007p

    adamus007p Member

    254
    17
    18
    Feb 8, 2019
    Ratings:
    +28
    Local Time:
    2:17 PM
    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.
     
  17. dandumit

    dandumit New Member

    19
    1
    3
    Jan 9, 2021
    Ratings:
    +1
    Local Time:
    3:17 PM
    @adamus007p do you still run production prestashop on nginx ?

    I am almost there but I still have some issues on temporary files permissions. IT seems that some files in chache folder are created without any permissions. did you hit this issue as well ?

    Do you know , that company have modified umask settings for nginx user ?