On 2002-01-30 11:28:21 -0500, Nick Vargish wrote:
> On Wed, 30 Jan 2002, Mike Lee wrote:
>
> > No particular reason. MySQL seems to be the one that most people
> > use for general purpose DB action.. :)
>
> Personally, I prefer PostgreSQL, but that's more from the feature
> perspecitve and not an efficiency perspective. I do remember several
> benchmarks that showed current versions of PostgreSQL to be comparable
> to MySQL for performance.
>
> MySQL is a fine product, I'm not trying to disparage it. And with one
> client, it may well be faster than PostgreSQL for raw inserts.
>
> I just whipped up a very basic test. Please don't take this as
> indicative of my usual approach to experimental rigor. :^)
>
> Given the following table, with no indexes or constraints:
>
> create table logtest (
> id serial,
> ts datetime,
> payload varchar(1024)
> )
Slightly modified for MySQL:
create table logtest (
id int primary key auto_increment,
ts datetime,
payload blob
);
> I ran the following Python script:
>
> ---------- cut here ----------
> #!/usr/bin/env python
>
> import os, pg, re, string, sys
>
> try:
> db = pg.DB ('navtest', '')
> except:
> print 'Unable to open "navtest". Bye.'
> sys.exit()
>
> count = 0
> iters = 10000
>
> print 'Inserting %d records...' % iters
> while count < iters:
> db.query ("insert into logtest (ts,payload) values (now(),'This is a
> fairly short test record.')")
> count += 1
>
> print 'Done.'
> ---------- cut here ----------
Again, slightly modified:
#!/usr/bin/env python
import os, MySQLdb, re, string, sys
try:
conn = MySQLdb.connect(db='logtest')
db = conn.cursor()
except:
print 'Unable to open "navtest". Bye.'
sys.exit()
count = 0
iters = 10000
print 'Inserting %d records...' % iters
while count < iters:
db.execute ("insert into logtest (ts,payload) values (now(),'This is a fairly short test record.')")
count += 1
print 'Done.'
> This took "0.27s user 0.15s system 0% cpu 1:01.53 total", according to
> "time". Obviously time doesn't know about all the backend work being
> done by the server process, but it looks like about 62 seconds for
> 10,000 inserts (including the call to the time() function). This comes
> out to about 161 records per second.
This took:
real 0m3.528s
user 0m2.010s
sys 0m0.200s
According to "time". That fits in with my observations about PostgreSQL
vs MySQL speed, about 20x difference.
> Next I added the following:
>
> create index logtest_ts_dx on logtest (ts)
>
> Cleared the table and re-ran the inserter script, it's up to 65.5 seconds
> for the 10000 inserts, or 153 inserts per second. Indexing clearly has a
> noticible performance impact on inserts.
Yeah, this bumps it up quite a bit:
real 0m3.883s
user 0m2.150s
sys 0m0.120s
;)
To be fair, I cranked it up to 100000 records to see the effects, and
got:
real 0m41.336s
user 0m19.200s
sys 0m1.070s
Note that during this time about 50% or so of the CPU was used by
Python (which explains the difference between real and user time).
> The test machine was a lightly-loaded dual 1 Ghz Coppermine (P3)
> running RedHat 7.1 smp kernel. The version of PostgreSQL is 7.2 beta
> 4.
Test machine was a lightly-loaded single-CPU 1 GHz PIII, 256 Mbyte of
RAM with Slackware and a 2.4.x kernel. MySQL 3.23.39.
> I do not have time at this moment to run the test against a MySQL
> database. If anyone would like to do so, I would be most interested in
> the results...
MySQL is really fast because it has no features. This is often okay,
for instance in a logging situation where each transaction is only a
single row in one table.
Anyway, I'd say that with a modern CPU you could probably get around 10k
inserts per second on a nice dual-CPU box with a simple table design.
--
Shane
Carpe Diem
---------------------------------------------------------------------
To unsubscribe, e-mail: loganalysis-unsubscribeat_private
For additional commands, e-mail: loganalysis-helpat_private
This archive was generated by hypermail 2b30 : Wed Jan 30 2002 - 09:47:45 PST