XF 1.1 mysql optimization

[xml]

my server hard disk was replaced and i had to move all my data to the new one and its like moving to a new server so i had to do the tweaks and optimizations all over again.

OS redhat 5.9 ( 32 bits )
dual processor
4GB ram
cpanel with mysql 5.1.66 & php 5.3.21 and ( i use MyISAM tables )

xcache is installed and running perfectly

i did some my.cnf tweaks as follwing:

[mysqld]
skip-innodb
max_connections = 250
thread_cache_size = 256
 
table_open_cache = 8000
table_definition_cache = 8000
tmp_table_size = 512M
max_heap_table_size = 512M
 
query_cache_limit = 4M
query_cache_size = 64M
query_cache_type = 1
 
key_buffer_size = 1024M
join_buffer_size = 1M
read_buffer_size = 1M
 
connect_timeout = 10
max_connect_errors = 10
max_allowed_packet = 128M
tmp_table_size = 64M
myisam_sort_buffer_size = 64M

i run mysqltuner.pl and the out put is:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.66-cll
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
 
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 4G (Tables: 364)
[--] Data in MEMORY tables: 3M (Tables: 7)
[!!] Total fragmented tables: 17
 
-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned
 
-------- Performance Metrics -------------------------------------------------
[--] Up for: 17h 58m 23s (838K q [12.961 qps], 95K conn, TX: 3B, RX: 186M)
[--] Reads / Writes: 48% / 52%
[--] Total buffers: 1.1G global + 4.4M per thread (250 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 2.2G (56% of installed RAM)
[OK] Slow queries: 0% (13/838K)
[OK] Highest usage of available connections: 16% (40/250)
[OK] Key buffer size / total MyISAM indexes: 1.0G/832.7M
[OK] Key buffer hit rate: 99.9% (74M cached / 74K reads)
[OK] Query cache efficiency: 67.5% (275K cached / 407K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 1% (630 temp sorts / 40K sorts)
[OK] Temporary tables created on disk: 13% (130 on disk / 976 total)
[OK] Thread cache hit rate: 99% (40 created / 95K connections)
[OK] Table cache hit rate: 28% (514 open / 1K opened)
[OK] Open file limit used: 5% (890/16K)
[OK] Table locks acquired immediately: 99% (487K immediate / 487K locks)
 
-------- 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

I want to know a bout the [!!] lines in the mysqltuner report, what action i need to do?

 

[MattW]

First thing is you need to move to a server is a 64bit OS, as mysql can't make use of the RAM available to it.

 

[xml]

Hello Matt

moving to a 64bit OS server is not an option for me now so i have to live with current hardware and software i got.

from output of tuning-primer i got

MEMORY USAGE
Max Memory Ever Allocated : 1.23 G
Configured Max Per-thread Buffers : 1.08 G
Configured Max Global Buffers : 1.06 G
Configured Max Memory Limit : 2.14 G
Physical Memory : 3.90 G
Max memory limit seem to be within acceptable norms

since mysql is limitted to 2 GB usage because of my server 32-bit OS, i would be in the safe side as long as Max Memory Ever Allocated is withen the range of 2 GB, its currently 1.23 GB, if you have comment on this please post it.

what about this line
[!!] Total fragmented tables: 17

what varialbe is related to this line and whats the best value for it?

 

[MattW]

what about this line
[!!] Total fragmented tables: 17

If you have phpMyAdmin installed, you can look for tables in the database which have an overhead on them, and Optimize them, which will reduce fragmentation.

As for the rest, I was in the same situation until I moved to a 64bit OS, so I had to live with the current settings I had until I could tell MySQL to use more.

Does you XenForo install have MyISAM tables, or are they InnoDB?

 

[xml]

Does you XenForo install have MyISAM tables, or are they InnoDB?

MyISAM tables

done table optimization from phpMyAdmin , I am amazed about phpMyAdim stability, it optimized xf_post table of size 2.2 GB and didnt crash !!

the only table with overhead and there was no optimize option was xf_session_activity , is that normal?