Mysql, Help & Optimisation (or move host?)

[craigf136]

Hey Folk's

I'm having issues with MYSQL gone away errors with IPS4 - these errors are abundant when running the background processes manually (log I attached for IPS support was 19MB for one day, after running background processes manually). IPS say it's a setup issues
I don't disbelieve them, however, I'm not having any downtime just slow loading on forum, gallery, downloads, I suspect it's to do with max connections and or timeout on queries? I also receive the following

As well as "Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'"

I was not having this issue on 3.4.7

We are with Hostgator at the moment we have CENTOS 5.11 x86_64, 2 cores, 2GB ram, mysql 5.6.23, PHP 5.4.29, memcached installed, cached pages for guests is disabled in ACP.

I'm not saying optimising isn't required as it is, I have included results from mysqltuner and my.cnf settings as they are, but I would need suggestions as to what would be best settings to optimise mysql / server for better performance and increase the overall speed. For example on IPS, the page loads are pretty quick. I'm considering moving to www.knownhosting.com for the following package.

https://www.knownhost.com/managed-ssd-vps-packages.html

SSD-4

Fully Managed & Optimized

5.25GB	Guaranteed RAM
80GB	RAID10 SSD Storage
Equal Share	24 CPU Cores
10000GB	Premium Bandwidth

Compare SSD VPS Plans

$90	$264	$1026
Monthly	Quarterly	Yearly

Central (TX) datacenter only

All Specs Order Now

• Unlimited domains
• Full root access
• 24/7/365 support
• Fully managed
• cPanel, DirectAdmin, or Plesk
• Free backups
• 1-click installers available

The open ticket I have basically says the following

The problem in your case seems to be that the sql server loses connection resulting in an "mysql has gone away" message. This is only shown when software cannot see the database server. Clicking on "try again" results in this continuing as normal. It does seem to be happening quite often though. Do you have any other points where you seem to loose connection on your site?

To which we don't, we just seem to have slow loading of the forum, gallery, downloads application, the last response receives was.

While I can't check every single entry, as there are 87,000 lines in that file, I have checked a lot of them, and every one is a "MySQL server has gone away" error. This is a server issue, I'm afraid, and something with which we unfortunately can't assist, as it's outside the scope of our support. The SQL server "going away" is always caused by its configuration or load. It may need to be tuned (mysqltuner.pl is suggested) or otherwise optimized.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 6h 40m 50s (2M q [19.575 qps], 44K conn, TX: 59B, RX: 9B)
[--] Reads / Writes: 67% / 33%
[--] Total buffers: 208.0M global + 4.2M per thread (100 max threads)
[OK] Maximum possible memory usage: 633.0M (30% of installed RAM)
[OK] Slow queries: 0% (26/2M)
[OK] Highest usage of available connections: 23% (23/100)
[OK] Key buffer size / total MyISAM indexes: 16.0M/1.6G
[OK] Key buffer hit rate: 99.8% (136M cached / 327K reads)
[OK] Query cache efficiency: 73.5% (1M cached / 1M selects)
[!!] Query cache prunes per day: 40967
[OK] Sorts requiring temporary tables: 0% (142 temp sorts / 40K sorts)
[!!] Joins performed without indexes: 19214
[!!] Temporary tables created on disk: 52% (36K on disk / 68K total)
[OK] Thread cache hit rate: 99% (40 created / 44K connections)
[!!] Table cache hit rate: 0% (75 open / 38K opened)
[OK] Open file limit used: 1% (140/14K)
[OK] Table locks acquired immediately: 99% (805K immediate / 806K locks)
[OK] InnoDB buffer pool / data size: 128.0M/56.7M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    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 without LIMIT clauses
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
    query_cache_size (> 16M)
    join_buffer_size (> 1.0M, or always use indexes with joins)
    tmp_table_size (> 32M)
    max_heap_table_size (> 32M)
    table_open_cache (> 2000)

tmp_table_size = 32M

max_heap_table_size = 32M

query_cache_limit=1M

query_cache_size=16M ## 32MB for every 1GB of RAM

query_cache_type=1

max_connections=100

collation_server=utf8_unicode_ci

character_set_server=utf8

# delayed_insert_timeout=40

interactive_timeout=10

wait_timeout=30

connect_timeout=20

thread_cache_size=64

key_buffer=16M ## 32MB for every 1GB of RAM

join_buffer=1M

max_connect_errors=20

max_allowed_packet=268435456

#table_cache=1024

#record_buffer=1M

sort_buffer_size=1M ## 1MB for every 1GB of RAM

read_buffer_size=1M ## 1MB for every 1GB of RAM

read_rnd_buffer_size=1M  ## 1MB for every 1GB of RAM

thread_concurrency=2 ## Number of CPUs x 2

myisam_sort_buffer_size=16M
open_files_limit=14000
innodb_buffer_pool_size=134217728
default-storage-engine=MyISAM
innodb_file_per_table=1

We have issues with "too many connections" but again not downtime

Fri, 24 Apr 2015 03:48:03 +0000 (Severity: 0)
85.113.55.152 - domain
Too many connections
 | File                                                                       | Function                                                                      | Line No.          |
 |----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------|
 | /system/Db/Db.php                                                          | [IPS\Db\_Exception].__construct                                               | 121               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Data/Store/Database.php                                            | [IPS\_Db].i                                                                   | 80                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Data/Store/Database.php                                            | [IPS\Data\Store\_Database].loadIntoMemory                                     | 96                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Data/Store/Database.php                                            | [IPS\Data\Store\_Database].get                                                | 143               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Data/AbstractData.php                                              | [IPS\Data\Store\_Database].exists                                             | 111               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Data/Store.php                                                     | [IPS\Data\_AbstractData].__isset                                              | 159               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Data/Store.php                                                     | [IPS\Data\_Store].__isset                                                     | 154               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Settings/Settings.php                                              | [IPS\Data\_Store].__isset                                                     | 121               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Settings/Settings.php                                              | [IPS\_Settings].loadFromDb                                                    | 88                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Theme/Theme.php                                                    | [IPS\_Settings].__get                                                         | 375               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Theme/Theme.php                                                    | [IPS\_Theme].designersModeEnabled                                             | 184               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Dispatcher/Standard.php                                            | [IPS\_Theme].i                                                                | 50                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /init.php(426) : eval()'d code                                             | [IPS\Dispatcher\_Standard].baseCss                                            | 17                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Dispatcher/Front.php                                               | [IPS\Dispatcher\featuredcontent_hook_includeJSandCSS].baseCss                 | 474               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Dispatcher/Front.php                                               | [IPS\Dispatcher\_Front].baseCss                                               | 51                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Dispatcher/Dispatcher.php                                          | [IPS\Dispatcher\_Front].init                                                  | 86                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /index.php                                                                 | [IPS\_Dispatcher].i                                                           | 13                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
------------------------------------------------------------------------
Fri, 24 Apr 2015 03:48:03 +0000 (Severity: 0)
85.113.55.152 - domain/index.php?/files/
Too many connections
 | File                                                                       | Function                                                                      | Line No.          |
 |----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------|
 | /system/Db/Db.php                                                          | [IPS\Db\_Exception].__construct                                               | 121               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Data/Store/Database.php                                            | [IPS\_Db].i                                                                   | 80                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Data/Store/Database.php                                            | [IPS\Data\Store\_Database].loadIntoMemory                                     | 96                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Data/Store/Database.php                                            | [IPS\Data\Store\_Database].get                                                | 143               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Data/AbstractData.php                                              | [IPS\Data\Store\_Database].exists                                             | 111               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Data/Store.php                                                     | [IPS\Data\_AbstractData].__isset                                              | 159               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Data/Store.php                                                     | [IPS\Data\_Store].__isset                                                     | 154               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Settings/Settings.php                                              | [IPS\Data\_Store].__isset                                                     | 121               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Settings/Settings.php                                              | [IPS\_Settings].loadFromDb                                                    | 88                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Theme/Theme.php                                                    | [IPS\_Settings].__get                                                         | 375               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Theme/Theme.php                                                    | [IPS\_Theme].designersModeEnabled                                             | 184               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Dispatcher/Standard.php                                            | [IPS\_Theme].i                                                                | 50                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /init.php(426) : eval()'d code                                             | [IPS\Dispatcher\_Standard].baseCss                                            | 17                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Dispatcher/Front.php                                               | [IPS\Dispatcher\featuredcontent_hook_includeJSandCSS].baseCss                 | 474               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Dispatcher/Front.php                                               | [IPS\Dispatcher\_Front].baseCss                                               | 51                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Dispatcher/Dispatcher.php                                          | [IPS\Dispatcher\_Front].init                                                  | 86                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /index.php                                                                 | [IPS\_Dispatcher].i                                                           | 13                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
------------------------------------------------------------------------
Fri, 24 Apr 2015 03:48:04 +0000 (Severity: 0)
85.113.55.152 - domain/index.php?/staff/
Too many connections
 | File                                                                       | Function                                                                      | Line No.          |
 |----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------|
 | /system/Db/Db.php                                                          | [IPS\Db\_Exception].__construct                                               | 121               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Data/Store/Database.php                                            | [IPS\_Db].i                                                                   | 80                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Data/Store/Database.php                                            | [IPS\Data\Store\_Database].loadIntoMemory                                     | 96                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Data/Store/Database.php                                            | [IPS\Data\Store\_Database].get                                                | 143               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Data/AbstractData.php                                              | [IPS\Data\Store\_Database].exists                                             | 111               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Data/Store.php                                                     | [IPS\Data\_AbstractData].__isset                                              | 159               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Data/Store.php                                                     | [IPS\Data\_Store].__isset                                                     | 154               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Settings/Settings.php                                              | [IPS\Data\_Store].__isset                                                     | 121               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Settings/Settings.php                                              | [IPS\_Settings].loadFromDb                                                    | 88                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Theme/Theme.php                                                    | [IPS\_Settings].__get                                                         | 375               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Theme/Theme.php                                                    | [IPS\_Theme].designersModeEnabled                                             | 184               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Dispatcher/Standard.php                                            | [IPS\_Theme].i                                                                | 50                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /init.php(426) : eval()'d code                                             | [IPS\Dispatcher\_Standard].baseCss                                            | 17                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Dispatcher/Front.php                                               | [IPS\Dispatcher\featuredcontent_hook_includeJSandCSS].baseCss                 | 474               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Dispatcher/Front.php                                               | [IPS\Dispatcher\_Front].baseCss                                               | 51                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Dispatcher/Dispatcher.php                                          | [IPS\Dispatcher\_Front].init                                                  | 86                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /index.php                                                                 | [IPS\_Dispatcher].i                                                           | 13                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
------------------------------------------------------------------------
Fri, 24 Apr 2015 03:48:04 +0000 (Severity: 0)
85.113.55.152 - domain
Too many connections
 | File                                                                       | Function                                                                      | Line No.          |
 |----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------|
 | /system/Db/Db.php                                                          | [IPS\Db\_Exception].__construct                                               | 121               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Data/Store/Database.php                                            | [IPS\_Db].i                                                                   | 80                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Data/Store/Database.php                                            | [IPS\Data\Store\_Database].loadIntoMemory                                     | 96                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Data/Store/Database.php                                            | [IPS\Data\Store\_Database].get                                                | 143               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Data/AbstractData.php                                              | [IPS\Data\Store\_Database].exists                                             | 111               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Data/Store.php                                                     | [IPS\Data\_AbstractData].__isset                                              | 159               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Data/Store.php                                                     | [IPS\Data\_Store].__isset                                                     | 154               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Settings/Settings.php                                              | [IPS\Data\_Store].__isset                                                     | 121               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Settings/Settings.php                                              | [IPS\_Settings].loadFromDb                                                    | 88                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Theme/Theme.php                                                    | [IPS\_Settings].__get                                                         | 375               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Theme/Theme.php                                                    | [IPS\_Theme].designersModeEnabled                                             | 184               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Dispatcher/Standard.php                                            | [IPS\_Theme].i                                                                | 50                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /init.php(426) : eval()'d code                                             | [IPS\Dispatcher\_Standard].baseCss                                            | 17                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Dispatcher/Front.php                                               | [IPS\Dispatcher\featuredcontent_hook_includeJSandCSS].baseCss                 | 474               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Dispatcher/Front.php                                               | [IPS\Dispatcher\_Front].baseCss                                               | 51                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Dispatcher/Dispatcher.php                                          | [IPS\Dispatcher\_Front].init                                                  | 86                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /index.php                                                                 | [IPS\_Dispatcher].i                                                           | 13                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
------------------------------------------------------------------------
Fri, 24 Apr 2015 03:48:03 +0000 (Severity: 0)
85.113.55.152 - domain/index.php?/contact/
Too many connections
 | File                                                                       | Function                                                                      | Line No.          |
 |----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------|
 | /system/Db/Db.php                                                          | [IPS\Db\_Exception].__construct                                               | 121               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Data/Store/Database.php                                            | [IPS\_Db].i                                                                   | 80                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Data/Store/Database.php                                            | [IPS\Data\Store\_Database].loadIntoMemory                                     | 96                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Data/Store/Database.php                                            | [IPS\Data\Store\_Database].get                                                | 143               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Data/AbstractData.php                                              | [IPS\Data\Store\_Database].exists                                             | 111               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Data/Store.php                                                     | [IPS\Data\_AbstractData].__isset                                              | 159               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Data/Store.php                                                     | [IPS\Data\_Store].__isset                                                     | 154               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Settings/Settings.php                                              | [IPS\Data\_Store].__isset                                                     | 121               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Settings/Settings.php                                              | [IPS\_Settings].loadFromDb                                                    | 88                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Theme/Theme.php                                                    | [IPS\_Settings].__get                                                         | 375               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Theme/Theme.php                                                    | [IPS\_Theme].designersModeEnabled                                             | 184               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Dispatcher/Standard.php                                            | [IPS\_Theme].i                                                                | 50                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /init.php(426) : eval()'d code                                             | [IPS\Dispatcher\_Standard].baseCss                                            | 17                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Dispatcher/Front.php                                               | [IPS\Dispatcher\featuredcontent_hook_includeJSandCSS].baseCss                 | 474               |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Dispatcher/Front.php                                               | [IPS\Dispatcher\_Front].baseCss                                               | 51                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /system/Dispatcher/Dispatcher.php                                          | [IPS\Dispatcher\_Front].init                                                  | 86                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'
 | /index.php                                                                 | [IPS\_Dispatcher].i                                                           | 13                |
 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'

 

[Canal World]

I cant say I have gone into the details of your post, but we spent two weeks banging our head against a brick wall of online technical help and 'your database is big' 'talk to your software provider' with our sire before moving to a host that specialised in forums and eCommerce can I can safely say I wont be looking back.

Rang them today at 4:46 to cancel the service now we have moved, and got an automated message suggesting I wing back between 8 and 5  ugghhh!!! Done.

 

Daniel