performance - Mysql Query taking more time than expected - Stack Overflow

admin管理员组

文章数量:1405347

I have a simple MYSQL table containing a varchar column as PRIMARY KEY. Now, I'm querying on that column and the query is taking ~4 secs.

select entity_name from entity where entity_name in ('sunshine-int-001', 'sunshine-int-002');

When I run this query from mysql client it takes some milliseconds. But when there is some load on the server it takes more time.

Table

CREATE TABLE `entity` (
  `entity_name` varchar(400) NOT NULL,
  `entity_detail` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`entity_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

I have a simple MYSQL table containing a varchar column as PRIMARY KEY. Now, I'm querying on that column and the query is taking ~4 secs.

select entity_name from entity where entity_name in ('sunshine-int-001', 'sunshine-int-002');

When I run this query from mysql client it takes some milliseconds. But when there is some load on the server it takes more time.

Table

CREATE TABLE `entity` (
  `entity_name` varchar(400) NOT NULL,
  `entity_detail` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`entity_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

• mysql
• performance
• indexing
• innodb

Share Improve this question Follow edited Mar 8 at 13:47 Progman 19.7k77 gold badges5555 silver badges8282 bronze badges asked Mar 8 at 13:26 user785461user785461 8111 gold badge22 silver badges77 bronze badges 6

• 1 Welcome to Stack Overflow. Please take the tour to learn how Stack Overflow works and read How to Ask on how to improve the quality of your question. It is unclear what you are asking or what the problem is. Please edit your question to include a more detailed description of the problem you have. You say that the query takes 4 seconds, but you also say that the query takes some milliseconds. It is not clear what exactly you are asking for. – Progman Commented Mar 8 at 13:48
• I want to know why my query is taking 4 seconds when it is straightforward query – user785461 Commented Mar 8 at 14:09
• 1 If the query is sometimes fast, but sometimes slow when the server is under load, then the problem is not with your query, but with your server. – Shadow Commented Mar 8 at 15:20
• 1 With respect, you already identified why it takes 4 seconds — you wrote that it happens when load on the server is very high. I would measure the load from applications and services running on that server, and identify the process(es) responsible for the high load. Then either move those processes to another server, or else upgrade your server. Based on your description, this is not a query optimization problem or a code problem It's a server sizing problem. You need a more powerful server, or else multiple servers capable of handling the load. – Bill Karwin Commented Mar 8 at 15:21
• One other possibility that might make it a code problem: you have other apps or processes running on the same server that are causing the high load and competing for resources against MySQL Server. In that case, you might be able to make code changes to run those apps more efficiently without causing high load. But your question above doesn't describe anything about that code, so there's no way folks on Stack Overflow can guess at the specific cause or solution. – Bill Karwin Commented Mar 8 at 15:23

 |  Show 1 more comment

1 Answer 1

Sorted by: Reset to default 1

Your query of

select entity_name from entity where entity_name in ('sunshine-int-001', 'sunshine-int-002');

is set to compare the primary key of each record with two strings. You state that sometimes it's quick, but when the server has high load, then it gets slow. So the difference between "quick" and "slow" stems from the server load. As a result, other applications or high load upon this one slows the query down.

Now, when a write operation happens on your entity table, that locks down the table and read operations wait for the write to end. It's not surprising that your reads wait for the write to finish. Now, if you have many small writes, they can add up.

So if you want to improve upon performance, then you could have two copies of the database, one that your application would use for reads and the other that your application would use for writes.

Your reads would not have live data, but your database meant for reads would be periodically updated by the stuff your database meant for writes has. This way reads would only get slow while updating in a periodic manner, but not constantly. But this comes with the cost of your data not being live for the users.

Whether it's good or bad for you is up to you to decide.

本文标签： performanceMysql Query taking more time than expectedStack Overflow