# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html
#
# Take care to only add/remove/change a setting if you are comfortable
# doing so! For Rackspace customers, if you have any questions or
# concerns, please contact the MySQL Database Services Team. Be aware
# that some work performed by this team can involve additional billable
# fees.
#
# This file generated for host Boodhii please modify
# variables if the server is resized from 4194304kB
[mysqld]
### General
user = mysql
port = 3306
datadir = /var/lib/mysql
tmpdir = /var/lib/mysqltmp
socket = /var/lib/mysql/mysql.sock
skip-external-locking = 1
## This prevents using host-based authentication. That means users must be
## created using an ip-address (ie 'myuser'@'192.168.100.1') or must make
## use of the % wildcard (ie 'myuser'@'%'). The benefit to not using
## host-based authentication is that DNS will not impact MySQL performance.
#skip-name-resolve
## If open-files-limit is set very low, MySQL may increase on its own. Either
## way, increase this if MySQL gives 'too many open files' errors. Setting
## this above 65535 could be unwise (MySQL may crash).
open-files-limit = 20000
## Set this to change the way MySQL handles validation, data conversion, etc.
## Be careful with this setting as it can cause unexpected results and
## horribly break some applications! Note, too, that it can be set per-session
## and can be hard set in stored procedures.
#sql-mode = TRADITIONAL
#event-scheduler = 1
### Cache
thread-cache-size = 16
table-open-cache = 2048
table-definition-cache = 512
## Generally, it is unwise to set the query cache to be larger than 64-128M
## as the costs associated with maintaining the cache outweigh the performance
## gains. A far superior solution would be to implement memcached, though this
## required modifying the application, among other things.
query-cache-size = 32M
query-cache-limit = 1M
### Per-thread Buffers
sort-buffer-size = 1M
read-buffer-size = 1M
read-rnd-buffer-size = 8M
join-buffer-size = 1M
### Temp Tables
tmp-table-size = 64M
max-heap-table-size = 64M
### Networking
back-log = 100
max-connections = 200
max-connect-errors = 10000
max-allowed-packet = 16M
interactive-timeout = 600
wait-timeout = 180
net_read_timeout = 30
net_write_timeout = 30
# This value is the size of the listen queue for incoming TCP/IP connections.
back_log = 128
#### Storage Engines
## Set this to force MySQL to use a particular engine / table-type
## for new tables. This setting can still be overridden by specifying
## the engine explicitly in the CREATE TABLE statement.
#default-storage-engine = InnoDB
## Makes sure MySQL does not start if InnoDB fails to start. This helps
## prevent ugly silent failures.
innodb = FORCE
### MyISAM
## Not sure what to set this to?
## Try running a 'du -sch /var/lib/mysql/*/*.MYI'
## This will give you a good estimate on the size of all the MyISAM indexes.
## (The buffer may not need to set that high, however)
key-buffer-size = 64M
## This setting controls the size of the buffer that is allocated when
## sorting MyISAM indexes during a REPAIR TABLE or when creating indexes
## with CREATE INDEX or ALTER TABLE.
myisam-sort-buffer-size = 128M
### InnoDB
## Note: While most settings in MySQL can be set at run-time, many InnoDB
## variables cannot be set at runtime as require restarting MySQL
###
## These settings control how much RAM InnoDB will use. Generally, when using
## mostly InnoDB tables, the innodb-buffer-pool-size should be as large as
## is possible without swapping or starving other processes of RAM. The other
## two settings usually do not need to be changed, but can help for very large
## datasets.
innodb-buffer-pool-size = 16M
innodb-log-buffer-size = 4M
#innodb-additional-mem-pool-size= 20M
## This can help, but can also hinder performance. Test appropriately!
## (For SAN, O_DIRECT is almost never a good idea)
#innodb-flush-method = O_DIRECT
## innodb-file-per-table can offer quite a few advantages, but does not work
## well when using it with a very large number of tables.
## If innodb-file-per-table is used, be sure to set innodb-open-files
## appropriately (which is roughly similar to open-files-limit, but is
## exclusive to InnoDB)
#innodb-file-per-table = 1
#innodb-open-files = 300
## If you are not sure what to set this to, the following formula can offer
## up a rough idea:
## (number of cpus * number of disks * 2)
#innodb-thread-concurrency = 16
## This can increase performance for single servers as disabling this enabled
## group-commit. This is not a viable option when using binary logging or
## replication, however.
#innodb-support-xa = 0
## Be careful when changing these as they require re-generating the
## ib-logfile* files, which must be done carefully. Do not change this unless
## you are familiar with the procedure.
#innodb-log-file-size = 100M
#innodb-log-group-home-dir = /var/lib/mysql
innodb-log-files-in-group = 2
## You cannot change this without dumping out the data and re-importing it!
#innodb-data-file-path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb-data-home-dir = /var/lib/mysql
### Replication
## Tired of running into replication errors due to having the same server
## id on two servers? Consider changing this variable to the Rackspace
## server number.
server-id = 1
## This sets the format used when logging to the binary log
## - ROW will force row-based logging
## - STATEMENT will force statement-based (ie pre 5.1) logging
## - MIXED wil use both, depending on the situation
## Note that this setting has implications for both replication and
## backups, so do not change this unless you know what you are doing!
#binlog-format = STATEMENT
#log-bin = /var/lib/mysqllogs/bin-log
#relay-log = /var/lib/mysqllogs/relay-log
#relay-log-space-limit = 4G
#expire-logs-days = 14
## This should be enabled on conventional MySQL slaves
#read-only = 1
## Enable this to make replication more resilient against server
## crashes and restarts, at the expense of higher I/O on the server.
#sync-binlog = 1
## This is usually only needed when setting up chained replication.
#log-slave-updates = 1
## Uncomment the following when enabling multi-master replication
## Do NOT uncomment these unless you know exactly what you are doing!
#auto-increment-offset = 1
#auto-increment-increment = 2
### Logging
## This option determines the destination for general query log and slow query log output.
## The option value can be given as one or more of the words TABLE, FILE, or NONE.
## NOTE: Table logging takes away 50% of performance and thus is not recommended
## http://bugs.mysql.com/bug.php?id=30414
## In addition, you cannot backup the contents of these tables properly
## (mysqldump skips these tables by default since they cannot be locked)
#log-output = FILE
#slow-query-log = 1
#slow-query-log-file = /var/lib/mysqllogs/slow-log
#long-query-time = 2
#log-queries-not-using-indexes = 1
[mysqld-safe]
log-error = /var/log/mysqld.log
[mysqldump]
max-allowed-packet = 16M
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/sysconfig/mysqld-config/
http://everythingmysql.ning.com/profiles/blogs/using-tmpfs-for-mysqls-tmpdir
I would like to talk about not "why" MySQL does this but how to speed up the performance when MySQL internally or users create temporary tables to disk. A great solution is TMPFS, a quick how to is as follows:
-- Before you start
1. Make sure you allocate enough space to TMPFS
-- 2GB is usually safe but if you are using larger data sets with inefficient queries then there are far worse performance issues to deal with.
-- The safe way to implement TMPFS for MySQL
shell> mkdir /tmp/mysqltmp
shell> chown mysql:mysql /tmp/mysqltmp
shell> id mysql
##NOTE: make sure you get the uid and gid for mysql
shell> vi /etc/fstab
## make sure this in in your fstab
tmpfs /tmp/mysqltmp tmpfs rw,uid=25,gid=26,size=2G,nr_inodes=10k,mode=0700 0 0
shell> mount /tmp/mysqltmp
shell> vi /etc/my.cnf #or the mysql config file for your server
## NOTE: inside the file add the following under [mysqld]
tmpdir=/tmp/mysqltmp/
shell> service mysql restart
How to Set time & timezone in mysql
SELECT CURRENT_TIMESTAMP;
SET GLOBAL time_zone = '-5:00';
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html
#
# Take care to only add/remove/change a setting if you are comfortable
# doing so! For Rackspace customers, if you have any questions or
# concerns, please contact the MySQL Database Services Team. Be aware
# that some work performed by this team can involve additional billable
# fees.
#
# This file generated for host Boodhii please modify
# variables if the server is resized from 4194304kB
[mysqld]
### General
user = mysql
port = 3306
datadir = /var/lib/mysql
tmpdir = /var/lib/mysqltmp
socket = /var/lib/mysql/mysql.sock
skip-external-locking = 1
## This prevents using host-based authentication. That means users must be
## created using an ip-address (ie 'myuser'@'192.168.100.1') or must make
## use of the % wildcard (ie 'myuser'@'%'). The benefit to not using
## host-based authentication is that DNS will not impact MySQL performance.
#skip-name-resolve
## If open-files-limit is set very low, MySQL may increase on its own. Either
## way, increase this if MySQL gives 'too many open files' errors. Setting
## this above 65535 could be unwise (MySQL may crash).
open-files-limit = 20000
## Set this to change the way MySQL handles validation, data conversion, etc.
## Be careful with this setting as it can cause unexpected results and
## horribly break some applications! Note, too, that it can be set per-session
## and can be hard set in stored procedures.
#sql-mode = TRADITIONAL
#event-scheduler = 1
### Cache
thread-cache-size = 16
table-open-cache = 2048
table-definition-cache = 512
## Generally, it is unwise to set the query cache to be larger than 64-128M
## as the costs associated with maintaining the cache outweigh the performance
## gains. A far superior solution would be to implement memcached, though this
## required modifying the application, among other things.
query-cache-size = 32M
query-cache-limit = 1M
### Per-thread Buffers
sort-buffer-size = 1M
read-buffer-size = 1M
read-rnd-buffer-size = 8M
join-buffer-size = 1M
### Temp Tables
tmp-table-size = 64M
max-heap-table-size = 64M
### Networking
back-log = 100
max-connections = 200
max-connect-errors = 10000
max-allowed-packet = 16M
interactive-timeout = 600
wait-timeout = 180
net_read_timeout = 30
net_write_timeout = 30
# This value is the size of the listen queue for incoming TCP/IP connections.
back_log = 128
#### Storage Engines
## Set this to force MySQL to use a particular engine / table-type
## for new tables. This setting can still be overridden by specifying
## the engine explicitly in the CREATE TABLE statement.
#default-storage-engine = InnoDB
## Makes sure MySQL does not start if InnoDB fails to start. This helps
## prevent ugly silent failures.
innodb = FORCE
### MyISAM
## Not sure what to set this to?
## Try running a 'du -sch /var/lib/mysql/*/*.MYI'
## This will give you a good estimate on the size of all the MyISAM indexes.
## (The buffer may not need to set that high, however)
key-buffer-size = 64M
## This setting controls the size of the buffer that is allocated when
## sorting MyISAM indexes during a REPAIR TABLE or when creating indexes
## with CREATE INDEX or ALTER TABLE.
myisam-sort-buffer-size = 128M
### InnoDB
## Note: While most settings in MySQL can be set at run-time, many InnoDB
## variables cannot be set at runtime as require restarting MySQL
###
## These settings control how much RAM InnoDB will use. Generally, when using
## mostly InnoDB tables, the innodb-buffer-pool-size should be as large as
## is possible without swapping or starving other processes of RAM. The other
## two settings usually do not need to be changed, but can help for very large
## datasets.
innodb-buffer-pool-size = 16M
innodb-log-buffer-size = 4M
#innodb-additional-mem-pool-size= 20M
## This can help, but can also hinder performance. Test appropriately!
## (For SAN, O_DIRECT is almost never a good idea)
#innodb-flush-method = O_DIRECT
## innodb-file-per-table can offer quite a few advantages, but does not work
## well when using it with a very large number of tables.
## If innodb-file-per-table is used, be sure to set innodb-open-files
## appropriately (which is roughly similar to open-files-limit, but is
## exclusive to InnoDB)
#innodb-file-per-table = 1
#innodb-open-files = 300
## If you are not sure what to set this to, the following formula can offer
## up a rough idea:
## (number of cpus * number of disks * 2)
#innodb-thread-concurrency = 16
## This can increase performance for single servers as disabling this enabled
## group-commit. This is not a viable option when using binary logging or
## replication, however.
#innodb-support-xa = 0
## Be careful when changing these as they require re-generating the
## ib-logfile* files, which must be done carefully. Do not change this unless
## you are familiar with the procedure.
#innodb-log-file-size = 100M
#innodb-log-group-home-dir = /var/lib/mysql
innodb-log-files-in-group = 2
## You cannot change this without dumping out the data and re-importing it!
#innodb-data-file-path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb-data-home-dir = /var/lib/mysql
### Replication
## Tired of running into replication errors due to having the same server
## id on two servers? Consider changing this variable to the Rackspace
## server number.
server-id = 1
## This sets the format used when logging to the binary log
## - ROW will force row-based logging
## - STATEMENT will force statement-based (ie pre 5.1) logging
## - MIXED wil use both, depending on the situation
## Note that this setting has implications for both replication and
## backups, so do not change this unless you know what you are doing!
#binlog-format = STATEMENT
#log-bin = /var/lib/mysqllogs/bin-log
#relay-log = /var/lib/mysqllogs/relay-log
#relay-log-space-limit = 4G
#expire-logs-days = 14
## This should be enabled on conventional MySQL slaves
#read-only = 1
## Enable this to make replication more resilient against server
## crashes and restarts, at the expense of higher I/O on the server.
#sync-binlog = 1
## This is usually only needed when setting up chained replication.
#log-slave-updates = 1
## Uncomment the following when enabling multi-master replication
## Do NOT uncomment these unless you know exactly what you are doing!
#auto-increment-offset = 1
#auto-increment-increment = 2
### Logging
## This option determines the destination for general query log and slow query log output.
## The option value can be given as one or more of the words TABLE, FILE, or NONE.
## NOTE: Table logging takes away 50% of performance and thus is not recommended
## http://bugs.mysql.com/bug.php?id=30414
## In addition, you cannot backup the contents of these tables properly
## (mysqldump skips these tables by default since they cannot be locked)
#log-output = FILE
#slow-query-log = 1
#slow-query-log-file = /var/lib/mysqllogs/slow-log
#long-query-time = 2
#log-queries-not-using-indexes = 1
[mysqld-safe]
log-error = /var/log/mysqld.log
[mysqldump]
max-allowed-packet = 16M
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/sysconfig/mysqld-config/
http://everythingmysql.ning.com/profiles/blogs/using-tmpfs-for-mysqls-tmpdir
I would like to talk about not "why" MySQL does this but how to speed up the performance when MySQL internally or users create temporary tables to disk. A great solution is TMPFS, a quick how to is as follows:
-- Before you start
1. Make sure you allocate enough space to TMPFS
-- 2GB is usually safe but if you are using larger data sets with inefficient queries then there are far worse performance issues to deal with.
-- The safe way to implement TMPFS for MySQL
shell> mkdir /tmp/mysqltmp
shell> chown mysql:mysql /tmp/mysqltmp
shell> id mysql
##NOTE: make sure you get the uid and gid for mysql
shell> vi /etc/fstab
## make sure this in in your fstab
tmpfs /tmp/mysqltmp tmpfs rw,uid=25,gid=26,size=2G,nr_inodes=10k,mode=0700 0 0
shell> mount /tmp/mysqltmp
shell> vi /etc/my.cnf #or the mysql config file for your server
## NOTE: inside the file add the following under [mysqld]
tmpdir=/tmp/mysqltmp/
shell> service mysql restart
How to Set time & timezone in mysql
SELECT CURRENT_TIMESTAMP;
SET GLOBAL time_zone = '-5:00';
Posts
