Hello mysql,
I'm repeating my test's now with more information and on more
powerfull maching with no load now, this should give more accurate
results.
The machine is Dual PIII-750/1GB RAM
I'm trying the following table structure:
CREATE TABLE g00hits (
id int(10) unsigned NOT NULL auto_increment,
counter_id int(10) unsigned NOT NULL default '0',
visitor_id int(10) unsigned NOT NULL default '0',
server_id smallint(5) unsigned NOT NULL default '0',
ip int(10) unsigned NOT NULL default '0',
ts timestamp(14) NOT NULL,
method_id tinyint(3) unsigned NOT NULL default '0',
http_ver_id tinyint(3) unsigned NOT NULL default '0',
page_id int(10) unsigned NOT NULL default '0',
referer_page_id int(10) unsigned NOT NULL default '0',
status smallint(5) unsigned NOT NULL default '0',
bytes int(10) unsigned NOT NULL default '0',
browser_id mediumint(8) unsigned NOT NULL default '0',
language smallint(5) unsigned NOT NULL default '0',
local_visitor_id bigint(10) unsigned NOT NULL default '0',
process_time int(10) unsigned NOT NULL default '0',
proxy_software_id mediumint(8) unsigned NOT NULL default '0',
proxy_client_ip int(10) unsigned NOT NULL default '0',
auth_user_id int(10) unsigned NOT NULL default '0',
flag int(10) unsigned NOT NULL default '0',
session_id int(10) unsigned NOT NULL default '0',
doc_type smallint(5) unsigned NOT NULL default '0',
online_users smallint(5) unsigned NOT NULL default '0',
src_id int(10) unsigned NOT NULL default '0',
PRIMARY KEY (counter_id,ts,id),
KEY visitor_id (visitor_id)
) TYPE=MyISAM PACK_KEYS=1 DELAY_KEY_WRITE=1;
which has:
mysql> select count(*) from g00hits;
+----------+
| count(*) |
+----------+
| 3722867 |
+----------+
1 row in set (0.01 sec)
MYD file takes 300MB, text output 400MB
With mysqldump which uses the following query to do this:
SELECT * INTO OUTFILE '/spylog/test/g00hits.txt' FROM g00hits
It takes:
real 1m23.177s (first time)
real 1m20.934s (second time)
The backup table for the same table takes:
first time:
mysql> backup table g00hits to '/spylog/test';
+--------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+--------+----------+----------+
| la00.g00hits | backup | status | OK |
+--------------+--------+----------+----------+
1 row in set (22.60 sec)
second time:
mysql> backup table g00hits to '/spylog/test';
+--------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+--------+----------+----------+
| la00.g00hits | backup | status | OK |
+--------------+--------+----------+----------+
1 row in set (21.34 sec)
Well. We have 4 times difference here, which is a bit strange as I
would expect the disk speed should be the limit here, but not
converting the rows in text format, which is in this case done at
speed of 45000rows/second, which is not really much I think for this
type of operation. Hope mysql does not use strcat to form the strings?
Well. Let's try to do the restore:
mysql> restore table g00hits from '/spylog/test';
+--------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| test.g00hits | restore | status | OK |
+--------------+---------+----------+----------+
1 row in set (1 min 31.16 sec)
and again:
mysql> restore table g00hits from '/spylog/test';
+--------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| test.g00hits | restore | status | OK |
+--------------+---------+----------+----------+
1 row in set (1 min 30.41 sec)
mysql> load data infile '/spylog/test/g00hits.txt' ignore into table g00hits;
Query OK, 3722867 rows affected (8 min 35.73 sec)
mysql> load data infile '/spylog/test/g00hits.txt' ignore into table g00hits;
Query OK, 3722867 rows affected (3 min 35.26 sec)
So it seems like cache really matters here, this would indicate bad
disk access way as this difference is to huge and is not found with
backup/restore table.
Now without ignore:
mysql> load data infile '/spylog/test/g00hits.txt' into table g00hits;
Query OK, 3722867 rows affected (3 min 34.75 sec)
So it seems like it does not really matter.
Well let's drop all indexes and try to load data fastest way ?
mysql> load data infile '/spylog/test/g00hits.txt' ignore into table g00hits;
Query OK, 3722867 rows affected (1 min 56.16 sec)
Records: 3722867 Deleted: 0 Skipped: 0 Warnings: 0
mysql> load data infile '/spylog/test/g00hits.txt' into table g00hits;
Query OK, 3722867 rows affected (1 min 56.38 sec)
Records: 3722867 Deleted: 0 Skipped: 0 Warnings: 0
mysql> alter table g00hits add primary key(counter_id,ts,id),add key(visitor_id);
Query OK, 3722867 rows affected (1 min 54.38 sec)
Records: 3722867 Duplicates: 0 Warnings: 0
So it does not give any improvement do add keys after loading date, it
seems to be allready optimal.
So the diference in restore speed is about 3 times, which is not
really expected, but a bit better then I reported before, but anyway I
can't understand so huge difference for load data then text file fits
to cache or does not. Well may be it was because of some other random
events - I was not able to reproduce so slow speed anymore.
also look at the difference of vmstat between restore and load data:
restore:
0 1 1 181624 42456 196488 206936 0 0 2823 4208 1126 1151 0 25 74
0 1 0 181624 24472 222220 199332 0 0 3411 3903 1144 1273 0 25 74
1 0 1 181624 30640 104856 268544 0 0 7030 371 682 792 29 24 47
1 0 0 181624 30636 104856 268544 0 0 0 0 104 115 50 0 50
1 0 0 181624 21108 104848 269308 0 0 0 0 104 114 50 0 49
So the file is copied quickly and then indexes are generated so one
CPU is completely out:
LOAD DATA:
2 1 0 181624 75816 166460 180356 0 0 425 223 411 8798 29 6 65
0 1 0 181624 68256 168944 185312 0 0 435 407 608 2827 10 5 85
1 1 0 181624 63676 171668 189108 0 0 388 363 656 7937 19 7 74
1 0 0 181624 23976 104856 245228 0 0 0 0 106 116 50 0 50
1 0 0 181624 23976 104856 245228 0 0 0 0 104 113 50 0 49
1 0 0 181624 29980 131516 212568 0 0 0 0 104 114 48 3 50
as you see the CPU is not really used, therefore the disk access is
not so huge, this may indicate the disk is bein accessed in random
order, therefore it should not i think. This may indicate to small
buffer used (?) my record_buffer is set to 512K by the way.
--
Best regards,
Peter mailto:[EMAIL PROTECTED]
---------------------------------------------------------------------
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
