Need my.ini optimizing help

[doug04]

My server:
Dual operton with 2GB RAM
Win 2000 server using IIS, Mysql 4.1.1, PHP w/zend optimizer

I have a shop that runs on PHP/Mysql and I cannot seem to get a good my.ini config to allow it fast response. It can go for about 8 hits then bogs down for a few minutes, then it's ok as long a no other hits come in (it recovers). A free pair of shoes to someone who gets it working super fast..

My Traffic ø per hour
Received 22,359 KB 5,604 KB
Sent 321,406 KB 80,553 KB
Total 343,765 KB 86,157 KB

Connections ø per hour %
Failed attempts 20 5.01 0.32 %
Aborted 0 0.00 0.00 %
Total 6,217 1,558.15 100.00 %

Stats...
Total ø per hour ø per minute ø per second
149,799 37,543.61 625.73 10.43
Query type ø per hour %
admin commands 0 0.00 0.00 %
alter db 0 0.00 0.00 %
alter table 0 0.00 0.00 %
analyze 0 0.00 0.00 %
backup table 0 0.00 0.00 %
begin 0 0.00 0.00 %
change db 6,306 1,580.45 4.39 %
change master 0 0.00 0.00 %
check 0 0.00 0.00 %
checksum 0 0.00 0.00 %
commit 0 0.00 0.00 %
create db 0 0.00 0.00 %
create function 0 0.00 0.00 %
create index 0 0.00 0.00 %
create table 683 171.18 0.48 %
dealloc sql 0 0.00 0.00 %
delete 35 8.77 0.02 %
delete multi 0 0.00 0.00 %
do 0 0.00 0.00 %
drop db 0 0.00 0.00 %
drop function 0 0.00 0.00 %
drop index 0 0.00 0.00 %
drop table 0 0.00 0.00 %
drop user 0 0.00 0.00 %
execute sql 0 0.00 0.00 %
flush 8 2.01 0.01 %
grant 0 0.00 0.00 %
ha close 0 0.00 0.00 %
ha open 0 0.00 0.00 %
ha read 0 0.00 0.00 %
help 0 0.00 0.00 %
insert 810 203.01 0.56 %
insert select 0 0.00 0.00 %
kill 0 0.00 0.00 %
load 0 0.00 0.00 %
load master data 0 0.00 0.00 %
load master table 0 0.00 0.00 %
lock tables 0 0.00 0.00 %
optimize 0 0.00 0.00 %
preload keys 0 0.00 0.00 %
prepare sql 0 0.00 0.00 %
purge 0 0.00 0.00 %
purge before date 0 0.00 0.00 %
rename table 0 0.00 0.00 %

Query type ø per hour %
repair 0 0.00 0.00 %
replace 4 1.00 0.00 %
replace select 0 0.00 0.00 %
reset 0 0.00 0.00 %
restore table 0 0.00 0.00 %
revoke 0 0.00 0.00 %
revoke all 0 0.00 0.00 %
rollback 0 0.00 0.00 %
savepoint 0 0.00 0.00 %
select 15,848 3,971.93 11.04 %
set option 9 2.26 0.01 %
show binlog events 0 0.00 0.00 %
show binlogs 0 0.00 0.00 %
show charsets 5 1.25 0.00 %
show collations 5 1.25 0.00 %
show column types 0 0.00 0.00 %
show create db 0 0.00 0.00 %
show create table 0 0.00 0.00 %
show databases 3 0.75 0.00 %
show errors 0 0.00 0.00 %
show fields 0 0.00 0.00 %
show grants 0 0.00 0.00 %
show innodb status 0 0.00 0.00 %
show keys 0 0.00 0.00 %
show logs 0 0.00 0.00 %
show master status 0 0.00 0.00 %
show new master 0 0.00 0.00 %
show open tables 0 0.00 0.00 %
show privileges 0 0.00 0.00 %
show processlist 0 0.00 0.00 %
show slave hosts 0 0.00 0.00 %
show slave status 0 0.00 0.00 %
show status 1 0.25 0.00 %
show storage engines 0 0.00 0.00 %
show tables 6 1.50 0.00 %
show variables 5 1.25 0.00 %
show warnings 0 0.00 0.00 %
slave start 0 0.00 0.00 %
slave stop 0 0.00 0.00 %
truncate 0 0.00 0.00 %
unlock tables 8 2.01 0.01 %
update 2,414 605.01 1.68 %
update multi 0

Variables...
Variable Value
Binlog cache disk use 0
Binlog cache use 0
Created tmp disk tables 2755
Created tmp files 0
Created tmp tables 3460
Delayed errors 0
Delayed insert threads 0
Delayed writes 0
Flush commands 9
Handler commit 0
Handler delete 35
Handler discover 0
Handler read first 676
Handler read key 1925496
Handler read next 302404
Handler read prev 13538
Handler read rnd 182991
Handler read rnd next 584958448
Handler rollback 0
Handler update 706717
Handler write 121801

Variable Value
Key blocks not flushed 0
Key blocks unused 690084
Key blocks used 8576
Key read requests 4027499
Key reads 17189
Key write requests 60459
Key writes 998
Max used connections 24
Not flushed delayed rows 0
Open files 93
Open streams 0
Open tables 50
Opened tables 2071
Qcache free blocks 2
Qcache free memory 132592944
Qcache hits 117464
Qcache inserts 15094
Qcache lowmem prunes 0
Qcache not cached 754
Qcache queries in cache 670
Qcache total blocks 1382

Variable Value
Rpl status NULL
Select full join 1996
Select full range join 0
Select range 606
Select range check 0
Select scan 6257
Slave open temp tables 0
Slave running OFF
Slave retried transactions 0
Slow launch threads 12
Slow queries 49
Sort merge passes 0
Sort range 2040
Sort rows 194643
Sort scan 5102
Table locks immediate 27576
Table locks waited 44
Threads cached 22
Threads connected 2
Threads created 24
Threads running 1

My my.ini file...

[client]
port=3306

[mysqld]
port=3306
basedir="C:/mysql/"
datadir="C:/mysql/Data/"
default-character-set=latin1
default-storage-engine=MYISAM
max_connections=400

query_cache_size=128M
table_cache=1520
tmp_table_size=80M
thread_cache_size=512
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=128M
key_buffer_size=768M
read_buffer_size=8M
read_rnd_buffer_size=64M
sort_buffer_size=8M


skip-innodb
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=8M
innodb_log_file_size=10M
innodb_thread_concurrency=8

----------
Thanks! I've tried other forums but TT is always the best.

 

[doug04]

nevermind. I fixed it somehow. heres my new my.ini


[mysqld]
port=3306
basedir="C:/mysql/"
datadir="C:/mysql/Data/"
default-character-set=latin1
default-storage-engine=MYISAM
max_connections=400

query_cache_size=256M
table_cache=2000
tmp_table_size=80M
thread_cache_size=512
myisam_max_sort_file_size=10G
myisam_max_extra_sort_file_size=10G
myisam_sort_buffer_size=256M
key_buffer_size=768M
read_buffer_size=16M
read_rnd_buffer_size=64M
sort_buffer_size=8M


skip-innodb
#comment out all the innodb settings below this.