Looking for tips on optimizing board with pages

Asiaforum.no · April 6, 2015

Looking for tips on how i could improve loading times on my board.

www.asiaforum.no

Hyper-V server with 2x Intel Xeon 5520, 4GB ram. disks in RAID10.

~9.500 users

~600.000 posts

Running IPB 3.4.6.

The frontpage is the slowest to load, sometimes taking up to 10 secs or more to load.

Please advise.

RevengeFNF · April 6, 2015

Php, Mysql version? Apache or Nginx? Post also your mysqltuner output.

Asiaforum.no · April 6, 2015

mysql Ver 14.14 Distrib 5.5.30, for Linux (i686) using readline 5.1

PHP 5.3.25 (cli)

I upgraded plesk on the server yesterday to 11.5, i belive that gives me nginx but i havent turned it on yet.

mysqltuner output:

 >>  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.5.30-cll
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 7G (Tables: 3854)
[--] Data in InnoDB tables: 115M (Tables: 945)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 126K (Tables: 24)
[!!] Total fragmented tables: 1234

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 18h 10m 33s (5M q [90.556 qps], 73K conn, TX: 75B, RX: 792M)
[--] Reads / Writes: 17% / 83%
[--] Total buffers: 168.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 573.8M (14% of installed RAM)
[OK] Slow queries: 0% (58/5M)
[OK] Highest usage of available connections: 20% (31/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/2.4G
[OK] Key buffer hit rate: 97.6% (247M cached / 6M reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (15 temp sorts / 29K sorts)
[!!] Joins performed without indexes: 317
[!!] Temporary tables created on disk: 38% (23K on disk / 60K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 3% (400 open / 10K opened)
[OK] Open file limit used: 0% (483/65K)
[OK] Table locks acquired immediately: 99% (6M immediate / 6M locks)
[OK] InnoDB buffer pool / data size: 128.0M/115.8M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    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 without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
    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 (>= 8M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_open_cache (> 400)

CheersnGears · April 6, 2015

You have a 4.5 second Time to First Byte. That isn't really Content related, that is server related.

You have two files (compile.css and compile.js) that product a 404 error

RevengeFNF · April 6, 2015

You have many problems, starting with using a 32Bit OS. You have 4Gb of Ram and you can't use it all because of that. You need to change to a 64Bit distro.

Your board have 600k posts and your Database occupies 7Gb,. I guess you have many sites on that server?

Your Mysql is also not optimized. You are not using the benefits of buffering your mysql data, not using thread cache, high amount of temporary tables on disk, etc etc

Asiaforum.no · April 6, 2015

You have a 4.5 second Time to First Byte. That isn't really Content related, that is server related.
You have two files (compile.css and compile.js) that product a 404 error

Like in CPU? Memory? or due to 32bit OS?

What specs would be recommended for a server to host a community like this?

The other db's are mostly not in use, primarily old backups and a cloned testsite for IP.4. The actual boarddb is ~1,2GB

What steps could give performance improvements other then changing to 64bit OS?

RevengeFNF · April 6, 2015

The Specs you have can run a board like this. In this case you would need to backup your site and data and then install a new distro in your server(i recommend Centos 6.6), then install everything else and optimize it.

CheersnGears · April 6, 2015

Like in CPU? Memory? or due to 32bit OS?
What specs would be recommended for a server to host a community like this?
The other db's are mostly not in use, primarily old backups and a cloned testsite for IP.4. The actual boarddb is ~1,2GB
What steps could give performance improvements other then changing to 64bit OS?

Changing to 64bit OS is step 1.

RevengeFNF · April 6, 2015

Like in CPU? Memory? or due to 32bit OS?
What specs would be recommended for a server to host a community like this?
The other db's are mostly not in use, primarily old backups and a cloned testsite for IP.4. The actual boarddb is ~1,2GB
What steps could give performance improvements other then changing to 64bit OS?

This is not the best solution, but it will help.

Add this to your my.cnf

tmp_table_size=128M
max_heap_table_size=128M
thread_cache_size = 8
join_buffer_size =2M
read_buffer_size=128K
sort_buffer_size=256K
table_open_cache=1000
key_buffer_size =1G

Asiaforum.no · April 6, 2015

Thanks, i will get the OS upgraded to CentOS 6.6 - 64bit and go from there. Will get back here once i get it done to do further optimizing

Codehusker · April 6, 2015

Upgrading to a 64-bit OS will not resolve your issues, but is worth eventually doing. A 32-bit OS can address 4GB of space, which is the same as your total server memory. There might be some RAM not being utilized (there are things other than RAM that need that address space), but I think there are other factors at play. Your actual forum DB is 1.2GB, so it could reside entirely in memory in a 32-bit OS without problem.

First, can you explain more about your general set-up? Your first post said that your server is running Hyper-V. How many virtual machines are running on the server? If you just have the single Linux VM running on top of Hyper-V, then it is worth removing Hyper-V and then installing Linux directly to metal. This will obviously allow you to upgrade to 64-bit, but it also allows you to better utilize the processor on your server without virtualization overhead.

You said that it's the front page that takes the longest to load. I'm across the globe (>200ms away), and the rest of your site seems adequately speedy, but the home page is unbearably slow. I believe this is an issue with your IPC homepage. I work on servers for a living, not IPC, so I can't help here too much. You should be able to cache all of the blocks on your homepage. You might also want to enable debugging in the ACP and see how many DB queries are being run every time you load the home page. I remember a lot of people have had trouble with IPC generating an absurd number of queries.

Your database is very write heavy. It might be worth migrating your tables to InnoDB, which can improve write performance and decrease latency.

Asiaforum.no · June 7, 2015

Moved to a new server with 8GB RAM, 64bit CentOS 7.1, Plesk 12.0.

Running nginx, PHP 5.4.16, MySQL version 5.5.41-MariaDB

Mysqltuner output:

 >>  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.5.41-MariaDB
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 6G (Tables: 3626)
[--] Data in InnoDB tables: 107M (Tables: 742)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 24)
[!!] Total fragmented tables: 767

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 23h 33m 55s (2M q [27.558 qps], 71K conn, TX: 97B, RX: 571M)
[--] Reads / Writes: 70% / 30%
[--] Total buffers: 288.0M global + 2.8M per thread (151 max threads)
[OK] Maximum possible memory usage: 708.0M (9% of installed RAM)
[OK] Slow queries: 0% (1/2M)
[OK] Highest usage of available connections: 7% (11/151)
[OK] Key buffer size / total MyISAM indexes: 128.0M/2.1G
[OK] Key buffer hit rate: 99.9% (127M cached / 138K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (147 temp sorts / 46K sorts)
[!!] Joins performed without indexes: 281
[!!] Temporary tables created on disk: 43% (48K on disk / 109K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 1% (400 open / 23K opened)
[OK] Open file limit used: 64% (660/1K)
[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
[OK] InnoDB buffer pool / data size: 128.0M/107.2M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    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 without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
    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 (>= 8M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_open_cache (> 400)

Any thoughts on optimization?

Asiaforum.no · June 13, 2015

No suggestions?

RevengeFNF · June 13, 2015

My primary suggestion would be for you to change your tables to Innodb. If you can't or don't feel comfortable with it, then you need at least to optimize for your current MyISAM Tables.

So:

key_buffer_size = 2500M
thread_handling = pool-of-threads
tmp_table_size = 256M
max_heap_table_size = 256M
table_open_cache = 1000
open_files_limit = 2000
query_cache_type = 1
query_cache_size = 32M
join_buffer_size = 2M
read_buffer_size = 128K
sort_buffer_size = 256K

Invision Community 4: SEO, prepare for v5 and dormant account notifications

Invision Community 5: Beta testing and latest updates

Invision Community 4: A more professional report center

Invision Community 5: A video walkthrough creating a custom theme and homepage

Looking for tips on optimizing board with pages

Recommended Posts

Asiaforum.no

RevengeFNF

Asiaforum.no

CheersnGears

RevengeFNF

Asiaforum.no

RevengeFNF

CheersnGears

RevengeFNF

Asiaforum.no

Codehusker

Asiaforum.no

Asiaforum.no

RevengeFNF

Archived

Recently Browsing 0 members

More