Re: [dspam-users] MySQL vs. PgSQL

John Peacock пишет:
> taras@elantech.ru wrote:
>> Hi to all!
>> I have about ~4000 users in mysql-4.1 (FreeBSD) and seems quite bad
>> behavior of mysql under havy load. May be switching to PostgreSQL can
>> help me?
>
> No, PostgreSQL will probably be [much] worse. Did you tune your MySQL
> instances for heavy usage (there should be multiple my*.cnf example
> files included with the package). Try my-medium.cnf, my-large.cnf, or
> even my-huge.cnf. You did read the wiki page on tuning:
I'm running FreeBSD 6.1 on P4-3.2 with 2Gb of RAM.
My my.cnf include:

---
[mysqld]
skip-locking
max_connections = 1700
key_buffer = 512M
max_allowed_packet = 32M
table_cache = 1024
sort_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 256M
thread_cache_size = 8
query_cache_size= 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
---
>
>
>     http://dspamwiki.expass.de/Performance_Tuning
>
> didn't you?  Also make sure that you optimize tables on a regular 
> basis (e.g. with your monthly purge run); this alone can be a 500% 
> increase in performance, as mysql will be more efficient once you have 
> optimized the tables under actual usage.
>
> John
>
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...
   2) How can we use TRANSACTIONS  in purge-4.1.sql if we still using 
MyISAM?
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...
I'm using TOE and filter only incoming mail (Postfix). Spam-tagged 
messages is sorting by maildrop to ./Spam subfolders on IMAP server 
(Courier, Maildir).
Nighty I'm running script to scan all Maildirs and retrain dspam based 
on user activities (moving messages to/from Spam subfolder).
It's quite interesting schema from users point of view.
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?
P.S.: Sorry for my english, thanks for help in advance.
-- 
С уважением, Савчук Тарас
"ЭЛАНТЕК"
+7 (495) 589 68 81
+7 (926) 575 22 11
http://www.elantech.ru