MySQL Forums
Forum List  »  Quality Assurance

My MySQL DB is unable to process 4+ requests at a time!
Posted by: Mahesh V S
Date: January 16, 2012 05:10AM

Hi,

Yesterday I was in the process of convincing one of my clients to use MySQL than SQL Server. I had a long fight with the technical team there and I was told to prove the performance of MySQL to them when there are more than 50 users do some reporting action.


So I defined a sample database with two master tables and one transaction table; and wrote a command line application (in C#) which will do 'n' requests to the server at the sametime (using threading). But when I did the test from the application, it failed! :(

I have to demo them a db that works with 5000000 rows in the txn table with 50 users accessing it in a given point of time.


I tried to call the proc "fetch_all_failed_students_with_subjectname" from my application from 20 threads. It failed! It failed even when I called 5 threads!

Then I removed most of the rows and tried it with 500000. It worked with 15 threads! but failed with 20 threads.

I still believe this will be some configuration change; I still hope in MySQL. Please share your thoughts. I am don't care about losing this client; but is very much care about my confidence in MySQL.

My demo system is 64 bit with 3GB RAM running in Windows Server 2008.

Here is the script for the sample DB
------------------------------------------------------------
delimiter $$

drop table if exists student_subject_mapping$$
drop table if exists student$$
drop table if exists subjects$$

drop procedure if exists fetch_all_failed_students_with_subjectname$$
drop procedure if exists insertSubjects$$
drop procedure if exists insertstudents$$

CREATE TABLE `student` (
`StudentId` int(11) NOT NULL AUTO_INCREMENT,
`FirstName` varchar(50) NOT NULL,
`LastName` varchar(50) DEFAULT NULL,
`RollNumber` int(11) DEFAULT NULL,
PRIMARY KEY (`StudentId`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1$$

CREATE TABLE `subjects` (
`SubjectId` int(11) NOT NULL AUTO_INCREMENT,
`Name` varchar(50) NOT NULL,
PRIMARY KEY (`SubjectId`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1$$


CREATE TABLE `student_subject_mapping` (
`mappingid` int(11) NOT NULL AUTO_INCREMENT,
`studentid` int(11) NOT NULL,
`subjectid` int(11) NOT NULL,
`status` int(11) DEFAULT NULL,
`remarks` varchar(1000) DEFAULT NULL,
`mark` int(11) DEFAULT NULL,
PRIMARY KEY (`mappingid`),
KEY `fk_student_student_sub_mapping` (`studentid`),
KEY `fk_subject_student_sub_mapping` (`subjectid`),
CONSTRAINT `fk_student_student_sub_mapping` FOREIGN KEY (`studentid`) REFERENCES `student` (`StudentId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_subject_student_sub_mapping` FOREIGN KEY (`subjectid`) REFERENCES `subjects` (`SubjectId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1$$


CREATE DEFINER=`root`@`%` PROCEDURE `insertstudents`()
BEGIN
declare vi int;
set vi = 1;

while (vi < 20000) do

INSERT INTO `student`
(`FirstName`,
`LastName`,
`RollNumber`)
VALUES
(
concat('first name ', cast(vi as char) ),
concat('last name ', cast(vi as char) ),
vi
);

set vi = vi + 1;

end while;

END$$

CREATE DEFINER=`root`@`%` PROCEDURE `insertSubjects`()
BEGIN
declare vi int;
set vi = 1;

while (vi < 200) do

INSERT INTO subjects
(`Name`)
VALUES
(
concat('subject ', cast(vi as char) )
);

set vi = vi + 1;


end while;

END$$

CREATE DEFINER=`root`@`%` PROCEDURE `fetch_all_failed_students_with_subjectname`()
BEGIN

select st.*, sb.name `subject name`, ssm.mark, ssm.remarks from student st
inner join student_subject_mapping ssm
on st.studentid = ssm.studentid and ssm.status=2
inner join subjects sb on ssm.subjectid = sb.subjectid
;
END$$

call insertstudents()$$

call insertSubjects()$$

INSERT INTO `student_subject_mapping`(`studentid`,`subjectid`) select studentid, subjectid from student join subjects $$

update student_subject_mapping set status = 2 , remarks = 'Fail', mark = 30 - FLOOR(1 + RAND() * 10) where studentid % subjectid = 0$$

update student_subject_mapping set status = 1 , remarks = 'Success', mark = 30 + FLOOR(10 + RAND() * 20) where studentid % subjectid <> 0 $$


Thanks

Options: ReplyQuote


Subject
Views
Written By
Posted
My MySQL DB is unable to process 4+ requests at a time!
2224
January 16, 2012 05:10AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.