Antoine,
Monday, August 19, 2002, 3:57:50 PM, you wrote:
A> I have tried to upgrade from MySQL 3.23.51 to 4.0.2-alpha (both source
A> distribution, same environment, same compiler, same configuration file)
A> and I was puzzled to see that my application became 5 to 10 times slower.
A> Most queries in my application (at least the critical part) are write
A> queries (UPDATEs, INSERTs, REPLACEs). I have isolated a simple example
A> of a query which has become really slower - but other, bigger or more
A> complex queries show the same slowdown.
A> Just do the following under 3.23.51 :
A> CREATE TABLE route_state_change (
A> date BIGINT UNSIGNED NOT NULL,
A> date_end BIGINT UNSIGNED NOT NULL,
A> duration BIGINT UNSIGNED NOT NULL,
A> route_k BIGINT UNSIGNED NOT NULL,
A> state ENUM ('down', 'up') NOT NULL,
A> KEY date (date),
A> KEY route (route_k, date_end),
A> KEY state (state));
A> Then try the following query (with the mysql client) :
A> INSERT route_state_change (date, route_k, state) VALUES
A> (10297598884803,0x84F00F09383D4C11,'down'),
A> (10297598884803,0xCE7E157EC14416A5,'down'),
A> (10297598884803,0xD665AB1D45B0A608,'down'),
A> (10297598884803,0xFD940CAC8CFD55A6,'down'),
A> (10297598884803,0x3DDB9286093F33C6,'down'),
A> (10297598884803,0xEC1D5ADC0D3C5DBB,'down'),
A> (10297598884803,0x51CB867E525127CC,'down'),
A> (10297598884803,0xCAA6EFB009DC2A02,'down'),
A> (10297598884803,0xF649CFA95CEDA18C,'down');
A> (just a plain example from my app)
A> In 3.23.51, it always takes 0.00 sec (even repeated several times).
A> After switching to 4.0.2, it always takes 0.08 sec. Which seems huge given
A> that we have a nearly empty table and the query is rather simple !
I tested your example on 4.0.2, 3.23.51, 3.23.52 and got on all
versions the same result 0.00-0.01 sec
A> Here is the config file /etc/my.cnf :
A> [client]
A> port = 3306
A> socket = /tmp/mysql.sock
A> [mysqld]
A> port = 3306
A> socket = /tmp/mysql.sock
A> skip-locking
A> skip-innodb
A> set-variable = key_buffer=512M
A> set-variable = max_allowed_packet=1M
A> set-variable = table_cache=1024
A> set-variable = sort_buffer=64M
A> set-variable = join_buffer_size=32M
A> set-variable = record_buffer=32M
A> set-variable = thread_cache=8
A> set-variable = tmp_table_size=256M
A> set-variable = max_heap_table_size=128M
A> set-variable = myisam_sort_buffer_size=64M
A> server-id = 1
Do you use so huge queries that you really need sort_buffer=64M and
record_buffer=32M? Don't forget that record_buffer and sort_buffer are
specified per-thread.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
---------------------------------------------------------------------
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
