Naglo usporena kombinacija MailScanner+MailWatch = MySQL problemi

Traje mi i po 25min da mail prođe kroz mail server, što je LOŠE.
Kopanjem sam došla do toga da je najverovatniji uzročnik jako spor upis u MySQL bazu.

Kako dobiti opšta podešavanja MySQL baze :
mysql> show global variables;
+—————————————–+———————————————–+
| Variable_name | Value
+—————————————–+———————————————–+
| auto_increment_increment | 1
…..
| innodb_buffer_pool_size | 8388608
…..
| innodb_flush_log_at_trx_commit | 1
…..
277 rows in set (0.01 sec)

Ova stavka (innodb_buffer_pool_size) za 64bitnu mašunu MORA biti najmanje 128M.
Stavka innodb_flush_log_at_trx_commit kad je 1, znači da baza posle svakog reda radi tzv COMMIT (This says to do an fsync after each COMMIT.), što je jako usporava. Ovaj parametar bi trebalo da je 0.
Stavka slow_query_log_file daje gde se direktno loguju spore transakcije.
Stavka innodb_flush_method=O_DIRECT – Avoid double buffering and reduce swap pressure, in most cases this setting improves performance. Po default-u, ova vrednost nije setovana.
Stavka innodb_log_file_size bi trebala da je veća od default-nih 5M, ali ju je komplikovano podesiti :
1. zaustaviti mysqld
2. premestiti ib_logfile0 i ib_logfile1 iz /var/lib/mysql na sigurno mesto
3. postaviti željenu vrednost parametra u /etc/my.cnf
4. startovati mysqld, i proveriti logove i stanje baze (ako ne valja, sve se vraća u nazad)

Sve ove stavke se direktno podešavaju u /etc/my.cnf pod stavkom “mysqld”. Posle izmene treba restartovati mysqld servis, da bi se promene “primile”.
# more my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
innodb_buffer_pool_size=128M
innodb_flush_log_at_trx_commit=0
slow_query_log_file=/var/log/mysql-slowlog.log
long_query_time=20
innodb_flush_method=O_DIRECT
innodb_log_file_size=24M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

mysql> show global status;
+———————————–+———-+
| Variable_name | Value |
+———————————–+———-+
| Aborted_clients | 0 |
| Aborted_connects | 1 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
…..

Kako videti koliko efikasno radi vaša baza :
mysql> show engine innodb status;
Dobar link sa objašnjenjima šta izlazi ove komade znače.

Dobar link za proveru šta koji parametar MySQL baze tačno znači.

Kako proveriti veličinu baze za MailScanner :
mysql> SELECT table_schema ‘mailscanner’, SUM( data_length + index_length) / 1024 / 1024
-> “Data Base Size in MB” FROM information_schema.TABLES GROUP BY table_schema ;
+——————–+———————-+
| mailscanner | Data Base Size in MB |
+——————–+———————-+
| information_schema | 0.00781250 |
| mailscanner | 1.33824539 |
+——————–+———————-+
2 rows in set (0.04 sec)
Ili otići u /var/lib/mysql :
# du -h mailscanner/
1.6M mailscanner/

Kako izlistati sve upise u mailscanner DB, u tabelu maillog, za imenovani datum :
mysql> select * from maillog where date=’2014-03-04′;

Kako proveriti da li korisnik mailwatch ima sve potrebne privilegije nad bazom mailscanner :
mysql> use mailscanner;
mysql> show grants for ‘mailwatch’@’localhost’;
+——————————————————————————————+
| Grants for mailwatch@localhost |
+——————————————————————————————+
| GRANT FILE ON *.* TO ‘mailwatch’@’localhost’ IDENTIFIED BY PASSWORD ‘*E6PF6D6D401EF872DD5BC6D700072987700CC3A16’ |
| GRANT ALL PRIVILEGES ON `mailscanner`.* TO ‘mailwatch’@’localhost’ |
+——————————————————————————————+

Kako proveriti koji je poslednji (po vremenu) upis u mailscanner bazu :
mysql> use mailscanner;
mysql> select max(timestamp) from maillog;
+———————+
| max(timestamp) |
+———————+
| 2014-03-03 14:45:58 |
+———————+

Kako promeniti root i user MySQL pass :
# mysqladmin -u root -pcurrentpassword password ‘newpassword’
A za običnog korisnika :
mysql> use mysql;
mysql> SET PASSWORD FOR ‘mailwatch’@’localhost’ = PASSWORD(‘nova-lozinka’);

Kako uljučiti debug (detaljno) logovanje za MailWatch :
U fajlu /usr/lib/MailScanner/MailScanner/CustomFunctions/MailWatch.pm, odhešovati stavku : DBI->trace(2,’/root/dbitrace.log’);
Naravno log se može usmeriti i na drugo mesto.
Posle završenog debaga, ovo ponovo hešovati!

Kako proveriti da vam nije spor HDD :
# dd bs=1M count=256 if=/dev/zero of=test conv=fdatasync
256+0 records in
256+0 records out
268435456 bytes (268 MB) copied, 1.49248 s, 180 MB/s
(na lokaciji gde ste napraviće fajl po imenu “test”).
Sve iznad 1s je apsolutno previše za iole brzi storage.
Videti link za više informacija.

Alat za proveru rada MailScanner-a :
# MailScanner –lint
Paziti to je – pa još jedan – ispred reči lint.
Pažljivo pročitati izlaz komande!!
Meni se javio ovaj problem :
Config: calling custom end function MailWatchLogging
commit ineffective with AutoCommit enabled at /usr/lib/MailScanner/MailScanner/CustomFunctions/MailWatch.pm line 93, line 1.
I zbog toga MailWatch ne upisuje u MySQL bazu, a MailScanner ne završava obradu mail poruka…..

Kako proveriti da li vam MySQL dobro radi :
Postoji alatka (Perl skript) na sajtu http://mysqltuner.com/ (alatka je ovde), koja na izlazu daje gomilu korisnih saveta (paziti, treba ga pustiti kad je MySQL up-and-running i u upotrebi najmanje 24h, onda daje bolje rezultate, kod mene je testna mašina u pitanju) :
# ./mysqltuner.pl
>> MySQLTuner 1.3.0 – Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with ‘–help’ for additional options and output filtering
[!!] Successfully authenticated with no password – SECURITY RISK!
[OK] Currently running supported MySQL version 5.1.67
[!!] Switch to 64-bit OS – MySQL cannot currently use all of your RAM
——– Storage Engine Statistics ——————————————-
[–] Status: +CSV +InnoDB +MRG_MYISAM
[–] Data in MyISAM tables: 1M (Tables: 14)
[–] Data in InnoDB tables: 304K (Tables: 12)
[!!] Total fragmented tables: 12
——– Security Recommendations ——————————————-
[OK] All database users have passwords assigned
——– Performance Metrics ————————————————-
[–] Up for: 18s (9 q [0.500 qps], 6 conn, TX: 8K, RX: 549)
[–] Reads / Writes: 100% / 0%
[–] Total buffers: 34.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 439.8M (11% of installed RAM)
[OK] Slow queries: 0% (0/9)
[OK] Highest usage of available connections: 0% (1/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/249.0K
[!!] Query cache is disabled
[OK] Temporary tables created on disk: 0% (0 on disk / 2 total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 53% (8 open / 15 opened)
[OK] Open file limit used: 1% (16/1K)
[OK] Table locks acquired immediately: 100% (18 immediate / 18 locks)
[!!] Connections aborted: 16%
[OK] InnoDB buffer pool / data size: 8.0M/304.0K
[OK] InnoDB log waits: 0
——– Recommendations —————————————————–
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours – recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Set thread_cache_size to 4 as a starting value
Your applications are not closing MySQL connections properly
Variables to adjust:
query_cache_size (>= 8M)
thread_cache_size (start at 4)

Kako se koji od ovih problema uklanja :
* Query cache is disabled
Ovo treba omogućiti ako imate mnogo konkcija, a spore diskove…..
mysql> SHOW VARIABLES LIKE ‘query_cache_size’;
+——————+——-+
| Variable_name | Value |
+——————+——-+
| query_cache_size | 0 |
+——————+——-+
Vrednost “0” znači da je opcija isključena.
Izmenimo /etc/my.cnf, dodajući sledeće pod stavku [mysqld] :
query_cache_size=64M
query_cache_type=1
query_cache_limit=1M
Evo šta je šta :
query_cache_size – This is the size of the cache in bytes. Setting this value to 0 will effectively disable caching.
query_cache_type – This value must be ON or 1 for query caching to be enabled by default.
query_cache_limit – This is the maximum size query (in bytes) that will be cached
Da bi se promene “primile” restartovati servis.
* Thread cache is disabled
Staviti u /etc/my.cnf, pod “mysqld” :
thread_cache_size=40
If you have a busy server that’s getting a lot of quick connections, set your thread cache.
* OPTIMIZE TABLE to defragment tables for better performance
# mysqlcheck -u root -p –auto-repair –check –optimize –all-databases
Enter password:
mailscanner.audit_log Table is already up to date
…..
mailscanner.maillog OK
…..
mysql.general_log
note : The storage engine for the table doesn’t support optimize
…..
roundcubemail.users
note : Table does not support optimize, doing recreate + analyze instead
status : OK
* Key buffer hit rate
Ovo treba da je 90-98%
* Connections aborted
Što manje to bolje

I šta na kraju i posle svega može da pomogne :
#GRANT ALL PRIVILEGES ON * . * TO ‘root’@’localhost’;
#GRANT ALL PRIVILEGES ON * . * TO ‘mailwatch’@’localhost’;