How do I multiply data in mySQL database to simulate scale?

Question

Input:

A database consisting of

static tables that do not scale with number of users or time
dynamic tables that grow when users interact with the application (so scale with number of users and time)
a database with real life data for x users

Task:

scale the database to simulate larger number of users

Example:

Tables: 

t_user (scale target)
UserId , Name
1 , John
2, Terry

t_post (dynamic)
AuthorId, PostId, TagId
1, 1 , 1
1, 2 , 2
1, 3 , 2
2, 4 , 1

t_tag (static)
TagId, Name
1, C#
2, Java

Desired output with scale factor = 2

t_user
UserId , Name
1 , John
2, Terry
3 , John
4, Terry

t_post (dynamic)
AuthorId, PostId, TagId
1, 1 , 1
1, 2 , 2
1, 3 , 2
2, 4 , 1
1, 5 , 1
1, 6 , 2
1, 7 , 2
2, 8 , 1

t_tag (static)
TagId, Name
1, C#
2, Java

Ofcourse for such a small database this can be done in MySQL but I need a solution that will work for a database with 150+ tables (writing a scaling routine for each is not a solution) and scale factors that will bring a database form 100 to up to 10 000 users.

Does anyone know a dedicated tool or hack that can accomplish this?

score 0 · Answer 1 · answered Feb 02 '13 at 23:47

0

Benchmark Factory for Databases looks like it might do what you need it to, or you could give the MySQL Benchmark Tool a try.

answered Feb 02 '13 at 23:47

Ian Atkin

6,302
2
17
24

Here's the [User Guide](https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&ved=0CDUQFjAA&url=http%3A%2F%2Fus-downloads.quest.com%2FRepository%2Fsupport.quest.com%2FBenchmark%2520Factory%2520for%2520Database%2F6.5%2FDocumentation%2FBenchmark%2520Factory%2520for%2520Databases%2520User%2520Guide.pdf&ei=qLwNUeeGLpDbigKD9YDYBg&usg=AFQjCNFD35q8fA6-0KHvZTkCI80P_WciBQ&sig2=4ePcuURwaqGjtDY3SAn1Uw&bvm=bv.41867550,d.cGE) – Ian Atkin Feb 03 '13 at 01:29
not very helpful. I was looking for an actual solution to this specific problem, not a link to a user guide. But thanks for trying. I ended up with writing my own script. – JohnnyM Mar 30 '13 at 15:47

score 0 · Accepted Answer · answered Mar 30 '13 at 15:53

I ended up with writing my own script. Below you will find a simplified version (many columns in tables are ommited for clarity). This worked very well. I was able to scale the DB by a factor of 100 quite efficiently. Hope this helps

SET autocommit = 0;

START TRANSACTION;

SET @UMAX = (SELECT MAX(UserID) AS MX FROM t_user);
SET @QSMAX = (SELECT MAX(QuestionSetID) AS MX FROM t_question_set);
SET @QGMAX = (SELECT MAX(QuestionGroupID) AS MX FROM t_question_group);
SET @QMAX = (SELECT MAX(QuestionID) AS MX FROM t_question);
SET @TMAX = (SELECT MAX(TestID) AS MX FROM t_test);


DROP TABLE IF EXISTS t_seq;
CREATE table t_seq AS
    (
    SELECT
        1 S
    );
INSERT INTO t_seq (S) VALUES (2),(3),(4),(5),(6),(7),(8),(9),(10);    


INSERT INTO `t_user`
    (
        `UserID`,
        `Login`,
        `Password`,
    )
SELECT
    `UserID` + 1000000 + @UMAX * t_seq.S,
    concat(if(Login is null, '', Login), `UserID` + 1000000 + @UMAX * t_seq.S),
    `Password`,
FROM t_user,
    t_seq;

INSERT INTO `t_question_set`(`QuestionSetID`) 
SELECT `QuestionSetID` + 1000000 + @QSMAX * t_seq.S 
FROM t_question_set,t_seq;   

INSERT INTO `t_question_group`(
    `QuestionGroupID`, 
    `QuestionSetID`
    )
SELECT 
  `QuestionGroupID` + 1000000 + @QGMAX * t_seq.S,  
  `QuestionSetID` + 1000000 + @QSMAX * t_seq.S, 
FROM t_question_group,t_seq;

INSERT INTO `t_question`(`QuestionID`, `QuestionGroupID`)
SELECT
  `QuestionID` + 1000000 + @QMAX * t_seq.S, 
  `QuestionGroupID` + 1000000 + @QGMAX * t_seq.S, 
FROM t_question, t_seq;

INSERT INTO `t_test`
    (
        `TestID`,
        `QuestionSetID`,
        `UserID`,
            )
SELECT
    `TestID` + 1000000 + @TMAX * t_seq.S,
    `QuestionSetID` + 1000000 + @QSMAX * t_seq.S,
    `UserID` + 1000000 + @UMAX * t_seq.S,
FROM t_test,t_seq;

INSERT INTO `t_question_answer`(
    `QuestionID`, 
    `TestID`
    )
SELECT 
  `QuestionID` + 1000000 + @QMAX * t_seq.S,
  `TestID` + 1000000 + @TMAX * t_seq.S,
FROM t_question_answer,t_seq;

COMMIT;

How do I multiply data in mySQL database to simulate scale?

2 Answers2