Ajcody-Mysql-Topics

Revision as of 21:24, 26 August 2010 by Ajcody (talk | contribs) (Mysql_Queries_To_See_What_Is_Shown_For_Message_ID's)

Attention.png - This article is NOT official Zimbra documentation. It is a user contribution and may include unsupported customizations, references, suggestions, or information.

Contents

Mysql

Actual Mysql Topics Homepage

Please see Ajcody-Mysql-Topics

Mysql Issues And Memory Usuage

My Ajcody-General-Notes#Trouble_Shooting_Memory_Issues has some stuff on memory troubleshooting - generalities.

Our performance guide for Mysql:

http://wiki.zimbra.com/index.php?title=Performance_Tuning_Guidelines_for_Large_Deployments#MySQL

Variable Within MySQL & Zimbra

These percentages below are used against physical RAM - disregard swap in the percentage division.

From the above url, we get some details.

"By default, we try to reserve 30% of system memory for use by this process, and 40% for use by MySQL."
# ZCS 5.0 and later
$ zmlocalconfig -e mailboxd_java_heap_memory_percent=40
Java - mailboxd_java_heap_memory_percent=

The command to set the java percentage is:

 zmlocalconfig -e mailboxd_java_heap_memory_percent=30
Mysql - mysql_memory_percent & my.cnf w/ innodb_buffer_pool_size

Looks like there's an issue with the command/variable (zmlocalconfig -e mysql_memory_percent=##) in regards to MySQL though.

"There is a local config variable [mysql_memory_percent] for mysql memory percent, but today my.cnf doesn't get rewritten after install, so you have to edit my.cnf for this setting if you want to change it."

In summary, this variable [ mysql_memory_percent ] for MySQL is basically not working.

Do this to get memory in bytes for division numbers:

free -b

Figure out what your % in bytes using the physical memory (Mem:) output. Now edit the /opt/zimbra/conf/my.cnf file to change the following line with your memory percentage in bytes:

vi /opt/zimbra/conf/my.cnf
#Modify this var to adjust memory usage
innodb_buffer_pool_size = xxx

This will require a restart.

A GREAT forum post on this is here:

So other background info:

Mysql And Swap

See this article first before exploring the options below.

Should you have your swap file enabled while running MySQL ?

Set swappiness Kernel Var To 0

Source of this summary is here: [http://www.mysqlperformanceblog.com/2007/09/28/heikki-tuuri-to-answer-your-in-depth-innodb-questions/#comment-174442 Swappiness To 0"

Set it on running box:

echo 0 > /proc/sys/vm/swappiness

Set it to persist after reboot - vi /etc/sysctl.conf :

vm.swappiness = 0

The value is a percentage. By setting it to 100, Linux will always swap programs out and never shrink the buffer cache.

Swap Ram Disk Instead Of Disk

Source of this summary is here:

MySQL and the Linux swap problem

Make a (or some) swap disks:

mkdir /mnt/ram0
dd bs=1024 count=14634 if=/dev/zero of=/mnt/ram0/swapfile
mkswap /mnt/ram0/swapfile
swapon /mnt/ram0/swapfile

Will look like this:

[root@mail3 ~]# mkdir /mnt/ram0

[root@mail3 ~]# dd bs=1024 count=14634 if=/dev/zero of=/mnt/ram0/swapfile
14634+0 records in
14634+0 records out
14985216 bytes (15 MB) copied, 0.246329 seconds, 60.8 MB/s

[root@mail3 ~]# mkswap /mnt/ram0/swapfile 
Setting up swapspace version 1, size = 14979 kB

[root@mail3 ~]# free
             total       used       free     shared    buffers     cached
Mem:       1555472    1461200      94272          0     159400     344328
-/+ buffers/cache:     957472     598000
Swap:      2031608        104    2031504

[root@mail3 ~]# swapon /mnt/ram0/swapfile 

[root@mail3 ~]# free
             total       used       free     shared    buffers     cached
Mem:       1555472    1476252      79220          0     159404     344376
-/+ buffers/cache:     972472     583000
Swap:      2046232        104    2046128

[root@mail3 ~]# swapon -s
Filename				Type		Size	Used	Priority
/dev/mapper/VolGroup00-LogVol01         partition	2031608	104	-1
/mnt/ram0/swapfile 
                     file		14624	0	-2
[root@mail3 ~]# cat /proc/swaps 
Filename				Type		Size	Used	Priority
/dev/mapper/VolGroup00-LogVol01         partition	2031608	104	-1
/mnt/ram0/swapfile                      file		14624	0	-2


Just add those lines to your relevant startup file, like /etc/rc.d/rc.local, and it’ll persist after reboots. </pre>

O_DIRECT On Linux And INNODB To Fix Swapping

Source reference for this part is here:

Using O_DIRECT on Linux and INNODB to Fix Swap Insanity

See also: Ajcody-Mysql-Topics#IO_DIRECT_And_Ext3_On_Linux_As_Possible_Reason_For_Corruption

IO_DIRECT And Ext3 On Linux As Possible Reason For Corruption

This is being investigated. Below are my notes on the issue.

Mysql Database Corruption/Repair/Checks

Please see the Mysql_Crash_Recovery page rather than the normal search results that give mysql-logger details.

Mysql Table Checks

Mysql Table Types - InnoDB or MyISAM

Tables are either InnoDB or MyISAM. Putting this information here because if a table is in need of repair or something, the steps to fix it might be different depending on the tables ENGINE type.

Example for MyISAM:

$ mysql -e "show create table zimbra.jiveRoster"
 [cut]
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | 
 [cut]

Example for InnoDB:

$ mysql -e "show create table zimbra.mailbox"
 [cut]
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | 
 [cut]
check zimbra.mailbox database tables
$ mysql -e "check table zimbra.mailbox"
+----------------+-------+----------+----------+
| Table          | Op    | Msg_type | Msg_text |
+----------------+-------+----------+----------+
| zimbra.mailbox | check | status   | OK       | 
+----------------+-------+----------+----------+
check mboxgroup[#].mail_item table
$ mysql -e "check table mboxgroup3.mail_item"
+----------------------+-------+----------+----------+
| Table                | Op    | Msg_type | Msg_text |
+----------------------+-------+----------+----------+
| mboxgroup3.mail_item | check | status   | OK       | 
+----------------------+-------+----------+----------+
jive.Roster or zimbra.jiveUserProp Errors From Daily Report With mysqlcheck Errors
Please note, these tables are MyISAM. Don't use the below steps for other type of tables as each table might be treated differently or with extra caution. [InnoDB vs MyISAM]

Your report from /opt/zimbra/libexec/zmdbintegrityreport might give the following for example:


Database errors found.
/opt/zimbra/mysql/bin/mysqlcheck --defaults-file=/opt/zimbra/conf/my.cnf -S /opt/zimbra/db/mysql.sock
 -A -C -s -u root --password=[mysql password]
zimbra.jiveID
 warning  : 1 client is using or hasn't closed the table properly  zimbra.jiveRoster  warning  :
  1 client is using or hasn't closed the table properly  zimbra.jiveUserProp
 warning  : 2 clients are using or haven't closed the table properly

Attempt the following:

mysql -e "check table zimbra.jiveRoster" 
+-------------------+-------+----------+----------+ 
| Table             | Op    | Msg_type | Msg_text | 
+-------------------+-------+----------+----------+ 
| zimbra.jiveRoster | check | status   | OK       |  
+-------------------+-------+----------+----------+

To see if the data "looks" fine or works correctly against a select, do:

mysql -e "select * from zimbra.jiveRoster"

Note, if the above command might dump out a lot of data, try:

mysql -e "select * from zimbra.jiveRoster" > /tmp/zimbra.jiveRoster.out

Yours might show something different for Msg_text. If you need to repair, you would run:

mysql -e "repair table zimbra.jiveRoster"

You can also do the same above but replacing zimbra.jiveRoster with zimbra.jiveUserProp .

Manual Change Of Mysql Passwords

Please see:

http://wiki.zimbra.com/index.php?title=Issues_with_mysql_and_logmysql_passwords

Working Within Mysql For Accounts

Note, the mailboxId is specific to the mailstore the user resides on. The zimbraId is the users unique id system wide - zmprov ga user@domain.com zimbraId .

Other References:

Getting User Variables We Need To Query MySQL

Getting The Users mailboxId # - zmprov gmi user@domain

For Mysql, this # returned below will become apart of mailbox_id=### . Example used is mailbox_id=3 .

Note, the mailboxId is specific to the mailstore the user resides on. The zimbraId is the users unique id system wide - zmprov ga user@domain.com zimbraId .
[root@mail39 ~]# su - zimbra
[zimbra@mail39 ~]$ zmprov gmi ajcody@mail39.zimbra.DOMAIN.com
  mailboxId: 3
  quotaUsed: 169831
Getting The Users Mailstore That Their DB Resides On - zmprov ga user@domain zimbraMailHost

The mailboxId found above for the user is unique to their actual mailstore and the db that resides there. Again:

Note, the mailboxId is specific to the mailstore the user resides on. The zimbraId is the users unique id system wide - zmprov ga user@domain.com zimbraId .

To get their particular mailstore their database resides on:

$ zmprov ga ajcody@mail39.zimbra.DOMAIN.com zimbraMailHost
# name ajcody@mail39.zimbra.DOMAIN.com
zimbraMailHost: mail39.zimbra.DOMAIN.com

So you would now log onto that mailstore to do your mysql operations (searches).

Confirm zimbra_mailbox_groups Variable

Need to confirm zimbra_mailbox_groups variable to get the users mboxgroup# since we'll then need to run the expression below to determine it.

[zimbra@mail39 ~]$ zmlocalconfig zimbra_mailbox_groups
zimbra_mailbox_groups = 100
The Mysql group_id Value

The group_id is controlled by a local configuration value. It defaults to 100.

 $ zmlocalconfig zimbra_mailbox_groups
   zimbra_mailbox_groups = 100

You'll see group_id in the following:

 $ mysql -e 'SELECT * FROM zimbra.mailbox WHERE id=3\G'

 *************************** 1. row ***************************
                 id: 3
           group_id: 3

*** CUT REST OF OUTPUT ***
Get Users mboxgroup #

Finally confirming what the mboxgroup is for the user by running the following below. The mboxgroup is comprised of multiple users that are unique by way of their mailbox_id number. This is to minimize the number of db's that are needed on the mailstore, rather than doing one db per user.

3 ='s the users mailboxId
100 ='s the variable from zimbra_mailbox_groups

For Mysql, this # returned below will become apart of mboxgroup###.mail_item = mboxgroup3.mail_item

 
[zimbra@mail39 ~]$ expr 3 % 100
3

Queries Against Mysql For User Information

By mailboxId

You'll need to know the mailboxId for the user for this. [ zmprov gmi USER@DOMAIN.com ]

Format:

$ mysql -e 'SELECT * FROM zimbra.mailbox WHERE id=<mailboxID of USER>\G'

Working example:

 $ mysql -e 'SELECT * FROM zimbra.mailbox WHERE id=3\G'

 *************************** 1. row ***************************
                 id: 3
           group_id: 3
         account_id: 3be48432-926c-4a54-bd66-3b16185a37a4
    index_volume_id: 2
 item_id_checkpoint: 339
      contact_count: 5
    size_checkpoint: 35912
  change_checkpoint: 7200
      tracking_sync: 0
      tracking_imap: 0
     last_backup_at: 1231567229
            comment: ajcody@zimbra.DOMAIN.com
   last_soap_access: 1229584283
       new_messages: 1
 idx_deferred_count: 0
By zimbraId

You'll need to know the zimbraId for the user for this.

Via zmprov:

$ zmprov ga ajcody@zimbra.DOMAIN.com zimbraId
  # name ajcody@zimbra.DOMAIN.com
  zimbraId: 3be48432-926c-4a54-bd66-3b16185a37a4

Or ldap:

$ su - zimbra
$ source ~/bin/zmshutil
$ zmsetvars
$ ldapsearch -LLL -x -H $ldap_master_url -D $zimbra_ldap_userdn -w $zimbra_ldap_password "mail=ajcody@zimbra.DOMAIN.com" zimbraId
   dn: uid=ajcody,ou=people,dc=zimbra,dc=DOMAIN,dc=com
   zimbraId: 3be48432-926c-4a54-bd66-3b16185a37a4

Format:

$ mysql -e 'SELECT * FROM zimbra.mailbox WHERE account_id=<zimbraID of USER>\G'

Working example:

 $ mysql -e 'SELECT * FROM zimbra.mailbox WHERE account_id="3be48432-926c-4a54-bd66-3b16185a37a4"\G'

 *************************** 1. row ***************************
                 id: 3
           group_id: 3
         account_id: 3be48432-926c-4a54-bd66-3b16185a37a4
    index_volume_id: 2
 item_id_checkpoint: 339
      contact_count: 5
    size_checkpoint: 35912
  change_checkpoint: 7200
      tracking_sync: 0
      tracking_imap: 0
     last_backup_at: 1231567229
            comment: ajcody@zimbra.DOMAIN.com
   last_soap_access: 1229584283
       new_messages: 1
 idx_deferred_count: 0
By Comment Field - I.E. Username

This way searches against the "comment field". The comment field is mostly correct, but it is deemed unreliable since there are some corner cases where it can be duplicated. Use it in a pinch.

Format:

$ mysql -e 'SELECT * FROM zimbra.mailbox WHERE comment LIKE "<USER>%"\G'

Working example:

 $ mysql -e 'SELECT * FROM zimbra.mailbox WHERE comment LIKE "ajcody%"\G'

 *************************** 1. row ***************************
                 id: 3
           group_id: 3
         account_id: 3be48432-926c-4a54-bd66-3b16185a37a4
    index_volume_id: 2
 item_id_checkpoint: 339
      contact_count: 5
    size_checkpoint: 35912
  change_checkpoint: 7200
      tracking_sync: 0
      tracking_imap: 0
     last_backup_at: 1231567229
            comment: ajcody@zimbra.DOMAIN.com
   last_soap_access: 1229584283
       new_messages: 1
  idx_deferred_count: 0

Deleting An Account In Mysql

This comes up in very rare circumstances, usually when an account rename was attempted and for some reason it fails -- leaving the account in an odd state, where ldap and mysql data no longer match up. Use with extreme caution and under guidance of support staff.

The Steps To Delete User In Mysql

Caution - This Should Almost Never Need To Be Done. Usually Only Needed When Ldap Doesn't Have Account Anymore And For Some Reason Account Still Exists In Mysql

Use the above section, Ajcody-Mysql-Topics#Basic_Query_Of_Information_For_Account_In_Mysql , to get mailboxId or other needed data. The below example will most likely fail with the zmprov command because of missing data in ldap. You still might need to do the expr example below when you do your mysql delete commands.

# Fetch the user's mailbox Id
# zmprov will most likely fail in the situation where you need to do this process
# Use Ajcody-Mysql-Topics#Basic_Query_Of_Information_For_Account_In_Mysql
# alternative methods to get this variable - mailboxId

[root@]# su - zimbra
[zimbra@]$ zmprov getMailboxInfo user1@example.com
mailboxId: 11
quotaUsed: 30620 

# Fetch the user's mboxgroup Id
# Default is 100 for zimbra_mailbox_groups
$ zmlocalconfig zimbra_mailbox_groups
  zimbra_mailbox_groups = 100
# expr [mailboxId] % [zimbra_mailbox_groups]
$ expr 11 % 100
11


Each user is referenced by a unique mailboxId within Mysql. Each mailstore has it's own Mysql database and therefore the user mailboxId's are only locally unique - i.e., the id isn't a unique global variable. Replace id=#####, mboxgroup## and mailbox_id=##### with relevant data. An example might have mboxgroup77 , id=18577 and mailbox_id=18577.

[root@]# su - zimbra
[zimbra@]# mysql
mysql> SET foreign_key_checks = 0;
mysql> use mboxgroup##;
mysql> delete from appointment where mailbox_id=#####;
mysql> delete from data_source_item where mailbox_id=#####;
mysql> delete from imap_folder where mailbox_id=#####;
mysql> delete from imap_message where mailbox_id=#####;
mysql> delete from mail_item where mailbox_id=#####;
mysql> delete from open_conversation where mailbox_id=#####;
mysql> delete from pop3_message where mailbox_id=#####;
mysql> delete from revision where mailbox_id=#####;
mysql> delete from tombstone where mailbox_id=#####;
mysql> use zimbra;
mysql> delete from mailbox where id=#####;
mysql> delete from mailbox_metadata where mailbox_id=#####;
mysql> SET foreign_key_checks = 1
mysql> quit
[zimbra@]# zmprov fc account

Mysql Queries To See What Is Shown For Message ID's

See Ajcody-Notes-No-Such-Blob#Mysql_Queries_To_See_What_Is_Shown_For_Missing_Message_ID

Mysql Tables

zimbra database

SHOW zimbra Database Tables

This output shows the tables of the zimbra database.

$ mysql -e 'SHOW tables FROM zimbra'
+----------------------+
| Tables_in_zimbra     |
+----------------------+
| config               | 
| current_volumes      | 
| deleted_account      | 
| jiveExtComponentConf | 
| jiveGroupProp        | 
| jiveGroupUser        | 
| jiveID               | 
| jiveOffline          | 
| jivePrivacyList      | 
| jivePrivate          | 
| jiveProperty         | 
| jiveRemoteServerConf | 
| jiveRoster           | 
| jiveRosterGroups     | 
| jiveSASLAuthorized   | 
| jiveUserProp         | 
| jiveVCard            | 
| jiveVersion          | 
| mailbox              | 
| mailbox_metadata     | 
| mucAffiliation       | 
| mucConversationLog   | 
| mucMember            | 
| mucRoom              | 
| mucRoomProp          | 
| out_of_office        | 
| scheduled_task       | 
| service_status       | 
| table_maintenance    | 
| volume               | 
+----------------------+
DESCRIBE zimbra.mailbox Database Table

Description of the zimbra.mailbox table in the zimbra database.

$ mysql -e 'DESCRIBE zimbra.mailbox'
+--------------------+---------------------+------+-----+---------+-------+
| Field              | Type                | Null | Key | Default | Extra |
+--------------------+---------------------+------+-----+---------+-------+
| id                 | int(10) unsigned    | NO   | PRI | NULL    |       | 
| group_id           | int(10) unsigned    | NO   |     | NULL    |       | 
| account_id         | varchar(127)        | NO   | UNI | NULL    |       | 
| index_volume_id    | tinyint(3) unsigned | NO   | MUL | NULL    |       | 
| item_id_checkpoint | int(10) unsigned    | NO   |     | 0       |       | 
| contact_count      | int(10) unsigned    | YES  |     | 0       |       | 
| size_checkpoint    | bigint(20) unsigned | NO   |     | 0       |       | 
| change_checkpoint  | int(10) unsigned    | NO   |     | 0       |       | 
| tracking_sync      | int(10) unsigned    | NO   |     | 0       |       | 
| tracking_imap      | tinyint(1)          | NO   |     | 0       |       | 
| last_backup_at     | int(10) unsigned    | YES  | MUL | NULL    |       | 
| comment            | varchar(255)        | YES  |     | NULL    |       | 
| last_soap_access   | int(10) unsigned    | NO   |     | 0       |       | 
| new_messages       | int(10) unsigned    | NO   |     | 0       |       | 
| idx_deferred_count | int(10) unsigned    | NO   |     | 0       |       | 
+--------------------+---------------------+------+-----+---------+-------+

mboxgroup[#] database

Users are associated with a mboxgroup database. Each mboxgroup database will be comprised of multiple users, the users and their data are unique by way for the mailbox_id of the user.

SHOW mboxgroup[#] Database Tables

This output shows the tables of the mboxgroup# database.

$ mysql -e 'SHOW tables FROM mboxgroup3'
+----------------------+
| Tables_in_mboxgroup3 |
+----------------------+
| appointment          | 
| data_source_item     | 
| imap_folder          | 
| imap_message         | 
| mail_item            | 
| open_conversation    | 
| pop3_message         | 
| revision             | 
| tombstone            | 
+----------------------+
DESCRIBE mboxgroup[#].mail_item Database Table

Description of the mail_item table in the mboxgroup# database.

$ mysql -e 'DESCRIBE mboxgroup3.mail_item'
+--------------+---------------------+------+-----+---------+-------+
| Field        | Type                | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| mailbox_id   | int(10) unsigned    | NO   | PRI | NULL    |       | 
| id           | int(10) unsigned    | NO   | PRI | NULL    |       | 
| type         | tinyint(4)          | NO   |     | NULL    |       | 
| parent_id    | int(10) unsigned    | YES  |     | NULL    |       | 
| folder_id    | int(10) unsigned    | YES  |     | NULL    |       | 
| index_id     | int(10) unsigned    | YES  |     | NULL    |       | 
| imap_id      | int(10) unsigned    | YES  |     | NULL    |       | 
| date         | int(10) unsigned    | NO   |     | NULL    |       | 
| size         | bigint(20) unsigned | NO   |     | NULL    |       | 
| volume_id    | tinyint(3) unsigned | YES  | MUL | NULL    |       | 
| blob_digest  | varchar(28)         | YES  |     | NULL    |       | 
| unread       | int(10) unsigned    | YES  |     | NULL    |       | 
| flags        | int(11)             | NO   |     | 0       |       | 
| tags         | bigint(20)          | NO   |     | 0       |       | 
| sender       | varchar(128)        | YES  |     | NULL    |       | 
| subject      | text                | YES  |     | NULL    |       | 
| name         | varchar(128)        | YES  |     | NULL    |       | 
| metadata     | mediumtext          | YES  |     | NULL    |       | 
| mod_metadata | int(10) unsigned    | NO   |     | NULL    |       | 
| change_date  | int(10) unsigned    | YES  |     | NULL    |       | 
| mod_content  | int(10) unsigned    | NO   |     | NULL    |       | 
+--------------+---------------------+------+-----+---------+-------+
Description of mboxgroup[#].mail_item Type
/** Item is a standard {@link Folder}. */
    public static final byte TYPE_FOLDER       = 1;
    /** Item is a saved search {@link SearchFolder}. */
    public static final byte TYPE_SEARCHFOLDER = 2;
    /** Item is a user-created {@link Tag}. */
    public static final byte TYPE_TAG          = 3;
    /** Item is a real, persisted {@link Conversation}. */
    public static final byte TYPE_CONVERSATION = 4;
    /** Item is a mail {@link Message}. */
    public static final byte TYPE_MESSAGE      = 5;
    /** Item is a {@link Contact}. */
    public static final byte TYPE_CONTACT      = 6;
    // /** Item is a {@link InviteMessage} with a <tt>text/calendar</tt> MIME part. */
    // public static final byte TYPE_INVITE       = 7;   // SKIP 7 FOR NOW!
    /** Item is a bare {@link Document}. */
    public static final byte TYPE_DOCUMENT     = 8;
    /** Item is a {@link Note}. */
    public static final byte TYPE_NOTE         = 9;
    /** Item is a memory-only system {@link Flag}. */
    public static final byte TYPE_FLAG         = 10;
    /** Item is a calendar {@link Appointment}. */
    public static final byte TYPE_APPOINTMENT  = 11;
    /** Item is a memory-only, 1-message {@link VirtualConversation}. */
    public static final byte TYPE_VIRTUAL_CONVERSATION = 12;
    /** Item is a {@link Mountpoint} pointing to a {@link Folder},
     *  possibly in another user's {@link Mailbox}. */
    public static final byte TYPE_MOUNTPOINT   = 13;
    /** Item is a {@link WikiItem} */
    public static final byte TYPE_WIKI         = 14;
    /** Item is a {@link Task} */
    public static final byte TYPE_TASK         = 15;
    /** Item is a {@link Chat} */
    public static final byte TYPE_CHAT         = 16;
Jump to: navigation, search