Re: [dspam-users] MySQL vs. PgSQL

taras@elantech.ru wrote:
> My my.cnf include:

That looks like a modified huge, so you are probably OK there.

> I did read the wiki page on tuning, but still have some questions.
> 1) Can I disable automatic adding of uids to dspam_virtual_uids? - I
> didn't found answer in dspam manuals and wiki...

As long as you always preconfigure new users manually, this shouldn't be
a problem. I think you can just drop the autosequence index (which I'd
have to dig around to figure out).

> 2) How can we use TRANSACTIONS in purge-4.1.sql if we still using
> MyISAM?

You cannot use TRANSACTIONS unless you upgrade the tables to InnoDB,
which may or may not be slower than PostgreSQL.

> I use TOE, my dspam_token_data is about 2GB in size and has about 100
> 000 000 rows.
> When I run purge.sql it wasn't finished for about 2 hours...

If you've never run optimize on the table, you are like to have a huge
delete chain (MySQL doesn't delete records, but rather marks them as
deleted and only recovers them when optimize is run). It's painful the
first time, but once you do it once, it is much faster later on. Use
myisamchk to see how healthy your tables are:

# myisamchk -dv /var/lib/mysql/dspam/dspam_token_data.MYI

MyISAM file: /var/lib/mysql/dspam/dspam_token_data.MYI
Record format: Fixed length
Character set: latin1 (8)
File-version: 1
Creation time: 2004-08-05 12:11:50
Recover time: 2006-05-18 13:12:40
Status: open,changed
Data records: 4434781 Deleted blocks: 0
Datafile parts: 4434781 Deleted data: 0
Datafile pointer (bytes): 4 Keyfile pointer (bytes): 3
Datafile length: 150782554 Keyfile length: 193130496
Max datafile length: 146028888062 Max keyfile length: 17179868159
Recordlength: 34

Note that if you have a large number of "Deleted blocks" all of your
database activity will be slowed. Note that I never have any deleted
records visible because I always run optimize at the end of the purge
script.

> I think that problems with mysql coming from many concurrent
> inserts/updates (table locking?).
> May be postgresql, as more mature RDBMS, under havy concurent queries
> can show us better behavior?

If you are concurrency-bound, then yes, either InnoDB or PostgreSQL will
help (both are row-lock databases), but you will pay a price in raw
performance (since both are transactional, i.e. have to write each
transaction to a temporary journal table before writing to the main table).

> P.S.: Sorry for my english, thanks for help in advance.

Trust me, your English is infinitely better than my Russian! ;-)

John

-- 
John Peacock
Director of Information Research and Technology
Rowman & Littlefield Publishing Group
4501 Forbes Boulevard
Suite H
Lanham, MD  20706
301-459-3366 x.5010
fax 301-429-5748