Hi, me again
I feel a bit guilty posting so many questions up here but you guys have saved me from so many hair-pulling episodes it always seems like the best place to start.
I've been gradually trouble-shooting slow queries on the MySQL/PHP website I run - - Basically a script harvests all queries that take over 1 second to execute and saves them (with other bits of info) to a table.
It nows seems like the queries are no longer appearing on the top of the table for reasons such as badly configured indexes, or other syntax problems. I'm guessing that the main problem is that at certain times of the day the number of users is causing the MySQL engine to overload, so queries are queuing, and taking a long to execute.
Below is the variables from my MySQL engine - if any of these seem particular bad for supporting a website that can have 100 simulatenous users on it please let me know.
Thank you again
Tama
Variable Value
back log 50
basedir /
binlog cache size 32768
character set latin1
character sets latin1 big5 czech euc_kr gb2312 gbk sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5
concurrent insert ON
connect timeout 5
datadir /home/mysql/
delay key write ON
delayed insert limit 100
delayed insert timeout 300
delayed queue size 1000
flush OFF
flush time 0
have bdb NO
have gemini NO
have innodb NO
have isam YES
have raid NO
have openssl NO
init file
interactive timeout 60
join buffer size 131072
key buffer size 104853504
language /usr/share/mysql/english/
large files support ON
locked in memory OFF
log OFF
log update OFF
log bin OFF
log slave updates OFF
log long queries OFF
long query time 10
low priority updates OFF
lower case table names 0
max allowed packet 1047552
max binlog cache size 4294967295
max binlog size 1073741824
max connections 100
max connect errors 10
max delayed threads 20
max heap table size 16777216
max join size 4294967295
max sort length 1024
max user connections 0
max tmp tables 32
max write lock count 4294967295
myisam max extra sort file size 256
myisam max sort file size 2047
myisam recover options 0
myisam sort buffer size 8388608
net buffer length 15360
net read timeout 30
net retry count 10
net write timeout 60
open files limit 0
pid file /home/mysql/mondo.vorb.org.nz.pid
port 3306
protocol version 10
record buffer 131072
record rnd buffer 131072
query buffer size 0
safe show database OFF
server id 0
slave net timeout 3600
skip locking ON
skip networking OFF
skip show database OFF
slow launch time 2
socket /var/lib/mysql/mysql.sock
sort buffer 524280
sql mode 0
table cache 128
table type MYISAM
thread cache size 0
thread stack 65536
transaction isolation READ-COMMITTED
timezone NZDT
tmp table size 33554432
tmpdir /tmp/
version 3.23.49
wait timeout 60
I feel a bit guilty posting so many questions up here but you guys have saved me from so many hair-pulling episodes it always seems like the best place to start.
I've been gradually trouble-shooting slow queries on the MySQL/PHP website I run - - Basically a script harvests all queries that take over 1 second to execute and saves them (with other bits of info) to a table.
It nows seems like the queries are no longer appearing on the top of the table for reasons such as badly configured indexes, or other syntax problems. I'm guessing that the main problem is that at certain times of the day the number of users is causing the MySQL engine to overload, so queries are queuing, and taking a long to execute.
Below is the variables from my MySQL engine - if any of these seem particular bad for supporting a website that can have 100 simulatenous users on it please let me know.
Thank you again
Tama
Variable Value
back log 50
basedir /
binlog cache size 32768
character set latin1
character sets latin1 big5 czech euc_kr gb2312 gbk sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5
concurrent insert ON
connect timeout 5
datadir /home/mysql/
delay key write ON
delayed insert limit 100
delayed insert timeout 300
delayed queue size 1000
flush OFF
flush time 0
have bdb NO
have gemini NO
have innodb NO
have isam YES
have raid NO
have openssl NO
init file
interactive timeout 60
join buffer size 131072
key buffer size 104853504
language /usr/share/mysql/english/
large files support ON
locked in memory OFF
log OFF
log update OFF
log bin OFF
log slave updates OFF
log long queries OFF
long query time 10
low priority updates OFF
lower case table names 0
max allowed packet 1047552
max binlog cache size 4294967295
max binlog size 1073741824
max connections 100
max connect errors 10
max delayed threads 20
max heap table size 16777216
max join size 4294967295
max sort length 1024
max user connections 0
max tmp tables 32
max write lock count 4294967295
myisam max extra sort file size 256
myisam max sort file size 2047
myisam recover options 0
myisam sort buffer size 8388608
net buffer length 15360
net read timeout 30
net retry count 10
net write timeout 60
open files limit 0
pid file /home/mysql/mondo.vorb.org.nz.pid
port 3306
protocol version 10
record buffer 131072
record rnd buffer 131072
query buffer size 0
safe show database OFF
server id 0
slave net timeout 3600
skip locking ON
skip networking OFF
skip show database OFF
slow launch time 2
socket /var/lib/mysql/mysql.sock
sort buffer 524280
sql mode 0
table cache 128
table type MYISAM
thread cache size 0
thread stack 65536
transaction isolation READ-COMMITTED
timezone NZDT
tmp table size 33554432
tmpdir /tmp/
version 3.23.49
wait timeout 60