MySQL Bugs: #42898: Slow mysql response time

Bug #42898	Slow mysql response time
Submitted:	16 Feb 2009 18:25	Modified:	22 Mar 2009 9:17
Reporter:	Iswarya Devi	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.67	OS:	Linux (Red Hat ES 4 Update 5)
Assigned to:		CPU Architecture:	Any
Tags:	Slow mysql response time

Description:
Hi Mysql team,

The mysql version we are using is 5.0.67, under linux Red Hat ES 4 Update 5. We are using three databases, with many tables, that includes millions of records. Now, our application is an IVR system, wherein depending on the no of callers, we do a lot of database select queries and fetch records corresponding to a caller. Now, when the no of simultaneos queries is less, there is no problem. But, when the no of simultaneos connections and select queries increase, we find a lot of delay in the response time from mysql. They are simple select queries like "select lang_option from language where mobile_no='XXX'". All the tables are properly indexed. Thanks in advance.

Thanks & Regards,
Iswarya

How to repeat:
Executing large no of simultaneous queries result in slow mysql response time

Thank you for the problem report. Please, send the results of

SHOW CREATE TABLE language\G

The output for the command SHOW CREATE TABLE language is:

[root@localhost ISS_2]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.67-community

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use airtel;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW CREATE TABLE language;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                 |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| language | CREATE TABLE `language` (
  `caller_id` varchar(50) default NULL,
  `lang_option` int(10) default NULL,
  KEY `test_index` (`caller_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> exit
Bye
[root@localhost ISS_2]# 

Thanks & Regards,
Iswarya

So, this is MyISAM table. Please, get the results of

show full processlist;

next time you will have slow response time.

I have given the requested file. Can you plz give a solution as soon as possible...

There are no queries at all besides SHOW PROCESSLIST itself in your results. So, looks like it was executed at some wrong moment of time. 

Please, send also your my.cnf.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".