MY.CNF Configuration file
The configuration file supports options for multiple programs. Each program has its own directive, which is a keyword in square brackets, such as [mysqld] or [client]. Here is a sample configuration file from a production system:
# The following options will be passed to all MySQL clients
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
#this will prevent mysql from starting
port = 3306
socket = /var/lib/mysql/mysql.sock
datadir =/var/lib/mysql
log_slow_queries = /var/log/mysql/mysqld_slow-queries.log
long_query_time = 4
max_connections=200
max_connect_errors = 400
wait_timeout=7200
connect_timeout=10
key_buffer = 512M
tmp_table_size = 32M
max_heap_table_size = 32M
max_allowed_packet = 32M
table_cache = 1800
join_buffer_size = 8M
sort_buffer_size = 16M
read_buffer_size = 8M
read_rnd_buffer_size = 524288
myisam_sort_buffer_size = 256M
thread_cache_size = 384
bulk_insert_buffer_size = 8M
query_cache_limit = 4M
query_cache_size = 128M
query_cache_type = 1
362
MySQL Server Tuning 10
query_prealloc_size = 65536
query_alloc_block_size = 131072
# Try number of CPU’s*2 for thread_concurrency
thread_concurrency = 4
#innodb configuration
innodb_data_home_dir = /mysql/data
innodb_data_file_path=ibdata1:200M:autoextend
innodb_log_group_home_dir = /mysql/data/data
innodb_log_arch_dir = /mysql/data/data
innodb_buffer_pool_size = 24576M
innodb_additional_mem_pool_size = 32M
innodb_log_file_size = 1024M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 120
sync-binlog = 1
innodb_support_xa = 0
innodb_thread_concurrency = 128
innodb_file_per_table
# binary logging is required for replication
log-bin=mysql-bin
max_binlog_size = 1024M
server-id = 4
slave-skip-errors = 1062
expire-logs-days = 7
[mysqldump]
quick
max_allowed_packet = 16M
[mysql.server]
user=mysql
group=mysql
basedir=/var/lib
[mysqld_safe]
nice = -5
open_files_limit = 8192
log-error=/var/log/mysql/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
RAID
(Redundant Array of Independent Disks)
Raid 0 (zero): Only One Copy of Data spread out into Multiple Disks. if One copy Lost all the Data is Lost.
Raid 1 : Two Disks Mirrored if one fails Other will Pickup.
Raid 5: Combines Three of more Disks. Storage Capacity is Reduced by One Disk.
Raid 10: Combination of Raid 1 and 0 Storage Capacity – 50% Mirrored.
Linux:
LVM: Logical Volume Manager: Which will Separate Log Files on a Different Server.
File System
EXT 2: Good Perfoermance & Slower Crash Recovery.
EXT 3: Good Performance over EXT2 Filer System.
IBM JFS: Fast Recovery After a file System Failure.
Tuning MYSQL Server
Show Innodb Status: Which will show all the Status Information of Tables, Queries, Locks, Memory Usage, Memory Dumps, Cache, Flushed Transactions, Thread information Etc.
show innodb status;
=====================================
091105 14:08:54 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 15 seconds
———-
SEMAPHORES
———-
OS WAIT ARRAY INFO: reservation count 107862, signal count 107846
Mutex spin waits 0, rounds 9648346, OS waits 26394
RW-shared spins 140432, OS waits 70114; RW-excl spins 63, OS waits 54
————
TRANSACTIONS
————
Trx id counter 0 966751765
Purge done for trx’s n:o < 0 966751662 undo n:o < 0 0
History list length 13
LIST OF TRANSACTIONS FOR EACH SESSION:
—TRANSACTION 0 0, not started, process no 26454, OS thread id 2923674480
MySQL thread id 466090, query id 8924458 c-24-6-189-185.hsd1.ca.comcast.net 24.6.189.185 HTN
show innodb status
—TRANSACTION 0 966751278, not started, process no 26454, OS thread id 2968963952
MySQL thread id 466080, query id 8921005 192.168.10.79 HTN
—TRANSACTION 0 0, not started, process no 26454, OS thread id 2946497392
MySQL thread id 466077, query id 8922846 75.7.48.50 HTN
—TRANSACTION 0 966751530, not started, process no 26454, OS thread id 3021794160
MySQL thread id 465759, query id 8922979 192.168.10.78 HTN
—TRANSACTION 0 0, not started, process no 26454, OS thread id 3076823920
MySQL thread id 466068, query id 8920769 75.7.48.50 HTN
—TRANSACTION 0 0, not started, process no 26454, OS thread id 2943998832
MySQL thread id 466051, query id 8920551 75.7.48.50 HTN
—TRANSACTION 0 0, not started, process no 26454, OS thread id 2938555248
MySQL thread id 466021, query id 8920902 75.7.48.50 HTN
—TRANSACTION 0 0, not started, process no 26454, OS thread id 2938956656
MySQL thread id 465966, query id 8920351 192.168.10.79 HTN
—TRANSACTION 0 0, not started, process no 26454, OS thread id 3077024624
MySQL thread id 465757, query id 8921477 192.168.10.78 HTN
—TRANSACTION 0 966751014, not started, process no 26454, OS thread id 2969365360
MySQL thread id 465566, query id 8921004 192.168.10.79 HTN
—TRANSACTION 0 0, not started, process no 26454, OS thread id 2900405104
MySQL thread id 465514, query id 8902812 192.168.10.52 HTN
—TRANSACTION 0 966751013, not started, process no 26454, OS thread id 2944400240
MySQL thread id 465472, query id 8919475 192.168.10.79 HTN
——–
FILE I/O
——–
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o’s: 0, sync i/o’s: 0
Pending flushes (fsync) log: 0; buffer pool: 0
86187 OS file reads, 11907 OS file writes, 10888 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1.47 writes/s, 0.47 fsyncs/s
————————————-
INSERT BUFFER AND ADAPTIVE HASH INDEX
————————————-
Ibuf: size 1, free list len 60, seg size 62,
246 inserts, 246 merged recs, 39 merges
Hash table size 34679, node heap has 4 buffer(s)
6.53 hash searches/s, 6.53 non-hash searches/s
—
LOG
—
Log sequence number 5 2711071627
Log flushed up to 5 2711071627
Last checkpoint at 5 2711071627
0 pending log writes, 0 pending chkp writes
8299 log i/o’s done, 0.20 log i/o’s/second
———————-
BUFFER POOL AND MEMORY
———————-
Total memory allocated 21219654; in additional pool allocated 1048576
Dictionary memory allocated 2740496
Buffer pool size 512
Free buffers 1
Database pages 507
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 1127608, created 33, written 2585
0.00 reads/s, 0.00 creates/s, 1.20 writes/s
Buffer pool hit rate 1000 / 1000
————–
ROW OPERATIONS
————–
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 26454, id 2987748208, state: sleeping
Number of rows inserted 1553, updated 618, deleted 299, read 293174006
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 150.26 reads/s
—————————-
END OF INNODB MONITOR OUTPUT
============================
SHOW VARIABLES: Displays all variables and the usage ex: Show Global Variables Like ‘%tmp%/’
By default, SHOW STATUS will show SESSION variables. This may or may not be your intention, so to avoid confusion, always specify GLOBAL or SESSION as a keyword.
mysql> SHOW GLOBAL STATUS LIKE ’%tmp%’;
+————————-+———+
| Variable_name | Value |
+————————-+———+
| Created_tmp_disk_tables | 2744623 |
| Created_tmp_files | 147344 |
| Created_tmp_tables | 5003238 |
+————————-+———+
The SHOW VARIABLES can be used to determine the values of system variables. This is a very useful command when tuning a server. As with the SHOW STATUS command, it can use the LIKE modifier, and you should always specify GLOBAL or SESSION to avoid possible confusion.
If you wanted to see the global settings for temporary tables:
mysql> SHOW GLOBAL VARIABLES LIKE ’%tmp\_%’;
+——————-+———-+
| Variable_name | Value |
+——————-+———-+
| max_tmp_tables | 32 |
| tmp_table_size | 33554432 |
+——————-+———-+
“max_tmp_tables”,”32″
“slave_load_tmpdir”,”/tmp”
“tmp_table_size”,”16777216″
“tmpdir”,”/tmp”
INNODB Performance Tuning Parameters
INNODB_BUFFER_POOL_SIZE : 70-80% memory can be allocated to Buffer Pool size, Set it to 12gb on a 16gb box.
INNODB_LOG_FILE_SIZE: This Depends on your Recovery Speed but 256mb Seems to be a good balance between Reasonable Recovery and Good Time Performance.
INNODB_LOG_BUFFER_SIZE: This can be Set to the
INNODB_FLUSH_LOG_AT_TRX_COMMAND: Works good before Server Crashes.
INNODB_THREAD_CONCURRENCY: 8
INNODB_FLUSH_METHOD: O-Direct
INNODB_FILE_PER_TABLE:
Transaction Isolation: Read Committed: This Option has some performance Benefits especially in locking in 5.0 and Even more to come with MySQL 5.1 and Row Level Application
Application Tuning for INNODB:
ERROR log: Sql Daemon Start Stop, Critical Errors will be Logged in this Error Log File
Binary Log: Update, Delete, Insert, Statements will be logged to Binary Log file.
General Query Log: All Select Queries, general data retrieval queries will be recorded.
Slow Query Log: Queries which are running slow and can be logged in to the file.
To activate the query log, simply place:
log-slow-queries = [slow_query_log_filename]
in your configuration file (my.cnf or my.ini), slow_query_log_filename being the optional filename for your log file. If you don’t supply a filename, the default name will be used, which is the name of the host machine, with -slow.log being appended.
The slow query log logs all queries that take longer than long_query_time, which is usually 10 seconds by default (more than long enough for a self-respecting query to complete). You can alter the long_query_time in the configuration file. The following example sets the time to 5 seconds:
set-variable = long_query_time = 5
The slow query log can also optionally log all queries that don’t use an index by placing the following in the configuration file:
log-long-format
s of MySQL 5.1.6, the destination can be a file or a table, or both
mysqld with the --log-slow-queries[=file_name] option to enable the slow query log, and optionally use --log-output to specify the log destination (as described in Section 5.2.1, “Selecting General Query and Slow Query Log Output Destinations”).
Tee: Turns on MYSQL Logging to a specified file. ex: Tee / Filename,
NoTee: Will stop logging mysql.
Memory Storage Engine Configuration
max_heap_table_size: The Maximum size of memory tables is limited by the system variable, it has a default value of 16 mega bytes.
Init_File: This can be used to specify a file to use to populate a memory table when mysqld starts, for ex: the file may contain LOAD DATA INFILE Command.
Maria Storage Engine is a Crash Safe Version of MYISAM.
Overall MySQL Server Options
Overall MySQL Server Options
Option Name Global or
memlock Global
Locks the mysqld daemon into server memory. While this can improve performance if the server runs out of RAM, the mysqld daemon will crash.
sort_buffer_size = buffer_size
Session Determines the amount of system memory allocated for SQL sorts. If this size is exceeded, the server will use hard drive space for sorting data.
thread_cache_size = num_of_threads
Global mysqld creates a cache of unused connection threads rather than destroying threads and creating new ones as need.
thread_concurrency = N Global
Only use on Solaris systems. This should typically be set to twice the number of CPUs. It should be tested carefully before using in production.
tmp_table_size Global
Both this and the max_heap_table_size setting are used to determine the maximum size allowed of an in-memory temporary table before it is converted to a MyISAM table. The smallest value for these two settings is the one utilized.
MOCA (MYSQL Optimal Configuration Architechture)
1) /opt/Mysql/5.1.33 -------------- Software Installed at this Location
2) db01/mysql/mysql01/data -------- Data Directory
Db01/mysql/mysql01/binlogs ------------- binary logs directory
Db01/mysql/mysql01/admin ------------ main administration
Db01/mysql/mysql01/Backups ———— Backups
Db01/mysql/mysql01/Trans ———— Transaction Data Files
Db01/mysql/mysql01/TransLogs ———- Trans Log Files.
Backup Strategies and Backup Procedures.
Logical Backup: Taking Backup Online While Database is Running.
Physical Backup: Taking Backup of Database Files or Disk Partitions: Occupies More Space.
Full Backup: Taking Full Backup of Database.
Incremental Backup: Taking Backups of the Database Since the last Backup. You may take first full Backup and take Incremental Backups to Save Time. Once a Week full backup might be necessary.
Consistent Backup: Taking Backup at an Exact Moment of Time, A Backup Process may take time to Complete
HOT Backup: Taking Backup while Database is Running.
Cold Backup: Taking Backup While Database is Offline.
Point-in-Time-Restore: Restoring Backup for Certain Time and Date.
REPLICATION – - Configuring Slave Backup Server.
Restore Database and Execute a CHANGE MASTER Command to configure the Slave.
LOAD DATA FROM Master – Master to Slave.
Grant Replication Slave on *.* to ‘Slave’ @ ‘Slave-host’ Identified by ‘Password’ ;
2) Edit My.cnf Configuration file and add the below line
[mysqld]
Server-Id = 1
Log – bin = Slave_log
Show Master Status;
PARTITIONING TABLES
1) Horizontal Partitioning : Partition tables horizontally as if 2 tables (table1 and table2) both are separated by a line. I) Archiving Old Data is a Common task in Horizontal Partitioning. Merge tables will be used to Partition Data Horizontally.
2) Vertical Partitioning: Partitioning table vertically using de-Normalization: A Customer Table will be devided into a two tables splitting customer Data Columns.
Partition Algorithm in MYSQL
1) Range: Selects a Partition by Determining if the partition key is inside a Range of Values. An Example of Partition for all Rows based on Customer in Values less than 500,000 Can go into one partition and values greater than or Equal to go into another Partition.
Ex:
Create TABLE INVOICES (ID,CLASS,DEPT)
Partition by Range (To_Days (Entry_Date),
Partition PO Values Less than (To_Days (‘2009-01-01)),
Partition PO Values Less than (To_Days (‘2009-02-01)),
Partition PO Values Less than (To_Days (‘2009-03-01)),
Partition PO Values Less than (To_Days (‘2009-04-01)),
Partition PO Values Less than (To_Days (‘2009-05-01)),
Partition PO Values Less than MAXVALUE.
SHOW PLUGINS (all are active except ndbcluster);
Binlog
Partition
CSV
MEMORY
InnoDB
MyISAM
MRG_MYISAM
ndbcluster – DISABLED – needs to be downloaded and Installed in order to work.
INNODB Row Level Locking
Advantages of row-level locking:
-
Fewer lock conflicts when different sessions access different rows
-
Fewer changes for rollbacks
-
Possible to lock a single row for a long time
Disadvantages of row-level locking:
-
Requires more memory than page-level or table-level locks
-
Slower than page-level or table-level locks when used on a large part of the table because you must acquire many more locks
-
Slower than other locks if you often do GROUP BY operations on a large part of the data or if you must scan the entire table frequently
