{"id":4068,"date":"2020-02-13T12:26:51","date_gmt":"2020-02-13T05:26:51","guid":{"rendered":"https:\/\/tino.vn\/?post_type=ht_kb&p=4068"},"modified":"2020-02-13T12:26:51","modified_gmt":"2020-02-13T05:26:51","slug":"mariadb-advance-innodb-buffer-pool","status":"publish","type":"ht_kb","link":"https:\/\/tino.vn\/blog\/docs\/mariadb-advance-innodb-buffer-pool\/","title":{"rendered":"MariaDB Advance: InnoDB Buffer Pool"},"content":{"rendered":"

Nh\u00f3m b\u1ed9 \u0111\u1ec7m XtraDB \/ InnoDB l\u00e0 th\u00e0nh ph\u1ea7n ch\u00ednh \u0111\u1ec3 t\u1ed1i \u01b0u h\u00f3a MariaDB. N\u00f3 l\u01b0u tr\u1eef d\u1eef li\u1ec7u v\u00e0 ch\u1ec9 m\u1ee5c, v\u00e0 b\u1ea1n th\u01b0\u1eddng mu\u1ed1n n\u00f3 c\u00e0ng l\u1edbn c\u00e0ng t\u1ed1t \u0111\u1ec3 gi\u1eef c\u00e0ng nhi\u1ec1u d\u1eef li\u1ec7u v\u00e0 indexes trong b\u1ed9 nh\u1edb, gi\u1ea3m IO \u0111\u0129a, nh\u01b0 l\u00e0 n\u00fat c\u1ed5 chai (bottleneck).<\/p>\n

C\u00e1ch Buffer Pool ho\u1ea1t \u0111\u1ed9ng<\/span><\/p>\n

Buffer Pool c\u1ed1 g\u1eafng gi\u1eef c\u00e1c kh\u1ed1i \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng th\u01b0\u1eddng xuy\u00ean trong b\u1ed9 \u0111\u1ec7m v\u00e0 v\u1ec1 c\u01a1 b\u1ea3n ho\u1ea1t \u0111\u1ed9ng nh\u01b0 hai danh s\u00e1ch con, danh s\u00e1ch con m\u1edbi<\/em> c\u1ee7a th\u00f4ng tin \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng g\u1ea7n \u0111\u00e2y v\u00e0 danh s\u00e1ch con c\u0169<\/em> c\u1ee7a th\u00f4ng tin c\u0169. Theo m\u1eb7c \u0111\u1ecbnh, 37% danh s\u00e1ch \u0111\u01b0\u1ee3c d\u00e0nh ri\u00eang cho danh s\u00e1ch c\u0169.<\/p>\n

Khi th\u00f4ng tin m\u1edbi \u0111\u01b0\u1ee3c truy c\u1eadp kh\u00f4ng xu\u1ea5t hi\u1ec7n trong danh s\u00e1ch n\u00e0o c\u1ea3, l\u00fac \u0111\u00f3 n\u00f3 s\u1ebd \u0111\u01b0\u1ee3c \u0111\u1eb7t \u1edf \u0111\u1ea7u danh s\u00e1ch c\u0169, m\u1ee5c c\u0169 nh\u1ea5t trong danh s\u00e1ch c\u0169 s\u1ebd b\u1ecb x\u00f3a.<\/p>\n

Khi th\u00f4ng tin \u0111\u01b0\u1ee3c truy c\u1eadp xu\u1ea5t hi\u1ec7n trong danh s\u00e1ch c\u0169, n\u00f3 s\u1ebd \u0111\u01b0\u1ee3c chuy\u1ec3n l\u00ean \u0111\u1ea7u danh s\u00e1ch m\u1edbi v\u00e0 m\u1ecdi th\u1ee9 \u1edf tr\u00ean s\u1ebd di chuy\u1ec3n xu\u1ed1ng m\u1ed9t v\u1ecb tr\u00ed d\u01b0\u1edbi th\u00f4ng tin v\u1eeba \u0111\u01b0\u1ee3c n\u1ea1p.<\/p>\n

innodb_buffer_pool_size<\/span><\/p>\n

Server system variable m\u00e1y ch\u1ee7 quan tr\u1ecdng nh\u1ea5t l\u00e0 innodb_buffer_pool_size , b\u1ea1n c\u00f3 th\u1ec3 \u0111\u1eb7t t\u1eeb 70-80% t\u1ed5ng ram kh\u1ea3 d\u1ee5ng tr\u00ean m\u1ed9t m\u00e1y ch\u1ee7 c\u01a1 s\u1edf d\u1eef li\u1ec7u chuy\u00ean d\u1ee5ng ch\u1ec9 c\u00f3 ho\u1eb7c ch\u1ee7 y\u1ebfu l\u00e0 c\u00e1c b\u1ea3ng XtraDB \/ InnoDB.<\/p>\n

L\u01b0u \u00fd r\u1eb1ng t\u1ed5ng ram \u0111\u01b0\u1ee3c ph\u00e2n b\u1ed5 nhi\u1ec1u h\u01a1n kho\u1ea3ng 10% so v\u1edbi k\u00edch th\u01b0\u1edbc \u0111\u00e3 ch\u1ec9 \u0111\u1ecbnh v\u00ec kh\u00f4ng gian th\u00eam c\u0169ng \u0111\u01b0\u1ee3c d\u00e0nh cho c\u00e1c c\u1ea5u tr\u00fac \u0111i\u1ec1u khi\u1ec3n v\u00e0 b\u1ed9 \u0111\u1ec7m.<\/p>\n

K\u00edch th\u01b0\u1edbc c\u00e0ng l\u1edbn, th\u1eddi gian kh\u1edfi t\u1ea1o c\u00e0ng l\u00e2u. Tr\u00ean m\u00e1y ch\u1ee7 64 bit hi\u1ec7n \u0111\u1ea1i c\u00f3 b\u1ed9 nh\u1edb 10GB, qu\u00e1 tr\u00ecnh n\u00e0y c\u00f3 th\u1ec3 m\u1ea5t n\u0103m gi\u00e2y tr\u1edf l\u00ean \u0111\u1ec3 kh\u1edfi \u0111\u1ed9ng.<\/p>\n

\u0110\u1ea3m b\u1ea3o r\u1eb1ng k\u00edch th\u01b0\u1edbc Buffer Pool kh\u00f4ng qu\u00e1 l\u1edbn \u0111\u1ec3 tr\u00e1nh tr\u01b0\u1eddng h\u1ee3p kh\u00f4ng \u0111\u1ee7 ram d\u1eabn \u0111\u1ebfn h\u1ec7 \u0111i\u1ec1u h\u00e0nh kill d\u1ecbch v\u1ee5.<\/p>\n

innodb_buffer_pool_instances<\/span><\/p>\n

N\u1ebfu innodb_buffer_pool_size \u0111\u01b0\u1ee3c \u0111\u1eb7t th\u00e0nh h\u01a1n 1GB, innodb_buffer_pool_instances chia nh\u00f3m b\u1ed9 \u0111\u1ec7m InnoDB th\u00e0nh m\u1ed9t s\u1ed1 tr\u01b0\u1eddng h\u1ee3p c\u1ee5 th\u1ec3. M\u1eb7c \u0111\u1ecbnh l\u00e0 1 trong MariaDB 5.5, nh\u01b0ng \u0111\u1ed1i v\u1edbi c\u00e1c h\u1ec7 th\u1ed1ng l\u1edbn c\u00f3 v\u00f9ng \u0111\u1ec7m nhi\u1ec1u gigabyte, nhi\u1ec1u tr\u01b0\u1eddng h\u1ee3p c\u00f3 th\u1ec3 gi\u00fap gi\u1ea3m s\u1ef1 tranh ch\u1ea5p. M\u1eb7c \u0111\u1ecbnh l\u00e0 8 trong MariaDB 10.0,\u00a0 \u00a0t\u00f9y thu\u1ed9c v\u00e0o gi\u00e1 tr\u1ecb c\u1ee7a innodb_buffer_pool_size. M\u1ed7i phi\u00ean b\u1ea3n qu\u1ea3n l\u00fd c\u00e1c c\u1ea5u tr\u00fac d\u1eef li\u1ec7u c\u1ee7a ri\u00eang n\u00f3 v\u00e0 chi\u1ebfm m\u1ed9t ph\u1ea7n b\u1eb1ng nhau trong t\u1ed5ng k\u00edch th\u01b0\u1edbc nh\u00f3m b\u1ed9 \u0111\u1ec7m, v\u00ec v\u1eady, v\u00ed d\u1ee5 n\u1ebfu innodb_buffer_pool_size l\u00e0 4GB v\u00e0 innodb_buffer_pool_instances \u0111\u01b0\u1ee3c \u0111\u1eb7t th\u00e0nh 4, m\u1ed7i phi\u00ean b\u1ea3n s\u1ebd l\u00e0 1GB. M\u1ed7i tr\u01b0\u1eddng h\u1ee3p l\u00fd t\u01b0\u1edfng n\u00ean c\u00f3 k\u00edch th\u01b0\u1edbc t\u1ed1i thi\u1ec3u 1GB.<\/p>\n

V\u00ed d\u1ee5<\/span>: b\u1ea1n c\u00f3 th\u1ec3 \u0111\u1eb7t innodb_buffer_pool_size th\u00e0nh 6 GB v\u00e0 innodb_buffer_pool_instances th\u00e0nh 4 trong t\u1ec7p tin c\u1ea5u h\u00ecnh my.cnf MariaDB c\u1ee7a b\u1ea1n:<\/div>\n
\n
; InnoDB buffer pool size in bytes. The primary value to adjust on a database server,\r\n; can be set up to 80% of the total memory in these environments\r\ninnodb_buffer_pool_size = 6000M\r\n;\u00a0 If innodb_buffer_pool_size is set to more than 1GB, innodb_buffer_pool_instances\r\n; divides the InnoDB buffer pool into this many instances.\r\ninnodb_buffer_pool_instances = 4<\/pre>\n
\u0110i\u1ec1u n\u00e0y c\u00f3 ng\u0129a l\u00e0 m\u1ed7i pool s\u1ebd c\u00f3 1,5GB \u0111\u1ec3 l\u01b0u tr\u1eef d\u1eef li\u1ec7u tr\u00ean ram.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
Nh\u00f3m b\u1ed9 \u0111\u1ec7m XtraDB \/ InnoDB l\u00e0 th\u00e0nh ph\u1ea7n ch\u00ednh \u0111\u1ec3 t\u1ed1i \u01b0u h\u00f3a MariaDB. N\u00f3 l\u01b0u tr\u1eef d\u1eef li\u1ec7u v\u00e0 ch\u1ec9 m\u1ee5c, v\u00e0 b\u1ea1n th\u01b0\u1eddng mu\u1ed1n n\u00f3 c\u00e0ng l\u1edbn c\u00e0ng t\u1ed1t \u0111\u1ec3 gi\u1eef c\u00e0ng nhi\u1ec1u d\u1eef li\u1ec7u v\u00e0 indexes trong b\u1ed9 nh\u1edb, gi\u1ea3m IO \u0111\u0129a, nh\u01b0 l\u00e0 n\u00fat c\u1ed5 chai (bottleneck). C\u00e1ch Buffer Pool […]<\/p>\n","protected":false},"author":11,"featured_media":16424,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","format":"standard","meta":{"footnotes":""},"tags":[],"ht_kb_category":[5156],"class_list":["post-4068","ht_kb","type-ht_kb","status-publish","format-standard","has-post-thumbnail","hentry","ht_kb_category-mariadb"],"_links":{"self":[{"href":"https:\/\/tino.vn\/blog\/wp-json\/wp\/v2\/ht_kb\/4068","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/tino.vn\/blog\/wp-json\/wp\/v2\/ht_kb"}],"about":[{"href":"https:\/\/tino.vn\/blog\/wp-json\/wp\/v2\/types\/ht_kb"}],"author":[{"embeddable":true,"href":"https:\/\/tino.vn\/blog\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/tino.vn\/blog\/wp-json\/wp\/v2\/comments?post=4068"}],"version-history":[{"count":0,"href":"https:\/\/tino.vn\/blog\/wp-json\/wp\/v2\/ht_kb\/4068\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/tino.vn\/blog\/wp-json\/wp\/v2\/media\/16424"}],"wp:attachment":[{"href":"https:\/\/tino.vn\/blog\/wp-json\/wp\/v2\/media?parent=4068"}],"wp:term":[{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tino.vn\/blog\/wp-json\/wp\/v2\/tags?post=4068"},{"taxonomy":"ht_kb_category","embeddable":true,"href":"https:\/\/tino.vn\/blog\/wp-json\/wp\/v2\/ht_kb_category?post=4068"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}