MyISAM versus InnoDB
Transactions, referential integrity, concurrency, and Storage limits are the main difference between MyISAM & InnoDB.
Posts in Category: performance
MySQL replication : Replicating only certain databases
Replication replicates EVERYTHING from the master to slave(s), whereas you may just want one or two databases replicated.
At first add this to /etc/my.cnf on the slave:
[mysqld]
replicate-do-db=MyDb
That didn’t work very well for statements like this, assuming you are in a database other than MyDb:
insert into MyDb.TableInMyDb values (SomeValue)Fine, I thought,
Poor man’s MySQL replication monitoring
Using MySQL replication slave(s) for reporting (with potentially different storage engines) is a very popular way of scaling database read activities. As usual, you want to be on top of things when replication breaks so end users can be notified and issues addressed. When Nagios, Zabbix, or whatever monitoring tools
Enable slow-query-log
You can enable slow-log by un-commenting following lines in /etc/mysql/my.cnf
slow-query-log = 1
slow-query-log-file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log-queries-not-using-indexesLast line will tell slow-log to log queries not using indexes. You can keep it commented if you want to ignore queries which are not using indexes.
If your server has less RAM and you are
MySQL :: performance
Oracle Enterprise Manager for MySQL
Oracle Enterprise Manager for MySQL provides real-time monitoring and delivers comprehensive performance, availability and configuration information for your MySQL databases.
MySQL Performance Monitoring
Comprehensive database monitoring enables you to identify the problem areas in your database environment that are degrading performance. View the real-time performance metrics and important KPIs for your MySQL
What is the Key Buffer?
The key buffer is MyISAM specific, a structure for index blocks that contains a number of block buffers where the most-used index blocks are placed. It’s mean for minimizing disk I/O, because memory is still faster than hard drives [currently]. The MyISAM key buffer is described in more detail
MySQL Database Replication
Database replication is another means of taking backup of a database. Database will be synchronized with master and slave.
We need two servers one for configuring Master and other for Slave.
Replication, how it works:
- Master has Binary Logging enabled
- Master records completed SQL Statements
- Master will inject ‘USE dbname;’ among the SQL Statements it