Posted by shyamala on August 9, 2008 at 6:26am
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
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
Comments
Try this
Try this configuration and read my comments inside it
#you really don't need more than 200 connections
max_connections = 200
#this can be safely reduced to 32 or even 16M if you have few (0-20) or small (<1gb total) databases
key_buffer = 128M
myisam_sort_buffer_size = 32M
join_buffer_size = 2M
read_buffer_size = 1M
sort_buffer_size = 8M
#use total number of database tables +20% (ALL databases) - if you just have a single drupal database 100-150 will be enough
table_cache = 2000
thread_cache_size = 32
wait_timeout = 3600
connect_timeout = 4
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
#if you get a long query selecting a lot of data you might want to increase this
query_cache_limit = 32M
#play with this until you get 80% cache filled after mysql server has been running for 24h+, if this is a dedicated database server you can go nuts with this up to 70% of RAM, but it there is no queries to be cached this will be pretty useless. Better stay under 512M and use rest for memcached
query_cache_size = 96M
query_cache_type = 1
query_prealloc_size = 65536
query_alloc_block_size = 131072
open_files_limit = 8196
key_buffer_size = 64M
thread_stack = 128K
add the rest of the config (user, pid, passwords, etc) from you current configuration
If you can't handle it on your own, or need someone to fine tune the settings let me know - I'll have a look.
Thanks, Will keep you
Thanks,
Will keep you updated with changes.
Netlink Technologies Ltd
http://shyamala-drupal.blogspot.com/
Shyamala
Unimity Solutions
My.ini
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?
Netlink Technologies Ltd
http://shyamala-drupal.blogspot.com/
Shyamala
Unimity Solutions
Can you paste some entries
Can you paste some entries from slow queries log?
doesn't look very good
Another thing that worries me is
Try increasing tmp_table_size and max_heap_table_size to 128M - it should help (unless this was caused by blob/text fields - in that case memory tables are simply not allowed)
BTW how exactly did you test the load? Did you allow some 'warm up' for database? Freshly started database server will perform much worse than one with some query cache already filled. To get more accurate data simulate low load situation, such as 10-50 concurrent users for at least 5 minutes and then perform the test. The longer you 'warm up' the database the more accurate the test result will be.
Will get the slow query log
Will get the slow query log updated my mid day IST.
The plan was to work on the following factors, please help to tell if we are on the right track?
The no of opened tables has increased -> So increasing thread_cache_size from 2000 to 2500 and thread_cache_size from 32 to 286
Also increase query_cache_limit to 64M.
Will check if the database warm up is being carried out too.
WHAT could be the reasons for:
Thanks
Shyamala
Netlink Technologies Ltd
http://shyamala-drupal.blogspot.com/
Shyamala
Unimity Solutions
It looks like you have a
It looks like you have a nasty query somewhere:
Suggest that you have queries that require MySQL to scan whole tables or you have joins that don't use keys properly. We'll know more once you've posted a few entries from slow queries log. This can be a serious problem - couple of weeks ego I've been optimizing mysql setup for a friend of mine and just changing 1 query, which was responsible for less than 0,02% total queries reduced server load by 95%.
So at the moment slow query is my best guess.
As for your questions:
1. There can be a number of reasons: some queries are badly written (like having selecting all rows matching a rule without using limit, and then using first 3, using like %val% on large datasets ), the database is badly designed - there could be indexes missing etc. Or simply there are so many perfectly correct queries on a nicely designed database - that's what we'll be trying to figure out.
2. Do you mean that there are 70-80 mysql still running? That is perfectly fine - overhead of starting a new one is high, so why kill them?
3. Yes, you do. Maximum MySQL memory use = key_buffer + max_connections * (join_buffer + record_buffer + sort_buffer + thread_stack + tmp_table_size) + some extras, once mysql memory use exceeds ram and starts swapping performance will plummet. Better to give some users 'too many connections error' than allow that.
I am pretty sure it can be done, since I am running a drupal site having ~400 concurrent users at peak hours on a single dual core xeon with 2gb ram (yes, no separate database server - all on one server) and the site is on the heavy side (16 views on frontpage)
Any updates? Is it working
Any updates? Is it working fine now?
A Couple Things
I would check to see what queries are showing up in the slow query log. I recently went through on a site and found that the pager query for the front page was slowing things down. This was the basic front page view for Drupal, but it was using a full table scan. To fix that I added a new index on promote, status, sticky, and created (in that order). This stopped the problem right away. I even got an issue in the queue on this:
http://drupal.org/node/295283
With the 70-80 connections still in MySQL, can you check and see what they are doing? Run a show processlist; and see if they are just sleeping and how long they been sleeping for. Having sleeping connections filling up the pool is common when you are running MySQL on a separate server. You might want to drop your wait_timeout to something lower (like 30-60 seconds). Even then, you can sometimes see sleeping processes past this time limit. This is more common on different OS's (ie: older versions of FreeBSD had a problem closing connections). Having waiting connections is only beneficial if PHP is using a persistent connection (which isn't used in Drupal). Using regular connections, PHP is supposed to close the connection at the end of the script execution. Also persistent connections requires better server tuning so that PHP doesn't try to establish more persistent connections than your database can handle.
HollyIT - Grab the Netbeans Drupal Development Tool at GitHub.
A few comments and my my.cnf
How do you measure 500 concurrent users - is that 500 page views every second or 500 users logged in the last 1 second, 30 seconds, 5 minutes?
Number of users don't really have any say on the server load...
Since we are discussing MySQL performance the number of queries, selects, insertst, update and deletes would be interesting to know etc.
You should install mysqlreport and innotop, which both give good reports about the current state of your database.
innotop - http://sourceforge.net/projects/innotop
mysqlreport - http://hackmysql.com/mysqlreport
I would not recommend some of the settings you have in your my.cnf.
Especially the read and sort buffer settings. If you are not sure what they do, don't change from default - these seem to be affected by cpu cache size +++. Because of that they are -really- hard to get right and increasing them could leads to severe drop in performance!
Here is the important parts for our my.cnf:
# cache indexes
key_buffer = 2G
thread_cache_size = 8
table_cache = 2K
max_connections = 130
# large sort buffer - not guaranteed to increase performance
# http://www.mysqlperformanceblog.com/2007/08/18/how-fast-can-you-sort-dat...
#sort_buffer_size =
# http://www.mysqlperformanceblog.com/2007/09/17/mysql-what-read_buffer_si...
#read_buffer_size =
#read_rnd_buffer_size =
# * Query Cache Configuration
# Max size of cached result
query_cache_limit = 256K
# Total available space for qcache
query_cache_size = 1GB
# Minimum size of mem.blocks allocated by qcache
query_cache_min_res_unit = 1K
# 1 = default on, 0 = no qchache, 2 = use SQL_CACHE switch in SQL-statement
query_cache_type = 1
long_query_time = 5
log_slow_queries = /var/log/mysql/mysql-slow.log
Take note of the commented out sort and read buffer variables - I don't mess with those.
key_buffer - sould be as large as possible but not more than 30-40% of total memory. You should leave some for linux/mysql to cache data (done outside of mysql)
max_connections - we have never reached 130, 97 at the most. A client request is server so fast that each connection handles several requests per second
query_cache - this requires a lot of fiddling to get right... But if you have a large number of potential queries for caching (selects which are often reused) leave it large enough to at least store these.
This my.cnf is for one of our main servers running some very large online stores with a rather large number of page views, generating around 250 qps (queries per second), peaks up to 1600 qps, total size of database is 30GB, with largest table at 8 million records and 20+ tables at 2+ million records. It even hosts one of our new drupal-base sites with 4+ million nodes. I've put down some work into getting the database on this server to keep up with the increasing amount of data.
Example of our innotop output (not that total QPS is low here - just restarted the server last night because of server-rotation)
CXN When Load QPS Slow QCacheHit KCacheHit BpsIn BpsOut
root Now 0.00 487.91 1 76.88% 99.99% 47.48k 352.19k
root Total 0.00 188.31 1.49k 55.73% 96.39% 19.70k 2.29k
Here you can see that the query cache served 76.88% of the last batch of queries, which is rather good. Key cache is at 99.99%. Total key cache is rather low because of the server restart. It will reach 99.99% in a week or so :)
I think that these are rather good numbers for our system.
This is our query cache report from mysqlreport:
__ Query Cache _________________________________________________________
Memory usage 922.88M of 1.00G %Used: 90.12
Block Fragmnt 6.57%
Hits 3.25M 57.8/s
Inserts 2.14M 38.1/s
Insrt:Prune 6.57:1 32.3/s
Hit:Insert 1.52:1
These are really not that good of a number - either we must increase query cache to be able to hold cache more queries, by doing that we will lower the number of prunes, or we should move to query cache on demand (query_cache_type = 2) which by the way is the direction we will go (but it takes some time to update code to include SQL_CACHE in potential cachable selects :)
PS.
-I don't think slow queries are any problem as long as any result from a slow query is either cached or stored in query_cache.
PPS.
If you use Linux and ext3 based file system, you should look into /etc/fstab and include "noatime" in the options, to speed up the file system a little:
/dev/mapper/grupp1-volum1 / ext3 defaults,noatime,errors=remount-ro 0 1
PPS.
"We are sorry, but the spam filter on this site decided that your submission could be spam. Please fill in the CAPTCHA below to get your submission accepted."
-What the hell?!
Erlend Stromsvik - erlend@nymedia.no
Ny Media AS - http://www.nymedia.no
Erlend Stromsvik - erlend@nymedia.no - erlendstromsvik @twitter
Ny Media AS - http://www.nymedia.no
PS. -I don't think slow
Since there are multiple entries in slow queries log the query is not cached.
Similar problem
We're having a very similar problem. With 10 million entries in our database, selects and deletes take on the order of 60 seconds to complete. here is our my.cnf:
#
* Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking
#
Instead of skip-networking the default is now to listen only on
localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
#
* Fine Tuning
#
key_buffer = 256M
max_allowed_packet = 64M
thread_stack = 512K
thread_cache_size = 8
max_connections = 200
wait_timeout = 900
table_cache = 1000
thread_concurrency = 10
#
* Query Cache Configuration
#
query_cache_limit = 32M
query_cache_size = 128M
The computer is a 4 core, 3ghz processor with 4 gbs of ram
Any suggestions?
Thanks in advance.
Can you paste some of the
Can you paste some of the slow queries?
You might have to enabel slow queries log by adding
to your mysql configuration.
just want to add some of my
just want to add some of my expieriences.
D5 and D6 in MyISAM DB
analyze the slow logs an add additional indexes. improvement are enormous.
D5 and D6 InnoDB
It is usefull to alter a lot of tables to InnoDB.
InnoDB need a lot of Memory but the performance is great.
My site www.arbeitsplatz24.com has gone through a lot performance improvements. From the standard Drupal Load with 5 to 6 sec. To now 1-2 seconds. A a lot of improvement were on the database.