How to gain performance through MySQL

[Andreas Baier]

Hello,

First excuse my bad english, I'll try to give my best.

A few weeks ago, I changed amarok from svn-version to the current stable 
release (1.4.5 r1). I use gentoo-linux and amarok with mysql backend.

Now, when I change ratings, skip songs, amarok AND mysql are using 90-100% of 
cpu-time for more than 3 seconds and surface of amarok is freezing for this 
time (the music is played normally, no stuttering of sound or so).

This behaviour didn't exist under the svn-version.

I tested various settings in my.cnf without improvements of speed.

The system:
Our collestion has more than 20k of songs, the datadir of the amarok-tables 
got 42MB. The datadir is on an extra-device (XFS-filesystem/RAID 0). My 
workstation has more than 6Gb of ram. There are a lot of other dbs in mysql, 
but these are testing-dbs for web-applications and rarely used.

It would be great, if anyone has some tips for me to turn this behaviour off.
greetings Andreas

Here's the current my.cnf:

# /etc/mysql/my.cnf: The global mysql configuration file.
# $Header: /var/cvsroot/gentoo-x86/dev-db/mysql/files/my.cnf-4.1,v 1.3 
2006/05/05 19:51:40 chtekk Exp $

[mysql]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8

[mysqladmin]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8

[mysqlcheck]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8

[mysqldump]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8

[mysqlimport]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8

[mysqlshow]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8

[myisamchk]
character-sets-dir=/usr/share/mysql/charsets

[myisampack]
character-sets-dir=/usr/share/mysql/charsets

[mysqld_safe]
err-log                                         = /var/log/mysql/mysql.err

[mysqld]
character-set-server            = utf8
default-character-set           = utf8
user                                            = mysql
port                                            = 6000
socket                                          = /var/run/mysqld/mysqld.sock
pid-file                                        = /var/run/mysqld/mysqld.pid
log-error                                       = /var/log/mysql/mysqld.err
basedir                                         = /usr
datadir                                         
= //srv/localhost/mysql/datadir

skip-locking

key_buffer                                      = 64M
max_allowed_packet                      = 16M
table_cache                             = 8M
sort_buffer_size                        = 8M
net_buffer_length                       = 4M
read_buffer_size                        = 4M
read_rnd_buffer_size            = 4M
myisam_sort_buffer_size         = 16M
language                                        = /usr/share/mysql/german

set-variable=local-infile=0

log-bin
server-id                                       = 1

tmpdir                                          = /var/tmp/mysql #RAMDISK

skip-innodb

[mysqldump]
quick
max_allowed_packet                      = 16M

[mysql]
[isamchk]
key_buffer                                      = 32M
sort_buffer_size                        = 32M
read_buffer                             = 4M
write_buffer                            = 4M

[myisamchk]
key_buffer                                      = 64M
sort_buffer_size                        = 64M
read_buffer                             = 16M
write_buffer                            = 16M
[mysqlhotcopy]
interactive-timeout