Author Topic: MySQL memory usage  (Read 11180 times)

Offline Drakon

  • Jr. Member
  • **
  • Posts: 45
MySQL memory usage
« on: October 14, 2013, 10:36:34 AM »
When I execute 'htop' I can see many processes with 'mysql' with any memory usage. It's normal?



My server: Ubuntu Server 12.04 + Postfix + MySQL + Roundcube 0.8.5

Thanks in advance!
« Last Edit: October 14, 2013, 10:39:53 AM by Drakon »

Offline SKaero

  • Administrator
  • Hero Member
  • *****
  • Posts: 5,876
    • SKaero - Custom Roundcube development
Re: MySQL memory usage
« Reply #1 on: October 14, 2013, 12:19:49 PM »
I wouldn't say its normal, but why do you think it connected to RoundCube?

Offline Drakon

  • Jr. Member
  • **
  • Posts: 45
Re: MySQL memory usage
« Reply #2 on: October 15, 2013, 02:10:47 AM »
Ok, I don't know but Ubuntu Server 12.04 performance has worsened after upgrade from 11.04.  :-\

Offline SKaero

  • Administrator
  • Hero Member
  • *****
  • Posts: 5,876
    • SKaero - Custom Roundcube development
Re: MySQL memory usage
« Reply #3 on: October 15, 2013, 12:45:11 PM »
Maybe you should try asking on the Ubuntu forums than since it seems that where your problem lies.

Offline Drakon

  • Jr. Member
  • **
  • Posts: 45
Re: MySQL memory usage
« Reply #4 on: October 15, 2013, 01:06:23 PM »
SKaero I have performance problems after upgrade to Ubuntu Server 12.04.

MySQL config seems OK after I checked with 'myslqtuner'. Apache is OK, Roundcube config OK, etc. I've checked again the performance tips for Roundcube (http://trac.roundcube.net/wiki/Howto_Config/Performance). When I open a folder with thousand of e-mails it's takes more than 1 minute to open. With the previous version (11.04) took between 15 to 30 seconds to open a folder...

Do you have any idea what I can do?

Thanks for your replies!

Offline SKaero

  • Administrator
  • Hero Member
  • *****
  • Posts: 5,876
    • SKaero - Custom Roundcube development
Re: MySQL memory usage
« Reply #5 on: October 15, 2013, 02:26:04 PM »
Is the mail server on the same server as RoundCube? Enable imap_debug and post the log.

Offline Drakon

  • Jr. Member
  • **
  • Posts: 45
Re: MySQL memory usage
« Reply #6 on: October 15, 2013, 02:44:34 PM »
Yes I have mail server on same server. I change "user@domain" for security...

This is result of imap log:
Quote
[15-Oct-2013 20:34:14 +0200]: [F27A] C: A0001 LOGIN user@domain pwd
[15-Oct-2013 20:34:14 +0200]: [F27A] S: A0001 OK User logged in
[15-Oct-2013 20:34:14 +0200]: [F27A] C: A0002 STATUS INBOX (MESSAGES UNSEEN)
[15-Oct-2013 20:34:14 +0200]: [F27A] S: * STATUS "INBOX" (MESSAGES 12388 UNSEEN 20)
[15-Oct-2013 20:34:14 +0200]: [F27A] S: A0002 OK STATUS Completed.
[15-Oct-2013 20:34:14 +0200]: [F27A] C: A0003 SELECT INBOX
[15-Oct-2013 20:34:14 +0200]: [F27A] S: * FLAGS ($MDNSent $Forwarded \Draft \Answered \Flagged \Deleted \Seen \Recent)
[15-Oct-2013 20:34:14 +0200]: [F27A] S: * OK [PERMANENTFLAGS ($MDNSent $Forwarded \* \Draft \Answered \Flagged \Deleted \Seen)] Limited
[15-Oct-2013 20:34:14 +0200]: [F27A] S: * 12388 EXISTS
[15-Oct-2013 20:34:14 +0200]: [F27A] S: * 0 RECENT
[15-Oct-2013 20:34:14 +0200]: [F27A] S: * OK [UIDVALIDITY 1299433429] Ok
[15-Oct-2013 20:34:14 +0200]: [F27A] S: * OK [MYRIGHTS "acdilrsw"] ACL
[15-Oct-2013 20:34:14 +0200]: [F27A] S: A0003 OK [READ-WRITE] Ok
[15-Oct-2013 20:34:14 +0200]: [F27A] C: A0004 UID SEARCH 12388
[15-Oct-2013 20:34:14 +0200]: [F27A] S: * SEARCH 58294
[15-Oct-2013 20:34:14 +0200]: [F27A] S: A0004 OK SEARCH done.
[15-Oct-2013 20:34:14 +0200]: [F27A] C: A0005 LOGOUT
[15-Oct-2013 20:34:14 +0200]: [F27A] S: * BYE LOGOUT received
[15-Oct-2013 20:34:14 +0200]: [F27A] S: A0005 OK Completed
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: * OK [CAPABILITY IMAP4rev1 UIDPLUS CHILDREN NAMESPACE THREAD=ORDEREDSUBJECT THREAD=REFERENCES SORT QUOTA IDLE ACL ACL2=UNION STARTTLS] Courier-IMAP ready. Copyright 1998-2011 Double Precision, Inc.  See COPYING for distribution information.
[15-Oct-2013 20:35:29 +0200]: [C3FD] C: A0001 LOGIN user2@domain pwd2
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: A0001 OK LOGIN Ok.
[15-Oct-2013 20:35:29 +0200]: [C3FD] C: A0002 STATUS INBOX (MESSAGES UNSEEN)
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: * STATUS "INBOX" (MESSAGES 1563 UNSEEN 0)
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: A0002 OK STATUS Completed.
[15-Oct-2013 20:35:29 +0200]: [C3FD] C: A0003 SELECT INBOX
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: * FLAGS ($MDNSent $Forwarded \Draft \Answered \Flagged \Deleted \Seen \Recent)
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: * OK [PERMANENTFLAGS ($MDNSent $Forwarded \* \Draft \Answered \Flagged \Deleted \Seen)] Limited
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: * 1563 EXISTS
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: * 0 RECENT
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: * OK [UIDVALIDITY 1326194928] Ok
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: * OK [MYRIGHTS "acdilrsw"] ACL
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: A0003 OK [READ-WRITE] Ok
[15-Oct-2013 20:35:29 +0200]: [C3FD] C: A0004 UID SEARCH 1563
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: * SEARCH 1568
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: A0004 OK SEARCH done.
[15-Oct-2013 20:35:29 +0200]: [C3FD] C: A0005 STATUS INBOX.Drafts (MESSAGES UNSEEN)
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: * STATUS "INBOX.Drafts" (MESSAGES 6 UNSEEN 0)
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: A0005 OK STATUS Completed.
[15-Oct-2013 20:35:29 +0200]: [C3FD] C: A0006 SELECT INBOX.Drafts
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: * FLAGS (\Draft \Answered \Flagged \Deleted \Seen \Recent)
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: * OK [PERMANENTFLAGS (\* \Draft \Answered \Flagged \Deleted \Seen)] Limited
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: * 6 EXISTS
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: * 0 RECENT
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: * OK [UIDVALIDITY 1327562283] Ok
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: * OK [MYRIGHTS "acdilrsw"] ACL
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: A0006 OK [READ-WRITE] Ok
[15-Oct-2013 20:35:29 +0200]: [C3FD] C: A0007 UID SEARCH 6
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: * SEARCH 133
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: A0007 OK SEARCH done.
[15-Oct-2013 20:35:29 +0200]: [C3FD] C: A0008 STATUS INBOX.Sent (MESSAGES UNSEEN)
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: * STATUS "INBOX.Sent" (MESSAGES 370 UNSEEN 0)
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: A0008 OK STATUS Completed.
[15-Oct-2013 20:35:29 +0200]: [C3FD] C: A0009 SELECT INBOX.Sent
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: * FLAGS ($Forwarded \Draft \Answered \Flagged \Deleted \Seen \Recent)
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: * OK [PERMANENTFLAGS ($Forwarded \* \Draft \Answered \Flagged \Deleted \Seen)] Limited
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: * 370 EXISTS
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: * 0 RECENT
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: * OK [UIDVALIDITY 1327562557] Ok
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: * OK [MYRIGHTS "acdilrsw"] ACL
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: A0009 OK [READ-WRITE] Ok
[15-Oct-2013 20:35:29 +0200]: [C3FD] C: A0010 UID SEARCH 370
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: * SEARCH 372
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: A0010 OK SEARCH done.
[15-Oct-2013 20:35:29 +0200]: [C3FD] C: A0011 STATUS INBOX.Junk (MESSAGES UNSEEN)
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: * STATUS "INBOX.Junk" (MESSAGES 0 UNSEEN 0)
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: A0011 OK STATUS Completed.
[15-Oct-2013 20:35:29 +0200]: [C3FD] C: A0012 STATUS INBOX.Trash (MESSAGES UNSEEN)
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: * STATUS "INBOX.Trash" (MESSAGES 0 UNSEEN 0)
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: A0012 OK STATUS Completed.
[15-Oct-2013 20:35:29 +0200]: [C3FD] C: A0013 STATUS INBOX.NoJunk (MESSAGES UNSEEN)
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: * STATUS "INBOX.NoJunk" (MESSAGES 0 UNSEEN 0)
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: A0013 OK STATUS Completed.
[15-Oct-2013 20:35:29 +0200]: [C3FD] C: A0014 STATUS INBOX.ValidationFolder (MESSAGES UNSEEN)
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: A0014 NO [ALERT] STATUS failed
[15-Oct-2013 20:35:29 +0200]: [C3FD] C: A0015 STATUS INBOX.ValidationFolder (MESSAGES UNSEEN)
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: A0015 NO [ALERT] STATUS failed
[15-Oct-2013 20:35:29 +0200]: [C3FD] C: A0016 SELECT INBOX.ValidationFolder
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: A0016 NO Unable to open this mailbox.
[15-Oct-2013 20:35:29 +0200]: [C3FD] C: A0017 LOGOUT
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: * BYE Courier-IMAP server shutting down
[15-Oct-2013 20:35:29 +0200]: [C3FD] S: A0017 OK LOGOUT completed

If you want you can connect to my server.

Offline SKaero

  • Administrator
  • Hero Member
  • *****
  • Posts: 5,876
    • SKaero - Custom Roundcube development
Re: MySQL memory usage
« Reply #7 on: October 15, 2013, 05:42:51 PM »
Post the imap log of it opening a folder, and check the timestamp between the requests.

Offline Drakon

  • Jr. Member
  • **
  • Posts: 45
Re: MySQL memory usage
« Reply #8 on: October 16, 2013, 02:27:23 AM »
SKaero, when I open a folder for a second time it's faster, Roundcube uses 'db cache' (MySQL in my case) but the searches always takes long time or it's ended with an IMAP error.

For example, this is a IMAP debug for a search on folder:
Quote
[16-Oct-2013 07:56:04 +0200]: [3556] S: * OK [CAPABILITY IMAP4rev1 UIDPLUS CHILDREN NAMESPACE THREAD=ORDEREDSUBJECT THREAD=REFERENCES SORT QUOTA IDLE ACL ACL2=UNION STARTTLS] Courier-IMAP ready. Copyright 1998-2011 Double Precision, Inc.  See COPYING for distribution information.
[16-Oct-2013 07:56:04 +0200]: [3556] C: A0001 LOGIN user@domain pass
[16-Oct-2013 07:56:04 +0200]: [3556] S: A0001 OK LOGIN Ok.
[16-Oct-2013 07:56:04 +0200]: [3556] C: A0002 SELECT INBOX
[16-Oct-2013 07:56:05 +0200]: [3556] S: * FLAGS ($MDNSent $Forwarded \Draft \Answered \Flagged \Deleted \Seen \Recent)
[16-Oct-2013 07:56:05 +0200]: [3556] S: * OK [PERMANENTFLAGS ($MDNSent $Forwarded \* \Draft \Answered \Flagged \Deleted \Seen)] Limited
[16-Oct-2013 07:56:05 +0200]: [3556] S: * 12313 EXISTS
[16-Oct-2013 07:56:05 +0200]: [3556] S: * 0 RECENT
[16-Oct-2013 07:56:05 +0200]: [3556] S: * OK [UIDVALIDITY 1299433429] Ok
[16-Oct-2013 07:56:05 +0200]: [3556] S: * OK [MYRIGHTS "acdilrsw"] ACL
[16-Oct-2013 07:56:05 +0200]: [3556] S: A0002 OK [READ-WRITE] Ok
[16-Oct-2013 07:56:05 +0200]: [3556] C: A0003 UID SORT (DATE) UTF-8 ALL OR HEADER FROM {4}
[16-Oct-2013 07:56:05 +0200]: [3556] S: + OK
[16-Oct-2013 07:56:05 +0200]: [3556] C: marc HEADER SUBJECT {4}
[16-Oct-2013 07:56:05 +0200]: [3556] S: + OK
[16-Oct-2013 07:56:05 +0200]: [3556] C: marc
[16-Oct-2013 07:57:03 +0200]: [22DD] S: * OK [CAPABILITY IMAP4rev1 UIDPLUS CHILDREN NAMESPACE THREAD=ORDEREDSUBJECT THREAD=REFERENCES SORT QUOTA IDLE ACL ACL2=UNION STARTTLS] Courier-IMAP ready. Copyright 1998-2011 Double Precision, Inc.  See COPYING for distribution information.
[16-Oct-2013 07:57:03 +0200]: [22DD] C: A0001 LOGIN user@domain pass
[16-Oct-2013 07:57:04 +0200]: [22DD] S: A0001 OK LOGIN Ok.
[16-Oct-2013 07:57:04 +0200]: [22DD] C: A0002 STATUS INBOX (MESSAGES UNSEEN)
[16-Oct-2013 07:57:04 +0200]: [22DD] S: * STATUS "INBOX" (MESSAGES 12313 UNSEEN 17)
[16-Oct-2013 07:57:04 +0200]: [22DD] S: A0002 OK STATUS Completed.
[16-Oct-2013 07:57:04 +0200]: [22DD] C: A0003 SELECT INBOX
[16-Oct-2013 07:57:04 +0200]: [22DD] S: * FLAGS ($MDNSent $Forwarded \Draft \Answered \Flagged \Deleted \Seen \Recent)
[16-Oct-2013 07:57:04 +0200]: [22DD] S: * OK [PERMANENTFLAGS ($MDNSent $Forwarded \* \Draft \Answered \Flagged \Deleted \Seen)] Limited
[16-Oct-2013 07:57:04 +0200]: [22DD] S: * 12313 EXISTS
[16-Oct-2013 07:57:04 +0200]: [22DD] S: * 0 RECENT
[16-Oct-2013 07:57:04 +0200]: [22DD] S: * OK [UIDVALIDITY 1299433429] Ok
[16-Oct-2013 07:57:04 +0200]: [22DD] S: * OK [MYRIGHTS "acdilrsw"] ACL
[16-Oct-2013 07:57:04 +0200]: [22DD] S: A0003 OK [READ-WRITE] Ok
[16-Oct-2013 07:57:04 +0200]: [22DD] C: A0004 UID SEARCH 12313
[16-Oct-2013 07:57:04 +0200]: [22DD] S: * SEARCH 58219
[16-Oct-2013 07:57:04 +0200]: [22DD] S: A0004 OK SEARCH done.
[16-Oct-2013 07:57:04 +0200]: [22DD] C: A0005 LOGOUT
[16-Oct-2013 07:57:04 +0200]: [22DD] S: * BYE Courier-IMAP server shutting down
[16-Oct-2013 07:57:04 +0200]: [22DD] S: A0005 OK LOGOUT completed

The browser shows the error "Error connecting to imap server failed" after 1 minute (from 07:56:05 to 07:57:03).

If I want that the search works fine and see results I need to change a parameter on my 'php.ini' (Apache): 'default_socket_timeout' from 60 to 240 (for example), restart 'Apache' and the searches now works fine after 1 minute. It normally takes 2 minutes max to return results.

With Ubuntu Server 11.04 performance was better, but not much better. Ubuntu Server 12.04 upgrades have new versions of Apache, MySQL, etc. Server hardware performance is pretty good and I don't know where's the problem.

Do you know exactly where I have the performance issue? Can it be that MySQL is taking to work or IMAP is a problem? Do you see anything unusual in the logs?

Thanks anyway!
« Last Edit: October 16, 2013, 03:33:08 AM by Drakon »

Offline alec

  • Hero Member
  • *****
  • Posts: 1,363
Re: MySQL memory usage
« Reply #9 on: October 16, 2013, 03:43:46 AM »
First, Roundcube does not use cache for searching. Second, as I know hmailserver uses mysql as a storage, so you need to look in hmailserver/mysql for better performance. Finally, you might want to try with disabled sorting. Set messages list sort column to "None".

Offline Drakon

  • Jr. Member
  • **
  • Posts: 45
Re: MySQL memory usage
« Reply #10 on: October 16, 2013, 04:02:03 AM »
alec, thanks for your reply. I have Apache server. I have set message list sort column to "none" and yes, I supose that Roundcube don't use cache for searching.
Do you know where is the problem? MySQL config? I tried to default config and I change with 'mysqltuner' report.

Offline Drakon

  • Jr. Member
  • **
  • Posts: 45
Re: MySQL memory usage
« Reply #11 on: October 16, 2013, 04:06:40 AM »
My courier-imap config have a 'SORT' setting active by default:
IMAP_CAPABILITY="IMAP4rev1 UIDPLUS CHILDREN NAMESPACE THREAD=ORDEREDSUBJECT THREAD=REFERENCES SORT QUOTA IDLE"
« Last Edit: October 16, 2013, 04:16:45 AM by Drakon »

Offline alec

  • Hero Member
  • *****
  • Posts: 1,363
Re: MySQL memory usage
« Reply #12 on: October 16, 2013, 04:11:12 AM »
Why did I think you're using hmailserver? So, this is not mysql related, I suppose Courier doesn't use sql database as a storage. However, the log suggests you didn't set sorting to None - use messages list menu to make sure.

Offline Drakon

  • Jr. Member
  • **
  • Posts: 45
Re: MySQL memory usage
« Reply #13 on: October 16, 2013, 04:21:53 AM »
I have tried now select "None" on sorting grid list and takes 1 minute 26 seconds (the same text search over the same folder), more or less the same time.

Offline Drakon

  • Jr. Member
  • **
  • Posts: 45
Re: MySQL memory usage
« Reply #14 on: October 16, 2013, 10:43:28 AM »
On the 'session' log I have many errors but I don't think this will affect performance...

Quote
....
[16-Oct-2013 15:11:14 +0200]: Session auth check failed for kit0cibn617tgin310pv7bc6v1; timeslot = 2013-10-16 15:10:00
[16-Oct-2013 15:11:14 +0200]: Send new auth cookie for kit0cibn617tgin310pv7bc6v1: Sb5f4032dd2503ef3b8b33650294257125ae51f1a
[16-Oct-2013 15:11:14 +0200]: Session auth check failed for k9a5hj07t2h9am836nf2oe2ba3; timeslot = 2013-10-16 15:10:00
[16-Oct-2013 15:11:14 +0200]: Send new auth cookie for k9a5hj07t2h9am836nf2oe2ba3: S5df3155983de8136625b6b4cfde5366451fd2548
[16-Oct-2013 15:11:16 +0200]: Session auth check failed for fm6nnre8n9s61taddfm9874qh3; timeslot = 2013-10-16 15:10:00
[16-Oct-2013 15:11:16 +0200]: Send new auth cookie for fm6nnre8n9s61taddfm9874qh3: Sc60cdc92ca11483e5024758799f154e57392f442
[16-Oct-2013 15:12:10 +0200]: Session auth check failed for 1klj7ft275mbmaann0ccmlh7c3; timeslot = 2013-10-16 15:10:00
[16-Oct-2013 15:12:10 +0200]: Send new auth cookie for 1klj7ft275mbmaann0ccmlh7c3: S8854b728508f8634914a8b1eb76cba915d0ea4c5
[16-Oct-2013 16:00:00 +0200]: Session auth check failed for 1klj7ft275mbmaann0ccmlh7c3; timeslot = 2013-10-16 16:00:00
[16-Oct-2013 16:00:00 +0200]: Send new auth cookie for 1klj7ft275mbmaann0ccmlh7c3: S178477a20c1d226b2a0b67ccf9edc0387a3d6528
[16-Oct-2013 16:00:04 +0200]: Session auth check failed for fd8da664vpspfi4ooof7uh2hk0; timeslot = 2013-10-16 16:00:00
[16-Oct-2013 16:00:04 +0200]: Send new auth cookie for fd8da664vpspfi4ooof7uh2hk0: Sf66c3acb9e761602056fa8982985a641c9ad1bdd
[16-Oct-2013 16:00:05 +0200]: Session auth check failed for ufnhu35pv54km0hf62snsnorl1; timeslot = 2013-10-16 16:00:00
[16-Oct-2013 16:00:05 +0200]: Send new auth cookie for ufnhu35pv54km0hf62snsnorl1: S006a9e07af3216f9db4e7136a3b35c4eac1aac5e
[16-Oct-2013 16:00:05 +0200]: Session auth check failed for 5gv3aib2t7iespscmtbpbu1kn6; timeslot = 2013-10-16 16:00:00
[16-Oct-2013 16:00:05 +0200]: Send new auth cookie for 5gv3aib2t7iespscmtbpbu1kn6: Se8b28cdc1f3ab00ed0e1fa9a2ae34d1dfcf69c45
...

Can it be that MySQL is taking to work or IMAP is a problem?