If you have setup your MySQL cluster or standalone database server correctly, you might need to do a stress test to the server. You can use many ways to achieve this and in my case, I will use the simplest way which is create generate a fake data and insert them into table using stored procedure. Actually, you can use any other script like BASH, Perl or PHP as long as it support looping.
We need to loop certain SQL command in order to generate more and more data. Before we start, make sure you have prepare the database and table to be insert into. Following informations are variable that I used:
MySQL: Version 5.5.20 Community Server
Firstly, we need create the database and table. Log into MySQL console or client via appropriate database user (in this case I will just use root), and execute following MySQL command:
CREATE DATABASE dbTest; USE dbTest; CREATE TABLE `tbl_data1` ( `a` INT(10) NULL, `b` INT(10) NULL, `c` INT(10) NULL ) ENGINE=InnoDB;
Our aim is to insert 100,000 rows random number between 0 to 1000 into the table.
DELIMITER // CREATE PROCEDURE GenerateFakeData() BEGIN DECLARE i INT DEFAULT 1; WHILE i <= 100000 DO INSERT INTO tbl_data1 (a,b,c) VALUES ((SELECT floor(rand() * 1000) AS randNum), (SELECT floor(rand() * 1000) AS randNum),(SELECT floor(rand() * 1000) AS randNum)); SET i = i + 1; END WHILE; END // DELIMITER ;
To call the procedure above, execute following command:
USE dbTest; CALL GenerateFakeData();
This will execute the stored procedure and the table will contains 100,000 rows of data.
SELECT * FROM dbTest.tbl_data1 LIMIT 1,10;
+------+------+------+ | a | b | c | +------+------+------+ | 521 | 662 | 748 | | 753 | 523 | 356 | | 212 | 994 | 334 | | 689 | 441 | 139 | | 374 | 453 | 145 | | 364 | 386 | 837 | | 30 | 637 | 96 | | 571 | 569 | 129 | | 940 | 312 | 741 | | 770 | 628 | 828 | +------+------+------+ 10 rows in set (0.00 sec)
You can start to play around with this table like do some heavy queries, play with indexes and much more.
- Install MySQL Cluster in Debian
- Customize and Disable PHPmyAdmin ‘Export’ Menu
- High Availability: cPanel with MySQL Cluster, Keepalived and HAProxy
- Monitor MySQL Galera Cluster from Split-Brain
- CentOS 6: Install MySQL Cluster – The Simple Way
- High Availability: Configure Piranha for HTTP, HTTPS and MySQL
- CentOS: Integrate ClusterControl into Existing MySQL Galera Cluster
- Easiest Way to Install A Complete MySQL Galera Cluster
- MySQL – Recover Data Using mysqlbinlog
- CentOS: Install Percona XtraDB Cluster
- Report: Iran behind wave of cyber attacks on US companies - Jerusalem Post 25 May 2013
- When the trio came calling - Daily News & Analysis 25 May 2013
- Samsung takes on Nokia Asha, launches Galaxy Star at Rs 5240 - Business Today 25 May 2013
- Eric Schmidt inteview: 'You have to fight for your privacy or you will lose it' - Telegraph.co.uk 25 May 2013
- 2014 launch predicted for Apple's 'iWatch' - Sin Chew Jit Poh 25 May 2013