Need help optimizing the server for faster load time

[SubStrider]

We have a six core HT server with 16 GB RAM. The httpd and my.cnf are all set as default values.

This is the current server status. Clearly there is a lot of scope to optimize. Can you please tell me what do I need to look into or post here for suggestions.

Server load 1.13 (12 CPUs) 

Memory Used 6.56% (1,100,228 of 16,777,216) 

Swap Used 0% (0 of 1)

[Aussie Cable]

Run "mysqltuner.pl" or "tuning-primer.pl" for mySQL, make sure your SQL server has been running for at least 48 hours before running either.

As for serving faster static files, look at nginx admin (for cPanel).

[SubStrider]

I am sorry I am a major noob at this. How do I run this file. I mean I am logged into the ssh and I can execute commands but how do I run this file?
Will post the results in a while.

Also how can I determine the optimum maxClients setting for apache? It is currently set at 150.

[Aussie Cable]

If you use "tuning-primer.pl" you need to make sure that bc is installed. In your shell window type:

yum -y install bc[/CODE]

Then you need to download the required file

[CODE]wget http://www.day32.com/MySQL/tuning-primer.sh

Set permissions

chmod 0777 tuning-primer.sh

Then you can execute the file

./tuning-primer.sh

Same goes with mysqltuner, download

wget mysqltuner.pl

set permissions

chmod 0777 mysqltuner.pl

execute

./mysqltuner.pl[/code]

Both of these files will do the same thing, it depends on which one you like the best, when they have finished, post there outputs here so we can take a look. If you have munin installed you can see if your apache clients are being optimally used.

[raindog308]

We have a six core HT server with 16 GB RAM. The httpd and my.cnf are all set as default values.

This is the current server status. Clearly there is a lot of scope to optimize. Can you please tell me what do I need to look into or post here for suggestions.

Server load 1.13 (12 CPUs)

Memory Used 6.56% (1,100,228 of 16,777,216)

Swap Used 0% (0 of 1)

Why do you say from these stats there is "clearly" room to optimize?

I would say there is room if you're using the stock settings, perhaps, but not from these stats. You are barely using any RAM, and your load average is only 1.13. A load average below the number of CPUs is OK, all other things being equal. You are only keeping 1 of your 6/12 CPUs busy.

[Grumpy]

Are you actually experiencing a problem with your website? The stats you gave show that your server's idling most of the time. I don't really understand what it is that you want to achieve.

optimum maxclients (this is VERY VERY rough) = 70% * (max memory) / (memory of how much single apache process takes on about 95th percentile)
That leaves room for other processes to run without having your apache hog all the ram and bring it to a stall. This also assumes that you only need 30% of the ram to do EVERYTHING else.

[SubStrider]

mysqltuner _ I think I will retry after 2 days.

>>  MySQLTuner 1.2.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


-------- General Statistics --------------------------------------------------

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.1.61-log

[OK] Operating on 64-bit architecture


-------- Storage Engine Statistics -------------------------------------------

[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster

[--] Data in MyISAM tables: 3G (Tables: 819)

[--] Data in InnoDB tables: 107M (Tables: 13)

[--] Data in MEMORY tables: 0B (Tables: 7)

[!!] Total fragmented tables: 37


-------- Security Recommendations  -------------------------------------------

[OK] All database users have passwords assigned


-------- Performance Metrics -------------------------------------------------

[--] Up for: 2h 23m 18s (521K q [60.678 qps], 22K conn, TX: 15B, RX: 205M)

[--] Reads / Writes: 57% / 43%

[--] Total buffers: 58.0M global + 4.2M per thread (250 max threads)

[OK] Maximum possible memory usage: 1.1G (6% of installed RAM)

[OK] Slow queries: 0% (0/521K)

[OK] Highest usage of available connections: 3% (9/250)

[OK] Key buffer size / total MyISAM indexes: 16.0M/1.7G

[OK] Key buffer hit rate: 97.6% (13M cached / 314K reads)

[OK] Query cache efficiency: 40.1% (133K cached / 332K selects)

[!!] Query cache prunes per day: 147034

[OK] Sorts requiring temporary tables: 0% (2 temp sorts / 6K sorts)

[!!] Joins performed without indexes: 207

[!!] Temporary tables created on disk: 43% (1K on disk / 3K total)

[OK] Thread cache hit rate: 99% (9 created / 22K connections)

[OK] Table cache hit rate: 24% (256 open / 1K opened)

[OK] Open file limit used: 37% (473/1K)

[OK] Table locks acquired immediately: 99% (544K immediate / 545K locks)

[!!] InnoDB data size / buffer pool: 107.5M/8.0M


-------- Recommendations -----------------------------------------------------

General recommendations:

	Run OPTIMIZE TABLE to defragment tables for better performance

	MySQL started within last 24 hours - recommendations may be inaccurate

	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

Variables to adjust:

	query_cache_size (> 16M)

	join_buffer_size (> 1.0M, or always use indexes with joins)

	tmp_table_size (> 16M)

	max_heap_table_size (> 16M)

	innodb_buffer_pool_size (>= 107M)

[SubStrider]

Are you actually experiencing a problem with your website? The stats you gave show that your server's idling most of the time. I don't really understand what it is that you want to achieve.

I want it to be faster. Using the speedtest shows that the time to first byte is very sub optimal.

[SubStrider]

Why do you say from these stats there is "clearly" room to optimize?

Using the stock settings this is what it gets at. So there is enough spare hardware capacity left to speed up the site is what I meant.

[Grumpy]

I don't think you'll gain much value by tuning your mysql.
I don't know what system you're running, but if you want it running faster, just install apc or xcache on your system and hook up IPB with it.

Optimizing your system will DECREASE the hardware resource requirements.
You can't really somehow turbo your hardware power to do the thing you're doing faster. You can decrease the needs (like apc/xcache which does opcache and user cache) to make things faster.

[SubStrider]

You are saying that now we need about 1/4th the RAM than we were needing 2 months back or even 1 year back. If we optimize it further we can go even lower. Isn't running stable and running faster two different things between which we have to find a balance.

We do have xcache installed on our server.

[Vypr1]

I am experiencing the same problem, my page loads are around 2 seconds.
It's very annoying, I have a server hosted on comcast 50 Mbps down and like 20 Mbps up.

IIS, I am looking into MySQL/PHP configuration problems.

[Grumpy]

You are saying that now we need about 1/4th the RAM than we were needing 2 months back or even 1 year back. If we optimize it further we can go even lower. Isn't running stable and running faster two different things between which we have to find a balance.

We do have xcache installed on our server.

Well, if these are the stats you see now, and assuming they are at peak, then yes. :P I guess you've already done a good at at optimizing.

of course, running stable and running faster are separate things. But I don't think anyone here brought stability into the equation.

I am experiencing the same problem, my page loads are around 2 seconds.

It's very annoying, I have a server hosted on comcast 50 Mbps down and like 20 Mbps up.

IIS, I am looking into MySQL/PHP configuration problems.

Sounds like you have things mixed up or you are running a site from your own home computer. The 2nd is a no-no.

And I don't see how you have the "same" or even remotely similar problem.

[Feld0]

Running PHP via FastCGI, if you aren't doing so already, is a great way to boost performance as a single process can handle hundreds of page requests. It also allows you to use an opcode cache, which has already been suggested, for further performance boosting.

The single greatest change I made that improved my sites' speed, though, was to move them to a server that uses solid state drives exclusively. The throughput is phenomenal and MySQL really flies. It could be a bit expensive or impractical to move to a new server, but the performance of SSD's is incredible, so you might want to consider this option.

[SubStrider]

Yes we were on a server which had SSD, since it did not make any performance difference we scaled back and instead moved to a higher RAM server. I don't see how SSD will have superior performance than a HDD.

Anyway all this is down to server settings is what I feel. So I wanted advice on tweaking settings to reduce site loading time as far as possible. Don't know whether I am not able to communicate or whether I am asking for the unthinkable.

[Grumpy]

SSD is literally about 50 times faster than a standard HDD. If this change doesn't not cause a higher load speed, then your drive was never your bottleneck.
" alt="" class="ipsImage" width="1000" height="597">

Perhaps you should
1. link your said forum. Then we can judge whether or not it's of good/reasonable speeds.
2. Write details of your hardware
3. Explain your current setup.

[SubStrider]

SSD is literally about 50 times faster than a standard HDD. If this change doesn't not cause a higher load speed, then your drive was never your bottleneck.

Perhaps you should

1. link your said forum. Then we can judge whether or not it's of good/reasonable speeds.

2. Write details of your hardware

3. Explain your current setup.

Details as asked

1. techenclave.com

2. 6 x 2.56 GHz, 16 GB 1333MHz DDR3, 500 GB SAS in Raid 1

3. Not sure what you mean by that

Current msqltuner output

-------- General Statistics --------------------------------------------------

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.1.61-log

[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------

[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster

[--] Data in MyISAM tables: 3G (Tables: 828)

[--] Data in InnoDB tables: 107M (Tables: 13)

[--] Data in MEMORY tables: 0B (Tables: 7)

[!!] Total fragmented tables: 55

-------- Security Recommendations  -------------------------------------------

[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------

[--] Up for: 1d 0h 59m 1s (7M q [80.072 qps], 301K conn, TX: 205B, RX: 1B)

[--] Reads / Writes: 58% / 42%

[--] Total buffers: 106.0M global + 7.2M per thread (250 max threads)

[OK] Maximum possible memory usage: 1.9G (11% of installed RAM)

[OK] Slow queries: 0% (10/7M)

[OK] Highest usage of available connections: 6% (17/250)

[OK] Key buffer size / total MyISAM indexes: 16.0M/1.8G

[OK] Key buffer hit rate: 99.2% (672M cached / 5M reads)

[OK] Query cache efficiency: 42.6% (2M cached / 4M selects)

[!!] Query cache prunes per day: 43757

[OK] Sorts requiring temporary tables: 0% (144 temp sorts / 163K sorts)

[!!] Joins performed without indexes: 774

[!!] Temporary tables created on disk: 39% (57K on disk / 144K total)

[OK] Thread cache hit rate: 99% (17 created / 301K connections)

[OK] Table cache hit rate: 90% (994 open / 1K opened)

[OK] Open file limit used: 79% (1K/2K)

[OK] Table locks acquired immediately: 99% (8M immediate / 8M locks)

[!!] InnoDB data size / buffer pool: 107.5M/8.0M

-------- 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

Variables to adjust:

    query_cache_size (> 64M)

    join_buffer_size (> 4.0M, or always use indexes with joins)

    tmp_table_size (> 64M)

    max_heap_table_size (> 16M)

    innodb_buffer_pool_size (>= 107M)

[Grumpy]

By 3... I meant
1. Are you running apache? litespeed? php-fpm? etc.
2. Are you running opcache? like xcache, apc?
3. Are you running user cache and integrated it to your invision? like xcache, apc, memcached?
4. Are you running sphinx and setup your invision?
5. Are you running a control panel like cpanel, plesk, etc?
6... I think I can go on... but I'll stop.

If no to any of the above and apache for server setup, there's your room to make things faster.

Your mysql is most likely NOT a factor if you don't even see performance differences between ssd and sas.
Lot of people post their mysqltuner stats here... but quite frankly, info LOT more useful to telling how well the server is running are stats like top (top -n 1), iostat (iostat -x 60 2), apachectl status (if running apache). Because these things show me your system. Tuning is all about altering settings to reflect your system. But without knowing the system, it's hard to make recommendations.

Below are recommendations based on your mysql tuner. But you will most likely NOT see a big difference with these. I do not think mysql is your bottleneck as already mentioned.

One primary thing I do notice from your tuner is this
join_buffer_size (> 4.0M, or always use indexes with joins)
The default installation of IPB shouldn't make any non-indexed joins. You either have an inefficient plugin or you should add indexes on joins that are using them.

Another line is this:
innodb_buffer_pool_size (>= 107M)
I don't know how much you are actually using innodb for. You certainly have lots of ram available. I suggest you increase this drastically. This is a balancing act between leaving enough memory free in your OS for other stuff and caching much crap as possible in the buffer pool.
So, if your DB that uses innodb is 1GB, you can make this buffer pool >1GB for maximum performance. But you don't want to make it so big that it'll use up all the OS'es ram, hogging it for more important things. You have plenty of ram, I don't know how big your DB is. So, you should set it accordingly.
Read this on more details:
http://www.mysqlperf...ization-basics/

These...
tmp_table_size (> 64M)
max_heap_table_size (> 16M)
You can increase, them, but you most likely won't see much improvement. But you SHOULD make them equal. They're different right now. If one's different, chances are, you are only using the minimum of the two. So, set max heap table size to 64mb.

===========
Closing note, your site loads at a reasonable speed. 743ms for me with 90ping plus download. So, 653ms to create everything... It's not a bad time.

[SubStrider]

Thanks for such a detailed reply. Let me answer most of them right away and I will post the stats later when I have access to shell.

We are running apache with standard settings. Only changes I had made were to the MaxClients which I raised from 150 to 450 which made no effect.
We are also running xcache in a standard setup for the server.
We are on cpanel.
Will you also need the versions of mysql, apache etc?


You are right I have made several changes to the mysql since the time I posted the mysqltuner results. In fact disabling the Classifieds add-on sidebar was the only thing that gave me significant improvement of around 300ms in the loading time per page.


The changes I have done so far.
join_buffer_size = 16M
but no improvement in loading time. Let me check for plugins and indexes deeper now.

innodb_buffer_pool_size (>= 107M)
This parameter was not present in the mycnf but when I tried adding it mysql just wont start. Brought down the site so I removed it.
I dont remember the DB size but will post that too.

These...
tmp_table_size and max_heap_table_size made equal and set them at 256M each. Like you said no improvement as such.

===========
I was checking the site speed on webpagetest and it shows that they time to first byte is rather slow taking everything else in account. So I wanted to tune the server for that so that I am able to extract the max out of it with server resources lying idle.

I even tried installing newrelic but it doesnt work out of the box for me so I have troubleshoot the installation too.

------

Finally no matter how high I take the temp_table_size the percentage of tables created on disk doesnt change. So I am guessing it has something to do with TEXT object being a forum and all. Guess this won't improve no matter what.
[!!] Temporary tables created on disk: 39% (57K on disk / 144K total)

[AlexJ]

Not trying to hijack your topic..but since I have same issue, I thought I would also post my stats and ask for help in same topic. If you think, I should create new topic, feel free to let me know.

-------- General Statistics --------------------------------------------------

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.1.58-1~dotdeb.0-log

[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------

[--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster

[--] Data in MyISAM tables: 975M (Tables: 1584)

[--] Data in MEMORY tables: 3M (Tables: 4)

[!!] Total fragmented tables: 140

-------- Security Recommendations  -------------------------------------------

[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------

[--] Up for: 33d 15h 21m 25s (72M q [24.775 qps], 2M conn, TX: 1609B, RX: 36B)

[--] Reads / Writes: 54% / 46%

[--] Total buffers: 704.0M global + 6.7M per thread (50 max threads)

[OK] Maximum possible memory usage: 1.0G (12% of installed RAM)

[OK] Slow queries: 0% (85/72M)

[!!] Highest connection usage: 86%  (43/50)

[OK] Key buffer size / total MyISAM indexes: 512.0M/259.4M

[OK] Key buffer hit rate: 100.0% (3B cached / 531K reads)

[OK] Query cache efficiency: 51.3% (23M cached / 44M selects)

[!!] Query cache prunes per day: 14203

[OK] Sorts requiring temporary tables: 0% (691 temp sorts / 1M sorts)

[!!] Joins performed without indexes: 9853

[!!] Temporary tables created on disk: 36% (521K on disk / 1M total)

[OK] Thread cache hit rate: 99% (43 created / 2M connections)

[!!] Table cache hit rate: 12% (2K open / 19K opened)

[OK] Open file limit used: 49% (4K/8K)

[OK] Table locks acquired immediately: 99% (69M immediate / 69M locks)

-------- Recommendations -----------------------------------------------------

General recommendations:

	Run OPTIMIZE TABLE to defragment tables for better performance

	Reduce or eliminate persistent connections to reduce connection usage

	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_cache gradually to avoid file descriptor limits

Variables to adjust:

	max_connections (> 50)

	wait_timeout (< 90)

	interactive_timeout (< 90)

	query_cache_size (> 128M)

	join_buffer_size (> 2.0M, or always use indexes with joins)

	tmp_table_size (> 64M)

	max_heap_table_size (> 64M)

	table_cache (> 3086)

My settings:

key_buffer			  = 512M

max_allowed_packet	  = 64M

thread_stack			= 192K

thread_cache_size	   = 128

# This replaces the startup script and checks MyISAM tables if needed

# the first time they are touched

myisam-recover		 = BACKUP

max_connections		= 50

open_files_limit = 8192

table_cache			= 3086

table_definition_cache = 1200

#thread_concurrency	 = 8

tmp_table_size = 64M

max_heap_table_size = 64M

interactive_timeout = 90

connect_timeout = 30

wait_timeout = 90

#innodb_file_per_table

innodb_buffer_pool_size = 64M

innodb_log_file_size = 32M

innodb_log_buffer_size = 8M

innodb_flush_log_at_trx_commit = 2

innodb_flush_method = O_DIRECT

innodb_thread_concurrency = 16

innodb_additional_mem_pool_size = 8M

sort_buffer_size = 4M

read_buffer_size = 256K

read_rnd_buffer_size = 256K

join_buffer_size = 2M

myisam_sort_buffer_size = 512M

low_priority_updates=1

concurrent_insert=2

# * Query Cache Configuration

#

query_cache_limit	   = 3M

query_cache_size		= 128M

query_cache_type = 1

#

How do I fix:

[!!] Query cache prunes per day: 14203

[!!] Joins performed without indexes: 9853

[!!] Temporary tables created on disk: 36% (521K on disk / 1M total)

[!!] Table cache hit rate: 12% (2K open / 19K opened)

[AlexJ]

@substrider... I noticed below in your output...

[--] Data in MEMORY tables: 0B (Tables: 7)

Why not change the session table to memory instead of myisam or innodb? It would speed up u forums ..

Also why not increase key buffer size?

Key buffer size / total MyISAM indexes: 16.0M/1.8G

I guess your keybuffer size is around 16Mb ? May be increase it?

[SubStrider]

[color=#666600]Why not change the session table to memory instead of myisam or innodb? It would speed up u forums ..[/color]

[color=#666600]Also why not increase key buffer size? [/color]

I remember reading somewhere that memory tables are not the best way to go specially for a forum. Maybe someone else can confirm this.

I increased the key buffer size but it did not help much towards speed. So I stopped going forward with half knowledge.

[AlexJ]

I remember reading somewhere that memory tables are not the best way to go specially for a forum. Maybe someone else can confirm this.

I increased the key buffer size but it did not help much towards speed. So I stopped going forward with half knowledge.

Moving session tables to memory is good...I had seen bfarber suggesting that to some people before... I am using memory for session tables and I have no issues since last couple of years.

[p4guru]

Lot of people post their mysqltuner stats here... but quite frankly, info LOT more useful to telling how well the server is running are stats like top (top -n 1), iostat (iostat -x 60 2), apachectl status (if running apache). Because these things show me your system. Tuning is all about altering settings to reflect your system. But without knowing the system, it's hard to make recommendations.

Grumpy and SubStrider, you will love mysqlmymonlite.sh tool then only takes less <12 seconds to gather all stats :smile:

[Grumpy]

@SubStrider

RE: innodb_buffer_pool_size
1. make sure to put that in the innodb section.
2. Make sure it's not too big or too small. Min is 5M i think. Too big = more than your system has. Try like 1G
3. Depending on your version, it may not be the exact term used. mysqltuner tends to spit terms out at some random version they chose to use, not the version you actually have. I'm making this example up, but it could be called "innodb_pool_size" in your version as an example.

RE: apache. (note, all these options are exclusive to the others)
You can choose to ditch apache or ditch it partially. apache isn't exactly a "fast" thing. Just big, reliable thing.
You can choose to setup a reverse proxy with something like nginx or varnish and use apache as php processor. Then your static contents will load faster. (this may break your cpanel. Though, there's an nginx cpanel plugin on the web. free and paid. I think the two perform identically though... I tried free one before and still using it on one of my small servers. It's quite simple and nice.)
You can choose to setup php-fpm or other fastcgi based to process php instead of apache via nginx or varnish. (this WILL break your cpanel)
You can choose to setup lsphp based php processor called litespeed (paid)
If you are using su_php (the safe one, if you are using this), you can choose to remove this and run alternatives. You can choose this in cpanel. su_php is the safest option, but also the slowest.

RE: cpanel
You can choose to uninstall cpanel. cpanel does a lot of things, and it's a sizable overhead. Just run no control panel is fastest.

RE: speed setups...
You can uninstall/remove lot of securities such as firewall, mod_php, anti-virus, etc that goes before, after or during php processing to speed things up. This is obviously risky though. Game servers often tend to take drastic measures to reduce even few more ms.

You should note, for me, your time to first byte was around 700~800ms. If you shaved off 300ms by removing that bad plugin (classifieds), you're at 400~500ms. You are already nearing the good-as-you-get with IPB. Comparatively, this website is at 300~400ms for me. IPB is a big software, you should remember that.