Ajcody-Mysql-Topics
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-Server-Topics#O_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