#At file:///home/thava/repo/bak/ based on revid:charles.bell@stripped
2916 Thava Alagu 2009-12-16
Bug#49264 BACKUP: Huge slowdown while backup triggers
Significant degrade in performance was observed when lots of
triggers present.
The main culprit of this slowdown is due to executing si_objects
select query on information schema to find the associated table
for the trigger. This query was executed for each trigger.
This was fixed to cache the associated table information in the
trigger object. The fix is mostly based on the suggested solution
approach by Rafal as described in the bug report.
modified:
sql/backup/backup_info.cc
sql/backup/kernel.cc
sql/si_objects.cc
sql/si_objects.h
=== modified file 'sql/backup/backup_info.cc'
--- a/sql/backup/backup_info.cc 2009-12-15 19:23:11 +0000
+++ b/sql/backup/backup_info.cc 2009-12-16 14:08:46 +0000
@@ -1375,12 +1375,11 @@ Backup_info::add_db_object(Db &db, const
*/
if (type == BSTREAM_IT_TRIGGER)
{
- THD *thd= ::current_thd;
- obs::Obj *tbl_obj= obs::find_table_for_trigger(thd, &db.name(),
- obj->get_name());
+ const String *trigger_tab_name= obs::get_trigger_table_name(obj);
+
DBUG_EXECUTE_IF("ER_BACKUP_CATALOG_ADD_TRIGGER_1",
- { delete tbl_obj; tbl_obj= NULL; };);
- if (!tbl_obj)
+ { trigger_tab_name= NULL; };);
+ if (!trigger_tab_name)
{
// TODO: Consider more specific error message.
m_log.report_error(error, db.name().ptr(), obj->get_name()->ptr());
@@ -1397,7 +1396,7 @@ Backup_info::add_db_object(Db &db, const
return NULL;
}
- trg_table= db_found->find_table(*tbl_obj->get_name());
+ trg_table= db_found->find_table(*trigger_tab_name);
DBUG_EXECUTE_IF("ER_BACKUP_CATALOG_ADD_TRIGGER_3", trg_table= NULL;);
if (!trg_table)
=== modified file 'sql/backup/kernel.cc'
--- a/sql/backup/kernel.cc 2009-12-15 19:23:11 +0000
+++ b/sql/backup/kernel.cc 2009-12-16 14:08:46 +0000
@@ -2143,7 +2143,7 @@ int bcat_add_item(st_bstream_image_heade
if (!db)
return BSTREAM_ERROR;
- obs::Obj *obj;
+ obs::Obj *obj= NULL;
int error;
switch (item->type)
@@ -2166,7 +2166,15 @@ int bcat_add_item(st_bstream_image_heade
break;
case BSTREAM_IT_TRIGGER:
error= ER_BACKUP_CATALOG_ADD_TRIGGER;
- obj= obs::get_trigger(&db->name(), &name_str);
+ /**
+ This trigger object is created during restore time.
+ Note that associated table name is just set to NULL.
+ This is acceptable since we don't do any strict reference
+ checking at this point during restore time.
+ We do use the associated table name during backup time for
+ more strict reference checking for table's existence.
+ */
+ obj= obs::get_trigger(&db->name(), &name_str, NULL);
break;
case BSTREAM_IT_PRIVILEGE:
error= ER_BACKUP_CATALOG_ADD_PRIV;
=== modified file 'sql/si_objects.cc'
--- a/sql/si_objects.cc 2009-12-15 20:28:28 +0000
+++ b/sql/si_objects.cc 2009-12-16 14:08:46 +0000
@@ -1099,21 +1099,58 @@ private:
public:
inline Trigger_obj(const Ed_row &ed_row)
- : Stored_routine_obj(ed_row[0], ed_row[1])
+ : Stored_routine_obj(ed_row[0], ed_row[1]),
+ m_lex_table_name(ed_row[2]), m_table_name(0)
{ }
- inline Trigger_obj(LEX_STRING db_name, LEX_STRING trigger_name)
- : Stored_routine_obj(db_name, trigger_name)
+ inline Trigger_obj(LEX_STRING db_name, LEX_STRING trigger_name,
+ LEX_STRING tbl_name)
+ : Stored_routine_obj(db_name, trigger_name),
+ m_lex_table_name(tbl_name), m_table_name(0)
{ }
virtual inline bool is_my_name(const String *name) const
{ return is_my_name_lctn(name); }
+ const String *get_table_name();
+
private:
virtual inline const LEX_STRING *get_type_name() const
{ return &Trigger_obj::TYPE_NAME; }
+
+ /* Contains the associated table name. */
+ LEX_STRING m_lex_table_name;
+
+ /* Initially set to NULL. Initialized from lex_table_name on demand. */
+ String *m_table_name;
};
+
+/**
+ Get the table name associated with the trigger.
+
+ The table name is internally converted to String* and reused on demand
+ basis only. The object's mem_root is used to allocate the string.
+
+ @note Currently this is used only during backup time and not used
+ during restore time.
+ Calling this during restore time, may return NULL.
+*/
+
+const String * Trigger_obj::get_table_name()
+{
+ if (m_table_name)
+ return m_table_name;
+
+ /* Caller should be able to handle NULL return value. */
+ if (m_lex_table_name.str == NULL)
+ return NULL;
+
+ m_table_name= new (&m_mem_root) String(m_lex_table_name.str,
+ m_lex_table_name.length, system_charset_info);
+ return m_table_name;
+}
+
///////////////////////////////////////////////////////////////////////////
const LEX_STRING Trigger_obj::TYPE_NAME= LXS_INIT("TRIGGER");
@@ -2741,7 +2778,7 @@ Obj_iterator *get_db_triggers(THD *thd,
{
String_stream s_stream;
s_stream <<
- "SELECT '" << db_name << "', trigger_name "
+ "SELECT '" << db_name << "', trigger_name, event_object_table "
"FROM INFORMATION_SCHEMA.TRIGGERS "
"WHERE trigger_schema COLLATE utf8_bin = '" << db_name << "'";
@@ -2903,9 +2940,20 @@ Obj *get_view(const String *db_name, con
///////////////////////////////////////////////////////////////////////////
-Obj *get_trigger(const String *db_name, const String *trigger_name)
+Obj *get_trigger(const String *db_name, const String *trigger_name,
+ const String *table_name)
{
- return new Trigger_obj(db_name->lex_string(), trigger_name->lex_string());
+ /* Note: we accept that the table_name may be NULL when this
+ instance is created during restore time.
+ However during backup time we use the associated table name
+ to do more strict checking.
+ */
+ LEX_STRING lex_table_name= { (char*) NULL, 0 };
+ if (table_name)
+ lex_table_name = table_name->lex_string();
+
+ return new Trigger_obj(db_name->lex_string(), trigger_name->lex_string(),
+ lex_table_name);
}
///////////////////////////////////////////////////////////////////////////
@@ -3142,6 +3190,34 @@ Obj *find_tablespace_for_table(THD *thd,
return new Tablespace_obj(*ts_name, *comment, *data_file_name, *engine);
}
+
+/**
+ Retrieve the associated table name for a given trigger object.
+
+ This method returns table name of the trigger.
+
+ @note Currently this is used only during backup time and not used
+ during restore time.
+ Calling this during restore time, may return NULL.
+
+ @param[in] trigger trigger object
+
+ @return String* associated table name for the trigger
+*/
+
+const String *get_trigger_table_name(Obj *obj)
+{
+ if (obj == NULL)
+ return NULL;
+
+ Trigger_obj *trigger= static_cast<Trigger_obj*>(obj);
+
+ if (trigger == NULL)
+ return NULL;
+
+ return trigger->get_table_name();
+}
+
///////////////////////////////////////////////////////////////////////////
/**
=== modified file 'sql/si_objects.h'
--- a/sql/si_objects.h 2009-12-15 19:23:11 +0000
+++ b/sql/si_objects.h 2009-12-16 14:08:46 +0000
@@ -330,7 +330,8 @@ Obj_iterator* get_view_base_views(THD *t
Obj *get_database(const String *db_name);
Obj *get_table(const String *db_name, const String *table_name);
Obj *get_view(const String *db_name, const String *view_name);
-Obj *get_trigger(const String *db_name, const String *trigger_name);
+Obj *get_trigger(const String *db_name, const String *trigger_name,
+ const String *table_name);
Obj *get_stored_procedure(const String *db_name, const String *sproc_name);
Obj *get_stored_function(const String *db_name, const String *sfunc_name);
Obj *get_event(const String *db_name, const String *event_name);
@@ -392,6 +393,11 @@ Obj *find_tablespace_for_table(THD *thd,
const String *table_name);
/**
+ Return associated table name of trigger object.
+*/
+const String *get_trigger_table_name(Obj *obj);
+
+/**
This method returns the table name for a given trigger.
*/
Obj *find_table_for_trigger(THD *thd,
Attachment: [text/bzr-bundle]
