# Max Concurrent Connections

**URL:** <https://forums.percona.com/t/max-concurrent-connections/874>\
**Category:** Other MySQL® Questions\
**Created:** [August 9, 2008, 2:02am UTC](https://forums.percona.com/t/max-concurrent-connections/874 "2008-08-09T02:02:55Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![shyamala](https://avatars.discourse-cdn.com/v4/letter/s/5f9b8f/32.png) [@shyamala](https://forums.percona.com/u/shyamala)\
**Post date:** [August 9, 2008, 2:02am UTC](https://forums.percona.com/t/max-concurrent-connections/874/1 "2008-08-09T02:02:55Z")

</div>

We have a database Server Configuration:  
4GB RAM  
600GB Hard Disk  
Xeon Processor 1.3 Ghz.

We are barely able to have 100 concurrent users!!! What are we doing wrong.

I know I need to configure mysql\_query cache, mysql\_limit\_size and table\_cache. But what should be the formula, and how do we go about checking the same.

Below is the details of our my.ini file.

[mysqld]  
datadir=/database/data  
socket=/var/lib/mysql/mysql.sock  
set-variable=max\_connections=2000  
set-variable = max\_allowed\_packet=64M  
default-storage-engine = innodb  
log-bin=/database/data/mysql-bin  
#skip-networking

# Default to using old password format for compatibility with mysql 3.x

# clients (those using the mysqlclient10 compatibility package).

old\_passwords=1

[mysql.server]  
user=mysql  
basedir=/var/lib

[mysqld\_safe]  
log-error=/var/log/mysqld.log  
pid-file=/var/run/mysqld/mysqld.pid

---

<div class="post-metadata">

**Author:** ![Speeple](https://avatars.discourse-cdn.com/v4/letter/s/73ab20/32.png) [@Speeple](https://forums.percona.com/u/Speeple)\
**Post date:** [August 9, 2008, 8:36am UTC](https://forums.percona.com/t/max-concurrent-connections/874/2 "2008-08-09T08:36:44Z")

</div>

Most important are the data buffers.

I’ll assume you’re using MyISAM so try this to get the size of cache for MyISAM indexes:

SHOW VARIABLES LIKE ‘key\_buffer\_size’

That’s the size in bytes.

Then you can guess the optimum value (making sure it’s with limits of your available RAM) by doing:

SHOW TABLE STATUS WHERE Engine=‘MyISAM’;

The sum of all the “Index\_length” columns would be your optimum key\_buffer size (plus a few megs).

---

<div class="post-metadata">

**Author:** ![shyamala](https://avatars.discourse-cdn.com/v4/letter/s/5f9b8f/32.png) [@shyamala](https://forums.percona.com/u/shyamala)\
**Post date:** [August 9, 2008, 10:59am UTC](https://forums.percona.com/t/max-concurrent-connections/874/3 "2008-08-09T10:59:27Z")

</div>

Ammended configuration:

While simulating 500 concurrent users on our Drupal installation we made the following observations:

1. In 500 users test, 415 users passed and 85 users failed. Users failed due to database constraints.
2. Row locks and table scan occurs throughout the test.
3. Time consuming queries also occurs throughout the test.
4. After users completed their actions, Connections are not closed and tables are opened because table cache value is not set properly.
5. CPU usage on database reaches maximum of 96% and average of 76%. In specific, the maximum of 90% occurs for particular period of time.
6. CPU usage is normal for web server
7. The Following were recorded at runtime:

Slow-queries 2,064  
Buffer-pool-reads 8220  
Row-lock waits 361  
Handler-read-rnd 2134  
Tmp\_disk\_tables 256  
Opened-tables 896  
Max-connections 250

My.ini:

[mysqld]  
key\_buffer = 128M  
join\_buffer\_size = 2M  
read\_buffer\_size = 1M  
sort\_buffer\_size = 8M

table\_cache = 2000  
thread\_cache\_size = 32

interactive\_timeout = 25  
wait\_timeout= 3600  
connect\_timeout = 4

max\_allowed\_packet = 64M  
max\_connect\_errors = 100

query\_cache\_limit = 32M  
query\_cache\_size = 96M  
query\_cache\_type = 1

tmp\_table\_size = 64M  
max\_heap\_table\_size = 64M  
read\_rnd\_buffer\_size = 524288  
bulk\_insert\_buffer\_size = 8M  
query\_prealloc\_size = 65536  
query\_alloc\_block\_size = 131072  
open\_files\_limit = 8196  
key\_buffer\_size = 64M  
thread\_stack = 128K

set-variable=long\_query\_time=1  
log-slow-queries = /database/data/log\_slow\_queries.log

myisam\_sort\_buffer\_size =32M

datadir=/database/data  
socket=/var/lib/mysql/mysql.sock  
set-variable=max\_connections=300  
set-variable = max\_allowed\_packet=64M  
default-storage-engine = innodb  
log-bin=/database/data/mysql-bin

[mysql.server]  
user=mysql  
basedir=/var/lib

[mysqld\_safe]  
log-error=/var/log/mysqld.log  
pid-file=/var/run/mysqld/mysqld.pid

What should be the configurational changes you recommend?

---

<div class="post-metadata">

**Author:** ![Speeple](https://avatars.discourse-cdn.com/v4/letter/s/73ab20/32.png) [@Speeple](https://forums.percona.com/u/Speeple)\
**Post date:** [August 10, 2008, 4:07am UTC](https://forums.percona.com/t/max-concurrent-connections/874/4 "2008-08-10T04:07:04Z")

</div>

I still have no idea how large your MyISAM indexes are, so I can’t make an absolutely accurate value for your settings. But these might help:

MyISAM key buffer:  
key\_buffer = 512M // I’m just guessing your indexes are \<= 512 Mb in size

(You list two key\_buffer’s, one being an alias in you config, remove one).

I have no idea what size you tmp tables are when created, nor do I know if they contain syntax that forces them to be written on disk, but to possibly reduce tmp tables being written to disk you could try:

tmp\_table\_size = 128M  
max\_heap\_table\_size = 128M

---

<div class="post-metadata">

**Author:** ![johnphilips](https://avatars.discourse-cdn.com/v4/letter/j/4af34b/32.png) [@johnphilips](https://forums.percona.com/u/johnphilips)\
**Post date:** [September 10, 2008, 6:35pm UTC](https://forums.percona.com/t/max-concurrent-connections/874/5 "2008-09-10T18:35:56Z")

</div>

Hi shyamala,

I agree with Speeple, and I am not the SQL expert, but may be I think you will get the right solution on the SQL forum. Just search on the internet “sql forum”.

John Philips

Foreclosed Homes