tekguru Posted June 12, 2015 Posted June 12, 2015 Okay firstly we run on a hosted server with not a lot of resource, no guaranteed CPU and 1.5Gb RAM. With IPS 3.4.7 we ran fine with no CPU overhead at all. Since uplifting to IPS4 we're running in burst mode most of the time and our host is recommending we move to a higher spec hosting plan which we can't afford and which will likely close us down.So far to reduce loads we've:Turned block refreshes from 5 to 7 minutesTurned off all blocks we can manage withoutTurned on Guest page caching 30 secondsEnsured we're using GZIP on the serverMod_defelate is being utilisedMod_expires is onBought a low resource using themeDisabled the Calendar module as we don't need it.Tried Memcached / Xcache (but as we're on a low spec hosting plan this works in reverse and half kills the server, page loads go to 30 seconds plus)Greatly regretting uplifting to IPS4 due to the greater resources it seems to need.We are getting GTMetrix scores of 96/91 so all looks good apart from times when we really slow down (assume background tasks running), and the CPU maxes out.Anybody any tips on what (if anything) further can be done?
action-reaction Posted June 12, 2015 Posted June 12, 2015 But what about the time to display a page with this 4.0 version ?Is it longer than before ? On my last host (hostgator), vey often I waited 10 sec to see something on the screen... It's long... nothing nothing nothing and hop ! Everything in one seconde...
tekguru Posted June 12, 2015 Author Posted June 12, 2015 It takes a little longer to open a page, but take a lot more server resources to do it. Which is what this thread is about.
RevengeFNF Posted June 12, 2015 Posted June 12, 2015 @tekguru can you run mysqltuner and post here the output?
tekguru Posted June 12, 2015 Author Posted June 12, 2015 Thanks I've asked my hosts to run and provide the output.
tekguru Posted June 12, 2015 Author Posted June 12, 2015 Here you go:>> MySQLTuner 1.4.0 - Major Hayden <major@mhtx.net>>> Bug reports, feature requests, and downloads at http://mysqltuner.com/>> Run with '--help' for additional options and output filtering[OK] Currently running supported MySQL version 5.6.23[OK] Operating on 64-bit architecture-------- Storage Engine Statistics -------------------------------------------[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM[--] Data in MyISAM tables: 497M (Tables: 185)[--] Data in InnoDB tables: 41M (Tables: 73)[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)[!!] Total fragmented tables: 42-------- Security Recommendations -------------------------------------------[OK] All database users have passwords assigned-------- Performance Metrics -------------------------------------------------[--] Up for: 4d 23h 59m 35s (3M q [7.156 qps], 158K conn, TX: 137B, RX: 5B)[--] Reads / Writes: 79% / 21%[--] Total buffers: 52.0M global + 1.1M per thread (200 max threads)[OK] Maximum possible memory usage: 277.0M (18% of installed RAM)[OK] Slow queries: 0% (71/3M)[OK] Highest usage of available connections: 31% (63/200)[OK] Key buffer size / total MyISAM indexes: 8.0M/63.9M[OK] Key buffer hit rate: 99.9% (155M cached / 203K reads)[OK] Query cache efficiency: 56.6% (1M cached / 2M selects)[!!] Query cache prunes per day: 88405[OK] Sorts requiring temporary tables: 0% (101 temp sorts / 55K sorts)[OK] Temporary tables created on disk: 20% (3K on disk / 18K total)[OK] Thread cache hit rate: 99% (1K created / 158K connections)[!!] Table cache hit rate: 2% (256 open / 10K opened)[OK] Open file limit used: 5% (273/5K)[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)[!!] InnoDB buffer pool / data size: 8.0M/41.8M[OK] InnoDB log waits: 0-------- Recommendations -----------------------------------------------------General recommendations:Run OPTIMIZE TABLE to defragment tables for better performanceIncrease table_open_cache gradually to avoid file descriptor limitsRead this before increasing table_open_cache over 64: http://bit.ly/1mi7c4CVariables to adjust:query_cache_size (> 4M)table_open_cache (> 256)innodb_buffer_pool_size (>= 41M)Any recommendations appreciated.
tekguru Posted June 12, 2015 Author Posted June 12, 2015 I got my host to do the modifications made in the script and we now get:>> MySQLTuner 1.4.0 - Major Hayden <major@mhtx.net>>> Bug reports, feature requests, and downloads at http://mysqltuner.com/>> Run with '--help' for additional options and output filtering[OK] Currently running supported MySQL version 5.6.23[OK] Operating on 64-bit architecture-------- Storage Engine Statistics -------------------------------------------[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM[--] Data in MyISAM tables: 511M (Tables: 185)[--] Data in InnoDB tables: 40M (Tables: 73)[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)[!!] Total fragmented tables: 43-------- Security Recommendations -------------------------------------------[OK] All database users have passwords assigned-------- Performance Metrics -------------------------------------------------[--] Up for: 5s (163 q [32.600 qps], 13 conn, TX: 7M, RX: 41K)[--] Reads / Writes: 87% / 13%[--] Total buffers: 155.0M global + 1.1M per thread (200 max threads)[OK] Maximum possible memory usage: 380.0M (24% of installed RAM)[OK] Slow queries: 0% (0/163)[OK] Highest usage of available connections: 1% (3/200)[OK] Key buffer size / total MyISAM indexes: 65.0M/64.0M[OK] Key buffer hit rate: 95.1% (3K cached / 159 reads)[OK] Query cache efficiency: 53.4% (71 cached / 133 selects)[OK] Query cache prunes per day: 0[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 8 sorts)[OK] Temporary tables created on disk: 0% (0 on disk / 2 total)[OK] Thread cache hit rate: 76% (3 created / 13 connections)[OK] Table cache hit rate: 90% (69 open / 76 opened)[OK] Open file limit used: 0% (26/5K)[OK] Table locks acquired immediately: 100% (141 immediate / 141 locks)[OK] InnoDB buffer pool / data size: 50.0M/40.8M[OK] InnoDB log waits: 0-------- Recommendations -----------------------------------------------------General recommendations:Run OPTIMIZE TABLE to defragment tables for better performanceMySQL started within last 24 hours - recommendations may be inaccurate
RevengeFNF Posted June 12, 2015 Posted June 12, 2015 Up for: 5s (163 q [32.600 qps], 13 conn, TX: 7M, RX: 41K)You need to wait at least 24 hours.
tekguru Posted June 12, 2015 Author Posted June 12, 2015 I did ask them to confirm they had done the table optimisation though as that was still listed.
NathanR Posted June 26, 2015 Posted June 26, 2015 @RevengeFNFHow do you run mysqltuner? Is this something you have to install on your CENTOS box? I'd like to give it a go as I'm having page loading issues as well on a full dedi setup.
RevengeFNF Posted June 26, 2015 Posted June 26, 2015 Just download it: wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.plThen run it:perl mysqltuner.pl
NathanR Posted June 26, 2015 Posted June 26, 2015 >> MySQLTuner 1.4.4 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering [OK] Currently running supported MySQL version 5.6.23 [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM [--] Data in MyISAM tables: 3G (Tables: 1560) [--] Data in InnoDB tables: 123M (Tables: 1311) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52) [!!] Total fragmented tables: 208 -------- Security Recommendations ------------------------------------------- [OK] There is no anonymous account in all database users [OK] All database users have passwords assigned [!!] There is not basic password file list ! -------- Performance Metrics ------------------------------------------------- [--] Up for: 3d 15h 59m 51s (33M q [106.051 qps], 974K conn, TX: 235B, RX: 38B) [--] Reads / Writes: 95% / 5% [--] Total buffers: 225.0M global + 1.1M per thread (151 max threads) [OK] Maximum possible memory usage: 394.9M (5% of installed RAM) [OK] Slow queries: 0% (2/33M) [OK] Highest usage of available connections: 33% (51/151) [OK] Key buffer size / total MyISAM indexes: 64.0M/716.8M [OK] Key buffer hit rate: 99.8% (1B cached / 4M reads) [!!] Query cache is disabled [OK] Sorts requiring temporary tables: 2% (30K temp sorts / 1M sorts) [!!] Joins performed without indexes: 120619 [OK] Temporary tables created on disk: 23% (485K on disk / 2M total) [OK] Thread cache hit rate: 99% (652 created / 974K connections) [!!] Table cache hit rate: 0% (1K open / 366K opened) [OK] Open file limit used: 8% (847/10K) [OK] Table locks acquired immediately: 99% (32M immediate / 32M locks) -------- InnoDB Metrics ----------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB BufferPool Size :128.0M [--] InnoDB BufferPool Inst :8 [OK] InnoDB buffer pool / data size: 128.0M/123.4M [!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(=1). [!!] InnoDB log waits: 8 -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance 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: http://bit.ly/1mi7c4C Variables to adjust: query_cache_type (=1) join_buffer_size (> 256.0K, or always use indexes with joins) table_open_cache (> 1024) innodb_buffer_pool_instances (=1) innodb_log_buffer_size (>= 8M)
RevengeFNF Posted June 26, 2015 Posted June 26, 2015 Are you able to convert your Myisam tables to innodb?
NathanR Posted June 26, 2015 Posted June 26, 2015 I'm not sure, is that a difficult process? Should I ask my host to run the other recommended changes first and check performance?
RevengeFNF Posted June 26, 2015 Posted June 26, 2015 From what im seeing, IPS4 works a lot better with Innodb than Myisam.Probably you are having table locks when the pages are slow loading. In Innodb you don't have that problem, because it only do row locks.
NathanR Posted June 26, 2015 Posted June 26, 2015 Ok, I'll look into how to convert it.As for the other optimizations is suggests, should I have my host apply those or are they generic?
AutoItScript Posted June 26, 2015 Posted June 26, 2015 You have the query cache disabled as well, that's going to be a big boost on its ownquery_cache_type=1 Host needs to do it in my.cnf
RevengeFNF Posted June 26, 2015 Posted June 26, 2015 Ok, I'll look into how to convert it.As for the other optimizations is suggests, should I have my host apply those or are they generic?You can enable query cache. But i wouldn't do much without first convert to Innodb. Its an easy process.
tekguru Posted June 26, 2015 Author Posted June 26, 2015 Guys based on my original:[--] Data in MyISAM tables: 511M (Tables: 185)[--] Data in InnoDB tables: 40M (Tables: 73)Is it worth me getting my host to convert my tables to InnoDB as well? Is it completely non-destructive / safe? Asking as we're on holiday way away from home and main resources.....
RevengeFNF Posted June 26, 2015 Posted June 26, 2015 Of course its safe. If your host do it for you, the better.
tekguru Posted June 28, 2015 Author Posted June 28, 2015 Having discussed with my host they have advised that we convert the large tables, and as such the following have been converted to InnoDB:core_theme_resources 22.55mbforums_posts 53.18mbcore_cache 1457.45mbThat core cache seems overly large though!
Owdy Posted June 28, 2015 Posted June 28, 2015 eThat core cache seems overly large though!r u running 4.0.8.1?
Recommended Posts
Archived
This topic is now archived and is closed to further replies.