Hi,
I have tried to upgrade from MySQL 3.23.51 to 4.0.2-alpha (both source
distribution, same environment, same compiler, same configuration file)
and I was puzzled to see that my application became 5 to 10 times slower.
Most queries in my application (at least the critical part) are write
queries (UPDATEs, INSERTs, REPLACEs). I have isolated a simple example
of a query which has become really slower - but other, bigger or more
complex queries show the same slowdown.
Just do the following under 3.23.51 :
CREATE TABLE route_state_change (
date BIGINT UNSIGNED NOT NULL,
date_end BIGINT UNSIGNED NOT NULL,
duration BIGINT UNSIGNED NOT NULL,
route_k BIGINT UNSIGNED NOT NULL,
state ENUM ('down', 'up') NOT NULL,
KEY date (date),
KEY route (route_k, date_end),
KEY state (state));
Then try the following query (with the mysql client) :
INSERT route_state_change (date, route_k, state) VALUES
(10297598884803,0x84F00F09383D4C11,'down'),
(10297598884803,0xCE7E157EC14416A5,'down'),
(10297598884803,0xD665AB1D45B0A608,'down'),
(10297598884803,0xFD940CAC8CFD55A6,'down'),
(10297598884803,0x3DDB9286093F33C6,'down'),
(10297598884803,0xEC1D5ADC0D3C5DBB,'down'),
(10297598884803,0x51CB867E525127CC,'down'),
(10297598884803,0xCAA6EFB009DC2A02,'down'),
(10297598884803,0xF649CFA95CEDA18C,'down');
(just a plain example from my app)
In 3.23.51, it always takes 0.00 sec (even repeated several times).
After switching to 4.0.2, it always takes 0.08 sec. Which seems huge given
that we have a nearly empty table and the query is rather simple !
Here is the config file /etc/my.cnf :
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
skip-innodb
set-variable = key_buffer=512M
set-variable = max_allowed_packet=1M
set-variable = table_cache=1024
set-variable = sort_buffer=64M
set-variable = join_buffer_size=32M
set-variable = record_buffer=32M
set-variable = thread_cache=8
set-variable = tmp_table_size=256M
set-variable = max_heap_table_size=128M
set-variable = myisam_sort_buffer_size=64M
server-id = 1
[mysql]
no-auto-rehash
The environment is :
System: Linux <machine name> 2.4.19 #3 SMP Fri Aug 9 15:18:06 CEST 2002 i686
unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/ccGCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
gcc version 2.96 20000731 (Red Hat Linux 7.3 2.96-110)
Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Jul 18 13:18 /lib/libc.so.6 ->
libc-2.2.5.so
-rwxr-xr-x 1 root root 1260480 Apr 15 15:44 /lib/libc-2.2.5.so
-rw-r--r-- 1 root root 2310808 Apr 15 16:02 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Apr 15 15:55 /usr/lib/libc.so
Configure command: ./configure --localstatedir=/data/mysql --with-innodb
Regards
Antoine.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
