Apache & MySQL Optimizing
- Thread starter yavuz
- Start date
CTXMedia
Well-known member
The values I've put 512K on are per-thread, rather than global - which means the memory footprint increases as the number of threads increases (as opposed to global values with are set once and remain fixed).
A slight adjustment in these values can have a dramatic effect (which is amplified when you have limited memory in your box). For example, 20 threads at 512K = 10MB - but increase that slightly to say, 2MB and you end up using 40MB. Obviously the higher the value and the busier your site, the more impact the change can have, and if you're not careful you can end up with stalling or bottlenecks.
If you haven't installed it yet or don't have it on your server, get phpMyAdmin - the status page can give you lots of info about your MySQL server and how the various adjustments are affecting performance.
Speak to your host too about enabling InnoDB.
Cheers,
Shaun
yavuz
Well-known member
Oh, and apologies for stating the obvious but you need to stop and start MySQL for the new values to take effect ...
No need, I've done that with your suggested my.conf:
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 639M (Tables: 1115)
[--] Data in InnoDB tables: 762M (Tables: 405)
[--] Data in MEMORY tables: 6M (Tables: 17)
[!!] Total fragmented tables: 448
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 40m 53s (103K q [42.107 qps], 8K conn, TX: 1B, RX: 34M)
[--] Reads / Writes: 48% / 52%
[--] Total buffers: 570.0M global + 2.8M per thread (300 max threads)
[OK] Maximum possible memory usage: 1.4G (34% of installed RAM)
[OK] Slow queries: 0% (12/103K)
[OK] Highest usage of available connections: 8% (25/300)
[OK] Key buffer size / total MyISAM indexes: 512.0M/262.9M
[OK] Key buffer hit rate: 99.8% (1M cached / 3K reads)
[OK] Query cache efficiency: 69.0% (39K cached / 57K selects)
[!!] Query cache prunes per day: 158182
[OK] Sorts requiring temporary tables: 0% (9 temp sorts / 3K sorts)
[!!] Joins performed without indexes: 590
[!!] Temporary tables created on disk: 45% (1K on disk / 2K total)
[OK] Thread cache hit rate: 99% (25 created / 8K connections)
[OK] Table cache hit rate: 98% (1K open / 1K opened)
[OK] Open file limit used: 53% (2K/4K)
[OK] Table locks acquired immediately: 99% (55K immediate / 56K locks)
[!!] InnoDB data size / buffer pool: 762.9M/8.0M
-------- 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:
query_cache_size (> 32M)
join_buffer_size (> 512.0K, or always use indexes with joins)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
innodb_buffer_pool_size (>= 762M)
yavuz
Well-known member
An update: Litespeed seems to kick in and I've read a tutorial about mysql optimization by cpanel, helped a lot!
I've changed my.conf to this:
Will dig deeper after 24 hours. Thanks everyone for their input
I've changed my.conf to this:
Code:
[mysqld]
general_log
max_allowed_packet=16777216
max_connections=500
slow_query_log
wait_timeout=28800
query_cache_size=8M
join_buffer_size=128
tmp_table_size=16M
max_heap_table_size= 16M
thread_cache_size=4
table_open_cache=64
innodb_buffer_pool_size=728MWill dig deeper after 24 hours. Thanks everyone for their input
yavuz
Well-known member
Thanks for the feedback Ghan:
I'm using APC for backend and core for frontent. Can I use Xcache for frontent caching?
What would I put in my config file if I dit?
lasertits
Active member
I'm a bit confused on how to read these too... tmp_table_size for example, is it telling you to reduce it below 54M, or increase it?
If XenForo is not use XCache's variable cache, in php.ini need change. This option disable variable cache in XCache.
Code:
xcache.var_size = 0MIf XenForo is use XCache's variable cache, need add to configuration on the XenForo end:
Code:
$config['cache'] = array(
'enabled' => true,
'frontend' => 'Core',
'frontendOptions' => array(
'caching' => true,
'automatic_serialization' => false,
'cache_id_prefix' => 'you_prefix_',
'lifetime' => 18000
),
'backend' => 'Xcache',
'backendOptions' => array(),
'cacheSessions' => true
);
craigiri
Well-known member
OK, I'm confused too!
Dedicated server - Apache/PHP
5.2.10 linux
large board
do I enable all of these? That is, xcache, apc, memcache? Or just one or two?
Are they definitely already in/on the server? That is, in Apache, PHP or where ever?
I see no files on the server with the name memcache or xcache....other than those the XF has in it's library...
Sorry for the dumb questions, but I have to walk before I can run!
Dedicated server - Apache/PHP
5.2.10 linux
large board
do I enable all of these? That is, xcache, apc, memcache? Or just one or two?
Are they definitely already in/on the server? That is, in Apache, PHP or where ever?
I see no files on the server with the name memcache or xcache....other than those the XF has in it's library...
Sorry for the dumb questions, but I have to walk before I can run!
XCache is opcode and variable cacher.
APC is opcode and variable cacher.
Memcached is only variable cacher. Must use with XCache or APC for full work (opcode + variable cache).
XCache for me is best choice (as 2 in 1).
APC I not use because he had bugs. Perhaps now they're gone.
For large boards often recommend memcached, but it contain only variable cacher.
APC is opcode and variable cacher.
Memcached is only variable cacher. Must use with XCache or APC for full work (opcode + variable cache).
XCache for me is best choice (as 2 in 1).
APC I not use because he had bugs. Perhaps now they're gone.
For large boards often recommend memcached, but it contain only variable cacher.
Adam Howard
Well-known member
APC is far better than xCache.
Uses less resources and far less conflicts in my opinion
Uses less resources and far less conflicts in my opinion
