Hi folks. I'm in the midst of moving from a debian unstable system to a
gentoo system and I'm having some problems getting mysql to give me the
same performance. On the new gentoo system (with more hardware) a fairly
complex query (a search on a UBBThreads forum) is taking 10+ seconds to
complete, while on the debian system it's in the 0.01s range. Before
I'm critisized on my distro choice, it appears I've traced part of the
problem down with EXPLAIN:
(sorry about the width :( )
New but slower system:
+-------+--------+--------------------------------------------------------+-----------------+---------+---------------+------+-----------------------------+
| table | type | possible_keys | key
| key_len | ref | rows | Extra |
+-------+--------+--------------------------------------------------------+-----------------+---------+---------------+------+-----------------------------+
| t1 | ref | w3t_Postsindex7,w3t_Postsindex8,ID_ndx,board_topic_ndx |
w3t_Postsindex7 | 3 | const | 6607 | Using where; Using filesort |
| t2 | eq_ref | indx1 | indx1
| 100 | t1.B_Board | 1 | |
| t3 | eq_ref | PRIMARY,indx3 | PRIMARY
| 4 | t1.B_PosterId | 1 | |
+-------+--------+--------------------------------------------------------+-----------------+---------+---------------+------+-----------------------------+
Old but faster system:
+-------+--------+--------------------------------------------------------+-----------------+---------+---------------+-------+-------------+
| table | type | possible_keys | key
| key_len | ref | rows | Extra |
+-------+--------+--------------------------------------------------------+-----------------+---------+---------------+-------+-------------+
| t1 | range | w3t_Postsindex7,w3t_Postsindex8,ID_ndx,board_topic_ndx |
w3t_Postsindex8 | 4 | NULL | 19645 | Using where |
| t2 | eq_ref | indx1 | indx1
| 100 | t1.B_Board | 1 | |
| t3 | ref | PRIMARY,indx3 | indx3
| 4 | t1.B_PosterId | 1 | |
+-------+--------+--------------------------------------------------------+-----------------+---------+---------------+-------+-------------+
The big thing here as I've read and understood it is that the gentoo
system is "Using filesort", which is horribly slow compared to plain old
where and indexes.
However, I've made no changes to the database files, just copied
/var/lib/mysql/<database> from the old system to the new. The faster
box is a 4.0.14 system and the slower is using 4.0.16.
Based on my reading of some of the docs on mysql.com using filesort is
used when mysql can't use indexes for the order by clause. Would these
indexes not be there and still available when moved to the new system?
It seems very strange to me. Oh, and I also tried dumping just that
database and re-importing it from the (sql) dump file, with the same
results.
Please help!
BTW, specs on the systems:
Old: debian unstable running linux 2.4.24 with mysql 4.0.14
XP1800 with 1G ram on two IDE drives with software RAID1
New: gentoo stable, kernels used were 2.4.24, 2.4.25_pre6, and 2.6.1
with and without preempt. Mysql tried 4.0.16 static and dynamic
with various cflags and 4.0.17 binaries from mysql.com. my.cnf has
been set to the same as o nthe old box, the default config, and the
huge, large and medium sample configs, all with the same results.
Many thanks.
alan
--
Alan <[EMAIL PROTECTED]> - http://arcterex.net
--------------------------------------------------------------------
"There are only 3 real sports: bull-fighting, car racing and mountain
climbing. All the others are mere games." -- Hemingway
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
