CODEWITHSUNDEEP
mysqlsqldatabase
Himz
Himz

Reputation: 523

MYSQL database server Optimize: Queries very slow with many connections

I am running a MySQL DB server. During load test, with thousands of connections, MySQL queries are taking time in minutes. What can I do to optimize this situation?

Here is the brief description of my mysql db configuration

  1. Only read access to tables.
  2. Using Innodb engine
  3. Have run mysqltuner, and implemented its recomendation
  4. The system is under lot of queries and probably half open connections too.
  5. Have done file system optimizations.

I realize it is very open ended question, but would like any inputs .

EDIT: Adding more info:

Queries are very simple like "select * from q6 where created_at='2013-10-02+00:00:00'". Queries will always of this form only. Just too many of them. Table has schema like(one of the six huge tables) : create table q4t_table ( created_at TIMESTAMP, tweet TEXT) engine=InnoDB;

The tables are indexed on timestamp(or userid which is int in some tables). At one time, only one table out of six will be accessed.

Table size is typicall 300Mb or 3Gb or 8Gb.

Upvotes: 4

Views: 1212

Answers (1)

Joe Love
Joe Love

Reputation: 5932

You're likely running out of memory with so many open connections. You should be using connection pooling like sqlRelay or the pooling mechanism built into your language platform.

Upvotes: 2

Related Questions