Hey All,
I'm about to put online a MySQL 4 database server on a dual Opteron boxen with 8G of RAM. The example my.cnf files do not seem to address this amount of hardware. What settings do you suggest for optimal use of that hardware?
The current server is a dual Xeon with 512M RAM. Here is the current MySQL 3 information:
Runtime Information
This MySQL server has been running for 21 days, 19 hours, 59 minutes and 1 seconds. It started up on Jan 19, 2004 at 02:21 AM.
* Server traffic: These tables show the network traffic statistics of this MySQL server since its startup.
Traffic ø per hour
Received 2,437 MB 4,762 KB
Sent 209,848 KB 410,097 Bytes
Total 2,641 MB 5,162 KB
Connections ø per hour %
Failed attempts 9 0.02 0.00 %
Aborted 555 1.06 0.07 %
Total 746,755 1,425.15 100,00 %
* Query statistics: Since its startup, 213,355,185 queries have been sent to the server.
Query type ø per hour %
admin commands 7,757 14.80 0.00 %
alter table 6 0.01 0.00 %
analyze 0 0.00 0.00 %
backup table 0 0.00 0.00 %
begin 0 0.00 0.00 %
change db 12,054,862 23,006.18 5.65 %
change master 0 0.00 0.00 %
check 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 358,572 684.32 0.17 %
delete 497,810 950.05 0.23 %
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 %
flush 2 0.00 0.00 %
grant 0 0.00 0.00 %
insert 4,496,627 8,581.62 2.11 %
insert select 515,688 984.17 0.24 %
kill 0 0.00 0.00 %
load 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 %
purge 0 0.00 0.00 %
rename table 0 0.00 0.00 %
Query type ø per hour %
repair 0 0.00 0.00 %
replace 0 0.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 %
rollback 0 0.00 0.00 %
select 187,473,692 357,785.41 87.87 %
set option 31 0.06 0.00 %
show binlogs 1 0.00 0.00 %
show create 31 0.06 0.00 %
show databases 101 0.19 0.00 %
show fields 298 0.57 0.00 %
show grants 0 0.00 0.00 %
show keys 85 0.16 0.00 %
show logs 0 0.00 0.00 %
show master status 3 0.01 0.00 %
show open tables 0 0.00 0.00 %
show processlist 92,014 175.60 0.04 %
show slave status 0 0.00 0.00 %
show status 4,322,943 8,250.15 2.03 %
show innodb status 0 0.00 0.00 %
show tables 947 1.81 0.00 %
show variables 508 0.97 0.00 %
slave start 0 0.00 0.00 %
slave stop 0 0.00 0.00 %
truncate 230,293 439.50 0.11 %
unlock tables 1 0.00 0.00 %
update 2,504,925 4,780.54 1.17 %
Total ø per hour ø per minute ø per second
213,355,185 407,179.12 6,786.32 113.11
* More status variables
Variable Value
Created tmp disk tables 53517943
Created tmp tables 58654158
Created tmp files 86104
Delayed insert threads 0
Delayed writes 0
Delayed errors 0
Flush commands 2
Handler delete 226745
Handler read first 3186383
Handler read key 1241773257
Handler read next 3048036899
Handler read prev 523512120
Handler read rnd 1806671616
Handler read rnd next 3548035223
Handler update 4014833219
Handler write 1801026349
Variable Value
Key blocks used 249376
Key read requests 2208631833
Key reads 9754936
Key write requests 589348041
Key writes 6085373
Max used connections 335
Not flushed key blocks 0
Not flushed delayed rows 0
Open tables 256
Open files 500
Open streams 0
Opened tables 2991269
Select full join 191399
Select full range join 51962
Select range 433102
Select range check 0
Variable Value
Select scan 11506764
Slave running OFF
Slave open temp tables 0
Slow launch threads 0
Slow queries 46712
Sort merge passes 44734
Sort range 10276622
Sort rows 2055863947
Sort scan 58998213
Table locks immediate 304573158
Table locks waited 3492544
Threads cached 7
Threads created 55742
Threads connected 158
Threads running 3
What other information about the current database sytem would help in determining what settings to use?
Thank you very much in advance and have a great day!!
Phade.
I'm about to put online a MySQL 4 database server on a dual Opteron boxen with 8G of RAM. The example my.cnf files do not seem to address this amount of hardware. What settings do you suggest for optimal use of that hardware?
The current server is a dual Xeon with 512M RAM. Here is the current MySQL 3 information:
Runtime Information
This MySQL server has been running for 21 days, 19 hours, 59 minutes and 1 seconds. It started up on Jan 19, 2004 at 02:21 AM.
* Server traffic: These tables show the network traffic statistics of this MySQL server since its startup.
Traffic ø per hour
Received 2,437 MB 4,762 KB
Sent 209,848 KB 410,097 Bytes
Total 2,641 MB 5,162 KB
Connections ø per hour %
Failed attempts 9 0.02 0.00 %
Aborted 555 1.06 0.07 %
Total 746,755 1,425.15 100,00 %
* Query statistics: Since its startup, 213,355,185 queries have been sent to the server.
Query type ø per hour %
admin commands 7,757 14.80 0.00 %
alter table 6 0.01 0.00 %
analyze 0 0.00 0.00 %
backup table 0 0.00 0.00 %
begin 0 0.00 0.00 %
change db 12,054,862 23,006.18 5.65 %
change master 0 0.00 0.00 %
check 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 358,572 684.32 0.17 %
delete 497,810 950.05 0.23 %
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 %
flush 2 0.00 0.00 %
grant 0 0.00 0.00 %
insert 4,496,627 8,581.62 2.11 %
insert select 515,688 984.17 0.24 %
kill 0 0.00 0.00 %
load 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 %
purge 0 0.00 0.00 %
rename table 0 0.00 0.00 %
Query type ø per hour %
repair 0 0.00 0.00 %
replace 0 0.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 %
rollback 0 0.00 0.00 %
select 187,473,692 357,785.41 87.87 %
set option 31 0.06 0.00 %
show binlogs 1 0.00 0.00 %
show create 31 0.06 0.00 %
show databases 101 0.19 0.00 %
show fields 298 0.57 0.00 %
show grants 0 0.00 0.00 %
show keys 85 0.16 0.00 %
show logs 0 0.00 0.00 %
show master status 3 0.01 0.00 %
show open tables 0 0.00 0.00 %
show processlist 92,014 175.60 0.04 %
show slave status 0 0.00 0.00 %
show status 4,322,943 8,250.15 2.03 %
show innodb status 0 0.00 0.00 %
show tables 947 1.81 0.00 %
show variables 508 0.97 0.00 %
slave start 0 0.00 0.00 %
slave stop 0 0.00 0.00 %
truncate 230,293 439.50 0.11 %
unlock tables 1 0.00 0.00 %
update 2,504,925 4,780.54 1.17 %
Total ø per hour ø per minute ø per second
213,355,185 407,179.12 6,786.32 113.11
* More status variables
Variable Value
Created tmp disk tables 53517943
Created tmp tables 58654158
Created tmp files 86104
Delayed insert threads 0
Delayed writes 0
Delayed errors 0
Flush commands 2
Handler delete 226745
Handler read first 3186383
Handler read key 1241773257
Handler read next 3048036899
Handler read prev 523512120
Handler read rnd 1806671616
Handler read rnd next 3548035223
Handler update 4014833219
Handler write 1801026349
Variable Value
Key blocks used 249376
Key read requests 2208631833
Key reads 9754936
Key write requests 589348041
Key writes 6085373
Max used connections 335
Not flushed key blocks 0
Not flushed delayed rows 0
Open tables 256
Open files 500
Open streams 0
Opened tables 2991269
Select full join 191399
Select full range join 51962
Select range 433102
Select range check 0
Variable Value
Select scan 11506764
Slave running OFF
Slave open temp tables 0
Slow launch threads 0
Slow queries 46712
Sort merge passes 44734
Sort range 10276622
Sort rows 2055863947
Sort scan 58998213
Table locks immediate 304573158
Table locks waited 3492544
Threads cached 7
Threads created 55742
Threads connected 158
Threads running 3
What other information about the current database sytem would help in determining what settings to use?
Thank you very much in advance and have a great day!!
Phade.