MySQL Performance: MySQL => Max Concurrent Connections

Members

Help

Home

Home » Performance » MySQL » Max Concurrent Connections

Show: Today's Messages :: Show Polls :: Message Navigator

Max Concurrent Connections [message #3386]

Sat, 09 August 2008 03:32

shyamala
Messages: 2
Registered: August 2008
Location: Chennai

Junior Member

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

Report message to a moderator

Re: Max Concurrent Connections [message #3390 is a reply to message #3386 ]

Sat, 09 August 2008 10:06

Speeple
Messages: 91
Registered: August 2006

Member

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).

Martin Gallagher | Speeple: The latest news

Report message to a moderator

Re: Max Concurrent Connections [message #3391 is a reply to message #3390 ]

Sat, 09 August 2008 12:29

shyamala
Messages: 2
Registered: August 2008
Location: Chennai

Junior Member

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?

Report message to a moderator

Re: Max Concurrent Connections [message #3397 is a reply to message #3386 ]

Sun, 10 August 2008 05:37

Speeple
Messages: 91
Registered: August 2006

Member

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

Martin Gallagher | Speeple: The latest news

Report message to a moderator

Re: Max Concurrent Connections [message #3514 is a reply to message #3386 ]

Wed, 10 September 2008 20:05

johnphilips
Messages: 1
Registered: September 2008

Junior Member

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

Report message to a moderator

Previous Topic:	BLOB and restoring
Next Topic:	Tables getting locked on one server but working fine on another server

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 20 20:03:22 EST 2008

Total time taken to generate the page: 0.02698 seconds

.:: Contact :: Home :: MySQL Support by Percona.com ::.