-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Both InnoDB and PostgreSQL - as well as many other databases - use a technique called multi-version concurrency control (MVCC) to provide transaction isolation: transactions should not see the work of other, uncommitted transactions. MVCC means that, when a row is updated, the database stores both the old and new versions of the row.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Tuesday, February 22, 2011

Some Useful Quotes


Block Size:
The block size specifies size, that the file system, will use to Read and Write data. Larger block sizes will help improve disk I/O performance when using large files, such as Databases.
Mostly of size 1024, need to be 4096 for bigger file support like in database server case.

Block Size: Different Terms
1: Hardware Block Size = Sector Size
2: File System Block Size = Block Size
3: Kernel Buffer Block Size = Block Size
4: Partition Table Block Size = Cylinder Size
----------------------------------------------------------------------------------------------

MyISAM tables support concurrent inserts. If a MyISAM table has no holes in the middle resulting from deleted or updated records, inserts always take place at the end of the table and can be performed while other clients are reading the table. Concurrent inserts can take place even for a table that has been read-locked explicitly if the locking client acquired a READ LOCAL lock rather than a regular READ lock. If a table does have holes, concurrent inserts cannot be performed. However, you can remove the holes by using OPTIMIZE TABLE to defragment the table.
----------------------------------------------------------------------------------------------

Clustered Index: Clustered index physically rearrange the data that users inserts in your tables. It is nothing but a dictionary type data where actual data remains.
Non-Clustered Index: It Non-Clustered Index contains pointers to the data that is stored in the data page. It is a kind of index backside of the book where you see only the reference of a kind of data.
----------------------------------------------------------------------------------------------

To change the default size limit for MyISAM tables, set the myisam_data_pointer_size, which sets the number of bytes used for internal row pointers. The value is used to set the pointer size for new tables if you do not specify the MAX_ROWS option. The value of myisam_data_pointer_size can be from 2 to 7. A value of 4 permits tables up to 4GB; a value of 6 permits tables up to 256TB.

You can check the maximum data and index sizes by using this statement:

SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';
----------------------------------------------------------------------------------------------

Both InnoDB and PostgreSQL - as well as many other databases - use a technique called multi-version concurrency control (MVCC) to provide transaction isolation: transactions should not see the work of other, uncommitted transactions.  MVCC means that, when a row is updated, the database stores both the old and new versions of the row.
----------------------------------------------------------------------------------------------

Tuesday, February 8, 2011

todays quote

Block Size:
The block size specifies size, that the file system, will use to Read and Write data. Larger block sizes will help improve disk I/O performance when using large files, such as Databases.
Mostly of size 1024, need to be 4096 for bigger file support like in database server case.

Block Size: Different Terms
1: Hardware Block Size = Sector Size
2: File System Block Size = Block Size
3: Kernel Buffer Block Size = Block Size
4: Partition Table Block Size = Cylinder Size
----------------------------------------------------------------------------------------------------------------------------------------------------
MyISAM tables support concurrent inserts. If a MyISAM table has no holes in the middle resulting from deleted or updated records, inserts always take place at the end of the table and can be performed while other clients are reading the table. Concurrent inserts can take place even for a table that has been read-locked explicitly if the locking client acquired a READ LOCAL lock rather than a regular READ lock. If a table does have holes, concurrent inserts cannot be performed. However, you can remove the holes by using OPTIMIZE TABLE to defragment the table.
----------------------------------------------------------------------------------------------------------------------------------------------------
Clustered Index: Clustered index physically rearrange the data that users inserts in your tables. It is nothing but a dictionary type data where actual data remains.
Non-Clustered Index: It Non-Clustered Index contains pointers to the data that is stored in the data page. It is a kind of index backside of the book where you see only the reference of a kind of data.
----------------------------------------------------------------------------------------------------------------------------------------------------

To change the default size limit for MyISAM tables, set the myisam_data_pointer_size, which sets the number of bytes used for internal row pointers. The value is used to set the pointer size for new tables if you do not specify the MAX_ROWS option. The value of myisam_data_pointer_size can be from 2 to 7. A value of 4 permits tables up to 4GB; a value of 6 permits tables up to 256TB.

You can check the maximum data and index sizes by using this statement:

SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';
----------------------------------------------------------------------------------------------------------------------------------------------------

Both InnoDB and PostgreSQL - as well as many other databases - use a technique called multi-version concurrency control (MVCC) to provide transaction isolation: transactions should not see the work of other, uncommitted transactions.  MVCC means that, when a row is updated, the database stores both the old and new versions of the row.
----------------------------------------------------------------------------------------------------------------------------------------------------