Merge lp:~vlad-lesin/percona-server/5.5-bug-1038940-table_cache_speed_up into lp:percona-server/5.5

Proposed by Vlad Lesin on 2013-01-03
Status: Work in progress
Proposed branch: lp:~vlad-lesin/percona-server/5.5-bug-1038940-table_cache_speed_up
Merge into: lp:percona-server/5.5
Diff against target: 320 lines (+116/-34)
10 files modified
Percona-Server/storage/myisam/ha_myisam.cc (+3/-0)
Percona-Server/storage/myisam/mi_close.c (+1/-1)
Percona-Server/storage/myisam/mi_dbug.c (+3/-3)
Percona-Server/storage/myisam/mi_keycache.c (+3/-3)
Percona-Server/storage/myisam/mi_open.c (+67/-13)
Percona-Server/storage/myisam/mi_panic.c (+24/-8)
Percona-Server/storage/myisam/mi_static.c (+1/-1)
Percona-Server/storage/myisam/myisamchk.c (+6/-0)
Percona-Server/storage/myisam/myisamdef.h (+3/-3)
Percona-Server/storage/myisam/myisampack.c (+5/-2)
To merge this branch: bzr merge lp:~vlad-lesin/percona-server/5.5-bug-1038940-table_cache_speed_up
Reviewer Review Type Date Requested Status
Laurynas Biveinis (community) Needs Fixing on 2013-03-25
Stewart Smith 2013-01-03 Pending
Review via email: mp+141748@code.launchpad.net

Description of the change

This MP is port of this http://lists.mysql.com/commits/121507 patch to 5.5(see issue #25137). The main idea of this patch is to replace linked list of opened tables with hash-table to improve performance of open table lookup mechanism.

Jenkins test:
http://jenkins.percona.com/view/PS%205.5/job/percona-server-5.5-param/610

To post a comment you must log in.
387. By Stewart Smith on 2013-01-03

Merge lp:~lp-dev-merge-bot/percona-server/staging-5.1 containing lp:~vlad-lesin/percona-server/5.1-bug1049871-injections-gca to 5.5 - one small conflict

388. By Stewart Smith on 2013-01-07

reverse the bad merge from previous revision (r387 - Merge lp:~lp-dev-merge-bot/percona-server/staging-5.1 containing lp:~vlad-lesin/percona-server/5.1-bug1049871-injections-gca to 5.5 - one small conflict
modified). This merge was incorrect and caused rpl.rpl_mdev382 to fail

389. By <email address hidden> on 2013-01-07

Merge lp:~hrvojem/percona-server/bug1070930-5.5

390. By <email address hidden> on 2013-01-07

Merge lp:~laurynas-biveinis/percona-server/BT-16274-bug1087202-10872128-5.5-2

391. By <email address hidden> on 2013-01-08

Merge lp:~hrvojem/percona-server/rn-5.5.28-29.3

392. By <email address hidden> on 2013-01-08

Empty merge from Percona Server 5.1

393. By <email address hidden> on 2013-01-09

Merge lp:~percona-core/percona-server/release-5.5.28-29.3

394. By <email address hidden> on 2013-01-09

Merge lp:~hrvojem/percona-server/rn-5.5.28-29.3-r2

395. By <email address hidden> on 2013-01-09

Merge lp:~hrvojem/percona-server/rn-5.1.66-14.2-r2-5.5

396. By <email address hidden> on 2013-01-09

Empty merge from Percona Server 5.1

397. By <email address hidden> on 2013-01-15

Merge lp:~stewart/percona-server/5.5.29

398. By <email address hidden> on 2013-01-17

Merge lp:~hrvojem/percona-server/bug1092106-5.5

399. By <email address hidden> on 2013-01-17

Empty merge from Percona Server 5.1

400. By <email address hidden> on 2013-01-17

Merge lp:~stewart/percona-server/5.5-5.1.66-14.1-merge

401. By <email address hidden> on 2013-01-18

Empty merge from Percona Server 5.1

402. By Stewart Smith on 2013-01-18

merge 5.1.66-14.2 release branch

403. By <email address hidden> on 2013-01-18

Merge lp:~hrvojem/percona-server/rn-5.1.67-14.3-5.5

404. By <email address hidden> on 2013-01-18

Empty merge from Percona Server 5.1

405. By <email address hidden> on 2013-01-18

Merge lp:~sergei.glushchenko/percona-server/ST-27220-bug1042946

406. By <email address hidden> on 2013-01-19

Empty merge from Percona Server 5.1

At line 193, "if (my_hash_inited(...))" should be !my_hash_inited instead I think.

Also, please make a 5.6 MP (without a bzr merge).

review: Needs Fixing
407. By <email address hidden> on 2013-01-20

Merge lp:~laurynas-biveinis/percona-server/bug1100159-5.5

408. By <email address hidden> on 2013-01-21

Merge lp:~sergei.glushchenko/percona-server/ps55-bug1017192

409. By <email address hidden> on 2013-01-21

Empty merge from Percona Server 5.1

410. By <email address hidden> on 2013-01-28

Merge lp:~laurynas-biveinis/percona-server/BT-16724-xtradb-bmp-requests-5.5

411. By <email address hidden> on 2013-01-29

Empty merge from Percona Server 5.1

412. By <email address hidden> on 2013-02-04

Merge lp:~laurynas-biveinis/percona-server/BT-16274-bug1082437-5.5

413. By <email address hidden> on 2013-02-05

Empty merge from Percona Server 5.1

414. By <email address hidden> on 2013-02-05

Merge lp:~laurynas-biveinis/percona-server/BT-16274-bug1083596-5.5

415. By <email address hidden> on 2013-02-05

Merge lp:~laurynas-biveinis/percona-server/BT-16274-bug1083669-5.5

416. By <email address hidden> on 2013-02-05

Merge lp:~laurynas-biveinis/percona-server/bug1083058-5.5

417. By <email address hidden> on 2013-02-06

Empty merge from Percona Server 5.1

418. By <email address hidden> on 2013-02-06

Merge lp:~stewart/percona-server/5.5-bug986247-remove-optimizer-fix

419. By <email address hidden> on 2013-02-06

Merge lp:~abychko/percona-server/bug1099809

420. By <email address hidden> on 2013-02-06

Merge lp:~hrvojem/percona-server/bug1065771-5.5

421. By <email address hidden> on 2013-02-06

Merge lp:~laurynas-biveinis/percona-server/bug1100178-1100643-5.5

422. By <email address hidden> on 2013-02-06

Empty merge from Percona Server 5.1

423. By <email address hidden> on 2013-02-06

Merge lp:~abychko/percona-server/bug1103328

424. By <email address hidden> on 2013-02-06

Merge lp:~laurynas-biveinis/percona-server/bug1105709-5.5

425. By <email address hidden> on 2013-02-06

Merge lp:~hrvojem/percona-server/ps-25

426. By <email address hidden> on 2013-02-06

Merge lp:~abychko/percona-server/bug881944

427. By <email address hidden> on 2013-02-07

Empty merge from Percona Server 5.1

428. By <email address hidden> on 2013-02-07

Merge lp:~laurynas-biveinis/percona-server/bpage-io-fix

429. By <email address hidden> on 2013-02-07

Empty merge from Percona Server 5.1

430. By <email address hidden> on 2013-02-07

Empty merge from Percona Server 5.1

431. By <email address hidden> on 2013-02-07

Empty merge from Percona Server 5.1

432. By <email address hidden> on 2013-02-07

Merge lp:~laurynas-biveinis/percona-server/bug1101030

433. By <email address hidden> on 2013-02-07

Merge lp:~laurynas-biveinis/percona-server/BT-16274-bug1111226-5.5

434. By <email address hidden> on 2013-02-08

Merge lp:~laurynas-biveinis/percona-server/bug1114612-5.5

435. By <email address hidden> on 2013-02-08

Merge lp:~laurynas-biveinis/percona-server/BT-16274-bug1108874-5.5

436. By <email address hidden> on 2013-02-09

Empty merge from Percona Server 5.1

437. By <email address hidden> on 2013-02-09

Empty merge from Percona Server 5.1

438. By <email address hidden> on 2013-02-11

Merge lp:~laurynas-biveinis/percona-server/slow-log-fixes-5.5

439. By Stewart Smith on 2013-02-11

null merge 5.1 implementation of slow query log fixes, as 5.5 is sep impl

440. By <email address hidden> on 2013-02-13

Merge lp:~laurynas-biveinis/percona-server/BT-16274-bug1105726-5.5

441. By <email address hidden> on 2013-02-13

Merge lp:~hrvojem/percona-server/bug1092189-5.5

442. By <email address hidden> on 2013-02-13

Empty merge from Percona Server 5.1

443. By <email address hidden> on 2013-02-13

Empty merge from Percona Server 5.1

444. By <email address hidden> on 2013-02-14

Empty merge from Percona Server 5.1

445. By <email address hidden> on 2013-02-14

Merge lp:~sergei.glushchenko/percona-server/55-tp

446. By Alexey Kopytov on 2013-02-15

Bug #1126077: Remove clang warnings

Fixed an annoying warning in the InnoDB code produced by clang.

This is a backport from 5.6.

447. By <email address hidden> on 2013-02-19

Merge lp:~gl-az/percona-server/BT-26980-5.5-bug1068210

448. By <email address hidden> on 2013-02-20

Merge lp:~laurynas-biveinis/percona-server/bug1089265-5.5

449. By <email address hidden> on 2013-02-20

Merge lp:~akopytov/percona-server/bug1125259

450. By <email address hidden> on 2013-02-20

Merge lp:~laurynas-biveinis/percona-server/bug1117067-5.5

451. By Vlad Lesin on 2013-02-24

As the number of open tables is increased, table lookup
(testing if a table is already open) and (in particular)
the case when a table is not open, became increasingly more
expensive.

The problem was caused by the open table lookup mechanism,
which was based on traversing a linked list comparing the
file names.

As the list was replaced by a hash table, the lookup
time dropped significantly when used on systems with
a large number of open tables.

The original patch can be found here:
http://lists.mysql.com/commits/121507

Vlad Lesin (vlad-lesin) wrote :

> At line 193, "if (my_hash_inited(...))" should be !my_hash_inited instead I
> think.

Fixed. As well unnecessary changes from mi_panic.c are removed. Currently 5.1, 5.5 and 5.6 patches are almost the same.

> Also, please make a 5.6 MP (without a bzr merge).
Done. See bug related branches.

Jenkins: http://jenkins.percona.com/view/PS%205.5/job/percona-server-5.5-param/692/

The patch is OK, but I'll have to ask you to redo it as a GCA involving all three 5.1/5.5/5.6.

review: Needs Fixing

Unmerged revisions

451. By Vlad Lesin on 2013-02-24

As the number of open tables is increased, table lookup
(testing if a table is already open) and (in particular)
the case when a table is not open, became increasingly more
expensive.

The problem was caused by the open table lookup mechanism,
which was based on traversing a linked list comparing the
file names.

As the list was replaced by a hash table, the lookup
time dropped significantly when used on systems with
a large number of open tables.

The original patch can be found here:
http://lists.mysql.com/commits/121507

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== modified file 'Percona-Server/storage/myisam/ha_myisam.cc'
2--- Percona-Server/storage/myisam/ha_myisam.cc 2012-10-17 03:47:45 +0000
3+++ Percona-Server/storage/myisam/ha_myisam.cc 2013-02-24 23:11:32 +0000
4@@ -2191,6 +2191,9 @@
5 myisam_hton->flags= HTON_CAN_RECREATE | HTON_SUPPORT_LOG_TABLES;
6 myisam_hton->is_supported_system_table= myisam_is_supported_system_table;
7
8+ if (mi_init_open_table_hash(table_cache_size))
9+ return 1;
10+
11 return 0;
12 }
13
14
15=== modified file 'Percona-Server/storage/myisam/mi_close.c'
16--- Percona-Server/storage/myisam/mi_close.c 2012-02-16 09:48:16 +0000
17+++ Percona-Server/storage/myisam/mi_close.c 2013-02-24 23:11:32 +0000
18@@ -54,7 +54,7 @@
19 info->opt_flag&= ~(READ_CACHE_USED | WRITE_CACHE_USED);
20 }
21 flag= !--share->reopen;
22- myisam_open_list=list_delete(myisam_open_list,&info->open_list);
23+ my_hash_delete(&myisam_open_table_hash, (uchar *) info);
24 mysql_mutex_unlock(&share->intern_lock);
25
26 my_free(mi_get_rec_buff_ptr(info, info->rec_buff));
27
28=== modified file 'Percona-Server/storage/myisam/mi_dbug.c'
29--- Percona-Server/storage/myisam/mi_dbug.c 2011-06-30 15:46:53 +0000
30+++ Percona-Server/storage/myisam/mi_dbug.c 2013-02-24 23:11:32 +0000
31@@ -181,14 +181,14 @@
32 my_bool check_table_is_closed(const char *name, const char *where)
33 {
34 char filename[FN_REFLEN];
35- LIST *pos;
36+ uint idx;
37 DBUG_ENTER("check_table_is_closed");
38
39 (void) fn_format(filename,name,"",MI_NAME_IEXT,4+16+32);
40 mysql_mutex_lock(&THR_LOCK_myisam);
41- for (pos=myisam_open_list ; pos ; pos=pos->next)
42+ for (idx= 0; idx < myisam_open_table_hash.records; ++idx)
43 {
44- MI_INFO *info=(MI_INFO*) pos->data;
45+ MI_INFO *info=(MI_INFO*) my_hash_element(&myisam_open_table_hash, idx);
46 MYISAM_SHARE *share=info->s;
47 if (!strcmp(share->unique_file_name,filename))
48 {
49
50=== modified file 'Percona-Server/storage/myisam/mi_keycache.c'
51--- Percona-Server/storage/myisam/mi_keycache.c 2011-06-30 15:46:53 +0000
52+++ Percona-Server/storage/myisam/mi_keycache.c 2013-02-24 23:11:32 +0000
53@@ -138,16 +138,16 @@
54 void mi_change_key_cache(KEY_CACHE *old_key_cache,
55 KEY_CACHE *new_key_cache)
56 {
57- LIST *pos;
58+ uint idx;
59 DBUG_ENTER("mi_change_key_cache");
60
61 /*
62 Lock list to ensure that no one can close the table while we manipulate it
63 */
64 mysql_mutex_lock(&THR_LOCK_myisam);
65- for (pos=myisam_open_list ; pos ; pos=pos->next)
66+ for (idx= 0; idx < myisam_open_table_hash.records; ++idx)
67 {
68- MI_INFO *info= (MI_INFO*) pos->data;
69+ MI_INFO *info=(MI_INFO*) my_hash_element(&myisam_open_table_hash, idx);
70 MYISAM_SHARE *share= info->s;
71 if (share->key_cache == old_key_cache)
72 mi_assign_to_key_cache(info, (ulonglong) ~0, new_key_cache);
73
74=== modified file 'Percona-Server/storage/myisam/mi_open.c'
75--- Percona-Server/storage/myisam/mi_open.c 2012-10-31 13:02:53 +0000
76+++ Percona-Server/storage/myisam/mi_open.c 2013-02-24 23:11:32 +0000
77@@ -37,23 +37,76 @@
78 }
79
80
81-/******************************************************************************
82-** Return the shared struct if the table is already open.
83-** In MySQL the server will handle version issues.
84-******************************************************************************/
85-
86+/*
87+ Get the value used as hash key (helper function for the
88+ open table hash). Function is used as a callback
89+ from the hash table
90+*/
91+static uchar *mi_open_table_hash_key(const uchar *record, size_t *length,
92+ my_bool not_used __attribute__((unused)))
93+{
94+ MI_INFO *info= (MI_INFO *) record;
95+ *length= info->s->unique_name_length;
96+ return (uchar*) info->s->unique_file_name;
97+}
98+
99+/**
100+ Initialize open table hash
101+
102+ Function is normally called from myisam_init
103+ with the system variable table_cache_size used
104+ as hash_size.
105+
106+ @param[in] hash_size Initial has size (elements)
107+ @return inidicates success or failure of initialization
108+ @retval 0 success
109+ @retval 1 failure
110+
111+*/
112+int mi_init_open_table_hash(ulong hash_size)
113+{
114+ if (hash_size == 0)
115+ hash_size= 32; /* default hash size */
116+
117+ if (my_hash_init(&myisam_open_table_hash, &my_charset_filename,
118+ hash_size, 0, 0, mi_open_table_hash_key, 0, 0))
119+ return 1; /* error */
120+
121+ return 0;
122+}
123+
124+
125+/**
126+ Retrieve the shared struct if the table is already
127+ open (i.e in the open table hash)
128+
129+ @param[in] filename table file name
130+ @return shared struct, 0 if not in the cache
131+*/
132 MI_INFO *test_if_reopen(char *filename)
133 {
134- LIST *pos;
135+ HASH_SEARCH_STATE current_record;
136+ int len= strlen(filename);
137
138- for (pos=myisam_open_list ; pos ; pos=pos->next)
139+ MI_INFO *info= (MI_INFO*) my_hash_first(&myisam_open_table_hash,
140+ (uchar *) filename,
141+ len, &current_record);
142+ /*
143+ There might be more than one instance of a table share for
144+ a given table in the hash table. We're interested in the one with
145+ last_version set, so we iterate until we find it
146+ */
147+ while (info)
148 {
149- MI_INFO *info=(MI_INFO*) pos->data;
150 MYISAM_SHARE *share=info->s;
151- if (!strcmp(share->unique_file_name,filename) && share->last_version)
152- return info;
153+ if (share->last_version)
154+ break;
155+
156+ info= (MI_INFO*) my_hash_next(&myisam_open_table_hash,
157+ (uchar *) filename,
158+ len, &current_record);
159 }
160- return 0;
161+ return info;
162 }
163
164
165@@ -623,8 +676,9 @@
166
167 *m_info=info;
168 thr_lock_data_init(&share->lock,&m_info->lock,(void*) m_info);
169- m_info->open_list.data=(void*) m_info;
170- myisam_open_list=list_add(myisam_open_list,&m_info->open_list);
171+
172+ if (my_hash_insert(&myisam_open_table_hash, (uchar *) m_info))
173+ goto err;
174
175 mysql_mutex_unlock(&THR_LOCK_myisam);
176
177
178=== modified file 'Percona-Server/storage/myisam/mi_panic.c'
179--- Percona-Server/storage/myisam/mi_panic.c 2011-06-30 15:46:53 +0000
180+++ Percona-Server/storage/myisam/mi_panic.c 2013-02-24 23:11:32 +0000
181@@ -27,22 +27,36 @@
182 int mi_panic(enum ha_panic_function flag)
183 {
184 int error=0;
185- LIST *list_element,*next_open;
186 MI_INFO *info;
187+ uint idx;
188 DBUG_ENTER("mi_panic");
189
190 mysql_mutex_lock(&THR_LOCK_myisam);
191- for (list_element=myisam_open_list ; list_element ; list_element=next_open)
192+
193+ if (!my_hash_inited(&myisam_open_table_hash))
194+ goto finish;
195+
196+ if (flag == HA_PANIC_CLOSE)
197 {
198- next_open=list_element->next; /* Save if close */
199- info=(MI_INFO*) list_element->data;
200- switch (flag) {
201- case HA_PANIC_CLOSE:
202+ while (myisam_open_table_hash.records)
203+ {
204+ /*
205+ As long as there are records in the hash, fetch the
206+ first, and close it.
207+ */
208+ info=(MI_INFO*) my_hash_element(&myisam_open_table_hash, 0);
209 mysql_mutex_unlock(&THR_LOCK_myisam); /* Not exactly right... */
210 if (mi_close(info))
211- error=my_errno;
212+ error= my_errno;
213 mysql_mutex_lock(&THR_LOCK_myisam);
214- break;
215+ }
216+ }
217+
218+ for (idx= 0; idx < myisam_open_table_hash.records; ++idx)
219+ {
220+ info=(MI_INFO*) my_hash_element(&myisam_open_table_hash, idx);
221+ switch (flag) {
222+ case HA_PANIC_CLOSE: break;
223 case HA_PANIC_WRITE: /* Do this to free databases */
224 #ifdef CANT_OPEN_FILES_TWICE
225 if (info->s->options & HA_OPTION_READ_ONLY_DATA)
226@@ -111,6 +125,8 @@
227 (void) mi_log(0); /* Close log if neaded */
228 ft_free_stopwords();
229 }
230+
231+finish:
232 mysql_mutex_unlock(&THR_LOCK_myisam);
233 if (!error)
234 DBUG_RETURN(0);
235
236=== modified file 'Percona-Server/storage/myisam/mi_static.c'
237--- Percona-Server/storage/myisam/mi_static.c 2011-06-30 15:46:53 +0000
238+++ Percona-Server/storage/myisam/mi_static.c 2013-02-24 23:11:32 +0000
239@@ -22,7 +22,7 @@
240 #include "myisamdef.h"
241 #endif
242
243-LIST *myisam_open_list=0;
244+HASH myisam_open_table_hash;
245 uchar myisam_file_magic[]=
246 { (uchar) 254, (uchar) 254,'\007', '\001', };
247 uchar myisam_pack_file_magic[]=
248
249=== modified file 'Percona-Server/storage/myisam/myisamchk.c'
250--- Percona-Server/storage/myisam/myisamchk.c 2012-10-30 14:10:42 +0000
251+++ Percona-Server/storage/myisam/myisamchk.c 2013-02-24 23:11:32 +0000
252@@ -88,6 +88,12 @@
253 get_options(&argc,(char***) &argv);
254 myisam_quick_table_bits=decode_bits;
255 error=0;
256+ if (mi_init_open_table_hash(0))
257+ {
258+ fprintf(stderr, "Can't initialize MyISAM storage engine\n");
259+ exit(-1);
260+ }
261+
262 while (--argc >= 0)
263 {
264 int new_error=myisamchk(&check_param, *(argv++));
265
266=== modified file 'Percona-Server/storage/myisam/myisamdef.h'
267--- Percona-Server/storage/myisam/myisamdef.h 2012-05-10 07:49:14 +0000
268+++ Percona-Server/storage/myisam/myisamdef.h 2013-02-24 23:11:32 +0000
269@@ -22,6 +22,7 @@
270 #include <my_pthread.h>
271 #include <thr_lock.h>
272 #include <mysql/psi/mysql_file.h>
273+#include "hash.h"
274
275 /* undef map from my_nosys; We need test-if-disk full */
276 #if defined(my_write)
277@@ -281,7 +282,6 @@
278 uint data_changed; /* Somebody has changed data */
279 uint save_update; /* When using KEY_READ */
280 int save_lastinx;
281- LIST open_list;
282 IO_CACHE rec_cache; /* When cacheing records */
283 uint preload_buff_size; /* When preloading indexes */
284 myf lock_wait; /* is 0 or MY_DONT_WAIT */
285@@ -465,8 +465,7 @@
286 #endif
287
288 /* Some extern variables */
289-
290-extern LIST *myisam_open_list;
291+extern HASH myisam_open_table_hash;
292 extern uchar myisam_file_magic[], myisam_pack_file_magic[];
293 extern uint myisam_read_vec[], myisam_readnext_vec[];
294 extern uint myisam_quick_table_bits;
295@@ -748,6 +747,7 @@
296 void mi_disable_non_unique_index(MI_INFO *info, ha_rows rows);
297
298 extern MI_INFO *test_if_reopen(char *filename);
299+extern int mi_init_open_table_hash(ulong size);
300 my_bool check_table_is_closed(const char *name, const char *where);
301 int mi_open_datafile(MI_INFO *info, MYISAM_SHARE *share, const char *orn_name,
302 File file_to_dup);
303
304=== modified file 'Percona-Server/storage/myisam/myisampack.c'
305--- Percona-Server/storage/myisam/myisampack.c 2011-06-30 15:46:53 +0000
306+++ Percona-Server/storage/myisam/myisampack.c 2013-02-24 23:11:32 +0000
307@@ -207,9 +207,12 @@
308 char **default_argv;
309 MY_INIT(argv[0]);
310
311- if (load_defaults("my",load_default_groups,&argc,&argv))
312+ if (load_defaults("my",load_default_groups,&argc,&argv) ||
313+ mi_init_open_table_hash(0))
314+ {
315+ fputs("Can't initialize MyISAM storage engine", stderr);
316 exit(1);
317-
318+ }
319 default_argv= argv;
320 get_options(&argc,&argv);
321

Subscribers

People subscribed via source and target branches