I am just getting in to doing some minor admin work on our mysql database. Ive been reading from the mysql online manual and also the mysql chapter in 'PHP and MySQL Web Development' by SAMS.
heres what I have come up with, but im far from sure i am doing it right. I suspect I am skipping important steps and also duplicating some...
heres my basic gameplan where tablename is X and when no apps are hitting the db and Im all alone with mysql
1) myisamchk X
2) myisamchk -r X if it needs it
3) mysql 'show index from X'
4) myisamchk --sort-index --sort-record=1 (if that table has an index)
5) mysql 'optimize table x' <-- is this redundant?? no clue what it does
6) myisamchk --analyze X
-- What am i missing?
-- Am i duplicating effort and can remove commands?
-- Should i do these steps in a different order??
Part 2 - Memory usage in myisamchk.
I cant get this sucker to use my ram. when i use
myisamchk --force -O key_buffer=256M -O sort_buffer=256M -O read_buffer=8M -O write_buffer=8M myTable
on a machine that has 2 gigs of ram, as far as i can tell myisamchk is still just using 3meg or so. heres a few lines from top:
last pid: 25102; load averages: 0.55, 0.51, 0.55 18:19:46
89 processes: 88 sleeping, 1 on cpu
CPU states: 70.6% idle, 2.9% user, 3.9% kernel, 22.6% iowait, 0.0% swap
Memory: 2048M real, 31M free, 1672M swap in use, 1914M swap free
PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
18345 root 3 60 0 2472K 1320K sleep 3:04 2.85% myisamchk
some of these tables are 15gig - how can i get myisamchk to use more memory?? how can i tell if its using the memory i told it to other than using 'top'?
any help with basic mysql optimization, tuning, admin, repair type stuff is greatly appreciated be it a post or links.
For example, you can give -O sort_buffer=xxx commands to mysqld, or to myisamchk. if i want myisamchk to use 50 megs, can i just tell it to use 50 megs or do i also have to change it for mysqld and restart? can the amounts i tell myisamchk to use exceed what mysqld was started with??
gedanken
heres what I have come up with, but im far from sure i am doing it right. I suspect I am skipping important steps and also duplicating some...
heres my basic gameplan where tablename is X and when no apps are hitting the db and Im all alone with mysql
1) myisamchk X
2) myisamchk -r X if it needs it
3) mysql 'show index from X'
4) myisamchk --sort-index --sort-record=1 (if that table has an index)
5) mysql 'optimize table x' <-- is this redundant?? no clue what it does
6) myisamchk --analyze X
-- What am i missing?
-- Am i duplicating effort and can remove commands?
-- Should i do these steps in a different order??
Part 2 - Memory usage in myisamchk.
I cant get this sucker to use my ram. when i use
myisamchk --force -O key_buffer=256M -O sort_buffer=256M -O read_buffer=8M -O write_buffer=8M myTable
on a machine that has 2 gigs of ram, as far as i can tell myisamchk is still just using 3meg or so. heres a few lines from top:
last pid: 25102; load averages: 0.55, 0.51, 0.55 18:19:46
89 processes: 88 sleeping, 1 on cpu
CPU states: 70.6% idle, 2.9% user, 3.9% kernel, 22.6% iowait, 0.0% swap
Memory: 2048M real, 31M free, 1672M swap in use, 1914M swap free
PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
18345 root 3 60 0 2472K 1320K sleep 3:04 2.85% myisamchk
some of these tables are 15gig - how can i get myisamchk to use more memory?? how can i tell if its using the memory i told it to other than using 'top'?
any help with basic mysql optimization, tuning, admin, repair type stuff is greatly appreciated be it a post or links.
For example, you can give -O sort_buffer=xxx commands to mysqld, or to myisamchk. if i want myisamchk to use 50 megs, can i just tell it to use 50 megs or do i also have to change it for mysqld and restart? can the amounts i tell myisamchk to use exceed what mysqld was started with??
gedanken