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

Proposed by Vlad Lesin on 2013-02-25
Status: Work in progress
Proposed branch: lp:~vlad-lesin/percona-server/5.6-bug-1038940-table_cache_speed_up
Merge into: lp:percona-server/5.6
Diff against target: 346 lines (+118/-40)
10 files modified
Percona-Server/storage/myisam/ha_myisam.cc (+3/-0)
Percona-Server/storage/myisam/mi_close.c (+4/-7)
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 (+66/-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.6-bug-1038940-table_cache_speed_up
Reviewer Review Type Date Requested Status
Laurynas Biveinis (community) 2013-02-25 Needs Fixing on 2013-03-25
Review via email: mp+150263@code.launchpad.net

Description of the change

This MP is port of this http://lists.mysql.com/commits/121507 patch to 5.6(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 tests:
http://jenkins.percona.com/view/PS%205.6/job/percona-server-5.6-param/43

To post a comment you must log in.

Spurious (C) change in diff lines 19-20.

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

317. 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
=== modified file 'Percona-Server/storage/myisam/ha_myisam.cc'
--- Percona-Server/storage/myisam/ha_myisam.cc 2013-01-18 04:53:12 +0000
+++ Percona-Server/storage/myisam/ha_myisam.cc 2013-02-25 04:28:26 +0000
@@ -2179,6 +2179,9 @@
2179 myisam_hton->flags= HTON_CAN_RECREATE | HTON_SUPPORT_LOG_TABLES;2179 myisam_hton->flags= HTON_CAN_RECREATE | HTON_SUPPORT_LOG_TABLES;
2180 myisam_hton->is_supported_system_table= myisam_is_supported_system_table;2180 myisam_hton->is_supported_system_table= myisam_is_supported_system_table;
21812181
2182 if (mi_init_open_table_hash(table_cache_size))
2183 return 1;
2184
2182 return 0;2185 return 0;
2183}2186}
21842187
21852188
=== modified file 'Percona-Server/storage/myisam/mi_close.c'
--- Percona-Server/storage/myisam/mi_close.c 2012-08-29 12:49:37 +0000
+++ Percona-Server/storage/myisam/mi_close.c 2013-02-25 04:28:26 +0000
@@ -1,4 +1,4 @@
1/* Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.1/* Copyright (c) 2001, 2011, Oracle and/or its affiliates. All rights reserved.
22
3 This program is free software; you can redistribute it and/or modify3 This program is free software; you can redistribute it and/or modify
4 it under the terms of the GNU General Public License as published by4 it under the terms of the GNU General Public License as published by
@@ -31,8 +31,7 @@
31 (long) info, (uint) share->reopen,31 (long) info, (uint) share->reopen,
32 (uint) share->tot_locks));32 (uint) share->tot_locks));
3333
34 if (info->open_list.data)34 mysql_mutex_lock(&THR_LOCK_myisam);
35 mysql_mutex_lock(&THR_LOCK_myisam);
36 if (info->lock_type == F_EXTRA_LCK)35 if (info->lock_type == F_EXTRA_LCK)
37 info->lock_type=F_UNLCK; /* HA_EXTRA_NO_USER_CHANGE */36 info->lock_type=F_UNLCK; /* HA_EXTRA_NO_USER_CHANGE */
3837
@@ -55,8 +54,7 @@
55 info->opt_flag&= ~(READ_CACHE_USED | WRITE_CACHE_USED);54 info->opt_flag&= ~(READ_CACHE_USED | WRITE_CACHE_USED);
56 }55 }
57 flag= !--share->reopen;56 flag= !--share->reopen;
58 if (info->open_list.data)57 my_hash_delete(&myisam_open_table_hash, (uchar *) info);
59 myisam_open_list= list_delete(myisam_open_list, &info->open_list);
60 mysql_mutex_unlock(&share->intern_lock);58 mysql_mutex_unlock(&share->intern_lock);
6159
62 my_free(mi_get_rec_buff_ptr(info, info->rec_buff));60 my_free(mi_get_rec_buff_ptr(info, info->rec_buff));
@@ -110,8 +108,7 @@
110 }108 }
111 my_free(info->s);109 my_free(info->s);
112 }110 }
113 if (info->open_list.data)111 mysql_mutex_unlock(&THR_LOCK_myisam);
114 mysql_mutex_unlock(&THR_LOCK_myisam);
115 if (info->ftparser_param)112 if (info->ftparser_param)
116 {113 {
117 my_free(info->ftparser_param);114 my_free(info->ftparser_param);
118115
=== modified file 'Percona-Server/storage/myisam/mi_dbug.c'
--- Percona-Server/storage/myisam/mi_dbug.c 2012-03-06 14:29:42 +0000
+++ Percona-Server/storage/myisam/mi_dbug.c 2013-02-25 04:28:26 +0000
@@ -181,14 +181,14 @@
181my_bool check_table_is_closed(const char *name, const char *where)181my_bool check_table_is_closed(const char *name, const char *where)
182{182{
183 char filename[FN_REFLEN];183 char filename[FN_REFLEN];
184 LIST *pos;184 uint idx;
185 DBUG_ENTER("check_table_is_closed");185 DBUG_ENTER("check_table_is_closed");
186186
187 (void) fn_format(filename,name,"",MI_NAME_IEXT,4+16+32);187 (void) fn_format(filename,name,"",MI_NAME_IEXT,4+16+32);
188 mysql_mutex_lock(&THR_LOCK_myisam);188 mysql_mutex_lock(&THR_LOCK_myisam);
189 for (pos=myisam_open_list ; pos ; pos=pos->next)189 for (idx= 0; idx < myisam_open_table_hash.records; ++idx)
190 {190 {
191 MI_INFO *info=(MI_INFO*) pos->data;191 MI_INFO *info=(MI_INFO*) my_hash_element(&myisam_open_table_hash, idx);
192 MYISAM_SHARE *share=info->s;192 MYISAM_SHARE *share=info->s;
193 if (!strcmp(share->unique_file_name,filename))193 if (!strcmp(share->unique_file_name,filename))
194 {194 {
195195
=== modified file 'Percona-Server/storage/myisam/mi_keycache.c'
--- Percona-Server/storage/myisam/mi_keycache.c 2011-09-07 10:08:09 +0000
+++ Percona-Server/storage/myisam/mi_keycache.c 2013-02-25 04:28:26 +0000
@@ -138,16 +138,16 @@
138void mi_change_key_cache(KEY_CACHE *old_key_cache,138void mi_change_key_cache(KEY_CACHE *old_key_cache,
139 KEY_CACHE *new_key_cache)139 KEY_CACHE *new_key_cache)
140{140{
141 LIST *pos;141 uint idx;
142 DBUG_ENTER("mi_change_key_cache");142 DBUG_ENTER("mi_change_key_cache");
143143
144 /*144 /*
145 Lock list to ensure that no one can close the table while we manipulate it145 Lock list to ensure that no one can close the table while we manipulate it
146 */146 */
147 mysql_mutex_lock(&THR_LOCK_myisam);147 mysql_mutex_lock(&THR_LOCK_myisam);
148 for (pos=myisam_open_list ; pos ; pos=pos->next)148 for (idx= 0; idx < myisam_open_table_hash.records; ++idx)
149 {149 {
150 MI_INFO *info= (MI_INFO*) pos->data;150 MI_INFO *info=(MI_INFO*) my_hash_element(&myisam_open_table_hash, idx);
151 MYISAM_SHARE *share= info->s;151 MYISAM_SHARE *share= info->s;
152 if (share->key_cache == old_key_cache)152 if (share->key_cache == old_key_cache)
153 mi_assign_to_key_cache(info, (ulonglong) ~0, new_key_cache);153 mi_assign_to_key_cache(info, (ulonglong) ~0, new_key_cache);
154154
=== modified file 'Percona-Server/storage/myisam/mi_open.c'
--- Percona-Server/storage/myisam/mi_open.c 2012-11-14 11:44:35 +0000
+++ Percona-Server/storage/myisam/mi_open.c 2013-02-25 04:28:26 +0000
@@ -48,23 +48,76 @@
48}48}
4949
5050
51/******************************************************************************51/*
52** Return the shared struct if the table is already open.52 Get the value used as hash key (helper function for the
53** In MySQL the server will handle version issues.53 open table hash). Function is used as a callback
54******************************************************************************/54 from the hash table
5555*/
56static uchar *mi_open_table_hash_key(const uchar *record, size_t *length,
57 my_bool not_used __attribute__((unused)))
58{
59 MI_INFO *info= (MI_INFO *) record;
60 *length= info->s->unique_name_length;
61 return (uchar*) info->s->unique_file_name;
62}
63
64/**
65 Initialize open table hash
66
67 Function is normally called from myisam_init
68 with the system variable table_cache_size used
69 as hash_size.
70
71 @param[in] hash_size Initial has size (elements)
72 @return inidicates success or failure of initialization
73 @retval 0 success
74 @retval 1 failure
75
76*/
77int mi_init_open_table_hash(ulong hash_size)
78{
79 if (hash_size == 0)
80 hash_size= 32; /* default hash size */
81
82 if (my_hash_init(&myisam_open_table_hash, &my_charset_filename,
83 hash_size, 0, 0, mi_open_table_hash_key, 0, 0))
84 return 1; /* error */
85
86 return 0;
87}
88
89
90/**
91 Retrieve the shared struct if the table is already
92 open (i.e in the open table hash)
93
94 @param[in] filename table file name
95 @return shared struct, 0 if not in the cache
96*/
56MI_INFO *test_if_reopen(char *filename)97MI_INFO *test_if_reopen(char *filename)
57{98{
58 LIST *pos;99 HASH_SEARCH_STATE current_record;
100 int len= strlen(filename);
59101
60 for (pos=myisam_open_list ; pos ; pos=pos->next)102 MI_INFO *info= (MI_INFO*) my_hash_first(&myisam_open_table_hash,
103 (uchar *) filename,
104 len, &current_record);
105 /*
106 There might be more than one instance of a table share for
107 a given table in the hash table. We're interested in the one with
108 last_version set, so we iterate until we find it
109 */
110 while (info)
61 {111 {
62 MI_INFO *info=(MI_INFO*) pos->data;
63 MYISAM_SHARE *share=info->s;112 MYISAM_SHARE *share=info->s;
64 if (!strcmp(share->unique_file_name,filename) && share->last_version)113 if (share->last_version)
65 return info;114 break;
115
116 info= (MI_INFO*) my_hash_next(&myisam_open_table_hash,
117 (uchar *) filename,
118 len, &current_record);
66 }119 }
67 return 0;120 return info;
68}121}
69122
70123
@@ -649,8 +702,8 @@
649702
650 if (!internal_table)703 if (!internal_table)
651 {704 {
652 m_info->open_list.data= (void*) m_info;705 if (my_hash_insert(&myisam_open_table_hash, (uchar *) m_info))
653 myisam_open_list= list_add(myisam_open_list, &m_info->open_list);706 goto err;
654 mysql_mutex_unlock(&THR_LOCK_myisam);707 mysql_mutex_unlock(&THR_LOCK_myisam);
655 }708 }
656709
657710
=== modified file 'Percona-Server/storage/myisam/mi_panic.c'
--- Percona-Server/storage/myisam/mi_panic.c 2011-09-07 10:08:09 +0000
+++ Percona-Server/storage/myisam/mi_panic.c 2013-02-25 04:28:26 +0000
@@ -27,22 +27,36 @@
27int mi_panic(enum ha_panic_function flag)27int mi_panic(enum ha_panic_function flag)
28{28{
29 int error=0;29 int error=0;
30 LIST *list_element,*next_open;
31 MI_INFO *info;30 MI_INFO *info;
31 uint idx;
32 DBUG_ENTER("mi_panic");32 DBUG_ENTER("mi_panic");
3333
34 mysql_mutex_lock(&THR_LOCK_myisam);34 mysql_mutex_lock(&THR_LOCK_myisam);
35 for (list_element=myisam_open_list ; list_element ; list_element=next_open)35
36 if (!my_hash_inited(&myisam_open_table_hash))
37 goto finish;
38
39 if (flag == HA_PANIC_CLOSE)
36 {40 {
37 next_open=list_element->next; /* Save if close */41 while (myisam_open_table_hash.records)
38 info=(MI_INFO*) list_element->data;42 {
39 switch (flag) {43 /*
40 case HA_PANIC_CLOSE:44 As long as there are records in the hash, fetch the
45 first, and close it.
46 */
47 info=(MI_INFO*) my_hash_element(&myisam_open_table_hash, 0);
41 mysql_mutex_unlock(&THR_LOCK_myisam); /* Not exactly right... */48 mysql_mutex_unlock(&THR_LOCK_myisam); /* Not exactly right... */
42 if (mi_close(info))49 if (mi_close(info))
43 error=my_errno;50 error= my_errno;
44 mysql_mutex_lock(&THR_LOCK_myisam);51 mysql_mutex_lock(&THR_LOCK_myisam);
45 break;52 }
53 }
54
55 for (idx= 0; idx < myisam_open_table_hash.records; ++idx)
56 {
57 info=(MI_INFO*) my_hash_element(&myisam_open_table_hash, idx);
58 switch (flag) {
59 case HA_PANIC_CLOSE: break;
46 case HA_PANIC_WRITE: /* Do this to free databases */60 case HA_PANIC_WRITE: /* Do this to free databases */
47#ifdef CANT_OPEN_FILES_TWICE61#ifdef CANT_OPEN_FILES_TWICE
48 if (info->s->options & HA_OPTION_READ_ONLY_DATA)62 if (info->s->options & HA_OPTION_READ_ONLY_DATA)
@@ -111,6 +125,8 @@
111 (void) mi_log(0); /* Close log if neaded */125 (void) mi_log(0); /* Close log if neaded */
112 ft_free_stopwords();126 ft_free_stopwords();
113 }127 }
128
129finish:
114 mysql_mutex_unlock(&THR_LOCK_myisam);130 mysql_mutex_unlock(&THR_LOCK_myisam);
115 if (!error)131 if (!error)
116 DBUG_RETURN(0);132 DBUG_RETURN(0);
117133
=== modified file 'Percona-Server/storage/myisam/mi_static.c'
--- Percona-Server/storage/myisam/mi_static.c 2011-12-09 21:08:37 +0000
+++ Percona-Server/storage/myisam/mi_static.c 2013-02-25 04:28:26 +0000
@@ -22,7 +22,7 @@
22#include "myisamdef.h"22#include "myisamdef.h"
23#endif23#endif
2424
25LIST *myisam_open_list=0;25HASH myisam_open_table_hash;
26uchar myisam_file_magic[]=26uchar myisam_file_magic[]=
27{ (uchar) 254, (uchar) 254,'\007', '\001', };27{ (uchar) 254, (uchar) 254,'\007', '\001', };
28uchar myisam_pack_file_magic[]=28uchar myisam_pack_file_magic[]=
2929
=== modified file 'Percona-Server/storage/myisam/myisamchk.c'
--- Percona-Server/storage/myisam/myisamchk.c 2013-01-18 04:53:12 +0000
+++ Percona-Server/storage/myisam/myisamchk.c 2013-02-25 04:28:26 +0000
@@ -89,6 +89,12 @@
89 get_options(&argc,(char***) &argv);89 get_options(&argc,(char***) &argv);
90 myisam_quick_table_bits=decode_bits;90 myisam_quick_table_bits=decode_bits;
91 error=0;91 error=0;
92 if (mi_init_open_table_hash(0))
93 {
94 fprintf(stderr, "Can't initialize MyISAM storage engine\n");
95 exit(-1);
96 }
97
92 while (--argc >= 0)98 while (--argc >= 0)
93 {99 {
94 int new_error=myisamchk(&check_param, *(argv++));100 int new_error=myisamchk(&check_param, *(argv++));
95101
=== modified file 'Percona-Server/storage/myisam/myisamdef.h'
--- Percona-Server/storage/myisam/myisamdef.h 2012-03-06 14:29:42 +0000
+++ Percona-Server/storage/myisam/myisamdef.h 2013-02-25 04:28:26 +0000
@@ -22,6 +22,7 @@
22#include <my_pthread.h>22#include <my_pthread.h>
23#include <thr_lock.h>23#include <thr_lock.h>
24#include <mysql/psi/mysql_file.h>24#include <mysql/psi/mysql_file.h>
25#include "hash.h"
2526
26/* undef map from my_nosys; We need test-if-disk full */27/* undef map from my_nosys; We need test-if-disk full */
27#if defined(my_write)28#if defined(my_write)
@@ -284,7 +285,6 @@
284 uint data_changed; /* Somebody has changed data */285 uint data_changed; /* Somebody has changed data */
285 uint save_update; /* When using KEY_READ */286 uint save_update; /* When using KEY_READ */
286 int save_lastinx;287 int save_lastinx;
287 LIST open_list;
288 IO_CACHE rec_cache; /* When cacheing records */288 IO_CACHE rec_cache; /* When cacheing records */
289 uint preload_buff_size; /* When preloading indexes */289 uint preload_buff_size; /* When preloading indexes */
290 myf lock_wait; /* is 0 or MY_DONT_WAIT */290 myf lock_wait; /* is 0 or MY_DONT_WAIT */
@@ -471,8 +471,7 @@
471#endif471#endif
472472
473 /* Some extern variables */473 /* Some extern variables */
474474extern HASH myisam_open_table_hash;
475extern LIST *myisam_open_list;
476extern uchar myisam_file_magic[], myisam_pack_file_magic[];475extern uchar myisam_file_magic[], myisam_pack_file_magic[];
477extern uint myisam_read_vec[], myisam_readnext_vec[];476extern uint myisam_read_vec[], myisam_readnext_vec[];
478extern uint myisam_quick_table_bits;477extern uint myisam_quick_table_bits;
@@ -754,6 +753,7 @@
754void mi_disable_non_unique_index(MI_INFO *info, ha_rows rows);753void mi_disable_non_unique_index(MI_INFO *info, ha_rows rows);
755754
756extern MI_INFO *test_if_reopen(char *filename);755extern MI_INFO *test_if_reopen(char *filename);
756extern int mi_init_open_table_hash(ulong size);
757my_bool check_table_is_closed(const char *name, const char *where);757my_bool check_table_is_closed(const char *name, const char *where);
758int mi_open_datafile(MI_INFO *info, MYISAM_SHARE *share, const char *orn_name,758int mi_open_datafile(MI_INFO *info, MYISAM_SHARE *share, const char *orn_name,
759 File file_to_dup);759 File file_to_dup);
760760
=== modified file 'Percona-Server/storage/myisam/myisampack.c'
--- Percona-Server/storage/myisam/myisampack.c 2013-02-12 07:47:19 +0000
+++ Percona-Server/storage/myisam/myisampack.c 2013-02-25 04:28:26 +0000
@@ -208,9 +208,12 @@
208 char **default_argv;208 char **default_argv;
209 MY_INIT(argv[0]);209 MY_INIT(argv[0]);
210210
211 if (load_defaults("my",load_default_groups,&argc,&argv))211 if (load_defaults("my",load_default_groups,&argc,&argv) ||
212 mi_init_open_table_hash(0))
213 {
214 fputs("Can't initialize MyISAM storage engine", stderr);
212 exit(1);215 exit(1);
213216 }
214 default_argv= argv;217 default_argv= argv;
215 get_options(&argc,&argv);218 get_options(&argc,&argv);
216219

Subscribers

People subscribed via source and target branches