Ajcody-Mysql-Topics: Difference between revisions
Line 187: | Line 187: | ||
mailboxId: 3 | mailboxId: 3 | ||
quotaUsed: 35912 | quotaUsed: 35912 | ||
And that mailboxID for the user can get mapped to the particular mailstore Mysql database by: | |||
$ zmprov ga ajcody@zimbra.DOMAIN.com zimbraMailHost | |||
# name ajcody@zimbra.DOMAIN.com | |||
zimbraMailHost: mail3.zimbra.DOMAIN.com | |||
So you would log onto that mailstore to do your mysql operations (searches). | |||
=====Queries Against Mysql For User Information===== | =====Queries Against Mysql For User Information===== |
Revision as of 19:19, 15 January 2009
- This article is NOT official Zimbra documentation. It is a user contribution and may include unsupported customizations, references, suggestions, or information. |
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:
- http://www.zimbra.com/forums/administrators/21519-memory-usage-slowness-backup-error.html#post107080
So other background info:
- http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/
- http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/
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.
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.
- We recommend this setting that might be causing issue:
- IO_DIRECT For MySQL Can Be Detrimental
- Mysql Bug - "innodb_flush_method = O_DIRECT may degrade performance of InnoDB on SAN":
- Comment about O_Direct from Linus:
- EXT3 not RAID-5 may be the cause of performance issues
- Numerous Items On This Blog Page:
- http://mysqldba.blogspot.com/2008_01_01_archive.html
- Most relevant are these sections:
- http://mysqldba.blogspot.com/2008_01_01_archive.html
Mysql Database Corruption/Repair
Please see the Mysql_Crash_Recovery page rather than the normal search results that give mysql-logger details.
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
Basic Query Of Information For Account In Mysql
- 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.
Zmprov And Mailbox ID
To get a users mailboxId on there mailstore server:
zmprov gmi USER@DOMAIN.com
Example output:
$ zmprov gmi ajcody@zimbra.DOMAIN.com mailboxId: 3 quotaUsed: 35912
And that mailboxID for the user can get mapped to the particular mailstore Mysql database by:
$ zmprov ga ajcody@zimbra.DOMAIN.com zimbraMailHost # name ajcody@zimbra.DOMAIN.com zimbraMailHost: mail3.zimbra.DOMAIN.com
So you would log onto that mailstore to do your mysql operations (searches).
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
The Mysql group_id Value
This is controlled by a local configuration value. It defaults to 100.
$ zmlocalconfig zimbra_mailbox_groups zimbra_mailbox_groups = 100