How to gain performance through MySQL

[Andreas Baier]

Hello,

first thanks for your posts.
> > in your initial mail, i noticed :
> > # /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
> > but that's probably some internal cvs version or something, right ?
> > could it be that you have updated mysql from 4.x at some point recently ?
No, I made the upgrade months ago. But yes, the file is old. To be sure, I 
made a diff with the current default-config a moment ago, and the syntax must 
be ok.
> > you could try looking at the db (especially statistics table), and see
> > what type does 'url' has.
> > if it is "varchar", try to change it to varbinary(255) and see whether
> > that helps a bit.
mysql> DESCRIBE statistics;
+-------------+----------------+------+-----+---------+-------+
| Field       | Type           | Null | Key | Default | Extra |
+-------------+----------------+------+-----+---------+-------+
| url         | varbinary(255) | NO   | PRI |         |       |
| deviceid    | int(11)        | NO   | PRI | 0       |       |
| createdate  | int(11)        | YES  |     | NULL    |       |
| accessdate  | int(11)        | YES  |     | NULL    |       |
| percentage  | float          | YES  | MUL | NULL    |       |
| rating      | int(11)        | YES  | MUL | 0       |       |
| playcounter | int(11)        | YES  | MUL | NULL    |       |
| uniqueid    | varbinary(32)  | YES  | UNI | NULL    |       |
| deleted     | tinyint(1)     | YES  |     | 0       |       |
+-------------+----------------+------+-----+---------+-------+
A good idea, but they're already varbinary.

> If there was a file system upgrade of Mysql 4 to 5 then it can have some
> odd effects with varbinary fields. They will be padded to the full
> lenght of the field.
As far as I know, I made a fresh amarok db after the upgrade to release 5.*. 

Apart from that, I made a fresh install of the amarok-db a few hours ago.
-> same issues. So I installed the old database again.

But apropos file system. I made a filesystem migration a few weeks ago. I 
changed the fs where the database is installed from Ext3 to XFS.
Does anybody know if this could cause the problem?

I had migrated all patitions (except a few exceptions) to XFS, and the system 
feels in any other situation (except mysql) as fast as it should be.

greetings Andreas