MariaDB server with 80K-100K DataBases
BACKGROUND :
We have an environment where there is a new DB created almost every minute and it will be Dropped after few days or weeks (maximum 1 month) such that the space used is about 300GB~400GB in /var/lib/mysql/ which is 500GB size.
We are using MariaDB 5.3.5 (FIXED, no chance of upgrade) on Centos6 VM.
Problem :
When the mysql volume is 90% used with 85K DataBases, creation of new Databases is very slow, and DB Dropping is also very slow.
Questions :
Is 85K DataBases too much for MariaDB 5.3.5 ?
What is the Documented safe limit ?
How do I make DB creation not get blocked by DB Deletion ?
What could be the real culprit for slowness when there are too many DataBases ?
What Parameters can I enable on the server to track the slowness ?
Details :
Each DB will have 12 tables. Each table will have 10~100 rows.
Each DB will be around 3MB~5MB.
We are using LVM, with / mounted as ext4.
MariaDB 5.3.5 is running on Centos6.6 VM with Linux Kernel 2.6.32-504.3.3.el6.x86_64.
VM has 4 cpus (3GHz) with 32GB RAM, hosted on VMWare ESX 5.5 running on a Cisco UCS Blade.
mysql performance optimization mariadb virtualisation
asked Sep 4 '15 at 10:23
PremPrem
1186
bumped to the homepage by Community♦ 10 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
|
show 3 more comments
BACKGROUND :
We have an environment where there is a new DB created almost every minute and it will be Dropped after few days or weeks (maximum 1 month) such that the space used is about 300GB~400GB in /var/lib/mysql/ which is 500GB size.
We are using MariaDB 5.3.5 (FIXED, no chance of upgrade) on Centos6 VM.
Problem :
When the mysql volume is 90% used with 85K DataBases, creation of new Databases is very slow, and DB Dropping is also very slow.
Questions :
Is 85K DataBases too much for MariaDB 5.3.5 ?
What is the Documented safe limit ?
How do I make DB creation not get blocked by DB Deletion ?
What could be the real culprit for slowness when there are too many DataBases ?
What Parameters can I enable on the server to track the slowness ?
Details :
Each DB will have 12 tables. Each table will have 10~100 rows.
Each DB will be around 3MB~5MB.
We are using LVM, with / mounted as ext4.
MariaDB 5.3.5 is running on Centos6.6 VM with Linux Kernel 2.6.32-504.3.3.el6.x86_64.
VM has 4 cpus (3GHz) with 32GB RAM, hosted on VMWare ESX 5.5 running on a Cisco UCS Blade.
mysql performance optimization mariadb virtualisation
asked Sep 4 '15 at 10:23
PremPrem
1186
bumped to the homepage by Community♦ 10 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
2
What filesystem? How many tables per database? Engine used? if Innodb, what is your value of innodb_files_per_table? Do you have filesystem contention or innodb buffer pool mutex contention (have you done basic profiling)?
– jynus
Sep 4 '15 at 10:31
2
85K databases means 85K subdirectories, most filesystems are not optimized for such things.
– jkavalik
Sep 4 '15 at 10:50
1
@jkavalik , great point. Implying that it might not be MariaDB issue, but FS issue.
– Prem
Sep 4 '15 at 10:57
1
Independently of your particular problem can be fixed, are you aware that creating 12 tables per minute (and consequentially, deleting 12 tables per minute) is a horrible design? MySQL/MariaDB are optimized for fast SELECT/UPDATES, not CREATE TABLE/DROP TABLE. If you have external factors/agents, please consider rewriting your queries with a proxy or MySQL rewriting plugin to be able to use @jkavalik model.
– jynus
Sep 4 '15 at 11:13
1
@jynus , I will pass on this particular comment to the appropriate parties, though I have no say in whether they accept it or not !!
– Prem
Sep 4 '15 at 11:19
|
show 3 more comments
BACKGROUND :
We have an environment where there is a new DB created almost every minute and it will be Dropped after few days or weeks (maximum 1 month) such that the space used is about 300GB~400GB in /var/lib/mysql/ which is 500GB size.
We are using MariaDB 5.3.5 (FIXED, no chance of upgrade) on Centos6 VM.
Problem :
When the mysql volume is 90% used with 85K DataBases, creation of new Databases is very slow, and DB Dropping is also very slow.
Questions :
Is 85K DataBases too much for MariaDB 5.3.5 ?
What is the Documented safe limit ?
How do I make DB creation not get blocked by DB Deletion ?
What could be the real culprit for slowness when there are too many DataBases ?
What Parameters can I enable on the server to track the slowness ?
Details :
Each DB will have 12 tables. Each table will have 10~100 rows.
Each DB will be around 3MB~5MB.
We are using LVM, with / mounted as ext4.
MariaDB 5.3.5 is running on Centos6.6 VM with Linux Kernel 2.6.32-504.3.3.el6.x86_64.
VM has 4 cpus (3GHz) with 32GB RAM, hosted on VMWare ESX 5.5 running on a Cisco UCS Blade.
mysql performance optimization mariadb virtualisation
asked Sep 4 '15 at 10:23
PremPrem
1186
BACKGROUND :
We have an environment where there is a new DB created almost every minute and it will be Dropped after few days or weeks (maximum 1 month) such that the space used is about 300GB~400GB in /var/lib/mysql/ which is 500GB size.
We are using MariaDB 5.3.5 (FIXED, no chance of upgrade) on Centos6 VM.
Problem :
When the mysql volume is 90% used with 85K DataBases, creation of new Databases is very slow, and DB Dropping is also very slow.
Questions :
Is 85K DataBases too much for MariaDB 5.3.5 ?
What is the Documented safe limit ?
How do I make DB creation not get blocked by DB Deletion ?
What could be the real culprit for slowness when there are too many DataBases ?
What Parameters can I enable on the server to track the slowness ?
Details :
Each DB will have 12 tables. Each table will have 10~100 rows.
Each DB will be around 3MB~5MB.
We are using LVM, with / mounted as ext4.
MariaDB 5.3.5 is running on Centos6.6 VM with Linux Kernel 2.6.32-504.3.3.el6.x86_64.
VM has 4 cpus (3GHz) with 32GB RAM, hosted on VMWare ESX 5.5 running on a Cisco UCS Blade.
mysql performance optimization mariadb virtualisation
mysql performance optimization mariadb virtualisation
asked Sep 4 '15 at 10:23
PremPrem
1186
asked Sep 4 '15 at 10:23
PremPrem
1186
edited Sep 4 '15 at 11:05
Prem
asked Sep 4 '15 at 10:23
PremPrem
1186
asked Sep 4 '15 at 10:23
PremPrem
1186
asked Sep 4 '15 at 10:23
PremPrem
1186
1186
bumped to the homepage by Community♦ 10 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 10 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
2
What filesystem? How many tables per database? Engine used? if Innodb, what is your value of innodb_files_per_table? Do you have filesystem contention or innodb buffer pool mutex contention (have you done basic profiling)?
– jynus
Sep 4 '15 at 10:31
2
85K databases means 85K subdirectories, most filesystems are not optimized for such things.
– jkavalik
Sep 4 '15 at 10:50
1
@jkavalik , great point. Implying that it might not be MariaDB issue, but FS issue.
– Prem
Sep 4 '15 at 10:57
1
Independently of your particular problem can be fixed, are you aware that creating 12 tables per minute (and consequentially, deleting 12 tables per minute) is a horrible design? MySQL/MariaDB are optimized for fast SELECT/UPDATES, not CREATE TABLE/DROP TABLE. If you have external factors/agents, please consider rewriting your queries with a proxy or MySQL rewriting plugin to be able to use @jkavalik model.
– jynus
Sep 4 '15 at 11:13
1
@jynus , I will pass on this particular comment to the appropriate parties, though I have no say in whether they accept it or not !!
– Prem
Sep 4 '15 at 11:19
|
show 3 more comments
2
What filesystem? How many tables per database? Engine used? if Innodb, what is your value of innodb_files_per_table? Do you have filesystem contention or innodb buffer pool mutex contention (have you done basic profiling)?
– jynus
Sep 4 '15 at 10:31
2
85K databases means 85K subdirectories, most filesystems are not optimized for such things.
– jkavalik
Sep 4 '15 at 10:50
1
@jkavalik , great point. Implying that it might not be MariaDB issue, but FS issue.
– Prem
Sep 4 '15 at 10:57
1
Independently of your particular problem can be fixed, are you aware that creating 12 tables per minute (and consequentially, deleting 12 tables per minute) is a horrible design? MySQL/MariaDB are optimized for fast SELECT/UPDATES, not CREATE TABLE/DROP TABLE. If you have external factors/agents, please consider rewriting your queries with a proxy or MySQL rewriting plugin to be able to use @jkavalik model.
– jynus
Sep 4 '15 at 11:13
1
@jynus , I will pass on this particular comment to the appropriate parties, though I have no say in whether they accept it or not !!
– Prem
Sep 4 '15 at 11:19
2
2
What filesystem? How many tables per database? Engine used? if Innodb, what is your value of innodb_files_per_table? Do you have filesystem contention or innodb buffer pool mutex contention (have you done basic profiling)?
– jynus
Sep 4 '15 at 10:31
What filesystem? How many tables per database? Engine used? if Innodb, what is your value of innodb_files_per_table? Do you have filesystem contention or innodb buffer pool mutex contention (have you done basic profiling)?
– jynus
Sep 4 '15 at 10:31
2
2
85K databases means 85K subdirectories, most filesystems are not optimized for such things.
– jkavalik
Sep 4 '15 at 10:50
85K databases means 85K subdirectories, most filesystems are not optimized for such things.
– jkavalik
Sep 4 '15 at 10:50
1
1
@jkavalik , great point. Implying that it might not be MariaDB issue, but FS issue.
– Prem
Sep 4 '15 at 10:57
@jkavalik , great point. Implying that it might not be MariaDB issue, but FS issue.
– Prem
Sep 4 '15 at 10:57
1
1
Independently of your particular problem can be fixed, are you aware that creating 12 tables per minute (and consequentially, deleting 12 tables per minute) is a horrible design? MySQL/MariaDB are optimized for fast SELECT/UPDATES, not CREATE TABLE/DROP TABLE. If you have external factors/agents, please consider rewriting your queries with a proxy or MySQL rewriting plugin to be able to use @jkavalik model.
– jynus
Sep 4 '15 at 11:13
Independently of your particular problem can be fixed, are you aware that creating 12 tables per minute (and consequentially, deleting 12 tables per minute) is a horrible design? MySQL/MariaDB are optimized for fast SELECT/UPDATES, not CREATE TABLE/DROP TABLE. If you have external factors/agents, please consider rewriting your queries with a proxy or MySQL rewriting plugin to be able to use @jkavalik model.
– jynus
Sep 4 '15 at 11:13
1
1
@jynus , I will pass on this particular comment to the appropriate parties, though I have no say in whether they accept it or not !!
– Prem
Sep 4 '15 at 11:19
@jynus , I will pass on this particular comment to the appropriate parties, though I have no say in whether they accept it or not !!
– Prem
Sep 4 '15 at 11:19
|
show 3 more comments
2 Answers
2
active
oldest
votes
If i understand you can create up to 85,000 databases? Hmm, I would agree with one of the post, not the best approach. The only issue i can think of if "information_schema" is not able to keep up with the quantity of databases.
What's your key_buffer_size?
What's your open file limit? Table_cache?
Honestly, you'd be better off approaching this issue differently. Instead of creating that many databases with few tables and few rows, it would be better merge all these database in few databases / tables.
If you must separate the schema, (DBs), i would spawn multiple Mysql server and have a round robin system to create the db on each of them.
(MySQL has no limit on the number of databases. The underlying file system may have a limit on the number of directories.)
answered Sep 4 '15 at 16:24
greenlitmysqlgreenlitmysql
32613
add a comment |
As already stated, the filesystem is the problem. However, there may be some tuning that would help.
If innodb_file_per_table has been ON, the there are 2*12 files in each database directory. Turning that OFF would lead to fewer files (but not fewer directories). This might help some.
SHOW VARIABLES LIKE 'table%';
SHOW VARIABLES LIKE 'innodb%files';
SHOW VARIABLES LIKE 'open%';
SHOW GLOBAL STATUS LIKE 'Opened%';
SHOW GLOBAL STATUS LIKE 'Uptime%';
SHOW GLOBAL STATUS LIKE 'Table%';
There is some info that can be gathered from those outputs.
Or, give me (1) amount of RAM, (2) SHOW VARIABLES;, and (3) SHOW GLOBAL STATUS;. I will run a number of checks to see if the table_open_cache is too small, and other things.
answered Sep 4 '15 at 16:43
Rick JamesRick James
42.2k22258
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f114195%2fmariadb-server-with-80k-100k-databases%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
If i understand you can create up to 85,000 databases? Hmm, I would agree with one of the post, not the best approach. The only issue i can think of if "information_schema" is not able to keep up with the quantity of databases.
What's your key_buffer_size?
What's your open file limit? Table_cache?
Honestly, you'd be better off approaching this issue differently. Instead of creating that many databases with few tables and few rows, it would be better merge all these database in few databases / tables.
If you must separate the schema, (DBs), i would spawn multiple Mysql server and have a round robin system to create the db on each of them.
(MySQL has no limit on the number of databases. The underlying file system may have a limit on the number of directories.)
answered Sep 4 '15 at 16:24
greenlitmysqlgreenlitmysql
32613
add a comment |
If i understand you can create up to 85,000 databases? Hmm, I would agree with one of the post, not the best approach. The only issue i can think of if "information_schema" is not able to keep up with the quantity of databases.
What's your key_buffer_size?
What's your open file limit? Table_cache?
Honestly, you'd be better off approaching this issue differently. Instead of creating that many databases with few tables and few rows, it would be better merge all these database in few databases / tables.
If you must separate the schema, (DBs), i would spawn multiple Mysql server and have a round robin system to create the db on each of them.
(MySQL has no limit on the number of databases. The underlying file system may have a limit on the number of directories.)
answered Sep 4 '15 at 16:24
greenlitmysqlgreenlitmysql
32613
add a comment |
If i understand you can create up to 85,000 databases? Hmm, I would agree with one of the post, not the best approach. The only issue i can think of if "information_schema" is not able to keep up with the quantity of databases.
What's your key_buffer_size?
What's your open file limit? Table_cache?
Honestly, you'd be better off approaching this issue differently. Instead of creating that many databases with few tables and few rows, it would be better merge all these database in few databases / tables.
If you must separate the schema, (DBs), i would spawn multiple Mysql server and have a round robin system to create the db on each of them.
(MySQL has no limit on the number of databases. The underlying file system may have a limit on the number of directories.)
answered Sep 4 '15 at 16:24
greenlitmysqlgreenlitmysql
32613
If i understand you can create up to 85,000 databases? Hmm, I would agree with one of the post, not the best approach. The only issue i can think of if "information_schema" is not able to keep up with the quantity of databases.
What's your key_buffer_size?
What's your open file limit? Table_cache?
Honestly, you'd be better off approaching this issue differently. Instead of creating that many databases with few tables and few rows, it would be better merge all these database in few databases / tables.
If you must separate the schema, (DBs), i would spawn multiple Mysql server and have a round robin system to create the db on each of them.
(MySQL has no limit on the number of databases. The underlying file system may have a limit on the number of directories.)
answered Sep 4 '15 at 16:24
greenlitmysqlgreenlitmysql
32613
answered Sep 4 '15 at 16:24
greenlitmysqlgreenlitmysql
32613
answered Sep 4 '15 at 16:24
greenlitmysqlgreenlitmysql
32613
answered Sep 4 '15 at 16:24
greenlitmysqlgreenlitmysql
32613
32613
add a comment |
add a comment |
As already stated, the filesystem is the problem. However, there may be some tuning that would help.
If innodb_file_per_table has been ON, the there are 2*12 files in each database directory. Turning that OFF would lead to fewer files (but not fewer directories). This might help some.
SHOW VARIABLES LIKE 'table%';
SHOW VARIABLES LIKE 'innodb%files';
SHOW VARIABLES LIKE 'open%';
SHOW GLOBAL STATUS LIKE 'Opened%';
SHOW GLOBAL STATUS LIKE 'Uptime%';
SHOW GLOBAL STATUS LIKE 'Table%';
There is some info that can be gathered from those outputs.
Or, give me (1) amount of RAM, (2) SHOW VARIABLES;, and (3) SHOW GLOBAL STATUS;. I will run a number of checks to see if the table_open_cache is too small, and other things.
answered Sep 4 '15 at 16:43
Rick JamesRick James
42.2k22258
add a comment |
As already stated, the filesystem is the problem. However, there may be some tuning that would help.
If innodb_file_per_table has been ON, the there are 2*12 files in each database directory. Turning that OFF would lead to fewer files (but not fewer directories). This might help some.
SHOW VARIABLES LIKE 'table%';
SHOW VARIABLES LIKE 'innodb%files';
SHOW VARIABLES LIKE 'open%';
SHOW GLOBAL STATUS LIKE 'Opened%';
SHOW GLOBAL STATUS LIKE 'Uptime%';
SHOW GLOBAL STATUS LIKE 'Table%';
There is some info that can be gathered from those outputs.
Or, give me (1) amount of RAM, (2) SHOW VARIABLES;, and (3) SHOW GLOBAL STATUS;. I will run a number of checks to see if the table_open_cache is too small, and other things.
answered Sep 4 '15 at 16:43
Rick JamesRick James
42.2k22258
add a comment |
As already stated, the filesystem is the problem. However, there may be some tuning that would help.
If innodb_file_per_table has been ON, the there are 2*12 files in each database directory. Turning that OFF would lead to fewer files (but not fewer directories). This might help some.
SHOW VARIABLES LIKE 'table%';
SHOW VARIABLES LIKE 'innodb%files';
SHOW VARIABLES LIKE 'open%';
SHOW GLOBAL STATUS LIKE 'Opened%';
SHOW GLOBAL STATUS LIKE 'Uptime%';
SHOW GLOBAL STATUS LIKE 'Table%';
There is some info that can be gathered from those outputs.
Or, give me (1) amount of RAM, (2) SHOW VARIABLES;, and (3) SHOW GLOBAL STATUS;. I will run a number of checks to see if the table_open_cache is too small, and other things.
answered Sep 4 '15 at 16:43
Rick JamesRick James
42.2k22258
As already stated, the filesystem is the problem. However, there may be some tuning that would help.
If innodb_file_per_table has been ON, the there are 2*12 files in each database directory. Turning that OFF would lead to fewer files (but not fewer directories). This might help some.
SHOW VARIABLES LIKE 'table%';
SHOW VARIABLES LIKE 'innodb%files';
SHOW VARIABLES LIKE 'open%';
SHOW GLOBAL STATUS LIKE 'Opened%';
SHOW GLOBAL STATUS LIKE 'Uptime%';
SHOW GLOBAL STATUS LIKE 'Table%';
There is some info that can be gathered from those outputs.
Or, give me (1) amount of RAM, (2) SHOW VARIABLES;, and (3) SHOW GLOBAL STATUS;. I will run a number of checks to see if the table_open_cache is too small, and other things.
answered Sep 4 '15 at 16:43
Rick JamesRick James
42.2k22258
answered Sep 4 '15 at 16:43
Rick JamesRick James
42.2k22258
answered Sep 4 '15 at 16:43
Rick JamesRick James
42.2k22258
answered Sep 4 '15 at 16:43
Rick JamesRick James
42.2k22258
42.2k22258
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f114195%2fmariadb-server-with-80k-100k-databases%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
What filesystem? How many tables per database? Engine used? if Innodb, what is your value of innodb_files_per_table? Do you have filesystem contention or innodb buffer pool mutex contention (have you done basic profiling)?
– jynus
Sep 4 '15 at 10:31
2
85K databases means 85K subdirectories, most filesystems are not optimized for such things.
– jkavalik
Sep 4 '15 at 10:50
1
@jkavalik , great point. Implying that it might not be MariaDB issue, but FS issue.
– Prem
Sep 4 '15 at 10:57
1
Independently of your particular problem can be fixed, are you aware that creating 12 tables per minute (and consequentially, deleting 12 tables per minute) is a horrible design? MySQL/MariaDB are optimized for fast SELECT/UPDATES, not CREATE TABLE/DROP TABLE. If you have external factors/agents, please consider rewriting your queries with a proxy or MySQL rewriting plugin to be able to use @jkavalik model.
– jynus
Sep 4 '15 at 11:13
1
@jynus , I will pass on this particular comment to the appropriate parties, though I have no say in whether they accept it or not !!
– Prem
Sep 4 '15 at 11:19