On Tue, Feb 21, 2017 at 09:41:07PM +0100, Sergei Golubchik wrote:
> Hi, Witold!
>
> On Feb 21, Witold Filipczyk wrote:
> > Hi,
> > I'm newbie, I have problem with INFORMATION_SCHEMA.
> > There is mariadb-10.0 with around 100000 databases. Disks are slow,
> > but most users have one database,
> > a few have more than one, but it is not a big number. Queries like
> > SHOW DATABASES or SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
> > bring to knees whole server.
> > The idea to resolve it is:
> > in find_files(...) instead of the for loop execute query:
> > SELECT REPLACE(TABLE_SCHEMA, '\\', '') FROM
> > INFORMATION_SCHEMA.SCHEMA_PRIVILEGES WHERE PRIVILEGE_TYPE = 'SELECT';
> > There are databases with names with _ in them and \_ in GRANTS.
>
> That's a very interesting idea.
>
> Basically, you're saying that if a user has no probal privileges and
> database privileges on only one database and no wildcards in the
> database name (*), then SELECT * FROM INFORMATION_SCHEMA.SCHEMATA essentially
> has an implicit WHERE schema_name="FOO" clause. Yes, that's certainly
> doable. It's even not very difficult to do, I'd think.
>
> (*) this can be extended to privileges on few (more than one) databases
> and on wildcards in the database names, but let's start from something simple.
>
> > This is theory, but I don't know how to do it. That's mean how to
> > freeze the current query, how to run new query, read results, and fill
> > files in the find_files function and resume the current query.
>
> Kind of. Privileges are stored in memory in lists and hashes. You'd need
> to traverse them to see what databases a user has grants for.
I guess, I did not understand.
I did something like this:
/* sql_show.cc */
static find_files_result
find_files(THD *thd, Dynamic_array<LEX_STRING*> *files, LEX_STRING *db,
const char *path, const LEX_STRING *wild)
{
MY_DIR *dirp;
Discovered_table_list tl(thd, files, wild);
DBUG_ENTER("find_files");
if (!(dirp = my_dir(path, MY_THREAD_SPECIFIC | (db ? 0 : MY_WANT_STAT))))
{
if (my_errno == ENOENT)
my_error(ER_BAD_DB_ERROR, MYF(ME_BELL | ME_WAITTANG), db->str);
else
my_error(ER_CANT_READ_DIR, MYF(ME_BELL | ME_WAITTANG), path, my_errno);
DBUG_RETURN(FIND_FILES_DIR);
}
if (!db) /* Return databases */
{
#ifndef NO_EMBEDDED_ACCESS_CHECKS
char bufor[201];
char *current;
uint counter, i, n;
ACL_DB *acl_db;
char *curr_host= thd->security_ctx->priv_host_name();
mysql_mutex_lock(&acl_cache->lock);
for (counter=0 ; counter < acl_dbs.elements ; counter++)
{
const char *user, *host;
acl_db=dynamic_element(&acl_dbs,counter,ACL_DB*);
user= safe_str(acl_db->user);
host= safe_str(acl_db->host.hostname);
if ((strcmp(thd->security_ctx->priv_user, user) ||
my_strcasecmp(system_charset_info, curr_host, host)))
continue;
if (is_in_ignore_db_dirs_list(acl_db->db))
continue;
for (i = 0, current = bufor; i < 200; ++i)
{
if (acl_db->db[i] == '\\') continue;
if (acl_db->db[i] == '\0') break;
*current++ = acl_db->db[i];
}
*current = '\0';
if (tl.add_file(bufor))
goto err;
}
mysql_mutex_unlock(&acl_cache->lock);
tl.sort();
#endif
}
else
{
if (ha_discover_table_names(thd, db, dirp, &tl, false))
goto err2;
}
DBUG_PRINT("info",("found: %zu files", files->elements()));
my_dirend(dirp);
DBUG_RETURN(FIND_FILES_OK);
err:
mysql_mutex_unlock(&acl_cache->lock);
err2:
my_dirend(dirp);
DBUG_RETURN(FIND_FILES_OOM);
}
And turned on INFORMATION_SCHEMA in phpmyadmin and it is slow, very slow.
What I'm doing wrong here?
How to speed up?
diff -Nru mariadb-10.0-10.0.29.info/sql/sql_acl.cc
mariadb-10.0-10.0.29/sql/sql_acl.cc
--- mariadb-10.0-10.0.29.info/sql/sql_acl.cc 2017-03-05 10:36:10.603234750
+0100
+++ mariadb-10.0-10.0.29/sql/sql_acl.cc 2017-03-05 10:38:17.155530562 +0100
@@ -213,11 +213,6 @@
/* Classes */
-struct acl_host_and_ip
-{
- char *hostname;
- long ip, ip_mask; // Used with masked ip:s
-};
#ifndef NO_EMBEDDED_ACCESS_CHECKS
static bool compare_hostname(const acl_host_and_ip *, const char *, const char
*);
@@ -225,12 +220,6 @@
#define compare_hostname(X,Y,Z) 0
#endif
-class ACL_ACCESS {
-public:
- ulong sort;
- ulong access;
-};
-
/* ACL_HOST is used if no host is specified */
class ACL_HOST :public ACL_ACCESS
@@ -335,14 +324,6 @@
};
-class ACL_DB :public ACL_ACCESS
-{
-public:
- acl_host_and_ip host;
- char *user,*db;
- ulong initial_access; /* access bits present in the table */
-};
-
#ifndef DBUG_OFF
/* status variables, only visible in SHOW STATUS after -#d,role_merge_stats */
ulong role_global_merges= 0, role_db_merges= 0, role_table_merges= 0,
@@ -588,15 +569,6 @@
#define FIRST_NON_YN_FIELD 26
-class acl_entry :public hash_filo_element
-{
-public:
- ulong access;
- uint16 length;
- char key[1]; // Key will be stored here
-};
-
-
static uchar* acl_entry_get_key(acl_entry *entry, size_t *length,
my_bool not_used __attribute__((unused)))
{
@@ -714,7 +686,8 @@
/* Flag to mark that on_node was already called for this role */
#define ROLE_OPENED (1L << 3)
-static DYNAMIC_ARRAY acl_hosts, acl_users, acl_dbs, acl_proxy_users;
+DYNAMIC_ARRAY acl_dbs;
+static DYNAMIC_ARRAY acl_hosts, acl_users, acl_proxy_users;
static HASH acl_roles;
/*
An hash containing mappings user <--> role
@@ -728,7 +701,7 @@
static bool allow_all_hosts=1;
static HASH acl_check_hosts, column_priv_hash, proc_priv_hash, func_priv_hash;
static DYNAMIC_ARRAY acl_wild_hosts;
-static Hash_filo<acl_entry> *acl_cache;
+Hash_filo<acl_entry> *acl_cache;
static uint grant_version=0; /* Version of priv tables. incremented by
acl_load */
static ulong get_access(TABLE *form,uint fieldnr, uint *next_field=0);
static bool check_is_role(TABLE *form);
diff -Nru mariadb-10.0-10.0.29.info/sql/sql_acl.h
mariadb-10.0-10.0.29/sql/sql_acl.h
--- mariadb-10.0-10.0.29.info/sql/sql_acl.h 2017-03-05 10:35:42.863169909
+0100
+++ mariadb-10.0-10.0.29/sql/sql_acl.h 2017-03-05 10:38:21.203540024 +0100
@@ -19,6 +19,7 @@
#include "my_global.h" /* NO_EMBEDDED_ACCESS_CHECKS */
#include "violite.h" /* SSL_type */
#include "sql_class.h" /* LEX_COLUMN */
+#include "hash_filo.h"
#define SELECT_ACL (1L << 0)
#define INSERT_ACL (1L << 1)
@@ -193,6 +194,36 @@
: ER_ACCESS_DENIED_ERROR;
}
+class acl_entry :public hash_filo_element
+{
+public:
+ ulong access;
+ uint16 length;
+ char key[1]; // Key will be stored here
+};
+
+class ACL_ACCESS {
+public:
+ ulong sort;
+ ulong access;
+};
+
+struct acl_host_and_ip
+{
+ char *hostname;
+ long ip, ip_mask; // Used with masked ip:s
+};
+
+class ACL_DB :public ACL_ACCESS
+{
+public:
+ acl_host_and_ip host;
+ char *user,*db;
+ ulong initial_access; /* access bits present in the table */
+};
+
+extern DYNAMIC_ARRAY acl_dbs;
+extern Hash_filo<acl_entry> *acl_cache;
/* prototypes */
diff -Nru mariadb-10.0-10.0.29.info/sql/sql_show.cc
mariadb-10.0-10.0.29/sql/sql_show.cc
--- mariadb-10.0-10.0.29.info/sql/sql_show.cc 2017-03-05 10:36:32.415285735
+0100
+++ mariadb-10.0-10.0.29/sql/sql_show.cc 2017-03-05 10:38:25.451549954
+0100
@@ -59,6 +59,8 @@
#include "debug_sync.h"
#include "keycaches.h"
+#include "hash_filo.h"
+
#ifdef WITH_PARTITION_STORAGE_ENGINE
#include "ha_partition.h"
#endif
@@ -811,6 +813,14 @@
FIND_FILES_DIR
};
+static char *safe_str(char *str)
+{ return str ? str : const_cast<char*>(""); }
+
+static const char *safe_str(const char *str)
+{ return str ? str : ""; }
+
+
+
/*
find_files() - find files in a given directory.
@@ -849,53 +859,62 @@
if (!db) /* Return databases */
{
- for (uint i=0; i < (uint) dirp->number_of_files; i++)
+#ifndef NO_EMBEDDED_ACCESS_CHECKS
+ char bufor[201];
+ char *current;
+ uint counter, i, n;
+ ACL_DB *acl_db;
+ char *curr_host= thd->security_ctx->priv_host_name();
+
+ mysql_mutex_lock(&acl_cache->lock);
+
+ for (counter=0 ; counter < acl_dbs.elements ; counter++)
{
- FILEINFO *file= dirp->dir_entry+i;
-#ifdef USE_SYMDIR
- char *ext;
- char buff[FN_REFLEN];
- if (my_use_symdir && !strcmp(ext=fn_ext(file->name), ".sym"))
- {
- /* Only show the sym file if it points to a directory */
- char *end;
- *ext=0; /* Remove extension */
- unpack_dirname(buff, file->name);
- end= strend(buff);
- if (end != buff && end[-1] == FN_LIBCHAR)
- end[-1]= 0; // Remove end FN_LIBCHAR
- if (!mysql_file_stat(key_file_misc, buff, file->mystat, MYF(0)))
- continue;
- }
-#endif
- if (!MY_S_ISDIR(file->mystat->st_mode))
+ const char *user, *host;
+
+ acl_db=dynamic_element(&acl_dbs,counter,ACL_DB*);
+ user= safe_str(acl_db->user);
+ host= safe_str(acl_db->host.hostname);
+
+ if ((strcmp(thd->security_ctx->priv_user, user) ||
+ my_strcasecmp(system_charset_info, curr_host, host)))
continue;
- if (is_in_ignore_db_dirs_list(file->name))
+ if (is_in_ignore_db_dirs_list(acl_db->db))
continue;
- if (tl.add_file(file->name))
+ for (i = 0, current = bufor; i < 200; ++i)
+ {
+ if (acl_db->db[i] == '\\') continue;
+ if (acl_db->db[i] == '\0') break;
+ *current++ = acl_db->db[i];
+ }
+ *current = '\0';
+
+ if (tl.add_file(bufor))
goto err;
}
+ mysql_mutex_unlock(&acl_cache->lock);
tl.sort();
+#endif
}
else
{
if (ha_discover_table_names(thd, db, dirp, &tl, false))
- goto err;
+ goto err2;
}
-
DBUG_PRINT("info",("found: %zu files", files->elements()));
my_dirend(dirp);
DBUG_RETURN(FIND_FILES_OK);
err:
+ mysql_mutex_unlock(&acl_cache->lock);
+err2:
my_dirend(dirp);
DBUG_RETURN(FIND_FILES_OOM);
}
-
/**
An Internal_error_handler that suppresses errors regarding views'
underlying tables that occur during privilege checking within SHOW CREATE
diff -Nru mariadb-10.0-10.0.29.info/sql/sql_show.h
mariadb-10.0-10.0.29/sql/sql_show.h
--- mariadb-10.0-10.0.29.info/sql/sql_show.h 2017-03-05 10:36:00.199210431
+0100
+++ mariadb-10.0-10.0.29/sql/sql_show.h 2017-03-05 10:38:07.759508600 +0100
@@ -35,6 +35,8 @@
struct TABLE;
typedef struct system_status_var STATUS_VAR;
+class ACL_DB;
+
/* Used by handlers to store things in schema tables */
#define IS_FILES_FILE_ID 0
#define IS_FILES_FILE_NAME 1
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
