Hi!
I tried a test similar to Peter. There is a table with 3 integer
columns. There is a primary key on the first column and a non-unique
key on the second column. I wrote a Perl program which inserts rows
one at a time, and also selects rows one at a time.
I ran the tests on a Linux 2-CPU Xeon 450 MHz. The times below are wall
clock times.
The results were:
MyISAM Innobase
------------------------------------------------------------
100 000 inserts 40 s. 25 s.
100 000 selects on
primary key 58 s. 57 s.
100 000 selects on
secondary key 95 s. 68 s.
------------------------------------------------------------
It is unusual for a database that a select is slower than an insert. There is
probably slowness in the communication between the client and the server
in the select case. Another possible explanation is that query optimization
takes time in a select. Innobase can do some 100 000 searches per second
when it calculates a count(*) on a large join. Thus the slowness cannot
come directly from the access method. It must have something to
do with query parsing and initialization, or more probably, communication.
Also the Perl code seems to take almost as much CPU as the database server.
Someone could perhaps measure using the C++ API?
Regards,
Heikki
........................
$dbh = $server->connect();
$dbh->do("set autocommit = 0");
$dbh->do("drop table speed1");
$dbh->do("create table speed1 (a int not null, b int, c int, primary key (a), i\
ndex (b)) type = innobase");
for ($j = 0; $j < 100000; $j = $j + 1) {
$dbh->do("insert into speed1 values ($j, $j, $j)");
}
$dbh->do("commit");
$dbh->disconnect; # close connection
........................
$dbh = $server->connect();
$dbh->do("set autocommit = 0");
$s = 0;
for ($j = 0; $j < 100000; $j = $j + 1) {
$s += fetch_all_rows($dbh, "select * from speed1 where b = $j");
}
$dbh->do("commit");
print("$s rows fetched \n");
$dbh->disconnect; # close connection
.........................
Peter Zaitsev writes:
> Hello mysql,
>
> I've resenty tried to becnhmark mysql in really simple case.
> I need to select indexes really fast so I did "select * from pages
> where hash=11" there was a key on hash and the query returned only
> one row. The query was constant and server ad no other load.
>
> I've run this on 2CPU PIII-700 under 2.2.18aa2 and on one CPU
> PIII700 on 2.4.2 The results are about 3300 req/sec and the second
> one about 1800 req/sec. Unix domain sockets was used.
>
> I found the rather huge number of context swithches in first case:
>
> 2 0 0 3296 265244 104848 111472 0 0 0 0 103 27570 41 19
40
> 2 0 0 3296 265244 104848 111472 0 0 0 0 103 27690 38 21
41
> 0 0 0 3296 265344 104848 111472 0 0 0 0 104 26405 37 18
46 >
> the second one looked better, but not much if you'll look at about 2
> times speed difference:
>
> 1 0 0 24 58656 1480 298180 0 0 0 0 105 7946 84 16
0
> 1 0 0 24 58656 1480 298180 0 0 0 0 106 7942 88 12
0
> 2 0 0 24 58656 1480 298180 0 0 0 4 110 7968 82 18
0
> 1 0 0 24 58656 1480 298180 0 0 0 0 105 7966 81 19
0
> 1 0 0 24 58656 1480 298180 0 0 0 0 105 7965 81 19
0
> 1 0 0 24 58656 1480 298180 0 0 0 0 105 7948 83 17
0
> 1 0 0 24 58656 1480 298180 0 0 0 0 105 7964 85 15
0 > > I tried to run dumb query "select 10" and got about 7000 queries on
> second machine - which is 4 times faster then query which touches the
> table. > > Has anyone any ideas about if this speed is peek - I have heard
> people having 10000 of inserts/sec into table with no indexes with
> mysql and so on so I was a bit surprised about this low perfomance,
> which is more likely to be not because of connection or pharsing
> speed as "select 10" works fast, and just the I/O needed should not
> take so much then everything should be in memory. >
> The another story is - I've tried few time ago read speed of reiserfs
> - I created 1000000 of files, each ten bytes in size and I was able
> to read this file set in speed of 25000/sec - I mean open/read/close
> - so select speed of about 2K queries per second looks quite
> surprising, then most of this time is not because of communication
> but because of really accessing the table. > > > > > > > > >
> > -- > 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
