100 Mysql Tuning Tips

MySQL Server Hardware and OS Tuning:

1. Have enough physical memory to load your entire InnoDB file into memory – InnoDB is much faster when the file can be accessed in memory rather than from disk.
2. Avoid Swap at all costs – swapping is reading from disk, its slow.
3. Use Battery-Backed RAM.
4. Use an advanced RAID – preferably RAID10 or higher.
5. Avoid RAID5 – the checksum needed to ensure integrity is costly.
6. Separate your OS and data partitions, not just logically, but physically – costly OS writes and reads will impact your database performance.
7. Put your mysql temp space and replication logs on a separate partition than your data – background writes will impact your database when it goes to write/read from disk.
8. More disks equals more speed.
9. Faster disks are better.
10. Use SAS over SATA.
11. Smaller disks are faster than larger disks, especially in RAID configs.
12. Use Battery-Backed Cache RAID controllers.
13. Avoid software raids.
14. Consider using Solid State IO Cards (not disk drives) for your data partition – these cards can sustain over 2GB/s writes for almost any amount of data.
15. On Linux set your swappiness value to 0 – no reason to cache files on a database server, this is more of a web server or desktop advantage.
16. Mount filesystem with noatime and nodirtime if available – no reason to update database file modification times for access.
17. Use XFS filesystem – a faster, smaller filesystem than ext3 and has more options for journaling, also ext3 has been shown to have double buffering issues with MySQL.
18. Tune your XFS filesystem log and buffer variables – for maximum performance benchmark.
19. On Linux systems, use NOOP or DEADLINE IO scheduler – the CFQ and ANTICIPATORY scheduler have been shown to be slow vs NOOP and DEADLINE scheduler.
20. Use a 64-bit OS – more memory addressable and usable to MySQL.
21. Remove unused packages and daemons from servers – less resource stealing.
22. Put your host that use MySQL and your MySQL host in a hosts file – no dns lookups.
23. Never force kill a MySQL process – you will corrupt your database and be running for the backups.
24. Dedicate your server to MySQL – background processes and other services can steal from the db cpu time.

MySQL Configuration:

25. Use innodb_flush_method=O_DIRECT to avoid a double buffer when writing.
26. Avoid O_DIRECT and EXT3 filesystem – you will serialize all your writes.
27. Allocate enough innodb_buffer_pool_size to load your entire InnoDB file into memory – less reads from disk.
28. Do not make innodb_log_file_size too big, with faster and more disks – flushing more often is good and lowers the recovery time during crashes.
29. Do not mix innodb_thread_concurrency and thread_concurrency variables – these two values are not compatible.
30. Allocate a minimal amount for max_connections – too many connections can use up your RAM and lock up your MySQL server.
31. Keep thread_cache at a relatively high number, about 16 – to prevent slowness when opening connections.
32. Use  skip-name-resolve – to remove dns lookups.
33. Use query cache if your queries are repetitive and your data does not change often – however using query cache on data that changes often will give you a performance hit.
34. Increase temp_table_size – to prevent disk writes.
35. Increase max_heap_table_size – to prevent disk writes.
36. Do not set your sort_buffer_size too high – this is per connection and can use up memory fast.
37. Monitor key_read_requests and key_reads to determine your key_buffer size – the key read requests should be higher than your key_reads, otherwise you are not efficiently using your key_buffer.
38. Set innodb_flush_log_at_trx_commit = 0 will improve performance, but leaving it to default (1), you will ensure data integrity, you will also ensure replication is not lagging
39. Have a test environment where you can test your configs and restart often, without affecting production.

MySQL Schema Optimization:

40. Keep your database trim.
41. Archive old data – to remove excessive row returns or searches on queries.
42. Put indexes on your data.
43. Do not overuse indexes, compare with your queries.
44. Compress text and blob data types – to save space and reduce number of disk reads.
45. UTF 8 and UTF16 is slower than latin1.
46. Use Triggers sparingly.
47. Keep redundant data to a minimum – do not duplicate data unnecessarily.
48. Use linking tables rather than extending rows.
49. Pay attention to your data types, use the smallest one possible for your real data.
50. Separate blob/text data from other data if other data is often used for queries when blob/text are not.
51. Check and optimize tables often.
52. Rewrite InnoDB tables often to optimize.
53. Sometimes, it is faster to drop indexes when adding columns and then add indexes back.
54. Use different storage engines for different needs.
55. Use ARCHIVE storage engine for Logging tables or Auditing tables – this is much more efficient for writes.
56. Store session data in memcache rather than MySQL – memcache allows for auto-expiring values and prevents you from having to create costly reads and writes to MySQL for temporal data.
57. Use VARCHAR instead CHAR when storing variable length strings – to save space since CHAR is fixed length and VARCHAR is not (utf8 is not affected by this).
58. Make schema changes incrementally – a small change can have drastic effects.
59. Test all schema changes in a development environment that mirrors production.
60. Do NOT arbitrarily change values in your config file, it can have disastrous affects.
61. Sometimes less is more in MySQL configs.
62. When in doubt use a generic MySQL config file.

Query Optimization:

63. Use the slow query log to find slow queries.
64. Use EXPLAIN to determine queries are functioning appropriately.
65. Test your queries often to see if they are performing optimally – performance will change over time.
66. Avoid count(*) on entire tables, it can lock the entire table.
67. Make queries uniform so subsequent similar queries will use query cache.
68. Use GROUP BY instead of DISTINCT when appropriate.
69. Use indexed columns in WHERE, GROUP BY, and ORDER BY clauses.
70. Keep indexes simple, do not reuse a column in multiple indexes.
71. Sometimes MySQL chooses the wrong index, use USE INDEX for this case
72. Check for issues using SQL_MODE=STRICT.
73. Use a LIMIT on UNION instead of OR for less than 5 indexed fields.
74. Use INSERT ON DUPLICATE KEY or INSERT IGNORE instead of UPDATE to avoid the SELECT prior to update.
75. Use a indexed field and ORDER BY instead of MAX.
76. Avoid using ORDER BY RAND().
77. LIMIT M,N can actually slow down queries in certain circumstances, use sparingly.
78. Use UNION instead of sub-queries in WHERE clauses.
79. For UPDATES, use SHARE MODE to prevent exclusive locks.
80. On restarts of MySQL, remember to warm your database, to ensure that your data is in memory and queries are fast.
81. Use DROP TABLE then CREATE TABLE instead of DELETE FROM to remove all data from a table.
82. Minimize the data in your query to only the data you need, using * is overkill most of the time.
83. Consider persistent connections instead of multiple connections to reduce overhead.
84. Benchmark queries, including using load on the server, sometimes a simple query can have affects on other queries.
85. When load increases on your server, use SHOW PROCESSLIST to view slow/problematic queries.
86. Test all suspect queries in a development environment where you have mirrored production data.

MySQL Backup Procedures:

87. Backup from secondary replicated server.
88. Stop replication during backups to prevent inconsistencies on data dependencies and foreign constraints.
89. Stop MySQL altogether and take a backup of the database files.
90. Backup binary logs at same time as dumpfile if MySQL dump used – to make sure replication does not break.
91. Do not trust an LVM snapshot for backups – this could create data inconsistencies that will give you issues in the future.
92. Make dumps per table for easier single table recovery – if data is isolated from other tables.
93. Use –opt when using mysqldump.
94. Check and Optimize tables before a backup.
95. When importing temporarily disable foreign constraints for a faster import.
96. When importing temporarily disable unique checks for a faster import.
97. Calculate size of database/tables data and indexes after each backup to monitor growth.
98. Monitor slave replication for errors and delay with a cron script.
99. Perform Backups regularly.
100. Test your backups regularly.

Time Limit Using IPTABLES

Many people might not have gauged the power of IPTABLES!

One of the nice features is that you can use it to limit the time limit outbound traffic is allowed to pass through this firewall. (Say you have a windows network and this Linux machine is the gateway and you want to limit the time limit within which the user can access internet using iptables.)
You will realise that most software firewalls like Untangle or Smoothwall dont provide the feature of Time Limit Control in their open source versions. So fear not, heres the way to go!

To limit Time of Internet Access Allowed (You can change the time parameters in the rule as per your requirement):

iptables -I FORWARD 7 -s <SOURCEIP> -p tcp -m multiport –dport http,https -o eth0 -i eth1 -m time –timestart 16:00 –timestop 18:00 –weekdays Mon,Tue,Wed,Thu,Fri,Sat,Sun -j ACCEPT

Limit Bandwidth Using IPTABLES

Many people might not have gauged the power of IPTABLES!

One of the nice features is that you can use it to limit the bandwidth of outbound traffic going through this firewall. (Say you have a windows network and this Linux machine is the gateway and you want to limit bandwidth of a particular machine using iptables.)
You will realise that most software firewalls like Untangle or Smoothwall don't provide the feature of bandwidth quota control in their open source versions. So fear not, heres the way to go!

To limit Bandwidth Quota (The below example will allot within 2GB max usage):

iptables -I FORWARD 5  -s <SOURCEIP> -p tcp -m quota –quota 2147483648 -j ACCEPT
iptables -I FORWARD 6  -s <SOURCEIP>  -j DROP

Load Average Explained

System Administrators and users working with Unix / Linux Servers must have noticed the "Load Average" but never really put much thought into how this number is generated. So let us dis cus this parameter in detail.
Load averages are generated using many different metrics, disk load, CPU usage, memory usage and much more. The load average number is the total number of processes waiting in the run queue.

One of the ways to view the Load Average is running the "top" command. Using "top" provides insight into the system’s general health status. The top command provides a view of the following main health statistics:

    Uptime (including days, hours and minutes, the user count and current time and load average)
    The total number of processes along with the number of running processes and sleeping processes
    Memory usage including total memory, used and free memory
    Swap memory usage (useful for troubleshooting slow systems)

The top command looks like this:

The topmost process on the top process list is the process using the highest percentage of CPU. The top command is available on most Unix and Linux variants.


As we’ll learn, CPU usage is not directly related to load average. Load average is an overall view of the system. Load Average value can be high generally for one of the following reasons:

1. CPU it self is busy/overloaded  in processing things
2. Processes  (typically called Blocking process) in run queue, waiting for I/O

If the first two figures %us and %sy are nearly 90% then Cpu is overloaded and needs to be upgrade. If the 5th figure in same line %wa is shows high numbers means there are some jobs in run queue waiting for I/O (may be trying to read data from mounting disk). Then look for that.

To diagnose which process causing this just run a command and look ‘D’ under 8th column STAT one. There may be lots of R and S as well.:
# ps faux

The explanation of the symbols D, R & S are given below:

D —> Waiting for either (CPU, Disk I/O, Network I/O)
R —-> Running
S —–> Sleeping

Also you can use the command below to find the process with stat D :

# ps axo stat,pid | grep D

One quick rule of thumb I try to use (to make sure systems do not see any latency … e.g. slow processes, slow page loads, slow queries etc…) is to keep the number of waiting processes in the run queue (the load average represents total number of processes that had to wait for resources in the last 1, 5 and 15 minutes) under the total number of processors in the machine.

To check the number of processors (recognized by the Unix/Linux OS) run the following command:

# cat /proc/cpuinfo | grep "processor" | wc -l

Keep in mind this command will return the total number of recognized processors. If you have a hyper-threaded Pentium IV you’ll see two processors when really you only have one core. The same rule applies with the load average rule of thumb.

Remember, keeping the load average under the total processor count will make for a healthy and fast-responding system.

Show All Cronjobs For All Users

To list all cronjobs for all users in a server you can run the following one-liner command in linux:

for user in $(cut -f1 -d: /etc/passwd); do echo "#### CRONJOBS FOR $user ####:";crontab -u $user -l;  done


Alternatively, you can create a shell script and put the following content in it and execute it:

#!/bin/bash
for user in $(cut -f1 -d: /etc/passwd); do echo "#### CRONJOBS FOR $user ####:";crontab -u $user -l;  done
exit 0

Note: Don't forget to check the system cron files like cron.daily / hourly etc because this script doesn't take them into account.

Extract a Table from Mysqldump File

Sometimes, you may need to extract a single table from a mysqldump file, because the database size is very large and you are not being able to open that file with any text editor. In such a case you will have to use the "awk" command in Linux to extract the required table from the dump. You can use your innovation to use this concept for other file manipulation of similar kinds.

First, you have to know where in your mysqldump output you want to begin your extraction, and where you want to end it. The key here is finding something unique at the beginning and ending of the block that won’t be found anywhere else.

A sample mysqldump contains something like the following:

--
-- Table structure for table `table1`
--
...
DROP TABLE IF EXISTS `table1`;
CREATE TABLE `test1` ( ...
LOCK TABLES `test1` WRITE;
INSERT INTO `test1` VALUES (1,0,’2 ...
UNLOCK TABLES;
...
–-
–- Table structure for table `table2`
–-
As you can see, we have a line with the comment "Table structure for table `table1`", then all of the dropping, creating, and inserting for the table, and then another comment for the next table. These two lines are perfect for grabbing all of the operations pertinent to our one table.

To extract the dump for a single table from an entire database dump, run the following from a command prompt:

#     awk '/Table structure for table `table1`/,/Table structure for table `table2`/{print}' databasedump.sql > extracted_table.sql

The above command searches through the dump file, and as soon as it matches a line containing the first search string (denoted by the first set of slashes), it prints that line and every subsequent line until it encounters a line containing the second search string (denoted by the second set of slashes). FYI, the periods surrounding the table names above are wildcard characters.
Now the extracted_table.sql file contains the SQL to restore your table.
Finally, There are usually various parameters at the top of your mysqldump file that you may need to set before restoring your table, depending on the complexity of your database (i.e. disabling foreign key checks.)

To restore your table, run:

# mysql -u user -ppassword databasename < extracted_table.sql

Shell Script To List All Files Changed On That Day

Sometimes System Admin's are asked to become sleuths and find out which files were modified on a specific day to find out what went wrong.

This script will list all the files in a directory that have been modified that day.

1. Copy the Shell script below and save it as modified_today.sh
2. Make it executable by chmod +x modified_today.sh
3. Now run the script as per the syntax below-

The syntax is :
sh modified_today.sh /name/of/directory

Note: You can also modify the 'mtime' parameter to make it display data for 'n' number of days as required. Enjoy ;-)

#!/bin/bash
# Script Name : modified_today.sh
# Modifications :
# Description : Lists all the files in a directory that have been modified that day

#################################
# Start of procedures/functions #
#################################

funct_check_params()    # Function Name
{    # Start of the function
  if [ ${NARG} -ne 1 ]; then    # If the number of arguments is not one, then output a message
    echo "$0 : Not enough Parameters passed, you need to supply a directory"
    exit 1 # Quit the program
  elif

  # If the argument passed is -h or --h then display the following message in the echo statement

  [[ ${SLICE} = "-h" ]] || [[ ${SLICE} = "--h" ]]; then
echo "Usage: You need to add a slice after the script name, e.g $0 /opt"
    exit 1 # Quit the program
  fi    # End of the if statement
}    # End of the function

funct_find_files()    # Function Name
{    # Start of the function
  find $SLICE -type f -mtime -1 > $LOGFILE    # Find all the files and put them into a logfile

  for files in $(cat $LOGFILE)    # Loop through all the files and show a long listing
    do
ls -l $files
  done
}    # End of the function

################
# Main Program #
################

# Variable Settings

DATE=`date +"%d-%B-%Y"` ; export DATE # Set the DATE variable, format it as 9-September-2012
SLICE=$1    # Set the variable SLICE as the first argument passed
LOGFILE=/tmp/modifed_$DATE.log # Set the variable LOGFILE, the stores the files found
NARG=$# # Set the variable NARG to a number of arguments on the command line

{    # Start of the main program
  funct_check_params # Call the function funct_check_params
  funct_find_files # Call the function funct_file_files
}    # End of the main program

## End of Script

UGC NET June 24 2012 Answer Keys OMR sheets

Yet again, I'm deviating from my regular blog topics but not from Computer Science. Well, the reason is simple you have to sometimes follow trend over topics. Of late a lot of people have been writing to me to provide the answerkeys for UGC net released for UGC net exam held on June 24 2012.

So I thought, let me clear the air about all this UGC mystery and various websites passing on wrong information to students.

You will find most of these websites filled with rubbish like this (hyperlinked to adverts):

"ANSWER KEYS Paper I Paper II Paper III UGC NET Result 2012 : University Grant Commission (UGC) published the result of University Grant Commission National Eligibility Test (UGC NET) 2012 / results of UGC NET 2012. UGC conducted UGC NET Exam in December 2011.  University Grant Commission (UGC) announced the result of UGC NET JRF Result 2012 on its  official UGC NET website ugcnetonline.in.
Candidates who appeared in UGC NET 2012 Exam can check their UGC NET 2012 Result / UGC NET JRF Result 2012 using the link given below : <The link to results of UGC NET 2012 going to ugcnetonline.in > "


Now lets come down to the facts:

1. The answerkeys for UGCNET / CSIR NET held on June 24 2012 will be made available once the results are officially out and not beforehand. Results for UGCNET / CSIR NET held on June 24 2012 will be available by October 2012. However, you can find certain braindumps of the UGCNET / CSIR NET exams over the internet. Well by that you can gauge if you will pass or not so be patient and WAIT. 

2. Result of UGC NET held on Dec 24, 2011 is out and available here- http://www.ugcnetonline.in/Result_dec2011.pdf and NOT UGCNET JRF or Lectureship June 24, 2012 exams.

3. The Results will be out probably on October 2012 as per reliable UGC sources. So till then, fingers crossed. I will definitely provide the link here for all of you. If you have any questions please comment below. Only after that you can tally your OMR sheets but you cant apply for re-evaluation as per UGC rules.

Enjoy and don't believe in crap ! You're would be research fellas' and Lecturers be logical. Looking forward to your reactions.

UGC NET June 2012 Results Declared

For the first time I'm deviating from my regular blog topics but not from Computer Science. Well, the reason is simple you have to sometimes follow trend over topics. Of late a lot of people have been writing to me about when the UGC NET exam results for Computer Science will be out, which was conducted on June 24, 2012.

So I thought, let me clear the air about all this UGC mystery and various websites passing on wrong information to students about declaration of UGC NET results.

You will find most of these websites filled with rubbish like this (hyperlinked to adverts):

"UGC NET Result 2012 : University Grant Commission (UGC) published the result of University Grant Commission National Eligibility Test (UGC NET) 2012 / results of UGC NET 2012. UGC conducted UGC NET Exam in December 2011.  University Grant Commission (UGC) announced the result of UGC NET JRF Result 2012 on its  official UGC NET website ugcnetonline.in.

Candidates who appeared in UGC NET 2012 Exam can check their UGC NET 2012 Result / UGC NET JRF Result 2012 using the link given below : <The link to results of UGC NET 2012 going to ugcnetonline.in > "


Now lets come down to the facts:

1. Result of UGC NET held on Dec 24, 2011 is out and available here- http://www.ugcnetonline.in/Result_dec2011.pdf and NOT UGCNET JRF or Lectureship June 24, 2012 exams.

2. The Results will be out probably on October 2012 as per reliable UGC sources. So till then, fingers crossed. I will definitely provide the link here for all of you. If you have any questions please comment below.

Enjoy and dont believe in crap ! You're would be research fellas' and Lecturers be logical.

Windows 8 Download

Good News for Windows Lovers ! Windows 8 is here for download and that too FREE !!!

Windows 8 is the next version of Microsoft Windows, a series of operating systems produced by Microsoft for use on personal computers, including home and business desktops, laptops, Microsoft Tablet PCs, and Home theater PCs. According to unofficial sources, the release date for Windows 8 has been set for October 2012, three years after the release of its predecessor, Windows 7. Windows 8's server counterpart, Windows Server 2012, is in development concurrently with Windows 8. The most recent official pre-release version of Windows 8 is the Release Preview, which was released on May 31, 2012.

Unlike Windows 7, which was intended to be a more focused, incremental upgrade to the Windows line, Windows 8 has been "reimagined from the chipset to the user experience" to connect more with the user. It features a new user interface inspired by Microsoft's Metro design language that can be used for multiple input methods, including touchscreen input similar to that in Windows Phone and physical button input similar to the Xbox 360 and a PC. A version of Windows 8, called Windows RT, also adds support for the ARM processor architecture in addition to the previously supported x86 microprocessors from Intel, VIA and AMD.                                                                 Courtesy: Wikipedia

Download Windows 8

Windows 8 Release Preview ISO files (.iso) are provided as an alternative to using Windows 8 Release Preview Setup. If you are on a PC running Windows and want to install the release preview on another partition, another PC, or a virtual machine, we recommend you download Windows 8 Release Preview Setup and use the built-in tools for converting an ISO image into installation media, such as a DVD or USB bootable flash drive. 

Enjoy your Windows 8 Stint !

Note before you download: Windows 8 Release Preview is pre-release software that may be substantially modified before it’s commercially released. Microsoft makes no warranties, express or implied, with respect to the information provided here. Some product features and functionality may require additional hardware or software.

Install Monit in CentOS Linux for Monitoring

Monit is an open source utility for managing and monitoring, processes, files, directories and filesystems on a UNIX system. Monit conducts automatic maintenance and repair and can execute meaningful causal actions in error situations. However, you are the master and monit would only do what you define in the config file for it to do. We will discuss here how to install and configure a basic monit installation in Centos or Redhat Linux.


Monit can start a process if it does not run, restart a process if it does not respond and stop a process if it uses too much resources. You can use Monit to monitor files, directories and filesystems for changes, such as timestamp changes, checksum changes or size changes. You can also monitor remote hosts; Monit can ping a remote host and can check TCP/IP port connections and server protocols. Monit is controlled via an easy to use control file based on a free-format, token-oriented syntax. Monit logs to syslog or to its own log file and notifies you about error conditions and recovery status via customizable alert. For more infop on monit you can visit their official site http://mmonit.com/monit/

Install Monit

The first, you need to enable EPEL (Extra Packages for Enterprise Linux) to install monit package. Login as root and type the following command:

[root@superman ~]# vi /etc/yum.repos.d/epel.repo

Add or uncomment the following content at end of the file

[epel] 

name=Extra Packages for Enterprise Linux 5 - $basearch 

mirrorlist=http://mirrors.fedoraproject.org/mirrorlist?

repo=epel-5&arch=$basearch

failovermethod=priority 

enabled=1 

gpgcheck=0

Save and close the file. And type the following command

[root@superman ~]# yum clean all

To install monit, type the following command

[root@superman ~]# yum install monit

Turn on monit when system start up

[root@superman ~]# chkconfig --levels 235 monit on

Configure Monit

The configuration file of monit in Centos or RedHat is /etc/monit.conf. Type the following command to edit

[root@superman ~]# vi /etc/monit.conf

Sampe configuration file

set daemon 60 

set logfile /var/log/monit.log 

set mailserver localhost 

set mail-format { from: alert@domain.com 

subject: $SERVICE $EVENT at $DATE 

message: Monit $ACTION $SERVICE at $DATE on $HOST: $DESCRIPTION.  } 

set alert admin@domain.com  include /etc/monit.d/*

Now to monitor Apache, create a file /etc/monit.d/httpd, enter

[root@superman ~]# vi /etc/monit.d/httpd

Add following content

check process httpd with pidfile /var/run/httpd.pid 

group apache  start program = "/etc/init.d/httpd start" 

stop program = "/etc/init.d/httpd stop"  if failed host 127.0.0.1 port 80 

protocol http then restart  if 5 restarts within 5 cycles then timeout

MySQL server restart configuration directives

check process mysqld with pidfile /var/run/mysqld/mysqld.pid 

group mysql  start program = "/etc/init.d/mysqld start" 

stop program = "/etc/init.d/mysqld stop"  if failed host 127.0.0.1 port 3306 

then restart  if 5 restarts within 5 cycles then timeout

SSH server configuration directives

check process sshd with pidfile /var/run/sshd.pid  start program "/etc/init.d/sshd start"  stop program "/etc/init.d/sshd stop"  if failed host 127.0.0.1 port 22 protocol ssh then restart  if 5 restarts within 5 cycles then timeout

Setup Monit Mail Alerts via SMTP: 

 set mailserver mail.yoursmtp.com, mail.foo.bar port 25 

 username "superman" password "binpip" using tlsv1, localhost 

 with timeout 15 seconds

Type the following command to start monit

[root@superman ~]# /etc/init.d/monit start

You can verify that monit is started from /var/log/monit.log log file:

[root@superman ~]# tail -f /var/log/monit.log  

Sample ouputs:

[IST June 17 14:51:18] info     : 'system_server2.domain.com' Monit started

To understand Monit config file better follow this link : http://mmonit.com/monit/documentation/monit.html
  
Do share your comments below.