We have Druapl 5.10, PHP 5.2.5, Apache 2.2.8, Mysql 5.0 running on a Duo Core dual Processors machine (3GHZ + 3GHZ) with 6GB RAM. We are epxerience heavy load and CPU usage. Any help will be appreciated.
Here are our MySQL variables:
----------------------
auto_increment_increment 1
auto_increment_offset 1
automatic_sp_privileges ON
back_log 50
basedir /
binlog_cache_size 32768
bulk_insert_buffer_size 8388608
character_set_client latin1
character_set_connection latin1
character_set_database latin1
character_set_filesystem binary
character_set_results latin1
character_set_server latin1
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/
collation_connection latin1_swedish_ci
collation_database latin1_swedish_ci
collation_server latin1_swedish_ci
completion_type 0
concurrent_insert 1
connect_timeout 5
datadir /var/lib/mysql/
date_format %Y-%m-%d
datetime_format %Y-%m-%d %H:%i:%s
default_week_format 0
delay_key_write ON
delayed_insert_limit 100
delayed_insert_timeout 300
delayed_queue_size 1000
div_precision_increment 4
engine_condition_pushdown OFF
expire_logs_days 0
flush OFF
flush_time 0
ft_boolean_syntax + -><()~*:""&
ft_max_word_len 84
ft_min_word_len 4
ft_query_expansion_limit 20
ft_stopword_file (built-in)
group_concat_max_len 1024
have_archive YES
have_bdb NO
have_blackhole_engine YES
have_compress YES
have_crypt YES
have_csv YES
have_dynamic_loading YES
have_example_engine YES
have_federated_engine YES
have_geometry YES
have_innodb YES
have_isam NO
have_merge_engine YES
have_ndbcluster DISABLED
have_openssl DISABLED
have_ssl DISABLED
have_query_cache YES
have_raid NO
have_rtree_keys YES
have_symlink YES
hostname web2-sles10
init_connect
init_file
init_slave
innodb_additional_mem_pool_size 1048576
innodb_autoextend_increment 8
innodb_buffer_pool_awe_mem_mb 0
innodb_buffer_pool_size 8388608
innodb_checksums ON
innodb_commit_concurrency 0
innodb_concurrency_tickets 500
innodb_data_file_path ibdata1:10M:autoextend
innodb_data_home_dir
innodb_doublewrite ON
innodb_fast_shutdown 1
innodb_file_io_threads 4
innodb_file_per_table OFF
innodb_flush_log_at_trx_commit 1
innodb_flush_method
innodb_force_recovery 0
innodb_lock_wait_timeout 50
innodb_locks_unsafe_for_binlog OFF
innodb_log_arch_dir
innodb_log_archive OFF
innodb_log_buffer_size 1048576
innodb_log_file_size 5242880
innodb_log_files_in_group 2
innodb_log_group_home_dir ./
innodb_max_dirty_pages_pct 90
innodb_max_purge_lag 0
innodb_mirrored_log_groups 1
innodb_open_files 300
innodb_rollback_on_timeout OFF
innodb_support_xa ON
innodb_sync_spin_loops 20
innodb_table_locks ON
innodb_thread_concurrency 8
innodb_thread_sleep_delay 10000
interactive_timeout 28800
join_buffer_size 131072
key_buffer_size 268435456
key_cache_age_threshold 300
key_cache_block_size 1024
key_cache_division_limit 100
language /usr/share/mysql/english/
large_files_support ON
large_page_size 0
large_pages OFF
lc_time_names en_US
license GPL
local_infile ON
locked_in_memory OFF
log OFF
log_bin OFF
log_bin_trust_function_creators OFF
log_error
log_queries_not_using_indexes OFF
log_slave_updates OFF
log_slow_queries OFF
log_warnings 1
long_query_time 10
low_priority_updates OFF
lower_case_file_system OFF
lower_case_table_names 0
max_allowed_packet 67107840
max_binlog_cache_size 18446744073709551615
max_binlog_size 1073741824
max_connect_errors 10
max_connections 100
max_delayed_threads 20
max_error_count 64
max_heap_table_size 16777216
max_insert_delayed_threads 20
max_join_size 18446744073709551615
max_length_for_sort_data 1024
max_prepared_stmt_count 16382
max_relay_log_size 0
max_seeks_for_key 18446744073709551615
max_sort_length 1024
max_sp_recursion_depth 0
max_tmp_tables 32
max_user_connections 0
max_write_lock_count 18446744073709551615
multi_range_count 256
myisam_data_pointer_size 6
myisam_max_sort_file_size 9223372036854775807
myisam_recover_options OFF
myisam_repair_threads 1
myisam_sort_buffer_size 67108864
myisam_stats_method nulls_unequal
ndb_autoincrement_prefetch_sz 32
ndb_force_send ON
ndb_use_exact_count ON
ndb_use_transactions ON
ndb_cache_check_time 0
net_buffer_length 32768
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
new OFF
old_passwords OFF
open_files_limit 8302
optimizer_prune_level 1
optimizer_search_depth 62
pid_file /var/lib/mysql/web2-sles10.pid
port 3306
preload_buffer_size 32768
profiling OFF
profiling_history_size 15
protocol_version 10
query_alloc_block_size 8192
query_cache_limit 1048576
query_cache_min_res_unit 4096
query_cache_size 67108864
query_cache_type ON
query_cache_wlock_invalidate OFF
query_prealloc_size 8192
range_alloc_block_size 2048
read_buffer_size 258048
read_only OFF
read_rnd_buffer_size 520192
relay_log_purge ON
relay_log_space_limit 0
rpl_recovery_rank 0
secure_auth OFF
secure_file_priv
server_id 1
skip_external_locking ON
skip_networking OFF
skip_show_database OFF
slave_compressed_protocol OFF
slave_load_tmpdir /tmp/
slave_net_timeout 3600
slave_skip_errors OFF
slave_transaction_retries 10
slow_launch_time 2
socket /var/lib/mysql/mysql.sock
sort_buffer_size 1048568
sql_big_selects ON
sql_mode
sql_notes ON
sql_warnings OFF
ssl_ca
ssl_capath
ssl_cert
ssl_cipher
ssl_key
storage_engine MyISAM
sync_binlog 0
sync_frm ON
system_time_zone EDT
table_cache 4096
table_lock_wait_timeout 50
table_type MyISAM
thread_cache_size 0
thread_stack 262144
time_format %H:%i:%s
time_zone SYSTEM
timed_mutexes OFF
tmp_table_size 33554432
tmpdir /tmp/
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
tx_isolation REPEATABLE-READ
updatable_views_with_limit YES
version 5.0.41-community
version_comment MySQL Community Edition (GPL)
version_compile_machine x86_64
version_compile_os unknown-linux-gnu
wait_timeout 28800
-------------
A portion of our Apache httpd.conf file:
KeepAlive On
timeout 30
KeepAliveTimeout 7
MinSpareServers 5
MaxSpareServers 10
MaxRequestsPerChild 10
Serverlimit 25
MaxClients 25
Thank You
Pammi
| Comment | File | Size | Author |
|---|---|---|---|
| #2 | meminfo.txt | 699 bytes | Pammi_Gill |
| #2 | mysql_extnded_status_info.txt | 66.25 KB | Pammi_Gill |
| #2 | vmstat_log.txt | 5.34 KB | Pammi_Gill |
Comments
Comment #1
ronaldbradford commentedYou really need to provide more details in order to get a better picture of your situation. For example, an indication of memory,swap,disk and distribution of load with
$ vmstat 1 60
Where is memory being used
$ cat /proc/meminfo
Your MySQL configuration variables doesn't give you a distribution of which storage engines, e.g. MyISAM, Innodb. Variables define the following
key_buffer_size 268435456 = 256M
innodb_buffer_pool_size 8388608 = 8M
With 6GB sizing your memory buffers appropriately for the size of your database is most important. Does your server run more then MySQL for memory for example. e.g.
$ ps -faxv
You also need to provide a state of the database in operation, for example
$ mysqladmin -uroot -p -i 60 -c 5 extended-status
This will provide 5 minutes of analysis of the database.
You need to pinpoint better the primary source of the problem "Web Site is slow" doesn't give you any indication.
Comment #2
Pammi_Gill commentedThanks Ronald. I have attached the following files for reference. We are running ISAM Storage engine. InnoDb is commented out in my.cnf. I did a load test also while getting the extended status of MySQL dB. Hoepfully, that should help.
Here is the load test screen shot:
----
gillp@web2-sles10:~> /usr/local/apache2/bin/ab -n2000 -k http://www.gpb.org/
This is ApacheBench, Version 2.0.40-dev <$Revision: 1.146 $> apache-2.0
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Copyright 2006 The Apache Software Foundation, http://www.apache.org/
Benchmarking www.gpb.org (be patient)
Completed 200 requests
Completed 400 requests
Completed 600 requests
Completed 800 requests
Completed 1000 requests
Completed 1200 requests
Completed 1400 requests
Completed 1600 requests
Completed 1800 requests
Finished 2000 requests
Server Software: Apache/2.2.8
Server Hostname: www.gpb.org
Server Port: 80
Document Path: /
Document Length: 17705 bytes
Concurrency Level: 1
Time taken for tests: 325.168046 seconds
Complete requests: 2000
Failed requests: 0
Write errors: 0
Keep-Alive requests: 0
Total transferred: 36472000 bytes
HTML transferred: 35410000 bytes
Requests per second: 6.15 [#/sec] (mean)
Time per request: 162.584 [ms] (mean)
Time per request: 162.584 [ms] (mean, across all concurrent requests)
Transfer rate: 109.53 [Kbytes/sec] received
Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 0.0 0 0
Processing: 16 161 332.7 115 9078
Waiting: 14 137 242.9 99 5135
Total: 16 161 332.7 115 9078
Percentage of the requests served within a certain time (ms)
50% 115
66% 139
75% 163
80% 179
90% 241
95% 317
98% 508
99% 901
100% 9078 (longest request)
--------------------
- Pammi
Comment #3
zahor commentedThere is no one step solution. Tuning has to be done on Php/Mysql side as well as the Drupal side - and there is a lot. Accelerators (like eaccelerator or zend), caching and lots of it, some more caching, and then analyzing and optimizing Drupal queries and some more caching.
Comment #4
ronaldbradford commentedOk, you say your site is slow, but your running a ab benchmark flat out. (CPU at 100%). This doesn't help pinpoint your performance problems.
You need to provide figures for real world situations, not just a blank benchmark.
You have amble RAM (over 2 GB unused) and 3GB in File System cache.
Your database throughput load is extremely low, like 50 statements per second. You do however have ~3,000 SELECTS per second that are cached which is very good. This may or may not be a true representation. Servers of your configuration should be able to support several thousands statements per second for well planned statements. (in the order of 2k DML, 4-5k Selects per second.
More analysis of the total time of your transactions, other server load is necessary. Your comment of heavy load and CPU needs greater justification of your real world situation.
Number of Snapshots: 2
====================================================================================================
Uptime: 20 hours 44 mins Snapshot Period 1: 4 minute interval
====================================================================================================
Variable Delta/Percentage Per Second Total
====================================================================================================
Database Activity
====================================================================================================
Threads Connected: -32 20
Threads Running: 0 1
Questions: 653,670 2,712.32 216,468,497
Bytes Recieved: 48M 207K 15G
Bytes Sent: 365M 1M 100G
Aborted Clients: 0 0.00 0
Aborted Connects: 0 0.00 83
====================================================================================================
Statement Activity
====================================================================================================
SELECT: 9,570 39.71 1,818,124 (93.54%)
INSERT: 186 0.77 47,476 (2.44%)
UPDATE: 384 1.59 70,670 (3.64%)
DELETE: 58 0.24 7,080 (0.36%)
REPLACE: 2 0.01 401 (0.02%)
INSERT ... SELECT: 0 0.00 0 (0.00%)
REPLACE ... SELECT: 0 0.00 0 (0.00%)
Multi UPDATE: 0 0.00 0 (0.00%)
Multi DELETE: 0 0.00 0 (0.00%)
COMMIT: 0 0.00 0 (0.00%)
ROLLBACK: 0 0.00 0 (0.00%)
====================================================================================================
Prepared Statements
====================================================================================================
Prepared Statement Count: 0 0.00 0
PREPARE: 0 0.00 0
EXECUTE: 0 0.00 0
DEALLOCATE PREPARE: 0 0.00 0
Fetch Roundtrips: 0 0.00 0
Send Long Data: 0 0.00 0
====================================================================================================
Admin Commands
====================================================================================================
KILL: 0 0.00 0
FLUSH: 0 0.00 0
ANALYZE TABLE: 0 0.00 0
OPTIMIZE TABLE: 0 0.00 138
CHECK TABLE: 0 0.00 0
REPAIR TABLE: 0 0.00 0
====================================================================================================
Thread Cache
====================================================================================================
Thread Efficiency: 100.00%
Connections: 1,468 6.09 64,876
Threads Created: 0 0.00 518
====================================================================================================
Table Cache
====================================================================================================
table_cache Efficiency: 0.69%
Open Tables: 0 0.00 64
Opened Tables: 42 0.17 9,311
====================================================================================================
MyISAM Key Cache
====================================================================================================
Cache Read Efficiency: 99.94%
Cache Write Efficiency: 45.78%
Memory Used: 20B 2K
Memory Free: 14B 73K
Key Reads: 141 0.59 38,777
Key Read Requests: 133,710 554.81 65,070,543
Key Writes: 963 4.00 250,418
Key Write Requests: 1,274 5.29 461,855
Blocks Not Flushed: 0 0.00 0
====================================================================================================
InnoDB Buffer Pool
====================================================================================================
Buffer Pool Read Efficiency: 84.62%
Memory Total: 0B 8M
Memory Free: 0B 7M
Memory Data: 0B 304K
Memory Dirty: 0B 0B
Data Read: 0B 2M
Data Written: 0B 1K
Buffer Pool Reads: 0 0.00 12
Buffer Pool Read Requests: 0 0.00 77
Buffer Pool Write Requests: 0 0.00 0
====================================================================================================
InnoDB Log Files
====================================================================================================
Log Data Written: 0B 512B
Log Writes: 0 0.00 1
Log Write Requests: 0 0.00 0
Log Waits: 0 0.00 0
====================================================================================================
Query Cache
====================================================================================================
QCache Hits / SELECT: 99.16%
QCache Hit/Qcache Insert: 99.19%
Qcache Hits/Invalidations: 1705.00%
SELECTs: 9,570 39.71 1,818,124
Query Cache Hits: 638,675 2,650.10 214,207,777
Query Cache Inserts: 9,410 39.05 1,753,032
Queries Not Cached: 162 0.67 64,182
Cache Low Memory Prunes: 0 0.00 203,858
Total Cache Blocks: 1,964 8.15 33,819
Queries In Cache: 770 3.20 14,751
Cache Free Blocks: 429 1.78 4,231
====================================================================================================
Index Usage
====================================================================================================
Index Efficiency: 62.38%
Full Index Scans: 157 0.65 55,323
Full Table Scans: 62 0.26 18,240
Full Join Scans: 0 0.00 4,801
Handler_read_first: 157 0.65 55,323 (0.06%)
Handler_read_key: 40,509 168.09 25,855,739 (28.81%)
Handler_read_next: 53,004 219.93 30,077,149 (33.51%)
Handler_read_prev: 0 0.00 541 (0.00%)
Handler_read_rnd: 686 2.85 197,043 (0.22%)
Handler_read_rnd_next: 123,828 513.81 33,566,201 (37.40%)
====================================================================================================
Temporary Space
====================================================================================================
tmp_table_size Efficiency: 59.73%
Memory Temp Tables: 374 1.55 86,009
Disk Temp Tables: 245 1.02 34,634
Temp Files: 2 0.01 195
====================================================================================================
Lock Contention
====================================================================================================
Percent of Locks Waited: 0.04%
Table Locks Waited: 11 0.05 791
Table Locks Immediate: 11,384 47.24 2,140,698
====================================================================================================
Sorting
====================================================================================================
Rows Sorted: 6,279 26.05 1,666,324
Sort Range: 17 0.07 3,833
Sort Scan: 377 1.56 84,061
Sort Merge Passes: 1 0.00 95
Full Range Joins: 0 0.00 0
Comment #5
Pammi_Gill commentedThanks Ronald. We will try to figure out the bottlenecks in the database.
Regards.
Comment #6
seaji commentedYou know, page cache is not working in Drupal 5.10 now.
http://drupal.org/node/302784
Comment #7
ainigma32 commented@Pammi_Gill: Just tried your site and seems to load decently. Are you still experiencing slow downs under load?
And if you did solve this could you share some of your insights? I'm sure people would be very interested.
- Arie
Comment #8
ainigma32 commentedLooks like Pammi_Gill won't be posting any feedback so I'm setting this to fixed.
Feel free to reopen if you think that is wrong.
- Arie
Comment #10
mcurry commentedsubscribing