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 on 2011-10-28
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 on 2012-08-22
Stewart Smith 2011-10-28 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.
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 on 2011-10-28

functional tests

194. By Vlad Lesin on 2011-10-27

system vars test

193. By Vlad Lesin on 2011-10-26

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

Subscribers

People subscribed via source and target branches