MySQL
How to Configure MySQL delayed replication
Delayed Replication MySQL 5.6 supports delayed replication such that a slave server deliberately lags behind the master by at least a specified amount of time. The default delay is 0 seconds. Use the MASTER_DELAY option for CHANGE MASTER TO to set the delay to N seconds: CHANGE MASTER TO MASTER_DELAY = N; An event received [...]
expire_logs_days – Tuning / Optimizing my.cnf file for MySQL
This is replication/binlogging related options – when to delete binary logs from master. It does not affect Innodb and other storage engines transaction logs, slow query log or general log. Command-Line Format –expire_logs_days=# Option-File Format expire_logs_days Option Sets Variable Yes, expire_logs_days Variable Name expire_logs_days Variable Scope Global Dynamic Variable Yes Permitted Values Type numeric Default 0 [...]
bulk_insert_buffer_size – Tuning / Optimizing my.cnf file for MySQL
Used by MyISAM tables only to optimize bulk inserts (multiple value inserts). Really helps if you have really bulk inserts 100-1000+ values per single insert statement. This cache is used by MyISAM to optimize bulk inserts. This cache is a special tree-like structure. Bulk inserts include statements like LOAD DATA INFILE…, INSERT … SELECT, INSERT … VALUES (…), [...]
read_rnd_buffer_size – Tuning / Optimizing my.cnf file for MySQL
read_rnd_buffer_size: The read_rnd_buffer_size is used after a sort, when reading rows in sorted order. If you use many queries with ORDER BY, upping this can improve performance. Remember that, unlike key_buffer_size and table_cache, this buffer is allocated for each thread. This variable was renamed from record_rnd_buffer in MySQL 4.0.3. It defaults to the same size [...]
sort_buffer – Tuning / Optimizing my.cnf file for MySQL
sort_buffer: The sort_buffer is very useful for speeding up myisamchk operations (which is why it is set much higher for that purpose in the default configuration files), but it can also be useful everyday when performing large numbers of sorts.
table_cache – Tuning / Optimizing my.cnf file for MySQL
table_cache: The default is 64. Each time MySQL accesses a table, it places it in the cache. If the system accesses many tables, it is faster to have these in the cache. MySQL, being multi-threaded, may be running many queries on the table at one time, and each of these will open a table. Examine [...]
key_buffer_size – Tuning / Optimizing my.cnf file for MySQL
key_buffer_size: The value of key_buffer_size is the size of the buffer used with indexes. The larger the buffer, the faster the SQL command will finish and a result will be returned. The rule-of-thumb is to set the key_buffer_size to at least a quarter, but no more than half, of the total amount of memory on [...]
query_cache_size – Tuning / Optimizing my.cnf file for MySQL
query_cache_size: MySQL 4 provides one feature that can prove very handy – a query cache. In a situation where the database has to repeatedly run the same queries on the same data set, returning the same results each time, MySQL can cache the result set, avoiding the overhead of running through the data over and [...]
How to enable Query Caching in Mysql – Query Cache Configuration
The performance tuning of MySQL is very important for high traffic websites with MySQL as there DB. Â The db Admin always looking for ways to speed up query, in mysql we can enable query cache to speed up query performance. Whenever query cache is enable, it will cache the query in memory and boost query [...]
Some of MySQL Key area of performance tuning
1. Disks The fundamental battle in a database server is usually between the CPU(s) and available disk I/O performance; we’ll discuss memory momentarily. The CPU in an average server is orders of magnitude faster than the hard disks. If you can’t get data to the CPU fast enough, it must sit idle while the disks [...]
How to Purge MySQL Binary Logs periodically
If you are a Database Administrator then you should know about  Purge MySQL Binary Logs periodically. If you are having a replication environment then you can find so many binary files in your master server. This will eat-up lot of space of your hard disk. what is MySQL Binary Logs.– The binary log is a set [...]
How to Create a User for MySQL Replication
Each Slave must connect to the Master using a standard username and password. The user that you use for this operation can be any user, providing they have been granted the REPLICATION SLAVE privilege You do not need to create a specific user for replication. However, you should be aware that the username and password will [...]
How to do Performance Tuning on MySQL – Best Practices for MySQL
It is very important to fine tune MySQL. There are so many things that may slowing down your MySQL server. May be the select queries may be scanning the entire table which may have millions of records. This happens because you may not using proper indexing. so let us go through the video for best [...]
How to Get MySQL
This section does not apply to MySQL Enterprise Server users. Check our downloads page at http://dev.mysql.com/downloads/ for information about the current version of MySQL and for downloading instructions. For a complete up-to-date list of MySQL download mirror sites, see http://dev.mysql.com/downloads/mirrors.html.
How to Determine your current MySQL version
To determine the version and release of your currently installed MySQL installation, there are a number of options. For example, here is the output from a MySQL Community Server edition installed on Linux: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.0.27-standard MySQL Community [...]