I currently have a server for proxy websites only.
I have disabled MySQL, is that ok?
Will my statistics software still work (awstats)?
What else can i disable or do to optimize the server for proxy websites.
I'm using the phproxy scripts.
My server is running a AMD Athlon(tm) XP 2000+ (1662 MHz)CPU
I only host one site, a vb forum on my server and its killing the CPU with only 80-100 people online.
The load averages are:
23.73 (1 mins) , 20.39 (5 mins) , 10.88 (15 mins)
and these are the processes that are making it slow:
Code:
ID Owner CPU Command
8003 mysql 34.1 % /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-f ...
7777 apache 2.1 % /usr/sbin/httpd
7911 apache 2.1 % /usr/sbin/httpd
8382 apache 2.1 % /usr/sbin/httpd
7857 apache 1.9 % /usr/sbin/httpd
7776 apache 1.8 % /usr/sbin/httpd
8384 apache 1.7 % /usr/sbin/httpd
7849 apache 1.6 % /usr/sbin/httpd
9159 root 1.6 % /usr/libexec/webmin/proc/index_cpu.cgi
7852 apache 1.5 % /usr/sbin/httpd
8790 apache 1.4 % /usr/sbin/httpd
8724 apache 1.2 % /usr/sbin/httpd
8775 apache 1.2 % /usr/sbin/httpd
8779 apache 1.1 % /usr/sbin/httpd
8841 apache 1.1 % /usr/sbin/httpd
155 root 1.0 % [kswapd0]
8752 apache 1.0 % /usr/sbin/httpd
8881 root 1.0 % /usr/bin/perl /usr/libexec/webmin/virtual-server/collectinfo.pl
8961 apache 1.0 % /usr/sbin/httpd
8759 apache 0.9 % /usr/sbin/httpd
8777 apache 0.9 % /usr/sbin/httpd
8956 apache 0.9 % /usr/sbin/httpd
8962 apache 0.9 % /usr/sbin/httpd ..
i run a fedora server with plesk 8 on it i was wondering if some one can help me to find the best way to optimize the databases on the server at once my servers contains many clients with unicode data in the database.
View 0 Replies View RelatedSo the site got featured on [url]and now the server is drowning...
The Coppermine Gallery usually hovers around 30~50 users daily and now, 1800, and im at a lost as how I should configure mysql to take on such a load. right now it takes about 10 secs or more to load a page and sometimes it would time out. Because it si coppermine, all pages are dynamic and can't be cached -_-"
Here's the my.cnf right now after i played around with the numbers
server spec
Opteron 170 (2ghz)
2gb ram
250 7200rpm
[mysqld]
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
#skip-networking
back_log = 75
key_buffer = 256M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1800
thread_cache_size = 384
wait_timeout = 7200
max_connections = 600
connect_timeout = 100
wait_timeout = 12000
tmp_table_size = 100M
max_heap_table_size = 64M
max_allowed_packet = 15M
max_connect_errors = 9999999
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
query_cache_limit = 40M
query_cache_size = 150M
query_cache_type = 1
query_prealloc_size = 65536
tmp_table_size = 64M
query_alloc_block_size = 131072
default-storage-engine = MyISAM
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
nice = -5
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
httpd.conf
Timeout 20
#
# KeepAlive: Whether or not to allow persistent connections (more than
# one request per connection). Set to "Off" to deactivate.
#
KeepAlive Off
#
# MaxKeepAliveRequests: The maximum number of requests to allow
# during a persistent connection. Set to 0 to allow an unlimited amount.
# We recommend you leave this number high, for maximum performance.
#
MaxKeepAliveRequests 100
#
# KeepAliveTimeout: Number of seconds to wait for the next request from the
# same client on the same connection.
#
KeepAliveTimeout 15
##
## Server-Pool Size Regulation (MPM specific)
##
# prefork MPM
# StartServers: number of server processes to start
# MinSpareServers: minimum number of server processes which are kept spare
# MaxSpareServers: maximum number of server processes which are kept spare
# ServerLimit: maximum value for MaxClients for the lifetime of the server
# MaxClients: maximum number of server processes allowed to start
# MaxRequestsPerChild: maximum number of requests a server process serves
<IfModule prefork.c>
StartServers 8
MinSpareServers 5
MaxSpareServers 20
ServerLimit 200
MaxClients 200
MaxRequestsPerChild 1500
</IfModule>
# worker MPM
# StartServers: initial number of server processes to start
# MaxClients: maximum number of simultaneous client connections
# MinSpareThreads: minimum number of worker threads which are kept spare
# MaxSpareThreads: maximum number of worker threads which are kept spare
# ThreadsPerChild: constant number of worker threads in each server process
# MaxRequestsPerChild: maximum number of requests a server process serves
<IfModule worker.c>
StartServers 2
MaxClients 150
MinSpareThreads 25
MaxSpareThreads 75
ThreadsPerChild 25
MaxRequestsPerChild 0
</IfModule>
I have a fairly high end server in which I have installed SIM. SIM is restarting Apache up to 10 times a day, presumably due to high load causing un-availability.
On restart, Apache / MYSQL is stable until the load / mem usage begins to climb then it is restarted again. Here are my 'load' stats for today:
Load for today
High (2:18am): 4.63
Low (3:30am): 1.20
Mean: 1.84
Latest: 1.61
Mem usage for today
High (1:36am): 9,192.9 MB
Low (1:48am): 7,995.7 MB
Mean: 8,683.1 MB
Latest: 8,781.7 MB
I have seen it using 20GB RAM before.
I have tried to follow various optimisation guides but these seem tailored to less powerful servers.
The web application I run on this server is almost entirely MYSQL based, with thousands of DB calls a day. Across the entire system I probably get 200,000 bot hits per day or even more. At peak times search engine bots are literally hammering the server.
My server specs are:
# Processor: Quad-Core Xeon Processor * 8
# CPU Speed: 3Ghz
# RAM: 32 GB DDR2
# HDD: 36 GB 15k Raptor X2 + 1TB Sata
# OS: 32Bit CentOS
Changes / optimisation of the actual application is not possible.
My configuration files are:
My.cnf ....
Can anyone recommend a Apached and MYSQL optimization expert? I just some optimization done on mysql and apache upgraded... but pache wouldn't restart with the following entries:
MinSpareThreads 25
MaxSpareThreads 35
ThreadsPerChild 25
if i remove above then it starts... but my CPU spikes and I am getting a ton of errors emailed to me:
Invalid SQL:
SELECT COUNT(DISTINCT(userid)) AS count FROM vb_session WHERE vb_session.userid>0 AND vb_session.lastactivity>1245294346;
MySQL Error : Out of memory (Needed 8388580 bytes)
Error Number : 5
Request Date : Wednesday, June 17th 2009 @ 09:39:06 PM
Error Date : Wednesday, June 17th 2009 @ 09:39:06 PM
Its constantly complaining about this same query and i thought i removed all of the vbulletin options...
i am pulling my hair out... cuz my server isn't swapping, ther are 0ver 8GBs in cache.... but my CPU goes up
Here is original problem thread...
[url]
2nd post has recommended config... and my subsequent probs
just got a new server and got my site up and running on it perfectly but ive noticed its a bit slower than my old server and i think this is down to my incorrctly optimized mysql setup. ive used the my.cnf off my old server with some minor edits but im wondering what people can suggest i change for the following server spec
AMD Athlon 64 X2 4200+
2GB DDR2
120gb Sata hd
currently my my.cnf is as follows
Quote:
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
old-passwords
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
max_connections=1000
key_buffer = 1M
long_query_time = 5
query_cache_size = 64M
query_cache_limit = 64M
max_allowed_packet = 5M
table_cache = 2000
max_heap_table_size=32M
tmp_table_size=30M
thread_cache_size = 256
sort_buffer_size = 32M
net_buffer_length = 8K
read_buffer_size = 6M
read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 32M
join_buffer_size = 3M
open_files_limit = 50000
#log-queries-not-using-indexes
low_priority_updates = 1
#loonsies
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
according to mysql tuning primer it reports the following
Quote:
-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -
MySQL Version 4.1.20-log x86_64
Uptime = 0 days 17 hrs 52 min 44 sec
Avg. qps = 15
Total Questions = 1028682
Threads Connected = 1
Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations
To find out more information on how each of these
runtime variables effects performance visit:
[url]
Visit [url]
for info about MySQL's Enterprise Monitoring and Advisory Service
SLOW QUERIES
Current long_query_time = 5 sec.
You have 0 out of 1028694 that take longer than 5 sec. to complete
The slow query log is NOT enabled.
Your long_query_time seems to be fine
WORKER THREADS
Current thread_cache_size = 256
Current threads_cached = 3
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine
MAX CONNECTIONS
Current max_connections = 1000
Current threads_connected = 1
Historic max_used_connections = 4
The number of used connections is 0% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See "MEMORY USAGE" section to make sure you are not over-allocating
MEMORY USAGE
Max Memory Ever Allocated : 243 M
Configured Max Per-thread Buffers : 41 G
Configured Max Global Buffers : 75 M
Configured Max Memory Limit : 41 G
Physical Memory : 1.90 G
Max memory limit exceeds 90% of physical memory
KEY BUFFER
Current MyISAM index space = 12 M
Current key_buffer_size = 1 M
Key cache miss rate is 1 : 14581
Key buffer fill ratio = 44.00 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere
QUERY CACHE
Query cache is enabled
Current query_cache_size = 64 M
Current query_cache_used = 5 M
Current query_cache_limit = 64 M
Current Query cache Memory fill ratio = 8.42 %
Current query_cache_min_res_unit = 4 K
Query Cache is 15 % fragmented
Run "FLUSH QUERY CACHE" periodically to defragment the query cache memory
If you have many small queries lower 'query_cache_min_res_unit' to reduce fragmentation.
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
MySQL won't cache query results that are larger than query_cache_limit in size
SORT OPERATIONS
Current sort_buffer_size = 32 M
Current record/read_rnd_buffer_size = 1020 K
Sort buffer seems to be fine
JOINS
Current join_buffer_size = 3.00 M
You have had 378 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.
Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.
OPEN FILES LIMIT
Current open_files_limit = 50000 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine
TABLE CACHE
Current table_cache value = 2000 tables
You have a total of 908 tables
You have 102 open tables.
The table_cache value seems to be fine
TEMP TABLES
Current max_heap_table_size = 31 M
Current tmp_table_size = 30 M
Of 3666 temp tables, 0% were created on disk
Created disk tmp tables ratio seems fine
TABLE SCANS
Current read_buffer_size = 5 M
Current table scan ratio = 1544 : 1
read_buffer_size seems to be fine
TABLE LOCKING
Current Lock Wait ratio = 1 : 4664
You may benefit from selective use of InnoDB.
PIV 2.8Ghz, 1GB Ram, MySQL 3.23, RHL E3.0
The servers load is vey bad, most of the time 7-8+ and the IO WAIT more than 60% which is decreasing server performace. Please help me to optimzie the MySQL specially. If you have the good my.cnf config please give me the complete config file.
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
safe-show-database
innodb_data_file_path=ibdata1:10M:autoextend
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable = connect_timeout=10
set-variable = interactive_timeout=100
set-variable = join_buffer_size=1M
set-variable = key_buffer=128M
set-variable = max_allowed_packet=16M
set-variable = max_connections=250
set-variable = max_connect_errors=10
set-variable = myisam_sort_buffer_size=16M
set-variable = table_cache=256
set-variable = thread_cache_size=32
set-variable = thread_concurrency=2
set-variable = wait_timeout=15
[mysqldump]
quick
set-variable = max_allowed_packet=16M
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-slow-queries = /var/log/mysql-slow.log
long_query_time = 5
someone to look at my DB and optimize it as it is running ridiculously high loads of 25. It is vbulletin. I'm only looking for professional companies for this job.
View 1 Replies View RelatedI have a friend which owns a 2k alexa rank forum using VB
The server hangs and he gets mysql connection error in logs
Quote:
Database error in vBulletin : mysql_connect() [<a href='function.mysql-connect'>function.mysql-connect</a>]: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'
The server is a dual Pentium D with 4GB RAM
currently 600 users online and 20% RAM used
in rush hours there are 2000 users online and 50% RAM used
We want to optimize mysql - this is the current my.cnf
Quote:
[mysqld]
safe-show-database
old_passwords
back_log = 75
skip-innodb
max_connections = 2000
key_buffer = 80M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 3M
table_cache = 2500
thread_cache_size = 384
wait_timeout = 15
connect_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
net_buffer_length = 16384
max_connect_errors = 10
thread_concurrency = 8
read_rnd_buffer_size = 786432
bulk_insert_buffer_size = 8M
query_cache_limit = 4M
query_cache_size = 64M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = MyISAM
[mysql.server]
user=mysql
basedir=/var/lib
old-passwords = 1
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
nice = -5
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 64M
write_buffer = 64M
[mysqlhotcopy]
interactive-timeout
I just switched to a new server - I went from a Dual Opteron 248 with 3GB of RAM to a Dual Clovertown with 4GB of RAM. Yet, despite having mild traffic right now, my load is rather high and my memory usage is like 80% (which is ridiculous, since that would mean I would be maxing out on the old box).
For some reason, my servers usually lose access when I get around 400 people on TEXT-BASED, non-MySQL pages? How is that possible? I have really solid servers, yet people running basic Pentiums with 1 or 2GB never run into this issue.
Is there any good, cheap site that would be able to properly optimize mySQL and Apache to lower the load and figure out why the memory usage is so high? Platinum Server Management never really got this done, so even though they're great for other support issues, please don't recommend them.
On one of my servers (Intel P4 2.8Ghz, 1GB RAM, cPanel), I've been tweaking some of the MySQL values in my.cnf and using the MySQL Tuning Primer shell script to evaluate the results.
This is my current my.cnf. I'm running MySQL 5.0.27.
Code:
[mysqld]
set-variable = max_connections=500
safe-show-database
skip-innodb
long_query_time=5
log-slow-queries=/var/log/mysql/log-slow-queries.log
query_cache_type=1
query_cache_size=16M
query_cache_limit=1M
table_cache=512
key_buffer=150M
thread_concurrency=2
thread_cache_size=80
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=768K
The tuning primer script is telling me the following.
PHP Code:
Current max_heap_table_size = 16 MCurrent tmp_table_size = 32 MOf 3875 temp tables, 52% were created on diskEffective in-memory tmp_table_size is limited to max_heap_table_size.Perhaps you should increase your tmp_table_size and/or max_heap_table_sizeto reduce the number of disk-based temporary tables
What values should I increase (if at all) max_heap_table_size and tmp_table_size to?
Code:
mysql> SHOW STATUS;
+----------------------------+------------+
| Variable_name | Value |
+----------------------------+------------+
| Aborted_clients | 510 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 441269798 |
| Bytes_sent | 2031527307 |
| Com_admin_commands | 27428 |
| Com_alter_db | 0 |
| Com_alter_table | 4 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 27481 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 16883 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 5515 |
| Com_insert_select | 379 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 1 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 154467 |
| Com_set_option | 132 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 7 |
| Com_show_charsets | 33 |
| Com_show_collations | 33 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 0 |
| Com_show_databases | 6 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 17 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 2 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 38 |
| Com_show_variables | 79 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 38964 |
| Com_update_multi | 0 |
| Connections | 306 |
| Created_tmp_disk_tables | 6302 |
| Created_tmp_files | 3 |
| Created_tmp_tables | 26220 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 7984 |
| Handler_discover | 0 |
| Handler_read_first | 10826 |
| Handler_read_key | 80354286 |
| Handler_read_next | 921590052 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 2852179 |
| Handler_read_rnd_next | 358019891 |
| Handler_rollback | 0 |
| Handler_update | 34804652 |
| Handler_write | 3299547 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 350900 |
| Key_blocks_used | 136652 |
| Key_read_requests | 177777142 |
| Key_reads | 234783 |
| Key_write_requests | 192997 |
| Key_writes | 70499 |
| Max_used_connections | 18 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 126 |
| Open_streams | 0 |
| Open_tables | 89 |
| Opened_tables | 124 |
| Qcache_free_blocks | 1493 |
| Qcache_free_memory | 101831304 |
| Qcache_hits | 247556 |
| Qcache_inserts | 154232 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 170 |
| Qcache_queries_in_cache | 2670 |
| Qcache_total_blocks | 6866 |
| Questions | 491633 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 15228 |
| Select_range_check | 0 |
| Select_scan | 42652 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 29 |
| Sort_merge_passes | 0 |
| Sort_range | 26186 |
| Sort_rows | 92603755 |
| Sort_scan | 28212 |
| Table_locks_immediate | 427295 |
| Table_locks_waited | 239 |
| Threads_cached | 1 |
| Threads_connected | 17 |
| Threads_created | 18 |
| Threads_running | 2 |
| Uptime | 9400 |
+----------------------------+------------+
163 rows in set (0.00 sec)
this is what SHOW STATUS; commands give me can anyone provide me any helpful tips on what I can adjust in my.cnf to lower my server load which is above 10 during peak hours and mysql is the only thing eating this serve up I have lots og 3gb ram and a 64bit AMD 2800 server
I'm trying to optimize a server (Intel E8300) with 8Gb of RAM and WHM/cPanel administration panel.
Now the server has only 46 accounts in WHM, and about 10.000 unique visitors / day
As mysql is always on top for CPU ussage I have edited 2 values in my.cnf, but this was done in 10 minutes and not to much research, any more help will be appreciated. On this server as I see it's been using MyISAM for storage engine and maybe just a few tabels with InnoDB ....
i am having quite problem with my server i cant get it to optimize it. During peak times the load goes beyond 10 sometimes 20 to 50 sometimes its just under 1. I have my webserver running on P4 Core 2 6300, 1 GB ram, 250 HDD, on CentOS 4.4 (kernel 2.6.9-42.0.10) etc. It has cpanel/whm running Apache 1.3.7, php 4.4, mysql 4.1, mailscanner, eaccelerator, etc i dont know really what other info to show you.
I host about 200 accounts and there are 4 large php forums (not like WHT) running on it. Sometimes pages stop loading especially these forums have to refresh to get page load.
During day there are approx 700-900 connections.
Previously i had phpsuexec and the load never went beyond 4-5, i have removed it to install eaccelerator.
Here are my configurations
httpd.conf
Code:
RLimitMEM 200990378
RLimitCPU 240
Timeout 45
KeepAlive On
MaxKeepAliveRequests 512
KeepAliveTimeout 2
MinSpareServers 25
MaxSpareServers 35
StartServers 15
MaxClients 512
MaxRequestsPerChild 0
my.cnf
Code:
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
skip-innodb
skip-networking
skip-name-resolve
query_cache_limit = 1M
query_cache_size = 64M
query_cache_type = 1
max_connections = 200
max_user_connections = 100
interactive_timeout = 30
wait_timeout = 30
connect_timeout = 10
thread_cache_size = 80
key_buffer = 256M
join_buffer = 1M
max_allowed_packet = 8M
table_cache = 1500
record_buffer = 1M
sort_buffer_size = 1M
read_buffer_size = 1M
max_connect_errors = 100
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4
myisam_sort_buffer_size = 32M
#log-bin
server-id = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 1M
write_buffer = 1M
[myisamchk]
key_buffer =128M
sort_buffer_size = 128M
read_buffer = 1M
write_buffer = 1M
[mysqlhotcopy]
interactive-timeout
top
Code:
top - 13:24:33 up 1 day, 23:24, 1 user, load average: 1.99, 2.26, 3.45
Tasks: 174 total, 1 running, 173 sleeping, 0 stopped, 0 zombie
Cpu(s): 13.3% us, 12.1% sy, 0.0% ni, 13.6% id, 31.8% wa, 29.1% hi, 0.0% si
Mem: 1000952k total, 974492k used, 26460k free, 49024k buffers
Swap: 2031608k total, 171580k used, 1860028k free, 288344k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
10804 mysql 15 0 354m 80m 2744 S 35 8.2 4:00.68 mysqld
12849 nobody 15 0 65816 33m 14m S 10 3.4 0:04.56 httpd
2956 named 18 0 66944 18m 1152 S 3 1.9 40:53.96 named
12800 nobody 15 0 57260 28m 17m S 3 2.9 0:06.95 httpd
336 root 15 0 0 0 0 D 3 0.0 112:24.57 kjournald
12854 nobody 15 0 55008 22m 14m S 2 2.3 0:03.68 httpd
12870 nobody 16 0 55108 23m 14m S 1 2.4 0:04.49 httpd
6373 root 0 -20 0 0 0 S 1 0.0 1:13.66 loop0
32021 mailnull 17 0 42500 13m 2480 S 1 1.4 0:09.05 MailScanner
12720 nobody 16 0 56088 22m 12m S 1 2.3 0:04.67 httpd
12877 nobody 15 0 55068 23m 15m S 1 2.4 0:04.05 httpd
12725 nobody 15 0 54836 19m 11m S 1 2.0 0:04.01 httpd
12831 nobody 15 0 55468 25m 16m S 1 2.6 0:03.80 httpd
12859 nobody 15 0 54932 22m 14m S 1 2.3 0:03.75 httpd
7 root 5 -10 0 0 0 S 0 0.0 0:28.61 events/1
30091 root 15 0 2828 520 452 D 0 0.1 0:45.08 syslogd
31213 mailnull 16 0 44896 14m 2472 S 0 1.5 0:13.50 MailScanner
12716 nobody 15 0 56016 24m 14m S 0 2.5 0:04.20 httpd
12774 nobody 15 0 56272 25m 16m S 0 2.6 0:04.76 httpd
12843 nobody 15 0 55156 23m 15m S 0 2.4 0:04.38 httpd
12851 nobody 15 0 56184 21m 11m S 0 2.2 0:03.58 httpd
12853 nobody 15 0 54888 22m 14m S 0 2.3 0:03.39 httpd
12864 nobody 16 0 54920 23m 14m S 0 2.4 0:04.43 httpd
13555 nobody 15 0 54492 21m 13m S 0 2.2 0:02.39 httpd
15385 nobody 16 0 52564 8388 2052 S 0 0.8 0:00.08 httpd
1 root 16 0 2260 396 368 S 0 0.0 0:06.43 init
I just got new server:
2.0 GHz, Xeon 5405 (Harpertown) (Quad Core) with CentOS 5 installed. It has 4 gigs of RAM.
I would like to optimize it for php/mysql based forum.
Any suggestions on how to tweak my.cnf or httpd.conf or anything else. My main goal is to optimize it for php/mysql.
I understand I can leave everything the way it is, but I would rather optimize my server, if I can.
Like should I install some kind of memory cache and if yes, which one? Should I edit default settings of PHP?
I hope some of you folks could give me some pointers here. I have a server that is used for a gaming clan's purposes. Nothing major as far as games go, but what I got I want running at peak efficiency.
The most important is the Counter-Strike source. I really need the kernel frequency to be tweaked for this. The number one question is can I merely modify the existing config file and recompile that or do I need to start all over? If I could do that is it something like make old or something to that matter?
I am running a couple other servers as well, but there is never a time where all the servers have anyone in them. I keep everything running. After installing some weird stuffs here and there I got the memory usage to go down. I think. Regarding a scenario of having more than one instance of a game, is there any way to share memory between them?
My Swap. Is there a way to force idling games to move to it instead of using physical memory?
The kernel is what I really need to take care of. I'm still somewhat of a n00b and have just switched from centos to debian to top it off. So any tips would make my day. Thanks for reading me.
Hi"Optimization of computing resources has long been an important management issue. One of its aspects concerns server scalability and the question of whether an organization should scale-up or scale out.Assume that the computing performance of the servers can be measured by variable 0 <=p, that their total cost is given by "c" and the relationship between server performance and cost is defined by c=αp^β"
a. What is the cost-performance elasticity(ђ), precisely?
b. What would be the range of values for ђ that would be expected by moore's law and what are its implications?
c. What would be the range of values for ђ that would lead managers to scale-out? Draw a graph and throughly explain the implications.
I'm on a dedicated server and the httpd service of the server keeps going down which results in downtime for the number of hours until i dont realise that the site is down.
So when I realise the site is down, I ask the hosting support to look into the issue, they tell me the server loads are high and ask me to upgrade even when I just upgraded the server last month.
Generally the forum has around (250 - 300 members) + guests visitors online and is in the alexa top 20k sites.
The first thing I would want to do is hire a person who specializes in server optimization but then I have heard a lot of bad things like the server admin stealing databases and selling it in open market.
The second thing I wanted to do is contact vbulletin for server optimization but they need me to give them server specs and I dont know how to obtain them.
I have purchased a proxy website using phproxy script hosted at dedicated server. Its now about 4 months and everything is fine but i want to move my site to another dedicated server provider because current one has some issues.
When i purchased that site seller told me that he has optimized server and installed some scripts {server scripts} to get best out of proxy website. Now seller is out of my contact.
I am very much pleased with current server settings but i need to move. So i want to make sure that i can optimize new server and install same scripts to keep going with current performance.
I only know about http.conf settings and i have noted it ... plz help me about copying every other settings which that guy had done to optimize the server and how can i know which and where those scripts are installed which has made server quite efficient?
Can anyone recommend a good company or someone that would be able to optimize Apache and MySQL on my server? I've slightly configured Apache so that it works well with my server so I'm mainly looking for someone to optimize MySQL (since it's using a lot of cpu).
The server load is pretty high so I'm hoping to reduce it before having to upgrade to a new server.
I'm sure by now most of you have read the Web Server Optimization Guide by Shaw Networks. It came in handy for me when I was first starting out, I reduced my load & memory usage by tonnes. I thought I would make a new thread with an updated How-To.
Recommended Tools/Programs:
Putty - Free SSH Client - [url]
WinSCP - Free SFTP and SCP Client - [url]
MySQL Optimization:
BACKUP:
cp /etc/my.cnf /etc/my.cnf.backup
Use Pico (pico /etc/my.cnf) or Download via WinSCP for editing,
Delete everything that is currently in the file and add the following...
Code:
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
skip-innodb
query_cache_limit=8M
query_cache_size=256M
query_cache_type=1
max_connections=500
max_user_connections=100
interactive_timeout=60
wait_timeout=60
connect_timeout=30
thread_cache_size=128
key_buffer=16M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=100
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=2
myisam_sort_buffer_size=64M
#log-bin
server-id=1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
To Save: CTRL-X
Restart Service: "service mysqld restart" or "service mysql restart" or "/etc/rc.d/init.d/mysql restart"
HTTP/APACHE Optimization:
BACKUP:
cp /usr/local/apache/conf/httpd.conf /usr/local/apache/conf/httpd.conf.backup
Use Pico (pico /usr/local/apache/conf/httpd.conf) or Download via WinSCP for editing,
Change the following settings in your httpd.conf...
Set "Timeout" value to "Timeout 60"
Set "KeepAlive" to "KeepAlive on"
Set "KeepAliveTimeout" to "KeepAliveTimeout 3"
Set "MinSpareServers" to "MinSpareServers 16"
Set "MaxSpareServers" to "MaxSpareServers 32"
Set "MaxRequestsPerChild" to "MaxRequestsPerChild 256"
Set "HostnameLookups" to "HostnameLookups Off"
Note:
These settings will not work under all server environments its recommended that you tweak around with the numbers until your web server is running 100% please read Apache documentation before changing any settings so you know what you are changing [url]
To Save: CTRL-X
Restart Service: "service httpd restart" or "/etc/rc.d/init.d/httpd restart"
Installing eAccelerator:
eAccelerator is a further development from mmcache PHP Accelerator & Encoder. It increases performance of PHP scripts by caching them in compiled state, so that the overhead of compiling is almost completely eliminated. [url]
If you run CPanel please visit [url] for an auto-installer which will do all the hard work for you.
Run the following in SSH.
Code:
cd /
mkdir ea
cd ea
wget [url]
bunzip2 eaccelerator-0.9.5.tar.bz2
tar -xvf eaccelerator-0.9.5.tar
cd eaccelerator-0.9.5
export PHP_PREFIX="/usr"
$PHP_PREFIX/bin/phpize
./configure --enable-eaccelerator=shared --with-php-config=$PHP_PREFIX/bin/php-config
make
make install
mkdir /phpcache
chmod 0777 /phpcache
Edit your PHP.INI file and at the bottom add...
Code:
To install as a ZEND extension:
zend_extension="/ea/eaccelerator-0.9.5/modules/eaccelerator.so"
eaccelerator.shm_size="16"
eaccelerator.cache_dir="/phpcache"
eaccelerator.enable="1"
eaccelerator.optimizer="1"
eaccelerator.check_mtime="1"
eaccelerator.debug="0"
eaccelerator.filter=""
eaccelerator.shm_max="0"
eaccelerator.shm_ttl="0"
eaccelerator.shm_prune_period="0"
eaccelerator.shm_only="0"
eaccelerator.compress="1"
eaccelerator.compress_level="9"
OR to install as a PHP extension:
extension="/ea/eaccelerator-0.9.5/modules/eaccelerator.so"
eaccelerator.shm_size="16"
eaccelerator.cache_dir="/phpcache"
eaccelerator.enable="1"
eaccelerator.optimizer="1"
eaccelerator.check_mtime="1"
eaccelerator.debug="0"
eaccelerator.filter=""
eaccelerator.shm_max="0"
eaccelerator.shm_ttl="0"
eaccelerator.shm_prune_period="0"
eaccelerator.shm_only="0"
eaccelerator.compress="1"
eaccelerator.compress_level="9"
Restart Service: "service httpd restart" or "/etc/rc.d/init.d/httpd restart"
For a great tutorial on Optimizing host.conf & sysctl.conf visit..
http://www.eth0.us/node/104
Common SSH Commands visit..
[url]
How to block an IP using iptables visit..
[url]
How can I disable INSERT access to specific table.
The user should have full access the the DB except insert to specific table.
The tables_priv allow to set what enable and not what disable.
I'm using mysql v5
I have a small VPS that is used only to send mail. It uses the HyperVM software, so I installed "Klaxo" (LXadmin) on it and set up the domain, etc..
I then went into the "Server : Linux --> Services" page and disabled everything except qmail. (I also set them so they are not auto-started at bootup.)
The problem is that after about 10 minutes or so... all the services are automatically restarted.
I have no idea what process is doing this, and it's driving me a little nutty.
Does anyone know how to permanently disable a service using the Klaxo/lxadmin control panel?
(Or at least, where I can find whatever monitoring system is checking if they are up, and then restarting them?)
I'm currently in the process of installing a mysql database server on my dedicated game server box. I was using shared hosting before, but i got a complaint from them that i was using too many connections and memory so i have to host the database myself.
Here's the stats of the server:
Dual Core Dual CPU Intel Xeon 3.0Ghz (2 core per cpu & 2 cpu's)
4GB RAM
120GB SATAII Hard Drive
Windows 2003 Enterprise Edition SP2 (180 Day Trial)
Currently, im hosting around 10 game servers on the machine with around 30% CPU load. I'm running with the page file disabled (its using 2 GB of RAM instead). Server runs fine like this (i heard its a performance boost, and it is). Over 2GB of ram are free at all times even when most of the game servers are full.
I want the database server to be able to as many connections are need from the game servers but at the same time i dont want to affect performance of the game servers. Any suggestions on what settings i should use?
I'm currently using these settings:
key_buffer = 512M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 1M
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 16M
max_connections = 100
query_cache_size = 128MB
query_cache_limit = 4MB
tmp_table_size = 30MB
wait_timeout = 120
P.S. The SQL will have 3 databases (AMXBans, SourceBans, & a RP character db). Everytime a player connects to one of my servers, their information is checked against the database if they are an admin. So in total i have around 126 slots for players to connect on.
We just upgraded to VBulletin 3.6.5, and are experiencing strange behavior. My forums are on a dedicated server, 27,000 users.
We'll be running along fine with loads of .5-2.00, etc. Then suddenly, the loads start climbing to 50, 70, 85+. We've been trying to figure out why. We even went on a different (much more robust) server, still the same result. This only started happening after the upgrade. Restarting the server corrects the problem, but only until it happens again. It can happen at anytime - during peak or off peak. The server may run for an hour or two until this happens, or it can happen 10 minutes after a restart.
Late last night when loads were normal we benched the server hard, and it ran just fine. We just can't figure out where this load spike is coming from.
Server info below:
Servers:
Mysql: server version: 5.0.27-log
PHP: PHP 5.1.6 (cli) Zend Engine v2.1.0,
Apache Server version: Apache/2.2.3
Kernel: 2.6.19-1.2911.fc6 #1 SMP
Server:
Pentium III with 1 gig of ram
15k RPM SCSI Raid 5
1 Gig RAM
-----------------------------------------------------------------------
[root@forums forums]# cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 8
model name : Pentium III (Coppermine)
stepping : 6
cpu MHz : 861.001
cache size : 256 KB
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca
cmov pat pse36 mmx fxsr sse
bogomips : 1722.41
processor : 1
vendor_id : GenuineIntel
cpu family : 6
model : 8
model name : Pentium III (Coppermine)
stepping : 6
cpu MHz : 861.001
cache size : 256 KB
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca
cmov pat pse36 mmx fxsr sse
bogomips : 1721.51
--------------------------------------------------------------------------
my.cnf:
[root@forums forums]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log_error = /var/log/mysql_error.log
log-slow-queries = /var/log/mysql_slow_queries.log
safe-show-database
old_passwords
back_log = 20
skip-innodb
key_buffer_size = 256M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 600
thread_cache_size = 384
wait_timeout = 35
connect_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 10
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
query_cache_limit = 4M
query_cache_size = 48M
query_cache_type = 1
query_prealloc_size = 163840
query_alloc_block_size = 32768
default-storage-engine = MyISAM
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
-------------------------------------------------------
Relevant Apache:
StartServers 10
MinSpareServers 10
MaxSpareServers 15
ServerLimit 256
MaxClients 90
MaxRequestsPerChild 1000
---------------------------------------------------------
I installed the Google Authenticator extension, and the inevitable happened: my phone died.So now I am left without access to the Plesk panel.How can I disable the authentication in the SQL database, so I can login again?
View 1 Replies View RelatedI have issue with my shared server. Someone used SSI to local attack user on my server.
I want to disable SSI but i dont find out how to ? (expect manual recomplie apache)
is there an option to disable directory browsing (browsing a directory without an index file) in WHM? I have seen a way to do it via .htaccess but this would not be possible as there are over 100 sites on the server...
View 8 Replies View Related