https://www.xaprb.com/blog/2009/04/01/how-mysql-really-executes-a-query/
Wednesday, November 30, 2016
Tuesday, November 29, 2016
innodb buffer pool
INNODB BUFFER POOL
The InnoDB Buffer Pool is the memory area where the InnoDB Storage Engine caches its data and index blocks On systems with large memory, you can improve concurrency by dividing the buffer pool into multiple buffer pool instances. Each InnoDB data and index block has a size of Innodb_page_size (16384 byte = 16 kbyte). The InnoDB Buffer Pool is configured in bytes with the innodb_buffer_pool_size variable. On a dedicated system the InnoDB Buffer Pool can be configured up to 80% of the systems physical RAM (free).
REDO log is Oracle terminology, transaction log is InnoDB terminology. Now that all are Oracle engineers, people use both to refer to the same thing in MySQL.
The transaction log is, by default- it can be changed- the two files located in $DATADIR called ib_logfile0 and ib_logfile1. It serves the same functions as the REDO log in other databases- storing writes in a safe way and recovering in the case of a crash, although there are some details in implementation that differ in functionality from other RDMS. It is the main component for InnoDB to be a transactional engine.
Do not confuse the transaction log with the binary logs in MySQL. The binlog, by default, is on the $DATADIR and is *hostname*-bin.index and several *hostname*-bin.00001, etc. It is particular confusing for people coming from other databases, because it is used for other things that other databases use the REDO log for: replication and point in time recovery. The main difference is that the transaction log is InnoDB-only, the binary log is (mostly) transaction-independent, as it is for all storage engines, transactional or not. MyISAM will write (if enabled) to the binary log. InnoDB will write to the transaction log and the binary log
The InnoDB Buffer Pool is the memory area where the InnoDB Storage Engine caches its data and index blocks On systems with large memory, you can improve concurrency by dividing the buffer pool into multiple buffer pool instances. Each InnoDB data and index block has a size of Innodb_page_size (16384 byte = 16 kbyte). The InnoDB Buffer Pool is configured in bytes with the innodb_buffer_pool_size variable. On a dedicated system the InnoDB Buffer Pool can be configured up to 80% of the systems physical RAM (free).
REDO log is Oracle terminology, transaction log is InnoDB terminology. Now that all are Oracle engineers, people use both to refer to the same thing in MySQL.
The transaction log is, by default- it can be changed- the two files located in $DATADIR called ib_logfile0 and ib_logfile1. It serves the same functions as the REDO log in other databases- storing writes in a safe way and recovering in the case of a crash, although there are some details in implementation that differ in functionality from other RDMS. It is the main component for InnoDB to be a transactional engine.
Do not confuse the transaction log with the binary logs in MySQL. The binlog, by default, is on the $DATADIR and is *hostname*-bin.index and several *hostname*-bin.00001, etc. It is particular confusing for people coming from other databases, because it is used for other things that other databases use the REDO log for: replication and point in time recovery. The main difference is that the transaction log is InnoDB-only, the binary log is (mostly) transaction-independent, as it is for all storage engines, transactional or not. MyISAM will write (if enabled) to the binary log. InnoDB will write to the transaction log and the binary log
Thursday, November 10, 2016
mysql trigger
IF(expression ,expr_true, expr_false);
eg. if(1>3,true,false)
mysql> select IF(1>3,'seems true','its false');
+----------------------------------+
| IF(1>3,'seems true','its false') |
+----------------------------------+
| its false |
+----------------------------------+
mysql> select IF(4<9,'seems true','its false');
+----------------------------------+
| IF(4<9,'seems true','its false') |
+----------------------------------+
| seems true |
+----------------------------------+
List triggers and delete trigger
mysql> show triggers \G;
DROP TRIGGER IF EXISTS `foo`;
Create table
mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Creating trigger
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount;
Test/Use trigger
mysql> SET @sum = 0;
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
mysql> SELECT @sum AS 'Total amount inserted';
Delete trigger
DROP TRIGGER table_name.ins_sum;\
mysql> delimiter //
CREATE TRIGGER upd_check BEFORE UPDATE ON account
FOR EACH ROW
BEGIN
IF NEW.amount < 0 THEN
SET NEW.amount = 0;
ELSEIF NEW.amount > 100 THEN
SET NEW.amount = 100;
END IF;
END;
mysql> delimiter ;
Create trigger to restrict zero values in employee.salary field
delimiter $$
create trigger foo before insert on emp
for each row
begin
if new.sal=0 then
signal sqlstate '45000';
#below is optional
#SET MESSAGE_TEXT = 'Salary should have value greater than zero';
end if;
end; $$
eg. if(1>3,true,false)
mysql> select IF(1>3,'seems true','its false');
+----------------------------------+
| IF(1>3,'seems true','its false') |
+----------------------------------+
| its false |
+----------------------------------+
mysql> select IF(4<9,'seems true','its false');
+----------------------------------+
| IF(4<9,'seems true','its false') |
+----------------------------------+
| seems true |
+----------------------------------+
List triggers and delete trigger
mysql> show triggers \G;
DROP TRIGGER IF EXISTS `foo`;
Create table
mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Creating trigger
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount;
Test/Use trigger
mysql> SET @sum = 0;
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
mysql> SELECT @sum AS 'Total amount inserted';
Delete trigger
DROP TRIGGER table_name.ins_sum;\
mysql> delimiter //
CREATE TRIGGER upd_check BEFORE UPDATE ON account
FOR EACH ROW
BEGIN
IF NEW.amount < 0 THEN
SET NEW.amount = 0;
ELSEIF NEW.amount > 100 THEN
SET NEW.amount = 100;
END IF;
END;
mysql> delimiter ;
Create trigger to restrict zero values in employee.salary field
delimiter $$
create trigger foo before insert on emp
for each row
begin
if new.sal=0 then
signal sqlstate '45000';
#below is optional
#SET MESSAGE_TEXT = 'Salary should have value greater than zero';
end if;
end; $$
Monday, November 7, 2016
innodb_flush_log_at_trx_commit
The innodb_flush_log_at_trx_commit is used with the purpose as ..
If the value of innodb_flush_log_at_trx_commit is 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit.
When the value is 1 (the default), the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file.
When the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues.
The default value of 1 is required for full ACID compliance. You can achieve better performance by setting the value different from 1, but then you can lose up to one second worth of transactions in a crash. With a value of 0, any mysqld process crash can erase the last second of transactions. With a value of 2, only an operating system crash or a power outage can erase the last second of transactions. InnoDB's crash recovery works regardless of the value.
In My opinion using innodb_flush_log_at_trx_commit to 2 should not be an issue.But to use 1 is the safest.
http://dba.stackexchange.com/questions/12611/is-it-safe-to-use-innodb-flush-log-at-trx-commit-2
If the value of innodb_flush_log_at_trx_commit is 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit.
When the value is 1 (the default), the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file.
When the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues.
The default value of 1 is required for full ACID compliance. You can achieve better performance by setting the value different from 1, but then you can lose up to one second worth of transactions in a crash. With a value of 0, any mysqld process crash can erase the last second of transactions. With a value of 2, only an operating system crash or a power outage can erase the last second of transactions. InnoDB's crash recovery works regardless of the value.
In My opinion using innodb_flush_log_at_trx_commit to 2 should not be an issue.But to use 1 is the safest.
http://dba.stackexchange.com/questions/12611/is-it-safe-to-use-innodb-flush-log-at-trx-commit-2
mysql order by
MySQL has two methods to produce ordered streams:-
The first is to use a “range“, “ref” or “index” access method over an ordered index. For versions up to 5.1, those access methods naturally return records in the index order, so we get ordering for free (the exception is NDB engine which needs to do merge-sort when it gets data from several storage nodes). In MySQL 5.2, MyISAM and InnoDB have MultiRangeRead optimization which breaks the ordering. We have a draft of how to make it preserve the ordering, but at the moment MRR is simply disabled whenever ordering is required.
The second, catch-all method, is to use the filesort algorithm. In a nutshell, filesort() does quicksort on chunks of data that fit into its memory and then uses mergesort approach to merge the chunks. The amount of memory available to filesort() is controlled by @@sort_buffer_size variable. if the sorted data doesn’t fit into memory (i.e. there is more than one chunk), filesort uses a temporary file to store the chunks.
If you see many sort_merge_passes per second in SHOW GLOBAL STATUS output, you can consider increasing the sort_buffer_size value to speed up ORDER BY or GROUP BY operations that cannot be improved with query optimization or improved indexing
sort_buffer_size is a per session buffer. That is this memory is assigned per connection/thread
The truth is, filesort is badly named. Anytime a sort can’t be performed from an index, it’s a filesort. It has nothing to do with files. Filesort should be called “sort.” It is quicksort at heart. If the sort is bigger than the sort buffer, it is performed a bit at a time, and then the chunks are merge-sorted to produce the final sorted output.
Subscribe to:
Posts (Atom)