XF 1.1 mysql optimization

xmlxp

Active member
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:

Code:
[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:
Code:
-------- 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?
 

xmlxp

Active member
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
Code:
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

Well-known member
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?
 

xmlxp

Active member
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?
 
Top