Showing posts from July, 2015

InnoDB data off the disk

MySQL's InnoDB storage engine data refresh every situation. This post from InnoDB down, look at the data from the memory to the InnoDB actually written on the media storage device in the end there is a buffer in which to work. We look through the following figure at the relevant buffer: From the figure above, we can see that the InnoDB data to disk need to go through     InnoDB buffer pool, Redo log buffer. This is a buffer InnoDB application itself.     page cache / Buffer cache (can be bypassed by o_direct). This is a buffer vfs layer.     Inode cache / directory buffer. This is the buffer vfs layer. Need to refresh by O_SYNC or fsync ().     Write-Back buffer. (You can set parameters to bypass the memory controller)     Disk on-borad buffer. (Can be bypassed by setting the disk controller parameters) Here we use the term "buffer" (typically buffer) to represent the data write scratch, using the term "cache" (usually cache) to represent the data re


20 Database Design Best Practices Use well defined and consistent names for tables and columns (e.g. School, StudentCourse, CourseID ...). Use singular for table names (i.e. use StudentCourse instead of StudentCourses). Table represents a collection of entities, there is no need for plural names. Don’t use spaces for table names. Otherwise you will have to use ‘{‘, ‘[‘, ‘“’ etc. characters to define tables (i.e. for accesing table Student Course you'll write “Student Course”. StudentCourse is much better). Don’t use unnecessary prefixes or suffixes for table names (i.e. use School instead of TblSchool, SchoolTable etc.). Keep passwords as encrypted for security. Decrypt them in application when required. Use integer id fields for all tables. If id is not required for the time being, it may be required in the future (for association tables, indexing ...). Choose columns with the integer data type (or its variants) for indexing. varchar column indexing will cause performanc