SQL Performance w/ SpamAssassin

[Michael Parker <pa...@pobox.com>]

Gary V wrote:
> 
> I was curious about a couple settngs that I heard can affect performance
> when using Innodb so I did a few ad hoc tests:
> 
> http://www200.pair.com/mecham/spam/mysqlspeed.txt
> 
> http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/
> 
> http://www.mysql.com/news-and-events/newsletter/2003-11/a0000000269.html
> 

Thanks Gary,

I've always pointed people elsewhere when it comes to SQL tuning, on the
theory that other places have much better information.

For sure, if you are using SQL in SpamAssassin you're going to want to
be doing some additional tuning on your database server.

Maybe its time we started up a wiki page that collects a few links and
various information about SQL performance tweaks that people are finding
that work.

If you could get that ball rolling I'm sure others would join in and add
to the wiki page with their own data.

Thanks
Michael

RE: SQL Performance w/ SpamAssassin

[Leon Kolchinsky <lk...@univ.haifa.ac.il>]

Hi,

Below is a little info on optimizing MySQL.
I've used it on my web server a while ago.
If you use InnoDB, you should change it according to InnoDB parameters.

Taken from here http://linuxgangster.org/modules.php?name=Content&file=printout&id=8


Below is a good start for getting mysql going a little faster editing the mysql.conf file


Code:

[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 64M
tmp_table_size             = 32M
max_allowed_packet = 16M
max_connections = 650
myisam_sort_buffer_size = 64M
table_cache = 1500
join_buffer_size = 1M
sort_buffer_size = 2M
read_buffer_size = 1M
myisam_sort_buffer_size = 64M
thread_cache_size = 128
wait_timeout = 900
connect_timeout = 10
query_cache_limit = 2M
query_cache_size = 64M
query_cache_type = 1
thread_concurrency = 8

[mysqld_safe]
open_files_limit = 8192


Explanation

key_buffer is the size of the buffer used with indexes. The larger the buffer, the faster the SQL command will finish and a result will be returned. The rule-of-thumb is to set the key_buffer_size to at least a quarter, but no more than half, of the total amount of memory on the server. Ideally, it will be large enough to contain all the indexes (http://linuxweblog.com/node/231)

tmp_table_size Created_tmp_disk_tables are the "number of implicit temporary tables on disk created while executing statements" and Created_tmp_tables are memory-based. Obviously it is bad if you have to go to disk instead of
memory. About 2% of temp tables go to disk, which doesn't seem too bad
but increasing the tmp_table_size probably couldn't hurt either. (http://www.interworx.com/forums/showthread.php?p=2346)

max_allowed_packet 16MB is the default. However, if you get the error lost connection to MySQL server during query, you might want up this to a higher value

max_connections The number of connections allowed. 100 is the default. This should be raised to a higher value when running multiple databases, or very busy sites.

myisam_sort_buffer_size Sets the size of the buffer used when recovering tables.

table_cache Each time MySQL accesses a table, it places it in the cache. If the system accesses many tables, it is faster to have these in the cache. MySQL, being multi-threaded, may be running many queries on the table at one time, and each of these will open a table. Examine the value of open_tables at peak times. If you find it stays at the same value as your table_cache value, and then the number of opened_tables starts rapidly increasing, you should increase the table_cache if you have enough memory. (http://linuxweblog.com/node/231)

join_buffer_size Sets the size of the buffer when joining without keys.

sort_buffer_size The sort_buffer is very useful for speeding up myisamchk operations (which is why it is set much higher for that purpose in the default configuration files), but it can also be useful everyday when performing large numbers of sorts. (http://linuxweblog.com/node/231)

read_buffer_size Sets the size of the buffer when scanning tables.

myisam_sort_buffer_size Same as sort_buffer_size bt for myisam tables.

thread_cache_size If you have a busy server that's getting a lot of quick connections, set your thread cache high enough that the Threads_created value in SHOW STATUS stops increasing. This should take some of the load off of the CPU. (http://linuxweblog.com/node/231)

connect_timeout The number of seconds before connection timeout. 

query_cache_limit maximum size of result set that can be cached.

query_cache_size MySQL 4 provides one feature that can prove very handy - a query cache. In a situation where the database has to repeatedly run the same queries on the same data set, returning the same results each time, MySQL can cache the result set, avoiding the overhead of running through the data over and over and is extremely helpful on busy servers. (http://linuxweblog.com/node/231)

query_cache_type If the query cache size is greater than 0, the query_cache_type variable influences how it works. This variable can be set to the following values:
A value of 0 or OFF prevents caching or retrieval of cached results.
A value of 1 or ON allows caching except of those statements that begin with SELECT SQL_NO_CACHE.
A value of 2 or DEMAND causes caching of only those statements that begin with SELECT SQL_CACHE.
thread_concurrency Try number of CPU's*2 
Reiserfs seems to be the best filesystem to use on a Linux system for Mysql performance. This is because it does well with multiple small files and is very fast for open, read and write. 

Running OPTIMIZE TABLE on a weekly basis is something I do for every table on every database on my servers. This can be easily done with PHPMyAdmin.

Just to give you a idea of how much the changes above helped my server:
94,082.20 queries per hour is what my server is averaging now. It does this without even trying. With the default settings, this particular amount caused the server to run very slow.

-----Original Message-----
From: Michael Parker [mailto:parkerm@pobox.com] 
Sent: Tuesday, November 28, 2006 3:28 PM
To: Gary V
Cc: users@spamassassin.apache.org
Subject: SQL Performance w/ SpamAssassin

Gary V wrote:
> 
> I was curious about a couple settngs that I heard can affect performance
> when using Innodb so I did a few ad hoc tests:
> 
> http://www200.pair.com/mecham/spam/mysqlspeed.txt
> 
> http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/
> 
> http://www.mysql.com/news-and-events/newsletter/2003-11/a0000000269.html
> 

Thanks Gary,

I've always pointed people elsewhere when it comes to SQL tuning, on the
theory that other places have much better information.

For sure, if you are using SQL in SpamAssassin you're going to want to
be doing some additional tuning on your database server.

Maybe its time we started up a wiki page that collects a few links and
various information about SQL performance tweaks that people are finding
that work.

If you could get that ball rolling I'm sure others would join in and add
to the wiki page with their own data.

Thanks
Michael