Magento 1.x - search fail requires mysql restart to work again?

[mario_z]

Hi everyone,

I'm looking after a Magento site for a client and it has some odd behaviour, the site will work fine for a few days and then suddenly if you attempt to search on the site or load a product, it wont respond.

The only fix? -> restart mysql, once mysql has restarted, the site responds perfectly fine once again.

This happened again today and like last time I ran mysqltuner to see if there was any bottlenecks on the mysql system , but it all seems fine.

 >>  MySQLTuner 1.7.10 - Major Hayden <major@mhtx.net>

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 6d 1h 51m 45s (27M q [53.250 qps], 247K conn, TX: 62G, RX: 6G)
[--] Reads / Writes: 93% / 7%
[--] Binary logging is disabled
[--] Physical Memory     : 9.7G
[--] Max MySQL memory    : 4.6G
[--] Other process memory: 2.9G
[--] Total buffers: 4.0G global + 1.9M per thread (300 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 4.1G (42.51% of installed RAM)
[OK] Maximum possible memory usage: 4.6G (47.35% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/27M)
[OK] Highest usage of available connections: 14% (44/300)
[OK] Aborted connections: 0.86%  (2129/247102)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 22M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (327 temp sorts / 2M sorts)
[!!] Joins performed without indexes: 236245
[OK] Temporary tables created on disk: 6% (413K on disk / 6M total)
[OK] Thread cache hit rate: 99% (176 created / 247K connections)
[!!] Table cache hit rate: 0% (4K open / 731K opened)
[OK] Open file limit used: 10% (1K/10K)
[OK] Table locks acquired immediately: 99% (8M immediate / 8M locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[OK] Key buffer used: 93.6% (7M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/131.4M
[OK] Read Key buffer hit rate: 99.9% (38M cached / 44K reads)
[!!] Write Key buffer hit rate: 32.6% (3M cached / 1M writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 4.0G/3.5G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 512.0M * 2/4.0G should be equal 25%
[OK] InnoDB buffer pool instances: 4
[--] Number of InnoDB Buffer Pool Chunk : 32 for 4 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (5518987578 hits/ 5519066114 total)
[OK] InnoDB Write log efficiency: 98.10% (28887057 hits/ 29447444 total)
[OK] InnoDB log waits: 0.00% (0 waits / 560387 writes)

General recommendations:
    Control warning line(s) into /var/log/mysqld.log file
    Control error line(s) into /var/log/mysqld.log file
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Adjust your join queries to always utilize indexes
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: 
    This is MyISAM only table_cache scalability problem, InnoDB not affected.
    See more details here: https://bugs.mysql.com/bug.php?id=49177
    This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
    Beware that open_files_limit (10000) variable
    should be greater than table_open_cache (4845)
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 6M, or use smaller result sets)
    join_buffer_size (> 1.0M, or always use indexes with JOINs)
    table_open_cache (> 4845)

my.cnf looks like

performance-schema=ON
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
default-storage-engine=MyISAM
max_allowed_packet=268435456
open_files_limit=10000
max_connections=300
wait_timeout = 1800
table_open_cache = 4900
join_buffer_size = 1M
query_cache_limit = 10M
innodb_file_per_table=ON
innodb_buffer_pool_size = 4G
innodb_log_file_size = 512M
innodb_buffer_pool_instances=4

I'm at a complete loss as to why mysql seems to be choking or stopping to respond.

I did think it was due to magento's cron jobs that run, the server has some other low traffic magento sites also with crons running, so we disabled those and it seemed to provide a couple more days before we saw the same behaviour.

System specs :

6 cores / 10gb ram 
Cpanel server, running Apache 2.4.34 + PHP-FPM

It's really becoming concerning now, the alerts we receive from ConfigServer Firewall on the box, grow quickly which is most likely due to not being able to get a response from mysql

 User:ovenpart PID:59267 PPID:42841 Run Time:1832(secs) Memory:908364(kb) RSS:43296(kb) exe:/opt/cpanel/ea-php56/root/usr/sbin/php-fpm cmd:php-fpm: pool ovenpartsaustralia_com_au                 
User:ovenpart PID:59822 PPID:42841 Run Time:1306(secs) Memory:984396(kb) RSS:59284(kb) exe:/opt/cpanel/ea-php56/root/usr/sbin/php-fpm cmd:php-fpm: pool ovenpartsaustralia_com_au                 
User:ovenpart PID:59824 PPID:42841 Run Time:1304(secs) Memory:908304(kb) RSS:46704(kb) exe:/opt/cpanel/ea-php56/root/usr/sbin/php-fpm cmd:php-fpm: pool ovenpartsaustralia_com_au                 

the php-fpm error log shows this : 

#0 /home/ovenpart/public_html/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /home/ovenpart/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 /home/ovenpart/public_html/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 /home/ovenpart/public_html/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#4 /home/ovenpart/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT `core_se...', Array)
#5 /home/ovenpart/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('SELECT `core_se...', Array)
#6 /home/ovenpart/public_html/lib/Zend/Db/Adapter/Abstract.php(825): Varien_Db_Adapter_Pdo_Mysql->query(Object(Varien_Db_Select), Arr in /home/ovenpart/public_html/lib/Zend/Db/Statement/Pdo.php on line 234
[06-Sep-2018 05:37:38 UTC] PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2006 MySQL server has gone away' in /home/ovenpart/public_html/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:

However it says mysql has gone away, but mysqltuner is able to connect and I can manually connect via root to the mysql server without problem.

Note it is a cpanel host, just for this and 3 other sites for this client, all services are run on the same system.

Really hoping someone may be able to assist here.

[JLHC]

It looks like your MySQL and server as a whole is badly configured and it sn't properly tuned or optimized for Magento. For example, Magento needs MySQL Query Cache to be enabled but it doesn't seem to be the case. 

Is this a managed server? If it is and the my.cnf is tuned by your hosting provider, I would highly recommend that you consider using a Magento focused company instead. 

[mario_z]

yes it is a managed server.

Currently the settings are a combination of some earlier 'magento recommendations' and also adhering to recommendations from mysqltuner.pl

Per the query cache, thought the query cache conflicts with innodb items, thats why mysqltuner recommends it to be disabled as well. 

In light of more reading/review, I've modified the mysql config as follows :

performance-schema=ON
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
default-storage-engine=MyISAM
max_allowed_packet=268435456
open_files_limit=10000
max_connections=300
wait_timeout = 1800
table_open_cache = 4900
join_buffer_size = 4M
query_cache_size = 128M
query_cache_limit = 4M
query_cache_type = 1
innodb_file_per_table=ON
innodb_buffer_pool_size = 4G
innodb_log_file_size = 512M
innodb_buffer_pool_instances=4

In reference to finding someone who has experience in this, also attempted that and found nothing but people wanting to resell hosting rather than offer advice, so I thought I'd try my luck on the forum instead.

Further to the actual original issue, I'm trying to read up and understand how some of these modifications to the mysql config would result in the behaviour experienced with the 'mysql gone away' , I have systems with higher loads/less resources and mysql has no issue, but magento is a special beast when it comes to resource management it seems. 

[JLHC]

Yes Magento has different MySQL requirements compared to other applications which is why Magento focused providers have servers that are tuned specifically for it.

Having the appropriate MySQL Query Cache size and also a high amount of InnoDB Buffer Pool size should help a lot. You can start with the current config that you have and have it tuned further.

I do still need to say that this shouldn't be your job if you are with a Managed hosting provider as they should be the one who tune the server for you. Unfortunately the average hosting providers are not very familiar with Magento so they may not know how to tune their servers properly.

[mario_z]

100% agree with that. 

Right now I'm fine with tweaking / tuning myself, hence me reaching out for advice from others who have gone down this path. 

Still it perplexes me as to the reasoning behind the 'mysql gone away' message when mysql was responding to other connection requests but not this sites. I'd be happier if having 'mysql gone away' errors scrolling past and not being able to log in as root into mysql, that would generally mean max connection count hit, but in this instance it was nowhere near it.

[mario_z]

 Any one have any ideas here?

The system seems to run fine, but still at times its like php-fpm gets overloaded or the connection to mysql perhaps.

The problem, Magento operates fine, until someone 'searches' for something in the search box, the system just sits there continually loading, normally the search result is within 1-2 seconds.

Navigating the site is fine, so its querying cache or mysql without issue but the search function wont respond. Until I restart mysql.