0

I am working on a magento store, though the magento seems to be working ok. Mysql server is throwing random errors like "No Such File or directory...." & "Mysql Has gone away..." & sometimes "The user has already reached the user limit.

Current Config:

Centos 6.7
nginx + APC + memcache
Max_Connection : 500
Max_User_Connection: 250

Please let me know what configuration should i change to resolve these issues.

Thanks

Thanks for your valuable time

@Muhammad: tweaks added

@MagenX : here's my my.cnf & mysqltuner's data

-------- Performance Metrics -------------------------------------------------
        [--] Up for: 10s (2K q [260.500 qps], 17 conn, TX: 9M, RX: 665K)
        [--] Reads / Writes: 95% / 5%
        [--] Binary logging is disabled
        [--] Total buffers: 4.3G global + 6.4M per thread (500 max threads)
        [!!] Maximum reached memory usage: 4.3G (115.75% of installed RAM)
        [!!] Maximum possible memory usage: 7.4G (198.45% of installed RAM)
        [OK] Slow queries: 0% (0/2K)
        [OK] Highest usage of available connections: 0% (4/500)
        [OK] Aborted connections: 0.00%  (0/17)
        [OK] Query cache efficiency: 66.9% (1K cached / 2K selects)
        [OK] Query cache prunes per day: 0
        [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 177 sorts)
        [!!] Joins performed without indexes: 1
        [!!] Temporary tables created on disk: 35% (31 on disk / 88 total)
        [OK] Thread cache hit rate: 76% (4 created / 17 connections)
        [OK] Table cache hit rate: 95% (153 open / 160 opened)
        [OK] Open file limit used: 0% (54/10K)
        [OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)

        -------- MyISAM Metrics -----------------------------------------------------
        [!!] Key buffer used: 18.5% (6M used / 33M cache)
        [OK] Key buffer size / total MyISAM indexes: 32.0M/268.7M
        [OK] Read Key buffer hit rate: 98.1% (4K cached / 90 reads)
        [!!] Write Key buffer hit rate: 90.1% (71 cached / 7 writes)

        -------- InnoDB Metrics -----------------------------------------------------
        [--] InnoDB is enabled.
        [OK] InnoDB buffer pool / data size: 4.0G/3.2G
        [!!] InnoDB buffer pool instances: 1
        [!!] InnoDB Used buffer: 0.93% (2431 used/ 262143 total)
        [OK] InnoDB Read buffer efficiency: 96.41% (64737 hits/ 67147 total)
        [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
        [OK] InnoDB log waits: 0.00% (0 waits / 14 writes)

        -------- AriaDB Metrics -----------------------------------------------------
        [--] AriaDB is disabled.

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

        -------- Recommendations -----------------------------------------------------
        General recommendations:
            Run OPTIMIZE TABLE to defragment tables for better performance
            MySQL started within last 24 hours - recommendations may be inaccurate
            Reduce your overall MySQL memory footprint for system stability
            Enable the slow query log to troubleshoot bad queries
            Adjust your join queries to always utilize indexes
            When making adjustments, make tmp_table_size/max_heap_table_size equal
            Reduce your SELECT DISTINCT queries which have no LIMIT clause
        Variables to adjust:
          *** MySQL's maximum memory usage is dangerously high ***
          *** Add RAM before increasing MySQL buffer variables ***
            join_buffer_size (> 128.0K, or always use indexes with joins)
            tmp_table_size (> 128M)
            max_heap_table_size (> 128M)
            innodb_buffer_pool_instances(=4)


    my.cnf is as follow:
    max_connections=500
    max_user_connections=250
    default-storage-engine=MyISAM
    innodb_thread_concurrency=2
    innodb_file_per_table=0
    innodb_buffer_pool_size=4GB
    wait_timeout            = 1800
    connect_timeout=120
    max_allowed_packet=268435456
    thread_cache_size       = 512
    sort_buffer_size        = 2M
    bulk_insert_buffer_size = 4M
    tmp_table_size          = 128M
    max_heap_table_size     = 128M
    key_buffer_size         = 32M
    myisam_sort_buffer_size = 128M
    read_buffer_size        = 2M
    read_rnd_buffer_size    = 2M
    query_cache_limit       = 2M
    query_cache_size        = 128M
    table_open_cache=400
    open_files_limit=10000

Apart from the homepage, rest of the pages are very slow.

  • please post your my.cnf file and output from mysqltuner.pl – MagenX Dec 28 '15 at 9:53
  • Can anybody help me with this .. – Mek Dec 30 '15 at 9:59
  • the problem is [OK] InnoDB buffer pool / data size: 4.0G/3.2G your data size is too big, probably you have to truncate log tables – MagenX Dec 30 '15 at 10:21
  • check in php shell/log.php -- status – MagenX Dec 30 '15 at 10:22
0

They are usually timeout errors for connection to SQL server or might be some kind of firewall issue on your server. Sometimes max_allowed_packetis being set to low. These all settings will have to be edited in server's my.cnf file by your hosting provider.

The connection to MySQL server is timing out because of a PHP script which is taking too long to execute, server overload, wretchedly huge requests or a wrong configuration.

Following settings for my.cnf might help:

max_allowed_packet = 16M
wait_timeout = 1800 
connect_timeout = 120

You will need to ask hosting provider to set the configurations and restart your MySQL server for these settings to load properly.

One more thing, Try bumping max_allowed_packet incrementally up, you may need 24M-32M for smooth working.

If you are using any shared hosting, it is a good indicator that your shared host is running out of resources and Magento timed out due to database growth and exceeded the shared hosting capabilities to run it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.