Advertisement:

Author Topic: Plz give advice about some setting of the my.cnf files  (Read 4158 times)

Offline qtime

  • Full Member
  • ***
  • Posts: 501
Plz give advice about some setting of the my.cnf files
« on: June 14, 2006, 01:46:30 AM »
I have a dedicated server with 1 gig ram, pentium 4 with 3 ghz cpu, using SMF 1.1 RC2
99703 Posts in 33200 Topics by 33270 Members. I am using the default theme.
Over 600 visitors an hour (set it to 60 minutes)
Do I have to lower or higher the query_cache_size to get the right figures?
Other info can be sent with PM, like url of the status and php file etc.
I have tested a lot of settings and increased the speed and avoid connection errors.

MySQL Statistics
MySQL 4.1.x
 Connections per second:      3.723
Kilobytes received per second:    19.5619
Kilobytes sent per second:    41.0835
Queries per second:    80.6799
Percentage of slow queries:    0
Opened vs. Open tables:
(table_cache)    9.4139 (should be <= 80)
Table cache usage:
(table_cache)    0.5073 (should be >= 0.5 and <= 0.9)
Key buffer read hit rate:
(key_buffer_size)    0.0008 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size)    0.4412 (should be <= 0.5)
Thread cache hit rate:
(thread_cache_size)    1793.9559 (should be >= 30 )
Thread cache usage:
(thread_cache_size)    0.76 (should be >= 0.7 and <= 0.9)
Temporary table disk usage:
(tmp_table_size)    0.1951 (should be <= 0.5)
Sort merge pass rate:
(sort_buffer)    0 (should be <= 0.001)
Query cache enabled:
(query_cache_type)    1 (should be >= 1 and <= 1)
Query cache miss rate:
(query_cache_limit)    0.0016 (should be <= 0.1)
Query cache prune rate:
(query_cache_size)    0.1109 (should be <= 0.05)

my.cnf settings:
[mysqld]
# * Basic Settings
max_connections = 300
max_user_connections = 125
interactive_timeout = 20
wait_timeout = 15
connect_timeout = 10
table_cache=2048
sort_buffer=128M
thread_cache_size = 150
skip-external-locking
read_buffer=2M
record_buffer=8M

# * Fine Tuning
kkey_buffer      = 384M
max_allowed_packet = 16M
thread_stack      = 128K
#
# * Query Cache Configuration
#
query_cache_limit   = 948576
query_cache_size        = 12777216
query_cache_type        = 1
« Last Edit: June 22, 2006, 01:04:47 PM by qtime »