I have one server where cPanel WHM displays the load averages at the top right, but another server where it doesn't. How do I enable this? And what do the three different numbers shown (e.g. "Load Averages: 0.99 1.17 1.43") stand for?
way to get MySQL stats such as queries per day and things like that?
I understand how the load averages are calculated and what the numbers represent but what would be considered a high load average and at what point would the server need upgraded?
View 7 Replies View RelatedThat would be impossible as the LOAD as given in linux/unix has nothing to do with the cpu load of a server but the total of processes queued (waiting for their turn). If their main node has such a high queue count it should be fairly easy to see for them which process is causing that. No idea why some hosts keep sending their customers to fairytale land every time when it comes to linux load information. Anyways; Exim with that many processes doesnt sound to good either.
As for one VPS bringing down the main node; this is really not that strange as you keep in concideration that a VPS is still a node with many shared customers. Where some things can be regulated, like cpu cycles and memory, but if your node goes completely balistic on its disk IO it will bring a VPS-main node to its knees.
I've never fully understood load averages, I've read articles about what it means but I still don't fully grasp what they are and their meaning.
For example, if I'm to understand correctly, if the amount of processors is greater than the load, then the server is not behind on processing. For example, if a server has a load average of 3.4 and is quad core, this would mean it's not 'under strain', correct?
I have a dedicated server, 2 3GHZ CPUs and the following load averages:
Quote:
Load Averages3.25 3.61 3.69
Does this equate to my server being strained, or handling fine?
the Load averages in Linux I tend to find confusing, I've read that its the system load averaged over 1, 5 and 15 minutes but beyond that I never seem to find a clear cut answer as to what is considered a normal system load.
For instance I have a p4 D 3Ghz with 2gb ram and a single 250gb SATA HDD
the 15min average sits around 0.85 during the busiest time Is that good bad or indifferent?
The server hosts my sites and that of a few friends.
[url]shows what MRTG is outputting about the load averages.
It also says the CPU is 520% although I believe it multiplies it by 10? (i changed the title to say that) so its around 52%
been checking out this site for a while and finally decided to register because I have a problem. Also hope this is the correct forum for this topic, sorry if it isn't.
So I have a problem with Apache. One of the sites that I run/host has a moderately large vBulletin board, and Apache just seems to eat up the CPU. Load averages have shot up between 20-30 and I've seen it as high as 80. Apache and MySQL are optimized already, I'm using suPHP for security because there are other sites on this box.
The funny thing about this is that it only started happening about a week ago. After checking for rootkits and all that garbage, I reinstalled the OS just to be on the safe side. Everything comes back clean still. I also got fed up and hired Platinum Server Management for a month, to see if they could find a solution (and I've been interested in reselling their services, but that's not relevant). So far the only thing they can come up with is disable suPHP, which isn't an option. I do realize that suPHP is ~20-25 times slower than mod_php, but what totally baffles me is that it worked beforehand and started going all crazy like this. I did try running the site using an dso configuration, the load did drop, but nothing to be proud of.
This site, and the server overall hasn't had any increase in load, I've held off putting new accounts on it until I get this fixed.
In the meantime, I have said forums running on lighttpd, which lowered the load. (Also writing a tutorial on having lighty work with cPanel)
because of following logs server disbled my dir . But i can what they tyr to say to me ?
for example what do following numbers mean ?
Code:
root@eagle [~]# ps aux | grep vbulletin
vbulletin 2277 1.0 0.3 24872 14460 ? SN 02:34 0:00 /usr/bin/php index.php
vbulletin 19632 1.1 0.3 24872 14456 ? SN 02:34 0:00 /usr/bin/php index.php
vbulletin 1887 1.3 0.3 24872 14460 ? SN 02:34 0:00 /usr/bin/php index.php
vbulletin 28620 4.4 0.3 24796 14460 ? SN 02:35 0:00 /usr/bin/php index.php
vbulletin 3511 4.5 0.3 24796 14460 ? SN 02:35 0:00 /usr/bin/php index.php
vbulletin 30745 5.1 0.3 24796 14460 ? SN 02:35 0:00 /usr/bin/php index.php
vbulletin 13859 5.0 0.3 24796 14460 ? SN 02:35 0:00 /usr/bin/php index.php
vbulletin 6864 4.8 0.3 24608 14204 ? RN 02:35 0:00 /usr/bin/php index.php
vbulletin 19513 4.7 0.3 24608 14176 ? RN 02:35 0:00 /usr/bin/php index.php
vbulletin 29706 5.0 0.3 24608 14156 ? RN 02:35 0:00 /usr/bin/php index.php
vbulletin 10099 4.8 0.3 24608 14196 ? RN 02:35 0:00 /usr/bin/php index.php
vbulletin 7125 5.3 0.3 24608 14204 ? RN 02:35 0:00 /usr/bin/php index.php
vbulletin 29849 3.8 0.3 24396 14028 ? RN 02:35 0:00 /usr/bin/php index.php
vbulletin 3634 4.3 0.3 24396 13984 ? RN 02:35 0:00 /usr/bin/php index.php
vbulletin 14476 4.8 0.3 24404 13972 ? RN 02:35 0:00 /usr/bin/php index.php
vbulletin 19630 4.1 0.3 24180 13668 ? RN 02:35 0:00 /usr/bin/php index.php
vbulletin 20126 3.5 0.3 23912 13500 ? RN 02:35 0:00 /usr/bin/php index.php
vbulletin 20727 3.5 0.2 22664 12148 ? RN 02:35 0:00 /usr/bin/php index.php
vbulletin 2332 5.0 0.2 22824 12304 ? RN 02:35 0:00 /usr/bin/php index.php
vbulletin 19602 6.3 0.3 23772 13344 ? RN 02:35 0:00 /usr/bin/php index.php
vbulletin 20436 5.3 0.3 23032 12552 ? RN 02:35 0:00 /usr/bin/php index.php
vbulletin 12178 8.0 0.3 22964 12512 ? RN 02:35 0:00 /usr/bin/php index.php
vbulletin 16656 10.0 0.2 21628 11180 ? RN 02:35 0:00 /usr/bin/php index.php
vbulletin 16012 7.0 0.1 18612 7888 ? RN 02:35 0:00 /usr/bin/php index.php
root 4547 0.0 0.0 3736 660 pts/1 R+ 02:35 0:00 grep vbulletin
I have a webserver that our main two company websites reside on and this box also hosts ~50 other misc. websites. None of the websites on the server are very CPU intensive and bandwidth usage on the box is next to none.
Every morning around 4-6am the server takes forever to load anything. Seeing as this is when I do most of my work, I quickly become frustrated when trying to load our admin area, webmail, etc. Note that things do load, just very slowly...
I've contacted my tech a couple of times but he says this is normal as it's backups and stats running. I guess my question is, IS this normal? If so, is there a way to lessen the load at all?
Not displaying CPU/Memory/MySQL Usage stats.Can u tell me how can i do it.
View 2 Replies View RelatedI look after a number of sites and monitor their stats. We use both webalizer and AWSTATS so we can have a comparison. Up until recently the stats for both of them where relatively the same, webalizer usually showing higher numbers as it doesn't filter bots but the progression of increase and decrease in stats was proportional. However, over the last few months a curious trend is appearing, the stats in AWSTATS are decreasing every month whereas the stats in Webalizer are increasing, the gap between them now is huge.
View 0 Replies View RelatedI am just a beginner and it is about a month that the mysql loads on my server is so high , I have checked the mysql processlist via cpanel and there is no account that takes high mysql and make it high , and even I have checked for any possible rotten databases. but still the server load is so high and I can not control it , even when I restart it just after a minute it goes up again.
just the spamd command takes a high CPU usage sometimes. what it should be from? the spamd for a special account!
I need to optimize mysql , and need help. please do not tell me to hire an expert , just help me thanks
and another question is that how can I check which account is sending spam and stop it?
I have a dual xeon server that host around 15 small website, but these days I see that the load generated from mysql is very high as you can see below is consumes 32.6 CPU
mysql 32.60 1.20 0.0
how I can know what is the cause of this load?
MySQL.
Quote:
top - 18:43:24 up 18 days, 18:05, 1 user, load average: 145.13, 92.77, 40.84
Tasks: 145 total, 10 running, 134 sleeping, 0 stopped, 1 zombie
Cpu(s): 28.7% us, 70.6% sy, 0.0% ni, 0.0% id, 0.7% wa, 0.1% hi, 0.0% si
Mem: 1034060k total, 605968k used, 428092k free, 21400k buffers
Swap: 1020088k total, 165076k used, 855012k free, 119448k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
6102 mysql 16 0 343m 146m 2868 S 364 14.5 1441:52 mysqld
My server almost went dead... until I rebooted MySQL.
MySql die with high load problem?
My server running: Server Load124.40 (2 cpus)[url] Memory Used70 %[url]
Server info:
Processor #1 Vendor: GenuineIntel
Processor #1 Name: Intel(R) Xeon(R) CPU 3060 @ 2.40GHz
Processor #1 speed: 2394.840 MHz
Processor #1 cache size: 4096 KB
Processor #2 Vendor: GenuineIntel
Processor #2 Name: Intel(R) Xeon(R) CPU 3060 @ 2.40GHz
Processor #2 speed: 2394.840 MHz
Processor #2 cache size: 4096 KB
Memory Information Memory: 4148800k/4718592k available (1883k kernel code, 43336k reserved, 761k data, 188k init, 3275648k highmem)
i'm using Vista RTM, and so then
my PHP module can load mysql 5.0.27.
Apache version: 2.0.55 ( ok )
PHP version: 5.2.0 ( unzip package - ok)
MySQL version: 5.0.27 ( connection is ok only with cmd line )
so, what's the problem.
i tried with this solution:
[url]
but it's take no effect.
what's the step i have been lost?
when mysql has heavy load average what else can be done rather than restarting mysql service.
View 6 Replies View RelatedThere is a problem with mysql and server load. Some how mysql usage is going to over 100, but server load is not increasing, but it is crashing more often.
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
5931 mysql 16 0 111m 20m 3888 S 71 2.1 10488:45 /usr/sbin/mysqld --basedir=/ --datadir=/var
What could cause the problem? I did mysqladmin pr and only 2 database connections are existing.
it was very fast until mysql upgraded to 5.0.45 it was 4.. i can’t even turn my forum if it is a busy time cos it is so slow i get page not found after a while but when it is quiet it is not too bad... but it was alot more faster with mysql 4 i don't really want to downgrade please give me some ideas to fix this issue ...........
View 3 Replies View RelatedMySQL the last few days seems to be constantly the most demanding process in top, which it never was before. As far as I can tell, nothing has substantially changed with regards to traffic to MySQL driven sites on the server. Is there anything that might be wrong with the databases, etc., that might throw MySQL into a tizzy?
View 8 Replies View Relatedmy server
Intel(R) Xeon(R) CPU E5310
1g
when i use a software update my site.maybe the sofe use mysql and httpd much.
the load of the server ofen goes high.i can only restart httped or mysql .
some info about the server when load is high.
[root@server ~]# free
total used free shared buffers cached
Mem: 1033676 1018980 14696 0 4352 428756
-/+ buffers/cache: 585872 447804
Swap: 2040244 132660 1907584
[root@server ~]# ps aux | grep mysql | wc -l
3
[root@server ~]# ps aux | grep httpd | wc -l
66
[root@server ~]# mysqladmin -u root -p extended-status
Enter password:
+----------------------------+-----------+
| Variable_name | Value |
+----------------------------+-----------+
| Aborted_clients | 2 |
| Aborted_connects | 89 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 61741575 |
| Bytes_sent | 11783074 |
| Com_admin_commands | 150 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 33678 |
| 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 | 3327 |
| 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 | 6939 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 96 |
| Com_optimize | 1 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 110 |
| 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 | 122075 |
| Com_set_option | 32609 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
I have a cheap managed server and support is of little help BUT they will do what I tell them, where should I tell them to start to fix high mysql load?
The server currently has a large IPB forum, a large 4images gallery, and a couple popular Wordpress blogs, and this combo keeps taking mysql down.
What can I do server side to help this? And what shouldn't I do?
My server very slow and the is very loaded. How can I fix this problem? My server specialities and loaded softwares which are below. And sometimes there is "mysql connect failed", "Lost connection to MySQL server during query" errors. Thanks for kinds...
---
Speed:
100Mbs
LOAD:
Load avarage : 5-30 +
Cpu usage: %30-95 +
Ram usage: %70-95 +
SYSTEM INFO:
2gb ram, P4 3200 cpu, 2x250 GB SATAII hdd, Plesk 8.1.1, centos 4.5 os, apache 2.0.59, php 4.4.7, mysql 4.1.21(client),
mysql 5.0.41(server), zend optimizer, kernel 2.6.19.1.SM #5 SMP, mod_evasive, plesk firewall, qscanq+clamav,
spamassain and system up-to-date.
SOFTWARE:
phpbb 2.0.22
4images 1.7.4
joomla 1.0.12
ONLINE USER:
100-500 user online
on 2 website
How to optimization?
I installed tuning-primer.sh
but I can’t understand this code:
I don’t know. How to set values, example?
NOTE: Red value
-------------------------
[root@lavinya bin]# tuning-primer.sh
-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -
MySQL Version 5.0.41-log i686
Uptime = 3 days 4 hrs 34 min 17 sec
Avg. qps = 92
Total Questions = 25406284
Threads Connected = 1
Server has been running for over 48hrs.
It should be safe to follow these recommendations
To find out more information on how each of these
runtime variables effects performance visit:
.............
SLOW QUERIES
Current long_query_time = 5 sec.
You have 115 out of 25406302 that take longer than 5 sec. to complete
The slow query log is enabled.
Your long_query_time seems to be fine
WORKER THREADS
Current thread_cache_size = 8
Current threads_cached = 7
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine
MAX CONNECTIONS
Current max_connections = 500
Current threads_connected = 2
Historic max_used_connections = 110
The number of used connections is 22% of the configured maximum.
Your max_connections variable seems to be fine.
MEMORY USAGE
Max Memory Ever Allocated : 835 M
Configured Max Per-thread Buffers : 3 G
Configured Max Global Buffers : 46 M
Configured Max Memory Limit : 3 G
Total System Memory : 3.96 G
Max memory limit exceeds 85% of total system memory
KEY BUFFER
Current MyISAM index space = 35 M
Current key_buffer_size = 32 M
Key cache miss rate is 1 : 73658
Key buffer fill ratio = 34.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 = 4 M
Current query_cache_used = 1 M
Current query_cach_limit = 1 M
Current Query cache fill ratio = 29.83 %
MySQL won't cache query results that are larger than query_cache_limit in size
SORT OPERATIONS
Current sort_buffer_size = 2 M
Current record/read_rnd_buffer_size = 1 M
Sort buffer seems to be fine
JOINS
Current join_buffer_size = 1.00 M
You have had 14127 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 = 2500 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 = 256 tables
You have a total of 636 tables
You have 256 open tables.
Current table_cache hit rate is 1% , while 100% of your table cache is in use
You should probably increase your table_cache
TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 32 M
Of 2271787 temp tables, 3% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Created disk tmp tables ratio seems fine
TABLE SCANS
Current read_buffer_size = 1 M
Current table scan ratio = 28 : 1
read_buffer_size seems to be fine
TABLE LOCKING
Current Lock Wait ratio =1 : 112
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrentcy of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.
[root@lavinya bin]#
----------------------
if your reply me message I will be happy
Just wondering if mysql load balancing is possible in a shared environment.
Example:
I have 3 shared accounts. On one server, I have the write and the 2nd and 3rd server I have the read (select). Or vice versa .. Would it be possible to create a php script to perform this function?
Quad Core server, 4GB ram. MySQL runs at all times between 200 - 300% CPU. Server does only 5K unique per day, and runs zen cart.
I am at a loss, I have experiece with tracking dowen reasons for this but this one has stumpped me. So I was hoping to get new eyes on this and see if anyone had any ideas.
my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#old_passwords=1
skip-locking
skip-name-resolve
skip-bdb
key_buffer = 64M
max_allowed_packet = 16M
table_cache = 2048
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 16
thread_concurrency = 8
query_cache_size = 25M
query_cache_type = 1
tmp_table_size=64M
back_log = 100
max_connect_errors = 10000
join_buffer_size=1M
open-files = 20000
interactive_timeout = 300
wait_timeout = 300
max_connections=200
# The following directives should be commented out
# but included as they are things that get added
# very frequently on tickets. These are more in a
# need-this-feature basis.
# The below 2 cannot be set on the fly. If the customer already has
# InnoDB tables and wants to change the size of the InnoDB tablespace
# and InnoDB logs, then:
# 1. Run a full backup with mysqldump
# 2. Stop MySQL
# 3. Move current ibdata and ib_logfiles out of /var/lib/mysql
# 4. Uncomment the below innodb_data_file_path and innodb_log_file_size
# 5. Start MySQL (it will recreate new InnoDB files)
# 6. Restore data from backup
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb_log_file_size = 100M
innodb_buffer_pool_size = 64M
innodb_additional_mem_pool_size = 8M
#log-slow-queries=/var/lib/mysqllogs/slow-log
#long_query_time=2
#log-queries-not-using-indexes
#log-bin=/var/lib/mysqllogs/bin-log
#log-slave-updates
#expire_logs_days = 14
server-id = 1
[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=65536
mysqladmin status
Uptime: 1458 Threads: 1 Questions: 366975 Slow queries: 0 Opens: 401 Flush tables: 1 Open tables: 395 Queries per second avg: 251.698
SHOW STATUS;
mysql> SHOW STATUS;
+-----------------------------------+----------+
| Variable_name | Value |
+-----------------------------------+----------+
| Aborted_clients | 0 |
| Aborted_connects | 8 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 116 |
| Bytes_sent | 157 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 0 |
| 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_create_user | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 0 |
| 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 | 0 |
| Com_insert_select | 0 |
| 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 | 0 |
| 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 | 1 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 0 |
| Com_show_databases | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| 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 | 1 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 0 |
| Com_show_triggers | 0 |
| Com_show_variables | 0 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 0 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connections | 1569 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 5 |
| Created_tmp_tables | 1 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 132 |
| Innodb_buffer_pool_pages_data | 307 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 1 |
| Innodb_buffer_pool_pages_free | 3787 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 2 |
| Innodb_buffer_pool_pages_total | 4096 |
| Innodb_buffer_pool_read_ahead_rnd | 2 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 48197 |
| Innodb_buffer_pool_reads | 205 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 1 |
| Innodb_data_fsyncs | 7 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 7213056 |
| Innodb_data_reads | 221 |
| Innodb_data_writes | 7 |
| Innodb_data_written | 35328 |
| Innodb_dblwr_pages_written | 1 |
| Innodb_dblwr_writes | 1 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 0 |
| Innodb_log_writes | 2 |
| Innodb_os_log_fsyncs | 5 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 1024 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 0 |
| Innodb_pages_read | 307 |
| Innodb_pages_written | 1 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 0 |
| Innodb_rows_read | 165 |
| Innodb_rows_updated | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 52512 |
| Key_blocks_used | 1074 |
| Key_read_requests | 24616475 |
| Key_reads | 1170 |
| Key_write_requests | 11301 |
| Key_writes | 4948 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 13 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 495 |
| Open_streams | 0 |
| Open_tables | 395 |
| Opened_tables | 0 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 440 |
| Qcache_free_memory | 2620672 |
| Qcache_hits | 293141 |
| Qcache_inserts | 69381 |
| Qcache_lowmem_prunes | 60094 |
| Qcache_not_cached | 20918 |
| Qcache_queries_in_cache | 1338 |
| Qcache_total_blocks | 5755 |
| Questions | 394590 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 1 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 173929 |
| Table_locks_waited | 3 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 11 |
| Threads_connected | 2 |
| Threads_created | 13 |
| Threads_running | 2 |
| Uptime | 1540 |
| Uptime_since_flush_status | 1540 |
+-----------------------------------+----------+
So 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>
if it was possible to load vhosts from a MySQL database and the only solutions i found didnt work for me. Im running Apache on Windows. Has anyone seen anything that would work with Windows? The only alternative i can think of is to read the database info using PHP and output it to a vhosts config file. I dont want to do that though because it means restarting the server to load the new config and i hear that a lot of vhosts slows the server down
View 4 Replies View RelatedI'm hosting about 6 private websites on my server, and some have problems.
My biggest website is a vb forum with 30.000 members and 1.5 million posts.
When i browse with firefox i see that vb forum loads fast, but phpads which is integrated on the top and footer loads very slow. When i browse with IE, it takes like 10 seconds before the whole site is loaded and sometimes IE even crashes.
When i try to login on another website / admin panel (oscommerce), i get a mysql gone away error, that means timeout. But why does it show that? I ran mysqlreport and can't find anything why it should display mysql gone away.
# ./mysqlreport
MySQL 5.0.15-standard uptime 3 19:36:47 Mon Feb 5 11:08:10 2007
__ Key _________________________________________________________________
Buffer usage 78.94M of 96.00M %Used: 82.23
Write ratio 0.60
Read ratio 0.00
__ Questions ___________________________________________________________
Total 19.61M 59.47/s
Slow 9 0.00/s %Total: 0.00 %DMS: 0.00
DMS 7.17M 21.74/s 36.56
__ Table Locks _________________________________________________________
Waited 7.57k 0.02/s %Total: 0.05
Immediate 16.50M 50.03/s
__ Tables ______________________________________________________________
Open 1.27k of 1800 %Cache: 70.56
Opened 1.82k 0.01/s
__ Connections _________________________________________________________
Max used 47 of 500 %Max: 9.40
Total 2.62M 7.95/s
__ Created Temp ________________________________________________________
Disk table 6.77k 0.02/s
Table 635.25k 1.93/s
File 50.37k 0.15/s
I can't find any problems on the server which may lead to this strange problem.
The server load is normal, around 1 to 1.5. No weird errors in the apache error log or system log.
This strange behaviour started 2 weeks ago, and appears 2 to 3 time a week.
The system is running:
centos 4.3 64-bit
kernel 2.6.16.17 #1 SMP
directadmin 1.29.0
mysql 5.0.15
php 5.2
apache 2
Zend optimizer 3.2.0
Eaccelerator 1.95
One thing i noticed is that every night when the directadmin cron finished, the server acts normal again.
The directadmin cronjobs are:
* * * * * root /usr/local/directadmin/dataskq
2 0-23/6 * * * root echo 'action=vacation&value=all' >> /usr/local/directadmin/data/task.queue;
#5 5 * * 0 root /sbin/quotaoff -a; /sbin/quotacheck -augm; /sbin/quotaon -a;
20 4 1 * * root echo 'action=reset&value=all' >> /usr/local/directadmin/data/task.queue
0 4 * * * root echo 'action=check&value=license' >> /usr/local/directadmin/data/task.queue
10 0 * * * root /usr/local/directadmin/plugins/awstats/hooks/cgi-bin/awstats_updateall.pl now -awstatsprog=/usr/local/directadmin/plugins/awstats/hooks/cgi-bin/awstats.pl && echo 'action=tally&value=all' >> /usr/local/directadmin/data/task.queue
How can i check (using SSH) which databases/users cause server load to mysql ?
I've tried "mysqladmin proc stat" but it shows just the current. How can i get stats of the last 24 hours for example ?
I've also seen slow connections stats. What is the command to check more detailed report of the slow connections; which databases caused it etc', in the last 24 hours for example as well.
Anyone know a good script that I can run with cron or something? Mysql seems to be the #1 problem with a lot of my web sites, a restart usually fixes it right away for me, but I can't keep restarting my servers everyday manually.
View 1 Replies View RelatedMy Server Is down nearly every day in the last three days
I think one of my clients is sending emails from his forum to his members.
How can I know who is he, And most important can I stop him from emailing his members?
Also,
How can I find out which account is making the biggest load on MySQL?
I currently have one server a
Dual Xeon 5130 2GHZ (woodcrest)
2GB Ram.
Running cPanel/WHM
Now I run a website that is VERY PHP & MySQL Intensive and MySQL is ALWAYS the top of the process list, hogging a ton of usage. It's getting to the point where the site is needed a second server and I know there's a few options; but I'm not sure which one would be the best.
They way I see it my two options are getting a 2nd server and setting up the two to do load balancing, or getting a 2nd server and setting one up for just Apache and the Other for MySQL and using the 2nd as a remote SQL server.
If I do the Apache on a seperate server would I need such a powerful server? And if I also would want to upgrade this server along with getting a 2nd server would I be better of upgrading to 4GB of RAM or upgrading the processors?