Apache & MySQL Optimizing

yavuz

yavuz

Well-known member
For several days, I'm trying to tune my server setting, my.conf and other stuff and I thought I ask for your suggestions.

Server Specs:

Intel(R) Core(TM) i7 CPU VPS with two cores avaliable (total 5000 Ghz)
4 GB RAM
cPanel as control panel
MySQL 5.1.56, php 5.3.10

Zend Engine v2.3.0, eAccelerator v0.9.6.1, ionCube PHP Loader v4.0.9, Suhosin v0.9.32.1
APC

my.conf is currently set to default:

Code: 
[mysqld]
set-variable = max_connections=500
log-slow-queries
safe-show-database
wait_timeout = 28800
max_allowed_packet = 16777216

php.ini

Code: 
extension_dir = "/usr/local/lib/php/extensions/no-debug-non-zts-20090626"
zend_extension = "/usr/local/IonCube/ioncube_loader_lin_5.3.so"
extension = "eaccelerator.so"
extension = "suhosin.so"
extension = "pdo.so"
extension = "pdo_sqlite.so"
extension = "sqlite.so"
extension = "pdo_mysql.so"
extension = "apc.so"
 
apc.enabled = 1
apc.shm_segments = 1
apc.shm_size = 128M

This is the output I get with mysqltuner:

Code: 
-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 5h 5m 25s (5M q [52.293 qps], 354K conn, TX: 176B, RX: 1B)
[--] Reads / Writes: 82% / 18%
[--] Total buffers: 34.0M global + 2.7M per thread (500 max threads)
[OK] Maximum possible memory usage: 1.4G (34% of installed RAM)
[OK] Slow queries: 0% (101/5M)
[OK] Highest usage of available connections: 5% (25/500)
[OK] Key buffer size / total MyISAM indexes: 8.0M/254.0M
[OK] Key buffer hit rate: 96.1% (72M cached / 2M reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 520K sorts)
[!!] Joins performed without indexes: 47179
[!!] Temporary tables created on disk: 46% (84K on disk / 182K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 1M opened)
[OK] Open file limit used: 1% (42/2K)
[OK] Table locks acquired immediately: 99% (6M immediate / 6M locks)
[!!] InnoDB data size / buffer pool: 693.9M/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
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
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_cache (> 64)
    innodb_buffer_pool_size (>= 693M)

This is what I put in my library/config.php

Code: 
$config['cache']['enabled'] = true;
$config['cache']['frontend'] = 'Core';
$config['cache']['frontendOptions']['cache_id_prefix'] = 'xf_';
$config['cache']['backend'] = 'Apc';

I assume this is the correct for frontent and backend caching?

Server load is usually ok but loading website pages become a pain. What would you suggest I set me my.conf and php.ini?

Thanks.
 
Here are a few MySQL settings I'd recommend putting in your my.cnf right now and then tuning over time:

key_buffer_size = 512M
max_allowed_packet = 8M
table_open_cache = 4096
sort_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 16M
join_buffer_size = 1M
query_cache_size = 256M
query_cache_limit = 2M
table_definition_cache = 4096

That VPS you have is a beast and I expect it to be able to handle thousands of concurrent users. You might also want to look into switching your webserver to Litespeed (http://www.litespeedtech.com) to increase performance even further. Litespeed is fully compatible with Apache config files and is generally considered to perform better.
 
I've installed nginx from nginxcp.com which supports reverse proxy.

I had several suggestions in the last couple of days, modified my.conf settings with your suggestions which look like this now:

Code: 
[mysqld]
wait_timeout = 28800
query_cache_limit=2M
query_cache_size=256M
query_cache_type=1
max_connections=1000
max_user_connections=300
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=512
key_buffer=512M
join_buffer_size=1M
max_allowed_packet=32M
table_cache=128
table_definition_cache = 4096
sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=3072K
max_connect_errors=10
thread_concurrency=8
myisam_sort_buffer_size=16M
max_allowed_packet = 8M
skip-locking
 
[myisamchk]
key_buffer=512M
sort_buffer=256M
read_buffer=64M
write_buffer=64M

Seem fine to you Ghan? Thanks for your input.
 
max_connection is far to high - have you considered your memory consumption??? Either you need really so many connctions (but then your 4GB will be exhausted) or you are wasting resources.
 
Walter said:
max_connection is far to high - have you considered your memory consumption??? Either you need really so many connctions (but then your 4GB will be exhausted) or you are wasting resources.
Click to expand...

Lowered to 500.

Code: 
[mysqld]
wait_timeout = 28800
query_cache_limit=2M
query_cache_size=256M
query_cache_type=1
max_connections=300
max_user_connections=100
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=512
key_buffer=512M
join_buffer_size=1M
max_allowed_packet=32M
table_cache=128
table_definition_cache = 2048
sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=3072K
max_connect_errors=10
thread_concurrency=8
myisam_sort_buffer_size=16M
max_allowed_packet = 8M
skip-locking

[myisamchk]
key_buffer=512M
sort_buffer=256M
read_buffer=64M
write_buffer=64M

I run mysqltuner and got back with this:

Code: 
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.56
[OK] Operating on 64-bit architecture

Code: 
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 625M (Tables: 1115)
[--] Data in InnoDB tables: 710M (Tables: 405)
[--] Data in MEMORY tables: 7M (Tables: 17)
[!!] Total fragmented tables: 440

Code: 
-------- Performance Metrics -------------------------------------------------
[--] Up for: 11s (546 q [49.636 qps], 42 conn, TX: 15M, RX: 232K)
[--] Reads / Writes: 72% / 28%
[--] Total buffers: 794.0M global + 8.2M per thread (300 max threads)
[OK] Maximum possible memory usage: 3.2G (79% of installed RAM)
[OK] Slow queries: 0% (0/546)
[OK] Highest usage of available connections: 1% (3/300)
[OK] Key buffer size / total MyISAM indexes: 512.0M/258.2M
[!!] Key buffer hit rate: 88.1% (1K cached / 211 reads)
[OK] Query cache efficiency: 40.8% (144 cached / 353 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 53 sorts)
[!!] Joins performed without indexes: 5
[!!] Temporary tables created on disk: 41% (7 on disk / 17 total)
[OK] Thread cache hit rate: 92% (3 created / 42 connections)
[OK] Table cache hit rate: 92% (128 open / 138 opened)
[OK] Open file limit used: 3% (54/1K)
[OK] Table locks acquired immediately: 100% (584 immediate / 584 locks)
[!!] InnoDB data size / buffer pool: 710.8M/8.0M

Code: 
-------- 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
Variables to adjust:
    join_buffer_size (> 1.0M, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    innodb_buffer_pool_size (>= 710M)
 
You have mysql configured so it will use a maxium of 3.2 GB out of 4 GB - if this server also runs a webserver you will run into problems.

Data in MyISAM tables: 625M
Data in InnoDB tables: 710M

But you reserve 512M as MyISAM key_buffer but have no Inndob buffer? I would lower the keybuffer and make at least some Innodb buffer (e.g. each 256M). The suggestions of mysqltuner are pretty good :)
 
Walter said:
You have mysql configured so it will use a maxium of 3.2 GB out of 4 GB - if this server also runs a webserver you will run into problems.

Data in MyISAM tables: 625M
Data in InnoDB tables: 710M

But you reserve 512M as MyISAM key_buffer but have no Inndob buffer? I would lower the keybuffer and make at least some Innodb buffer (e.g. each 256M). The suggestions of mysqltuner are pretty good :)
Click to expand...

Walter thanks again. Would you modify my my.conf and write down your suggestions? I'm doing so much tuning that everything is blurr to me :D
 
Guys, how do should I read these suggestions and what example (to put in my.conf)

Code: 
    join_buffer_size (> 1.0M, or always use indexes with joins)
    tmp_table_size (> 54M)
    max_heap_table_size (> 65M)
    innodb_buffer_pool_size (>= 752M)
 
yavuz said:
I currently have nginx installed ChemicalKicks, haven't considered LEMP, no.
Click to expand...
Maybe consider it. If you're at the stage now where you're having to spend so much time configure Apache why not just get rid of it altogether and you'll have the added bonus of not having cPanel installed on your server :p but seriously if you do need cPanel then it wouldn't be a goer.
 
If you want cPanel and you don't want Apache, use Litespeed. I've had great success with it. (Though I've never installed cPanel myself so I'm not sure what you need to do to get it running)
 
I know that there is a Litespeed plugin for cPanel that allows EasyApache to work seamlessly with Litespeed. You might want to check that out.
 
Ghan_04 said:
I know that there is a Litespeed plugin for cPanel that allows EasyApache to work seamlessly with Litespeed. You might want to check that out.
Click to expand...

just installed and yes I'm using that plugin. Maybe it's too early but I couldn't see any performance boost at all
 
Litespeed, like anything else, still requires some configuration tuning. Specifically, you should look into tuning the settings for the PHP external app on the server config level.
 
Backup your current my.cnf and try this alternative one:

Rich (BB code): 
[mysqld]
wait_timeout = 28800
query_cache_limit=2M
query_cache_size=32M
query_cache_type=1
max_connections=300
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=40
key_buffer=512M
join_buffer_size=512K
max_allowed_packet=16M
table_cache=2000
table_definition_cache = 2048
sort_buffer_size=512K
read_buffer_size=1M
read_rnd_buffer_size=512K
max_connect_errors=10
thread_concurrency=8
myisam_sort_buffer_size=64M
max_allowed_packet = 16M
skip-locking

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

Also, have you turned on and configured InnoDB for your MySQL server (it doesn't appear so if the my.cnf you posted above is your full my.cnf)?

Cheers,
Shaun :D
 
It was the default that cpanel created, yes Shaun. Rebooted the server, will add that when it comes up.
 
You must log in or register to reply here.

Similar threads

H
Improve MySQL write log efficiency
Replies
0
Views
495
hellreturn
H
D
Need help importing vbulettin forum to xenforo, can someone check mysql config?
Replies
3
Views
319
Black Tiger
Black Tiger
PeterChen
Extreme high open file limit used - MariaDB 10.3
Replies
7
Views
2K
MySiteGuy
MySiteGuy
I
My.cnf Suggestions after Mysql 5.7 Upgrade
Replies
7
Views
3K
jeffatrackaid
jeffatrackaid
Nirjonadda
Performance optimization for my.cnf
Replies
0
Views
2K
Nirjonadda
Nirjonadda
Top Bottom