Percona Server moved to https://jira.percona.com/projects/PS

Merge lp:~vlad-lesin/percona-server/preload_tables.5.5.16 into lp:percona-server/5.5

  1. preload_tables.5.5.16
  2. Merge into 5.5
Proposed by Vlad Lesin
Status: Work in progress
Proposed branch: lp:~vlad-lesin/percona-server/preload_tables.5.5.16
Merge into: lp:percona-server/5.5
Diff against target: 801 lines (+771/-0)
3 files modified
patches/innodb_preload_tables.patch (+768/-0)
patches/mysql-test.diff (+2/-0)
patches/series (+1/-0)
To merge this branch: bzr merge lp:~vlad-lesin/percona-server/preload_tables.5.5.16
Reviewer Review Type Date Requested Status
Laurynas Biveinis (community) Needs Fixing
Stewart Smith Pending
Review via email: mp+80693@code.launchpad.net

Description of the change

The implementation of https://blueprints.launchpad.net/percona-server/+spec/table-preload blueprint. The more detailed description can be read here https://code.launchpad.net/~vlad-lesin/percona-server/pintables/+merge/67855 . The code was merged from lp:~vlad-lesin/percona-server/pintables. All remarks from the last (https://code.launchpad.net/~vlad-lesin/percona-server/pintables/+merge/67855) proposition was taken into account.

To post a comment you must log in.
Revision history for this message
Laurynas Biveinis (laurynas-biveinis) wrote :

Needs update to the current branch-based lp:percona-server.

review: Needs Fixing
Reply

Unmerged revisions

195. By Vlad Lesin

functional tests

194. By Vlad Lesin

system vars test

193. By Vlad Lesin

Preload tables code has been added. The tests will be in futher commits.

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
=== added file 'patches/innodb_preload_tables.patch'
--- patches/innodb_preload_tables.patch 1970-01-01 00:00:00 +0000
+++ patches/innodb_preload_tables.patch 2011-10-28 16:11:24 +0000
@@ -0,0 +1,768 @@
1diff -ruN a/mysql-test/suite/sys_vars/r/innodb_preload_tables_basic.result b/mysql-test/suite/sys_vars/r/innodb_preload_tables_basic.result
2--- a/mysql-test/suite/sys_vars/r/innodb_preload_tables_basic.result 1970-01-01 03:00:00.000000000 +0300
3+++ b/mysql-test/suite/sys_vars/r/innodb_preload_tables_basic.result 2011-10-27 17:43:15.502166789 +0400
4@@ -0,0 +1,53 @@
5+'#---------------------BS_STVARS_035_01----------------------#'
6+SELECT COUNT(@@GLOBAL.innodb_preload_tables);
7+COUNT(@@GLOBAL.innodb_preload_tables)
8+0
9+1 Expected
10+'#---------------------BS_STVARS_035_02----------------------#'
11+SET @@GLOBAL.innodb_preload_tables=1;
12+ERROR HY000: Variable 'innodb_preload_tables' is a read only variable
13+Expected error 'Read only variable'
14+SELECT COUNT(@@GLOBAL.innodb_preload_tables);
15+COUNT(@@GLOBAL.innodb_preload_tables)
16+0
17+1 Expected
18+'#---------------------BS_STVARS_035_03----------------------#'
19+SELECT @@GLOBAL.innodb_preload_tables = VARIABLE_VALUE
20+FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
21+WHERE VARIABLE_NAME='innodb_preload_tables';
22+@@GLOBAL.innodb_preload_tables = VARIABLE_VALUE
23+NULL
24+1 Expected
25+SELECT COUNT(@@GLOBAL.innodb_preload_tables);
26+COUNT(@@GLOBAL.innodb_preload_tables)
27+0
28+1 Expected
29+SELECT COUNT(VARIABLE_VALUE)
30+FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
31+WHERE VARIABLE_NAME='innodb_preload_tables';
32+COUNT(VARIABLE_VALUE)
33+1
34+1 Expected
35+'#---------------------BS_STVARS_035_04----------------------#'
36+SELECT @@innodb_preload_tables = @@GLOBAL.innodb_preload_tables;
37+@@innodb_preload_tables = @@GLOBAL.innodb_preload_tables
38+NULL
39+1 Expected
40+'#---------------------BS_STVARS_035_05----------------------#'
41+SELECT COUNT(@@innodb_preload_tables);
42+COUNT(@@innodb_preload_tables)
43+0
44+1 Expected
45+SELECT COUNT(@@local.innodb_preload_tables);
46+ERROR HY000: Variable 'innodb_preload_tables' is a GLOBAL variable
47+Expected error 'Variable is a GLOBAL variable'
48+SELECT COUNT(@@SESSION.innodb_preload_tables);
49+ERROR HY000: Variable 'innodb_preload_tables' is a GLOBAL variable
50+Expected error 'Variable is a GLOBAL variable'
51+SELECT COUNT(@@GLOBAL.innodb_preload_tables);
52+COUNT(@@GLOBAL.innodb_preload_tables)
53+0
54+1 Expected
55+SELECT innodb_preload_tables = @@SESSION.innodb_preload_tables;
56+ERROR 42S22: Unknown column 'innodb_preload_tables' in 'field list'
57+Expected error 'Readonly variable'
58diff -ruN a/mysql-test/suite/sys_vars/t/innodb_preload_tables_basic.test b/mysql-test/suite/sys_vars/t/innodb_preload_tables_basic.test
59--- a/mysql-test/suite/sys_vars/t/innodb_preload_tables_basic.test 1970-01-01 03:00:00.000000000 +0300
60+++ b/mysql-test/suite/sys_vars/t/innodb_preload_tables_basic.test 2011-10-27 17:43:15.502166789 +0400
61@@ -0,0 +1,103 @@
62+
63+
64+################## mysql-test\t\innodb_preload_tables_basic.test #######
65+# #
66+# Variable Name: innodb_preload_tables #
67+# Scope: Global #
68+# Access Type: Static #
69+# Data Type: numeric #
70+# #
71+# #
72+# Creation Date: 2011-05-06 #
73+# Author : Vladislav Lesin #
74+# #
75+# #
76+# Description:Test Cases of Dynamic System Variable #
77+# innodb_preload_tables #
78+# that checks the behavior of this variable in the following ways #
79+# * Value Check #
80+# * Scope Check #
81+# #
82+# Reference: http://dev.mysql.com/doc/refman/5.1/en/ #
83+# server-system-variables.html #
84+# #
85+###############################################################################
86+
87+--source include/have_innodb.inc
88+
89+--echo '#---------------------BS_STVARS_035_01----------------------#'
90+####################################################################
91+# Displaying default value #
92+####################################################################
93+SELECT COUNT(@@GLOBAL.innodb_preload_tables);
94+--echo 1 Expected
95+
96+
97+--echo '#---------------------BS_STVARS_035_02----------------------#'
98+####################################################################
99+# Check if Value can set #
100+####################################################################
101+
102+--error ER_INCORRECT_GLOBAL_LOCAL_VAR
103+SET @@GLOBAL.innodb_preload_tables=1;
104+--echo Expected error 'Read only variable'
105+
106+SELECT COUNT(@@GLOBAL.innodb_preload_tables);
107+--echo 1 Expected
108+
109+
110+
111+
112+--echo '#---------------------BS_STVARS_035_03----------------------#'
113+#################################################################
114+# Check if the value in GLOBAL Table matches value in variable #
115+#################################################################
116+
117+SELECT @@GLOBAL.innodb_preload_tables = VARIABLE_VALUE
118+FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
119+WHERE VARIABLE_NAME='innodb_preload_tables';
120+--echo 1 Expected
121+
122+SELECT COUNT(@@GLOBAL.innodb_preload_tables);
123+--echo 1 Expected
124+
125+SELECT COUNT(VARIABLE_VALUE)
126+FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
127+WHERE VARIABLE_NAME='innodb_preload_tables';
128+--echo 1 Expected
129+
130+
131+
132+--echo '#---------------------BS_STVARS_035_04----------------------#'
133+################################################################################
134+# Check if accessing variable with and without GLOBAL point to same variable #
135+################################################################################
136+SELECT @@innodb_preload_tables = @@GLOBAL.innodb_preload_tables;
137+--echo 1 Expected
138+
139+
140+
141+--echo '#---------------------BS_STVARS_035_05----------------------#'
142+################################################################################
143+# Check if innodb_preload_tables can be accessed with and without @@ sign #
144+################################################################################
145+
146+SELECT COUNT(@@innodb_preload_tables);
147+--echo 1 Expected
148+
149+--Error ER_INCORRECT_GLOBAL_LOCAL_VAR
150+SELECT COUNT(@@local.innodb_preload_tables);
151+--echo Expected error 'Variable is a GLOBAL variable'
152+
153+--Error ER_INCORRECT_GLOBAL_LOCAL_VAR
154+SELECT COUNT(@@SESSION.innodb_preload_tables);
155+--echo Expected error 'Variable is a GLOBAL variable'
156+
157+SELECT COUNT(@@GLOBAL.innodb_preload_tables);
158+--echo 1 Expected
159+
160+--Error ER_BAD_FIELD_ERROR
161+SELECT innodb_preload_tables = @@SESSION.innodb_preload_tables;
162+--echo Expected error 'Readonly variable'
163+
164+
165diff -ruN a/mysql-test/include/innodb_preload_tables.inc b/mysql-test/include/innodb_preload_tables.inc
166--- a/mysql-test/include/innodb_preload_tables.inc 1970-01-01 03:00:00.000000000 +0300
167+++ b/mysql-test/include/innodb_preload_tables.inc 2011-10-28 19:39:09.254999005 +0400
168@@ -0,0 +1,92 @@
169+# ==== Purpose ====
170+#
171+# Test preloading tables into innodb buffer pool with
172+# different parameters
173+#
174+# ==== Usage ====
175+# [--let $file_format= format]
176+# [--let $create_table_tail= tail]
177+# --source include/restart_mysqld_with_parameters.inc
178+#
179+# Parameters:
180+#
181+# $file_format
182+# Innodb tablespace file format
183+# $create_table_tail
184+# This tail will be added to table creation statement
185+
186+# Restart server with innodb_file_per_table enabled and Antelope file format
187+--let $server_parameters= --innodb-file-per-table --innodb-file-format=$file_format
188+--source include/restart_mysqld_with_parameters.inc
189+
190+--disable_warnings
191+drop table if exists t1, t2, t3;
192+--enable_warnings
193+
194+# Create test tables
195+eval CREATE TABLE t1 (
196+ col1 int not null auto_increment primary key,
197+ col2 char(255) not null) ENGINE=INNODB $create_table_tail;
198+
199+eval CREATE TABLE t2 (
200+ col1 int not null auto_increment primary key,
201+ col2 char(255) not null) ENGINE=INNODB $create_table_tail;
202+
203+eval CREATE TABLE t3 (
204+ col1 int not null auto_increment primary key,
205+ col2 char(255) not null) ENGINE=INNODB $create_table_tail;
206+
207+--disable_query_log
208+# Fill the tables with some data
209+start transaction;
210+--let $num= 1000
211+--while($num) {
212+--eval insert into t1 set col2 = 'some value $num';
213+--dec $num
214+--}
215+--let $num= 1000
216+--while($num) {
217+--eval insert into t2 set col2 = 'some value $num';
218+--dec $num
219+--}
220+--let $num= 1000
221+--while($num) {
222+--eval insert into t3 set col2 = 'some value $num';
223+--dec $num
224+--}
225+commit;
226+--enable_query_log
227+
228+# Restart server with preloading the whole testing tables
229+--let $server_parameters= --innodb-file-per-table --innodb-file-format=$file_format --innodb-preload-tables=test.t1,test.t2,test.t3
230+--source include/restart_mysqld_with_parameters.inc
231+
232+# Check if the tables in the buffer pool
233+let $t1_size= `select count(*) from information_schema.INNODB_BUFFER_POOL_PAGES where space_id =
234+ (select space from information_schema.INNODB_SYS_TABLES where name = 't1' and `schema` = 'test')`;
235+--echo $t1_size
236+let $t2_size= `select count(*) from information_schema.INNODB_BUFFER_POOL_PAGES where space_id =
237+ (select space from information_schema.INNODB_SYS_TABLES where name = 't2' and `schema` = 'test')`;
238+--echo $t2_size
239+let $t3_size= `select count(*) from information_schema.INNODB_BUFFER_POOL_PAGES where space_id =
240+ (select space from information_schema.INNODB_SYS_TABLES where name = 't3' and `schema` ='test')`;
241+--echo $t3_size
242+
243+# Restart server with tails preloading
244+--let $server_parameters= --innodb-file-per-table --innodb-file-format=$file_format --innodb-preload-tables=test.t1:1,test.t2:2,test.t3:3
245+--source include/restart_mysqld_with_parameters.inc
246+
247+# It's impossible to predict the exactly number of preloaded pages due to read ahead algorithm.
248+# That's why only comparision between the whole size and the readed size is used.
249+eval select $t1_size >
250+ (select count(*) from information_schema.INNODB_BUFFER_POOL_PAGES where space_id =
251+ (select space from information_schema.INNODB_SYS_TABLES where name = 't1' and `schema` = 'test'));
252+eval select $t2_size >
253+ (select count(*) from information_schema.INNODB_BUFFER_POOL_PAGES where space_id =
254+ (select space from information_schema.INNODB_SYS_TABLES where name = 't2' and `schema` = 'test'));
255+eval select $t3_size >
256+ (select count(*) from information_schema.INNODB_BUFFER_POOL_PAGES where space_id =
257+ (select space from information_schema.INNODB_SYS_TABLES where name = 't3' and `schema` = 'test'));
258+
259+drop table t1, t2, t3;
260+
261diff -ruN a/mysql-test/include/restart_mysqld_with_parameters.inc b/mysql-test/include/restart_mysqld_with_parameters.inc
262--- a/mysql-test/include/restart_mysqld_with_parameters.inc 1970-01-01 03:00:00.000000000 +0300
263+++ b/mysql-test/include/restart_mysqld_with_parameters.inc 2011-10-28 18:47:07.095020567 +0400
264@@ -0,0 +1,25 @@
265+# ==== Purpose ====
266+#
267+# Shut down and shut up a server with parameters.
268+#
269+# ==== Usage ====
270+#
271+# [--let $server_parameters= --flag1 --flag2 ...]
272+# --source include/restart_mysqld_with_parameters.inc
273+#
274+# ==== See also ====
275+#
276+# restart_mysqld.inc
277+
278+--let $_server_id= `SELECT @@server_id`
279+--let $_start_server_command= restart
280+--let $_expect_file_name= $MYSQLTEST_VARDIR/tmp/mysqld.$_server_id.expect
281+
282+--exec echo "wait" > $_expect_file_name
283+--shutdown_server 10
284+--source include/wait_until_disconnected.inc
285+--exec echo $_start_server_command:$server_parameters > $_expect_file_name
286+--enable_reconnect
287+--source include/wait_until_connected_again.inc
288+
289+
290diff -ruN a/mysql-test/r/innodb_preload_tables.result b/mysql-test/r/innodb_preload_tables.result
291--- a/mysql-test/r/innodb_preload_tables.result 1970-01-01 03:00:00.000000000 +0300
292+++ b/mysql-test/r/innodb_preload_tables.result 2011-10-28 19:37:36.045004542 +0400
293@@ -0,0 +1,70 @@
294+drop table if exists t1, t2, t3;
295+CREATE TABLE t1 (
296+col1 int not null auto_increment primary key,
297+col2 char(255) not null) ENGINE=INNODB ;
298+CREATE TABLE t2 (
299+col1 int not null auto_increment primary key,
300+col2 char(255) not null) ENGINE=INNODB ;
301+CREATE TABLE t3 (
302+col1 int not null auto_increment primary key,
303+col2 char(255) not null) ENGINE=INNODB ;
304+25
305+25
306+25
307+select 25 >
308+(select count(*) from information_schema.INNODB_BUFFER_POOL_PAGES where space_id =
309+(select space from information_schema.INNODB_SYS_TABLES where name = 't1' and `schema` = 'test'));
310+25 >
311+(select count(*) from information_schema.INNODB_BUFFER_POOL_PAGES where space_id =
312+(select space from information_schema.INNODB_SYS_TABLES where name = 't1' and `schema` = 'test'))
313+1
314+select 25 >
315+(select count(*) from information_schema.INNODB_BUFFER_POOL_PAGES where space_id =
316+(select space from information_schema.INNODB_SYS_TABLES where name = 't2' and `schema` = 'test'));
317+25 >
318+(select count(*) from information_schema.INNODB_BUFFER_POOL_PAGES where space_id =
319+(select space from information_schema.INNODB_SYS_TABLES where name = 't2' and `schema` = 'test'))
320+1
321+select 25 >
322+(select count(*) from information_schema.INNODB_BUFFER_POOL_PAGES where space_id =
323+(select space from information_schema.INNODB_SYS_TABLES where name = 't3' and `schema` = 'test'));
324+25 >
325+(select count(*) from information_schema.INNODB_BUFFER_POOL_PAGES where space_id =
326+(select space from information_schema.INNODB_SYS_TABLES where name = 't3' and `schema` = 'test'))
327+1
328+drop table t1, t2, t3;
329+drop table if exists t1, t2, t3;
330+CREATE TABLE t1 (
331+col1 int not null auto_increment primary key,
332+col2 char(255) not null) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
333+CREATE TABLE t2 (
334+col1 int not null auto_increment primary key,
335+col2 char(255) not null) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
336+CREATE TABLE t3 (
337+col1 int not null auto_increment primary key,
338+col2 char(255) not null) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
339+25
340+25
341+25
342+select 25 >
343+(select count(*) from information_schema.INNODB_BUFFER_POOL_PAGES where space_id =
344+(select space from information_schema.INNODB_SYS_TABLES where name = 't1' and `schema` = 'test'));
345+25 >
346+(select count(*) from information_schema.INNODB_BUFFER_POOL_PAGES where space_id =
347+(select space from information_schema.INNODB_SYS_TABLES where name = 't1' and `schema` = 'test'))
348+1
349+select 25 >
350+(select count(*) from information_schema.INNODB_BUFFER_POOL_PAGES where space_id =
351+(select space from information_schema.INNODB_SYS_TABLES where name = 't2' and `schema` = 'test'));
352+25 >
353+(select count(*) from information_schema.INNODB_BUFFER_POOL_PAGES where space_id =
354+(select space from information_schema.INNODB_SYS_TABLES where name = 't2' and `schema` = 'test'))
355+1
356+select 25 >
357+(select count(*) from information_schema.INNODB_BUFFER_POOL_PAGES where space_id =
358+(select space from information_schema.INNODB_SYS_TABLES where name = 't3' and `schema` = 'test'));
359+25 >
360+(select count(*) from information_schema.INNODB_BUFFER_POOL_PAGES where space_id =
361+(select space from information_schema.INNODB_SYS_TABLES where name = 't3' and `schema` = 'test'))
362+1
363+drop table t1, t2, t3;
364diff -ruN a/mysql-test/t/innodb_preload_tables.test b/mysql-test/t/innodb_preload_tables.test
365--- a/mysql-test/t/innodb_preload_tables.test 1970-01-01 03:00:00.000000000 +0300
366+++ b/mysql-test/t/innodb_preload_tables.test 2011-10-28 18:48:01.645213042 +0400
367@@ -0,0 +1,13 @@
368+#################################################################
369+# Check preloading noncompressed innodb tables into buffer pool #
370+#################################################################
371+--let $file_format= antelope
372+--let $create_table_tail=
373+--source include/innodb_preload_tables.inc
374+
375+################################################################################
376+# Check preloading compressed and noncompressed innodb tables into buffer pool #
377+################################################################################
378+--let $file_format= barracuda
379+--let $create_table_tail= ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4
380+--source include/innodb_preload_tables.inc
381
382diff -ruN a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
383--- a/storage/innobase/handler/ha_innodb.cc 2011-10-26 19:22:35.610637723 +0400
384+++ b/storage/innobase/handler/ha_innodb.cc 2011-10-27 17:43:15.862148091 +0400
385@@ -11778,6 +11778,13 @@
386 "Stores each InnoDB table to an .ibd file in the database dir.",
387 NULL, NULL, FALSE);
388
389+static MYSQL_SYSVAR_STR(preload_tables, srv_preload_tables,
390+ PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_MEMALLOC | PLUGIN_VAR_READONLY,
391+ "A coma separated list of names of tables (db_name.tbl_name:page_no,...) which need "
392+ "to be preloaded into innodb buffer pool. page_no - a pages count to preload from "
393+ "the end of tablespace",
394+ NULL, NULL, NULL);
395+
396 static MYSQL_SYSVAR_STR(file_format, innobase_file_format_name,
397 PLUGIN_VAR_RQCMDARG,
398 "File format to use for new tables in .ibd files.",
399@@ -12290,6 +12297,7 @@
400 MYSQL_SYSVAR(read_io_threads),
401 MYSQL_SYSVAR(write_io_threads),
402 MYSQL_SYSVAR(file_per_table),
403+ MYSQL_SYSVAR(preload_tables),
404 MYSQL_SYSVAR(file_format),
405 MYSQL_SYSVAR(file_format_check),
406 MYSQL_SYSVAR(file_format_max),
407diff -ruN a/storage/innobase/include/srv0srv.h b/storage/innobase/include/srv0srv.h
408--- a/storage/innobase/include/srv0srv.h 2011-10-26 19:22:35.260643726 +0400
409+++ b/storage/innobase/include/srv0srv.h 2011-10-27 17:43:15.502166789 +0400
410@@ -168,6 +168,8 @@
411 extern ulint srv_mem_pool_size;
412 extern ulint srv_lock_table_size;
413
414+extern char *srv_preload_tables;
415+
416 extern ibool srv_thread_concurrency_timer_based;
417
418 extern ulint srv_n_file_io_threads;
419diff -ruN a/storage/innobase/srv/srv0srv.c b/storage/innobase/srv/srv0srv.c
420--- a/storage/innobase/srv/srv0srv.c 2011-10-26 19:22:35.260643726 +0400
421+++ b/storage/innobase/srv/srv0srv.c 2011-10-27 17:43:15.502166789 +0400
422@@ -236,6 +236,11 @@
423 UNIV_INTERN ulint srv_mem_pool_size = ULINT_MAX;
424 UNIV_INTERN ulint srv_lock_table_size = ULINT_MAX;
425
426+/*A coma separated list of names of tables (db_name.tbl_name:page_no,...) which need
427+ to be preloaded into innodb buffer pool. page_no - a pages count to preload from
428+ the end of tablespace */
429+UNIV_INTERN char *srv_preload_tables = NULL;
430+
431 /* This parameter is deprecated. Use srv_n_io_[read|write]_threads
432 instead. */
433 UNIV_INTERN ulint srv_n_file_io_threads = ULINT_MAX;
434diff -ruN a/storage/innobase/srv/srv0start.c b/storage/innobase/srv/srv0start.c
435--- a/storage/innobase/srv/srv0start.c 2011-10-26 19:22:35.140685396 +0400
436+++ b/storage/innobase/srv/srv0start.c 2011-10-27 17:43:15.502166789 +0400
437@@ -62,6 +61,8 @@
438 #include "ibuf0ibuf.h"
439 #include "srv0start.h"
440 #include "srv0srv.h"
441+#include "mysql_com.h" /* NAME_LEN */
442+#include "dyn0dyn.h"
443 #ifndef UNIV_HOTBACKUP
444 # include "os0proc.h"
445 # include "sync0sync.h"
446@@ -1125,6 +1126,313 @@
447 return(DB_SUCCESS);
448 }
449
450+
451+/* The struct is used for preloading tables into buffer pool */
452+struct table_to_preload_struct {
453+ char name[NAME_LEN + 1]; /*!< table name */
454+ ulint n_pages; /*!< number of pages to preload
455+ from the end of a tablespace */
456+};
457+
458+typedef struct table_to_preload_struct table_to_preload_t;
459+
460+/********************************************************************
461+Removing spaces from the begining and the end of a string.
462+@return the result string length */
463+UNIV_INTERN
464+ulint
465+remove_spaces_from_begin_and_end(
466+ char *str, /*!< in/out: the pointer to a string */
467+ ulint str_len) /*!< in: a string length */
468+{
469+ char *p;
470+ char *p_end;
471+ char *begin;
472+ char *end;
473+
474+ ut_a(str);
475+
476+ p_end = str + str_len;
477+
478+ for (p = str; *p || p < p_end; ++p)
479+ if (*p != ' ')
480+ break;
481+
482+ if (p == p_end) {
483+ *str = 0;
484+ return(0);
485+ }
486+
487+ begin = p;
488+
489+ for (p = p_end - 1; p >= str; --p)
490+ if (*p != ' ')
491+ break;
492+ end = p + 1;
493+
494+ str_len = end - begin;
495+
496+ if (begin != str)
497+ memmove(str, begin, str_len);
498+
499+ str[str_len] = 0;
500+
501+ return str_len;
502+}
503+
504+/********************************************************************
505+A comma separated list of tables iteration. All dots in the names of tables
506+is replaced with slashes.
507+@return TRUE if there is an element to iterate and FALSE otherwise */
508+UNIV_INTERN
509+ibool
510+next_table_name(
511+ const char **begin, /*!< in/out: a beginning of a string,
512+ the *begin will point to the next table name
513+ in a list after extracting a current one */
514+ const char *end, /*!< in: the end of the string to iterate */
515+ table_to_preload_t *table_info) /*!< out: table space and pages number to preload */
516+{
517+
518+ const char *table_name_end;
519+ ulint table_name_len;
520+ char *p;
521+ char *n_pages_end;
522+
523+ ut_a(begin);
524+ ut_a(end);
525+ ut_a(table_info);
526+
527+ table_name_end = strchr(*begin, ',');
528+ if (!table_name_end)
529+ table_name_end = end;
530+ table_name_len = table_name_end - *begin;
531+ if (table_name_len > sizeof(table_info->name) - 1) {
532+ fprintf(stderr, "InnoDB: Warning:"
533+ " A full table name length is greater then %lu limit."
534+ " Skipping preloading table with wrong name.\n",
535+ sizeof(table_info->name) - 1);
536+ goto error;
537+ }
538+
539+ memcpy(table_info->name, *begin, table_name_len);
540+ *begin = *table_name_end ? table_name_end + 1 : table_name_end;
541+
542+ table_info->name[table_name_len] = 0;
543+ table_name_len = remove_spaces_from_begin_and_end(table_info->name, table_name_len);
544+ if (!table_name_len) {
545+ fprintf(stderr, "InnoDB: Warning:"
546+ " The list of tables names in 'innodb_preload_table'"
547+ " system variable contains an empty element.\n");
548+ goto error;
549+ }
550+
551+ table_info->n_pages = 0;
552+ for (p = table_info->name; *p; ++p) {
553+ if (*p == '.')
554+ *p = '/';
555+ else if (*p == ':') {
556+ *p = 0;
557+ ++p;
558+ table_info->n_pages = strtoul(p, &n_pages_end, 10);
559+ if (n_pages_end - p < strlen(p)) {
560+ fprintf(stderr, "InnoDB: Warning:"
561+ " The number of pages of table '%s' to preload"
562+ " into innodb buffer pool can't be parsed. Skip"
563+ " the table preloading.\n", table_info->name);
564+ goto error;
565+ }
566+ break;
567+ }
568+ }
569+
570+ return TRUE;
571+
572+error:
573+ table_info->n_pages = 0;
574+ table_info->name[0] = 0;
575+ return FALSE;
576+}
577+
578+
579+/********************************************************************
580+Finding a dup of the table in array of tables.
581+@return TRUE if a dup was found and FALSE otherwise
582+*/
583+UNIV_INTERN
584+ibool
585+find_dup(
586+ dyn_array_t *tables_info, /*!< in: the array of tables */
587+ const table_to_preload_t *table_info) /*!< in: the table to find */
588+{
589+ ulint tables_info_size;
590+ ulint tables_info_offset;
591+ const table_to_preload_t *current_table_info;
592+
593+ ut_a(tables_info);
594+ ut_a(table_info);
595+
596+ tables_info_size = dyn_array_get_data_size(tables_info);
597+ tables_info_offset = 0;
598+
599+ while (tables_info_offset < tables_info_size) {
600+ current_table_info =
601+ (const table_to_preload_t *)
602+ dyn_array_get_element(tables_info, tables_info_offset);
603+ tables_info_offset += sizeof(table_to_preload_t);
604+ if (table_info == current_table_info)
605+ continue;
606+ if (!strcmp(current_table_info->name, table_info->name)) {
607+ fprintf(stderr, "Innodb: Warning:"
608+ " The innodb_preload_tables contains a duplicate of '%s'\n",
609+ current_table_info->name);
610+ if ((current_table_info->n_pages || table_info->n_pages) &&
611+ current_table_info->n_pages != table_info->n_pages)
612+ fprintf(stderr, "Innodb: Warning:"
613+ " Ambigious declaration the number of pages to preload"
614+ " for table '%s' in preloading tables into buffer pool list."
615+ " The first declaration '%lu' will be used.\n",
616+ current_table_info->name, current_table_info->n_pages);
617+ return TRUE;
618+ }
619+ }
620+
621+ return FALSE;
622+}
623+
624+/********************************************************************
625+Parsing a comma separated list of names of tables into dynamic array
626+of able_to_preload_t. The duplicated elements are ignored.
627+*/
628+UNIV_INTERN
629+void
630+parse_tables_list(
631+ dyn_array_t *tables_info, /*!< out: the array of names of tables */
632+ const char *str_tables_list) /*!< in: the string to parse */
633+{
634+ const char *p;
635+ const char *str_tables_list_end;
636+ table_to_preload_t *table_info;
637+
638+ ut_a(tables_info);
639+ ut_a(str_tables_list);
640+
641+ str_tables_list_end = str_tables_list + strlen(str_tables_list);
642+ table_info = NULL;
643+ /* Using while(*p) here causes code generation error in gcc4.3 */
644+ for (p = str_tables_list; *p; ) {
645+ if (!table_info)
646+ table_info = (table_to_preload_t *)dyn_array_open(tables_info, sizeof(table_to_preload_t));;
647+ if (!next_table_name(&p, str_tables_list_end, table_info))
648+ break;
649+ if (table_info->name[0] && !find_dup(tables_info, table_info)) {
650+ dyn_array_close(tables_info, (byte *)(table_info + 1));
651+ table_info = NULL;
652+ }
653+ }
654+}
655+
656+
657+/********************************************************************
658+Preloading needed tables into the buffer pool. The innodb_preload_tables
659+system variable must contain a comma separated list of tables which need
660+to be preloaded. This feature will work only if the innodb_file_per_table
661+is enabled. Each element in the list may contain the number of pages which
662+will be preloaded from the end of the tablespace. The number of pages may
663+be listen through a colon (base.table:pages). If the buffer pool size is
664+less than the total size of tables listed in the innodb_preload_tables then
665+the preloading procedure will stop when the read size reaches the innodb
666+buffer pool size.
667+*/
668+UNIV_INTERN
669+void
670+preload_tables_into_buffer_pool(void)
671+{
672+ dict_table_t *table;
673+ unsigned space;
674+ ulint page;
675+ ulint zip_size;
676+ ulint space_size;
677+ ulint page_size;
678+ ulint readed_size;
679+ ulint buf_pool_size;
680+ mtr_t mtr;
681+ dyn_array_t tables_info;
682+ ulint tables_info_size;
683+ ulint tables_info_offset;
684+ table_to_preload_t *table_info;
685+ ulint last_page;
686+
687+ if (!srv_preload_tables)
688+ return;
689+
690+ if (!srv_file_per_table) {
691+ ut_print_timestamp(stderr);
692+ fprintf(stderr, " InnoDB: Warning:"
693+ " The option innodb_file_per_table is disabled,"
694+ " so using innodb_preload_table does no sense.\n");
695+ return;
696+ }
697+
698+ ut_print_timestamp(stderr);
699+ fprintf(stderr, " InnoDB: Preloading tables into buffer pool\n");
700+
701+ readed_size = 0;
702+ buf_pool_size = srv_buf_pool_curr_size;
703+
704+ dyn_array_create(&tables_info);
705+ parse_tables_list(&tables_info, (const char *)srv_preload_tables);
706+ tables_info_size = dyn_array_get_data_size(&tables_info);
707+ tables_info_offset = 0;
708+
709+ while (tables_info_offset < tables_info_size) {
710+ table_info = (table_to_preload_t *)dyn_array_get_element(&tables_info, tables_info_offset);
711+ tables_info_offset += sizeof(table_to_preload_t);
712+
713+ table = dict_table_get(table_info->name, 1);
714+ if (!table) {
715+ fprintf(stderr, "InnoDB: Warning:"
716+ " Can't open the table '%s' while preloading tables"
717+ " into innodb buffer pool.\n", table_info->name);
718+ continue;
719+ }
720+
721+ ut_print_timestamp(stderr);
722+ fprintf(stderr,
723+ " InnoDB: Preloading '%s' table into buffer pool\n",
724+ table_info->name);
725+
726+ space = table->space;
727+ space_size = fil_space_get_size(space);
728+ zip_size = fil_space_get_zip_size(space);
729+ page_size = zip_size ? zip_size : UNIV_PAGE_SIZE;
730+ last_page = (table_info->n_pages && table_info->n_pages < space_size) ?
731+ space_size - table_info->n_pages :
732+ 0;
733+ for (page = space_size; page > last_page; --page) {
734+ mtr_start(&mtr);
735+ buf_page_get_with_no_latch(space, zip_size, page - 1, &mtr);
736+ mtr_commit(&mtr);
737+ readed_size += page_size;
738+ if (readed_size >= buf_pool_size) {
739+ ut_print_timestamp(stderr);
740+ fprintf(stderr, " InnoDB: Warning:"
741+ " the buffer pool size %lu is less than"
742+ " the total size of preloaded tables,"
743+ " the preloading is stopped.\n", buf_pool_size);
744+ dict_table_decrement_handle_count(table, FALSE);
745+ goto exit;
746+ }
747+ };
748+ dict_table_decrement_handle_count(table, FALSE);
749+ }
750+ ut_print_timestamp(stderr);
751+ fprintf(stderr, " InnoDB: Preloading tables into buffer pool done\n");
752+exit:
753+ dyn_array_free(&tables_info);
754+}
755+
756+
757 /********************************************************************
758 Starts InnoDB and creates a new database if database files
759 are not found and the user wants.
760@@ -2256,6 +2564,8 @@
761
762 srv_was_started = TRUE;
763
764+ preload_tables_into_buffer_pool();
765+
766 return((int) DB_SUCCESS);
767 }
768
0769
=== modified file 'patches/mysql-test.diff'
--- patches/mysql-test.diff 2011-10-10 14:35:27 +0000
+++ patches/mysql-test.diff 2011-10-28 16:11:24 +0000
@@ -1255,6 +1255,7 @@
1255+INNODB_OLD_BLOCKS_TIME1255+INNODB_OLD_BLOCKS_TIME
1256+INNODB_OPEN_FILES1256+INNODB_OPEN_FILES
1257+INNODB_PAGE_SIZE1257+INNODB_PAGE_SIZE
1258+INNODB_PRELOAD_TABLES
1258+INNODB_PURGE_BATCH_SIZE1259+INNODB_PURGE_BATCH_SIZE
1259+INNODB_PURGE_THREADS1260+INNODB_PURGE_THREADS
1260+INNODB_RANDOM_READ_AHEAD1261+INNODB_RANDOM_READ_AHEAD
@@ -1634,6 +1635,7 @@
1634+INNODB_OLD_BLOCKS_TIME1635+INNODB_OLD_BLOCKS_TIME
1635+INNODB_OPEN_FILES1636+INNODB_OPEN_FILES
1636+INNODB_PAGE_SIZE1637+INNODB_PAGE_SIZE
1638+INNODB_PRELOAD_TABLES
1637+INNODB_PURGE_BATCH_SIZE1639+INNODB_PURGE_BATCH_SIZE
1638+INNODB_PURGE_THREADS1640+INNODB_PURGE_THREADS
1639+INNODB_RANDOM_READ_AHEAD1641+INNODB_RANDOM_READ_AHEAD
16401642
=== modified file 'patches/series'
--- patches/series 2011-10-13 09:13:42 +0000
+++ patches/series 2011-10-28 16:11:24 +0000
@@ -59,3 +59,4 @@
59xtradb_bug317074.patch59xtradb_bug317074.patch
60subunit.patch60subunit.patch
61bug860910.patch61bug860910.patch
62innodb_preload_tables.patch

Subscribers

People subscribed via source and target branches