1 Topic by aris.dizdarevic

  • aris.dizdarevic
  • Member
  • Offline
  • Registered: 2020-01-31
  • Posts: 10

Topic: mySQL 100% for 10 seconds when loging to iredadmin

Our server-data:
iRedMail 0.9.9 MARIADB edition
Downloadable installer
CentOS Linux release 7.6.1810 (Core) VMware virtualized - (32gb ram, 12 Cores)
MySQL
NginX
iRedAdminPRO 3.6
1400 Mailboxes

At first "504 - Gateway time-out started to show up" - now i reset the server and i can login to iredadmin panel but:
I don't see any problems in log-files, but when login to iredadmin-pro it takes 10-15 seconds and i noticed mysql process goes to 100% in that time.

Before restart in nginx-error-log:
2020/01/31 09:59:00 [error] 12092#0: *27857 upstream timed out (110: Connection timed out) while reading response header from upstream, client: 192.168.5.129, server: _, request: "GET /iredadmin/dashboard?checknew HTTP/1.1", upstream: "uwsgi://127.0.0.1:7791", host: "iredmail.alcad.si", referrer: "https://iredmail.alcad.si/iredadmin"

Thank you for your help.

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

2 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,088

Re: mySQL 100% for 10 seconds when loging to iredadmin

Do you have many sql records in database “amavisd”? Especially in table “amavisd.msgs”.

3 Reply by RajeshM (edited by RajeshM 2020-02-01 02:39:16)

  • RajeshM
  • Member
  • Offline
  • Registered: 2019-09-06
  • Posts: 136

Re: mySQL 100% for 10 seconds when loging to iredadmin

hi

we had the same issue. My machine is a 48 core, 256 gb ram with ssd drives

it was due to mysql not being tuned correctly.

i first used mysqltuner.pl to get the recommendations for tuning
https://github.com/major/MySQLTuner-perl

and then i edited the following file
/etc/mysql/mariadb.conf.d/50-server.cnf

innodb_buffer_pool_size=8G
innodb_buffer_pool_instances=8
innodb_read_io_threads=4
innodb_write_io_threads=4
innodb_log_file_size=1G

the above settings may be very much on the higher side for you so make sure to use mysqltuner.pl first.

best regds,
rajesh

4 Reply by aris.dizdarevic (edited by aris.dizdarevic 2020-02-11 14:50:17)

  • aris.dizdarevic
  • Member
  • Offline
  • Registered: 2020-01-31
  • Posts: 10

Re: mySQL 100% for 10 seconds when loging to iredadmin

RajexhM and ZhangHuangbin - thanks for the answers...

RajexhM - I tried tuning it up but MariaDB failed to start - i changed parameters as mySql tuner suggested
innodb_buffer_pool_size=512M
innodb_buffer_pool_size=4G
innodb_log_file_size=512M

I added parameters in file my.cnf under [mysqld].

this error:
200131 10:05:28 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 536870912 bytes!
InnoDB: Possible causes for this error:
(a) Incorrect log file is used or log file size is changed
(b) In case default size is used this log file is from 10.0
(c) Log file is corrupted or there was not enough disk space
In case (b) you need to set innodb_log_file_size = 48M
200131 10:05:28 [ERROR] Plugin 'InnoDB' init function returned error.
200131 10:05:28 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
200131 10:05:28 [Note] Plugin 'FEEDBACK' is disabled.
200131 10:05:28 [ERROR] Unknown/unsupported storage engine: InnoDB
200131 10:05:28 [ERROR] Aborting

ZhangHuangbin: amavisd table is in attachment. Is there any way to disable amavisd statistics in admin-panel. Seems allways we click on link "Control panel" we must wat 10-20 seconds for page to show.

Thank you both for further suggestions.

Post's attachments

amavisd-mysql.png
amavisd-mysql.png 76.97 kb, 2 downloads since 2020-02-11 

You don't have the permssions to download the attachments of this post.

5 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,088

Re: mySQL 100% for 10 seconds when loging to iredadmin

Try to run script “tools/cleanup_amavisd_db.py” in Iraq directory to clean up old sql records then login. It should reduce the login time.

6 Reply by aris.dizdarevic

  • aris.dizdarevic
  • Member
  • Offline
  • Registered: 2020-01-31
  • Posts: 10

Re: mySQL 100% for 10 seconds when loging to iredadmin

I must admit it is a little bit faster after running the script - but still it takes about 5 seconds to get in.

Interesting - the script is ran from crontab everyday:
1   2   *   *   *   python /opt/www/iredadmin/tools/cleanup_amavisd_db.py >/dev/null

Is there any other option to speed up login-time - perhaps disable amavisd-new statistics?

Thanks for additional suggestions.

7 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,088

Re: mySQL 100% for 10 seconds when loging to iredadmin

Try this: add parameter "REDIRECT_TO_DOMAIN_LIST_AFTER_LOGIN = True" (without quotes) in iRedAdmin-Pro config file and restart "iredadmin" service.

It will redirect to the "Domains and Accounts" page instead of Dashboard page after login. Hope it helps. smile

8 Reply by RajeshM

  • RajeshM
  • Member
  • Offline
  • Registered: 2019-09-06
  • Posts: 136

Re: mySQL 100% for 10 seconds when loging to iredadmin

Hi
While changing innodb log file size in the cnf file

Make sure to move the existing log files to some backup location

So basically

Stop MySQL Service

Update innodb_log_file_size in my.cnf

Move the existing log files (there would be 2 files) out of the mysql directory

mv /var/lib/mysql/ib_logfile* /usr/local/src/backup

  Start MySQL Service

After around 24 hours of peak usage check again with MySQL tuner

Cheers!!!
Raj

9 Reply by aris.dizdarevic

  • aris.dizdarevic
  • Member
  • Offline
  • Registered: 2020-01-31
  • Posts: 10

Re: mySQL 100% for 10 seconds when loging to iredadmin

Yes, you were right... Changing "REDIRECT_TO_DOMAIN_LIST_AFTER_LOGIN = True" made it work and now works unbelivable fast.

I wonder what makes it so slow on the first "statistics" webpage?

RajeshM - thanks for tips but this is a production mail-server so i will have to clone it and test it so perhaps in the future...

Thank you both very much!