# Need help tuning MYSQL, please.
**URL:** https://forums.percona.com/t/need-help-tuning-mysql-please/1110
**Category:** Other MySQL® Questions
**Created:** [March 13, 2009, 2:33am UTC](https://forums.percona.com/t/need-help-tuning-mysql-please/1110 "2009-03-13T02:33:11Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@gemino](https://forums.percona.com/u/gemino)
#### Post date: [March 13, 2009, 2:33am UTC](https://forums.percona.com/t/need-help-tuning-mysql-please/1110/1 "2009-03-13T02:33:11Z")
Hello everyone,
I’m hoping someone from this forum to help me quick solve our server problem.
Long story short, our old DB admin/designer leave our company last year. And since then we don’t really have a DB admin.
Which is fine, at least the server is running all good without him till few weeks ago. All the queues takes 10 to 20 times longer to get get result.
I’m guessing the config need some kind of fine tune after a year.
Please feel free to let us know what we need to do. 😉
Thank you!
The server is still on a P4 631 with 2G ram. Linux 2.4.20-8. MySQL 3.23
About 4G data, tables range from 300MB to 1MB.
Read/Write ratio is 0.002
MyISAM
Here’s the setting:
| [B]Quote:[/B] |
| # The MySQL server [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-locking set-variable = key\_buffer=384M set-variable = max\_allowed\_packet=3M # Modified on 2007-10-23 #set-variable = max\_allowed\_packet=1M set-variable = table\_cache=512 set-variable = sort\_buffer=2M set-variable = record\_buffer=2M set-variable = thread\_cache=8 # Try number of CPU's\*2 for thread\_concurrency set-variable = thread\_concurrency=8 set-variable = myisam\_sort\_buffer\_size=64M #log-bin server-id = 1
log-update = /home/mysql/log/mysqllog
log-long-format
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname
[mysqldump]
quick
#set-variable = max\_allowed\_packet=16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
set-variable = key\_buffer=256M
set-variable = sort\_buffer=256M
set-variable = read\_buffer=2M
set-variable = write\_buffer=2M
[myisamchk]
set-variable = key\_buffer=256M
set-variable = sort\_buffer=256M
set-variable = read\_buffer=2M
set-variable = write\_buffer=2M
[mysqlhotcopy]
interactive-timeout
[safe\_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
|
---
### Author:  [@MarkRose](https://forums.percona.com/u/MarkRose)
#### Post date: [March 13, 2009, 3:54pm UTC](https://forums.percona.com/t/need-help-tuning-mysql-please/1110/2 "2009-03-13T15:54:10Z")
Login to MySQL and run:
show status;
And paste the results here. Please make sure MySQL has been running under load for a few days first.
---
### Author:  [@gemino](https://forums.percona.com/u/gemino)
#### Post date: [March 17, 2009, 10:32pm UTC](https://forums.percona.com/t/need-help-tuning-mysql-please/1110/3 "2009-03-17T22:32:43Z")
here’s my showstatus:
| [B]Quote:[/B] |
|
Variable\_name Value
Aborted\_clients 1
Aborted\_connects 0
Bytes\_received 29121325
Bytes\_sent 219271982
Com\_admin\_commands 290
Com\_alter\_table 0
Com\_analyze 0
Com\_backup\_table 0
Com\_begin 0
Com\_change\_db 29
Com\_change\_master 0
Com\_check 0
Com\_commit 0
Com\_create\_db 0
Com\_create\_function 0
Com\_create\_index 0
Com\_create\_table 0
Com\_delete 194
Com\_drop\_db 0
Com\_drop\_function 0
Com\_drop\_index 0
Com\_drop\_table 0
Com\_flush 0
Com\_grant 0
Com\_insert 747
Com\_insert\_select 0
Com\_kill 0
Com\_load 0
Com\_load\_master\_table 0
Com\_lock\_tables 0
Com\_optimize 0
Com\_purge 0
Com\_rename\_table 0
Com\_repair 0
Com\_replace 0
Com\_replace\_select 0
Com\_reset 0
Com\_restore\_table 0
Com\_revoke 0
Com\_rollback 0
Com\_select 68527
Com\_set\_option 0
Com\_show\_binlogs 0
Com\_show\_create 0
Com\_show\_databases 2
Com\_show\_fields 0
Com\_show\_grants 0
Com\_show\_keys 0
Com\_show\_logs 0
Com\_show\_master\_status 0
Com\_show\_open\_tables 0
Com\_show\_processlist 2
Com\_show\_slave\_status 0
Com\_show\_status 6
Com\_show\_innodb\_status 0
Com\_show\_tables 11
Com\_show\_variables 4
Com\_slave\_start 0
Com\_slave\_stop 0
Com\_truncate 0
Com\_unlock\_tables 89
Com\_update 1305
Connections 188
Created\_tmp\_disk\_tables 0
Created\_tmp\_tables 835
Created\_tmp\_files 0
Delayed\_insert\_threads 0
Delayed\_writes 0
Delayed\_errors 0
Flush\_commands 1
Handler\_delete 119
Handler\_read\_first 1493
Handler\_read\_key 2675458
Handler\_read\_next 64982270
Handler\_read\_prev 0
Handler\_read\_rnd 327571
Handler\_read\_rnd\_next 21850579
Handler\_update 1115
Handler\_write 75286
Key\_blocks\_used 24520
Key\_read\_requests 10103800
Key\_reads 24453
Key\_write\_requests 4673
Key\_writes 4265
Max\_used\_connections 41
Not\_flushed\_key\_blocks 0
Not\_flushed\_delayed\_rows 0
Open\_tables 84
Open\_files 157
Open\_streams 0
Opened\_tables 90
Questions 71137
Select\_full\_join 0
Select\_full\_range\_join 0
Select\_range 7667
Select\_range\_check 0
Select\_scan 6864
Slave\_running OFF
Slave\_open\_temp\_tables 0
Slow\_launch\_threads 0
Slow\_queries 6884
Sort\_merge\_passes 0
Sort\_range 2868
Sort\_rows 327568
Sort\_scan 1872
Table\_locks\_immediate 108415
Table\_locks\_waited 18
Threads\_cached 0
Threads\_created 42
Threads\_connected 42
Threads\_running 1
Uptime 49930
|
Uptime is low, because the cornjob resstart mysqld every night.
I’m thinking the other possible reason we have that many slow\_queries is because our hard drive is not the DMA mode is not on after a reset… but not 100% confirmed yet.
Or maybe it was never on since day one…
| [B]Quote:[/B] |
| # /sbin/hdparm -d1 /dev/hdc
/dev/hdc:
setting using\_dma to 1 (on)
HDIO\_SET\_DMA failed: Operation not permitted
using\_dma = 0 (off)
|
---
### Author:  [@MarkRose](https://forums.percona.com/u/MarkRose)
#### Post date: [March 18, 2009, 6:32am UTC](https://forums.percona.com/t/need-help-tuning-mysql-please/1110/4 "2009-03-18T06:32:48Z")
Your server has only run 71137 queries. It’s pretty lightly loaded. However, a lot of the queries it’s running are forcing full table scans because of lacking indexes. Select\_range and Select\_scan show that over 14,000 queries required table scans – meaning part or all of the table had to be read to satisfy the query. This probably occured quickly when there wasn’t much data, but as the tables got larger, this has taken more and more time.
You need to look at the queries being run and figure out how to add the appropriate indexes. If you enable the MySQL slow log, it’s a good place to start looking.