Robert,
>-----Original Message-----
>From: Robert Sundstrvm [mailto:[EMAIL PROTECTED]]
>Sent: quinta-feira, 13 de Dezembro de 2001 11:55To: [EMAIL PROTECTED]
>Subject: RE: MySQL v.s. Oracle
>At 04:23 2001-12-02 , you wrote:
>>Not sure that total users is a good metric; queries per second may be>better.>
>>We host web sites and use MySQL with MyISAM tables for small and
>>medium-sized sites, Oracle for the big ones. Oracle's row-level locking
>>abilities make a big, deciding difference for the bigger, more active sites.
>>Oracle just scales much better than MySQL.
>
>>Big downside of Oracle is the price, which is why we use it for just the big
>>boys. Costs too much to install on all our servers.
>
>>I'm currently evaluating Berkeley and InnoDB tables to see how they compare
>>to Oracle. If they compare well then we may be using'em in place of Oracle
>>for most sites.
>
>I have done some admittedly not-so-scientific testing on MySQL (both with
>MyISAM and InnoDB) to find that both combinations performs best in single
>user systems. The test I run made about 50% updates/inserts and about 50%
>queries, with medium sized transactions (3-5 statements per transaction,
>where transactions was supported). On my regular desktop box I was able to
>get about 700 statements per second using MyISAM and about two thirds of
>that using InnoDB. Already at 2 simultaneous users (doing the same
>transactions) total throughput was less than for the single user case.
what MySQL version did you use? Versions from 3.23.44 up have a new my.cnf
parameter innodb_thread_concurrency which you can use to tune highly
concurrent systems, and whose default value 8 already improves the performance.
>Most stable commercial products exposes the opposite behavior. It may be
>the case that MySQL performs pretty well in single (or few) user cases, but
>the commercial alternatives will, in my experience, in most cases beat
>MySQL on 3-5 users and above.
Was your test CPU-bound or disk-bound? Note that in the first case, if there
is only a single CPU, all databases will run fastest in the single user
case, because adding more execution threads really does not add more cycles
to the CPU. Instead, thread switching reduces processor cache hit rate, and
slows down execution of all threads. This behavior is common to all server
type programs.
If execution is disk-bound, then in rare cases adding more execution threads
will improve performance.
I just ran the following scalability test in Perl which measures InnoDB
scalability in concurrent inserts and selects to the same table:
10 threads each insert and select 100 000 rows into a table. All threads do
this to the same table.
The computer was a 2-way 450 MHz Xeon. 50 MB buffer pool.
Sequential execution lasted 20 minutes
Parallel execution lasted 10 minutes
The benchmark was CPU-bound. Since parallel execution can use 2 CPUs, it is
natural that it runs faster.
10 copies of the following Perl program were used (create table only in the
first, the loop start value of $j different for each copy):
..........................
use DBI;
use Benchmark;
chomp($pwd =`pwd`); $pwd = "." if ($pwd eq '');
require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n";
$dbh = $server->connect();
$dbh->do("set autocommit = 1");
$dbh->do("drop table speed1");
$dbh->do(
"create table speed1 (a int not null, b int, c int, primary key (a), index (b))\
type = innodb")
|| die $dbh->err;
$s = 0;
for ($j = 0; $j < 1000000; $j = $j + 10) {
$dbh->do("insert into speed1 values ($j, $j, $j)");
$s += fetch_all_rows($dbh, "select * from speed1 where a = $j - 100");
}
print("$s rows fetched\n");
$dbh->disconnect;
..........................
>It is also the case that most commercial products supports better
>optimization methods than MySQL. Two important things are caching statement
>compilations at the server and stored procedures. If I have a performance
>problem in my database it is often the case that I can improve things by
>putting some SQL-code in a stored procedure.
Stored procedures are important because they reduce communication between
the client and the server process.
Regards,
Heikki
Innobase Oy
---
See http://www.innodb.com for the latest news about InnoDB
Order commercial MySQL/InnoDB support at https://order.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
