Database query cache linit reached - cron script to clear

[howey]

Hi

I don't know if this is the right board to post this in, but I need some help with implementing a php script to run as a cron job. My site periodically fails. I became aware of this by using situptime to monitor the site. Most times when I got a failure notice it had cleared when I came to check. On a couple of occasions the error message Database Connection Failure 1040.

I have queried my host and have been advised I need to run a cron job to clear the cached queries. I can set up a scheduled task through the hosting panel, but have no idea on the script to use.

Secondly, is there a potential fault in the way I have set the site up/with CMS MS that's causing this?

Any help or direction gratefully received.

[calguy1000]

Please provide system info for every post.

Some quick googling on error 1040 describes a completely different problem. So I think you may be barking up the wrong tree.

[howey]

Hi

Thanks, I have just Googled and it does seem to be a different problem.

I'll get back on to my hosts as that would seem to be where the problem lies.

Regards

[howey]

Hi

I've had this response from the host:

Too many connections are generated by hanging queries.

Normally these are cached until being executed, which can sometimes not occur.

Hence why I asked about the possibilty of creating a php script to clear connections via their connection ID as they appear in the process list.

I've had a look at the phpmyadmin status, once the site had cleared again.

Code: Select all

Traffic 	ø per hour
Received	59.4 GiB	491.2 MiB
Sent	1.6 TiB	12.8 GiB
Total	1.6 TiB	13.3 GiB
Connections	ø per hour	%
max. concurrent connections	152	---	---
Failed attempts	224.1 k	1,808.25	6.41%
Aborted	718	5.79	0.02%
Total	3,494 k	28.19 k	100.00%

I have included system information below, which needs updating by the look of it, although it is only one behind.

I dont know if its relevant as the code indicates a database cache problem not a site cache problem. But the last time it failed (this morning) I went on via ftp and cleared the site cache and c_templates. The site then came back on line. THis could have been a coincidence as the site does come and go. Another point seems to be most of the failures I am aware of occur over night GMT.

Still perpexed as this is way beyond my experience/knowledge.

----------------------------------------------
Cms Version: 2.1.5
Installed Modules:
AdminSearch: 1.0.2
CGBlog: 1.14.1
CGExtensions: 1.53.13
CGSimpleSmarty: 2.1.4
CGSmartImage: 1.21.5
CMSContentManager: 1.1.1
CMSMailer: 6.2.14
DesignManager: 1.1.1
FileManager: 1.5.2
Gallery: 2.2RC1
JQueryTools: 1.3.9
MicroTiny: 2.0.3
ModuleManager: 2.0.4
Navigator: 1.0.3
News: 2.50.5
Search: 1.50.2

Config Information:
php_memory_limit:
max_upload_size: 64000000
url_rewriting: mod_rewrite
page_extension:
query_var: page
auto_alias_content: true
locale:
set_names: true
timezone: Europe/London
permissive_smarty: false

Php Information:
phpversion: 5.5.38
md5_function: On (True)
json_function: On (True)
gd_version: 2
tempnam_function: On (True)
magic_quotes_runtime: Off (False)
E_ALL: 22519
E_STRICT: 0
E_DEPRECATED: 0
test_file_timedifference: No time difference found
test_db_timedifference: No time difference found
create_dir_and_file: 1
memory_limit: 128M
max_execution_time: 120
register_globals: Off (False)
output_buffering: 4096
disable_functions:
open_basedir:
test_remote_url: Success
file_uploads: On (True)
post_max_size: 64M
upload_max_filesize: 64M
session_save_path: /tmp (1777)
session_use_cookies: On (True)
xml_function: On (True)
xmlreader_class: On (True)
check_ini_set: On (True)
curl: On

Performance Information:
allow_browser_cache: Off (False)
browser_cache_expiry: 60
php_opcache: Off (False)
smarty_cache: Off (False)
smarty_compilecheck: Off (False)
smarty_cache_udt: Off (False)
auto_clear_cache_age: On (True)
Server Information:
Server Software: Apache/2.4.18 (Unix)
Server Api: cgi-fcgi
Server Os: Linux 2.6.32-042stab120.11 On x86_64
Server Db Type: MySQL (mysql)
Server Db Version: 10.1.24
Server Db Grants: Found a "GRANT ALL" statement that appears to be suitable

Permission Information:
tmp: /home/cluster-sites/910/i/infotec.co.uk/public_html/tmp (0755)
tmp_cache: /home/cluster-sites/910/i/infotec.co.uk/public_html/tmp/cache (0755)
templates_c: /home/cluster-sites/910/i/infotec.co.uk/public_html/tmp/templates_c (0755)
modules: /home/cluster-sites/910/i/infotec.co.uk/public_html/modules (0755)
uploads: /home/cluster-sites/910/i/infotec.co.uk/public_html/uploads (0755)
File Creation Mask (umask): /home/cluster-sites/910/i/infotec.co.uk/public_html/tmp/cache (0755)
config_file: 0444
----------------------------------------------

[calguy1000]

a: Are you using shared hosting?
b: Are you using persistent connections in your config.php ?

You are now 3 versions behind in CMSMS, and due to critical security vulnerabilities, we recommend you upgrade ASAP.

From my experience, the error you are experiencing is not in any way related to caching.

Too many database connection errors can be caused by many of things, but typically that error is caused by the number of incoming requests being processed at one time being greater than the number of database connections available (in your case 152). On shared hosting, you share those database connections with all of the applications on the server.

Persistent connections can be problematic in that the connection may not be released in some circumstances so that 'connection slot' can be lost for a long period of time.

[PinkElephant]

Hi,

It may be nothing but ...

Server Db Type: MySQL (mysql)

... shouldn't that be "MySQL (mysqli)"

via config.php;

Code: Select all

$config['dbms'] = 'mysqli';

?

[calguy1000]

It's not necessary (till you are on php7)... it's just a significant performance boost.