[mysqlhotcopy]
interactive-timeout
Whenever there are a lot of members online on my forum, the iowait shoot up, >30% and often times hovering at 60%. It'll eventually drop down to normal levels. However, during the high iowait, there are over 2.5GB of free memory according to the command, free -m
is it due to mysql not optimized or other processes? I don't think the bottleneck is on the cpu or ram.
I am running in a High load problem lately. I have one of those cheap 1and1 servers which was running fine until 2 weeks ago. Once I rebooted accidentaly, it did not come back with some unrepairable kernel errors and I had to re-image it.
I chose to reimage the server with CentOS 5, for better support. The new re-image worked fine for some days, at least so I thought and now I am having high loads. The server crashes if not monitored every moment as the load is unpredictable.
Just a restart of the Apache will bring the server back to normality, but I am not sure if it is apache or some other script to be blamed. I have beeing monitoring through apache server-status, but I cannot organize something unusual in the high load moments.
12:00:29 AM CPU %user %nice %system %iowait %steal %idle 12:10:01 AM all 9.14 0.00 5.52 44.66 0.00 40.68 12:20:14 AM all 6.83 0.00 3.98 27.88 0.00 61.32 12:30:10 AM all 6.44 0.00 4.20 81.25 0.00 8.11 12:40:09 AM all 5.25 0.00 4.09 81.93 0.00 8.73 12:50:15 AM all 5.11 0.00 3.79 90.74 0.00 0.36 01:00:07 AM all 7.22 0.00 4.52 57.11 0.00 31.15 01:10:13 AM all 6.89 0.00 4.01 55.38 0.00 33.71 01:20:14 AM all 4.37 0.00 3.27 41.88 0.00 50.48 01:30:25 AM all 4.26 0.00 3.29 63.42 0.00 29.03 01:40:06 AM all 27.18 0.00 4.75 58.27 0.00 9.80 01:50:03 AM all 29.64 0.00 6.61 51.50 0.00 12.25 02:00:07 AM all 27.00 0.00 8.48 55.49 0.00 9.03 02:10:10 AM all 19.29 0.00 4.97 73.80 0.00 1.94 02:20:04 AM all 37.85 0.00 6.78 40.70 0.00 14.67 02:30:05 AM all 15.65 0.00 4.80 68.47 0.00 11.08 02:40:08 AM all 9.06 0.00 5.60 37.49 0.00 47.86 02:50:07 AM all 5.36 0.00 3.62 42.29 0.00 48.73 03:00:02 AM all 6.05 0.00 4.08 47.27 0.00 42.60 03:10:02 AM all 4.22 0.00 3.68 38.17 0.00 53.93 03:20:02 AM all 4.06 0.00 3.75 41.37 0.00 50.82 03:30:22 AM all 4.42 0.00 3.93 45.25 0.00 46.41 03:40:11 AM all 4.34 0.00 3.95 39.58 0.00 52.13 03:50:02 AM all 4.67 0.00 4.01 32.53 0.00 58.80 04:00:08 AM all 3.72 0.00 3.87 28.40 0.00 64.02 04:10:02 AM all 13.49 0.00 6.58 20.82 0.00 59.10 04:20:01 AM all 6.70 0.00 4.63 6.06 0.00 82.61 04:30:02 AM all 1.44 0.00 1.21 4.75 0.00 92.59 04:40:01 AM all 12.42 0.00 8.12 7.65 0.00 71.81 04:50:02 AM all 1.43 0.00 1.07 4.02 0.00 93.47 05:00:02 AM all 1.60 0.00 1.40 8.62 0.00 88.38 05:10:10 AM all 3.80 0.00 3.02 17.86 0.00 75.32 05:20:06 AM all 5.10 0.00 4.22 23.34 0.00 67.34 05:30:02 AM all 1.54 0.00 1.40 11.22 0.00 85.85 05:40:05 AM all 1.75 0.00 1.89 13.12 0.00 83.23 05:50:12 AM all 2.15 0.00 2.22 18.92 0.00 76.72 06:00:02 AM all 1.92 0.00 2.01 12.87 0.00 83.20 06:10:02 AM all 2.27 0.00 2.16 11.53 0.00 84.04 06:20:03 AM all 3.56 0.00 3.02 25.26 0.00 68.16 06:30:10 AM all 2.66 0.00 2.05 18.13 0.00 77.16 06:40:02 AM all 2.58 0.00 2.25 22.87 0.00 72.30 06:50:02 AM all 2.68 0.00 1.92 15.77 0.00 79.63 07:00:03 AM all 3.06 0.00 2.48 26.01 0.00 68.46 07:10:03 AM all 3.65 0.00 3.20 36.54 0.00 56.61
07:10:03 AM CPU %user %nice %system %iowait %steal %idle 07:20:03 AM all 4.40 0.00 3.28 43.86 0.00 48.46 07:30:02 AM all 4.10 0.00 3.17 31.30 0.00 61.43 07:40:06 AM all 7.67 0.00 3.95 50.79 0.00 37.59 07:50:02 AM all 4.72 0.00 3.11 44.30 0.00 47.86 08:00:03 AM all 5.57 0.00 3.72 47.15 0.00 43.56 08:10:07 AM all 10.66 0.00 3.59 71.62 0.00 14.13 08:20:17 AM all 5.67 0.00 3.42 58.81 0.00 32.10 08:30:10 AM all 11.12 0.00 3.49 76.71 0.00 8.67 08:40:03 AM all 7.00 0.00 3.36 47.94 0.00 41.71 Average: all 7.53 0.00 3.76 38.90 0.00 49.81 Some configurations: The reimage partittioning looks like this:
processor : 1 vendor_id : GenuineIntel cpu family : 15 model : 3 model name : Intel(R) Pentium(R) 4 CPU 2.80GHz stepping : 4 cpu MHz : 2793.324 cache size : 1024 KB
For the past few days, one of the server is causing a bandwidth utilization surge for the entire rack on almost daily basis. It happen for a few minutes and it went off then it will be the same thing again the next day. When the surge is happening, most of the servers on the same segment will be inaccessible for the few minutes.
The bandwidth utilization graph for my rack is recording an abnormal surge from (6mbps --> 90mbps) for the few minutes. My MRTG is showing 2 of the Plesk servers giving the problem but the NOC guys said it the Cpanel that causing the problem.
I tried logging in to both servers but could not find what's the cause for this.
It's been a while since I've posted in this area of the forum, but was just wondering if by now someone or some company has developed a script/software that you can install on your server that will tell you the exact area where the high CPU load is coming from, such as from someone sending email, a certain users account, and etc?
Most times, you have to be mointoring your server at the time of the high load to be exactly sure of what is causing the high load, so, I'm talking about something that will email you right on the spot of the high load in case you are away and will know what caused it.
Most I've seen only tell you that you have a high load, but don't tell you what exactly caused it.
How to find what Perl script is causing high CPU usage?
I logged into my CentOS box tonight and notice after viewing the output of the "top" command that there are the following lines of output that have me concerned. What command can I use to find the source of the Perl script that is causing the CPU spike?
It seems it's been running for some time too. PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 30120 apache 25 0 6908 4544 1216 R 99.8 0.2 13416:07 perl 30654 apache 25 0 8028 4508 1212 R 99.4 0.2 13417:22 perl 21273 apache 25 0 7584 4528 1200 R 98.1 0.2 2225:12 perl 20516 apache 25 0 6760 4540 1232 R 96.8 0.2 2221:30 perl
The main function of my dedicated server is the vBulletin forum that we run.
Ever since I had bastille firewall installed and configured on my server we have been getting regular vBulletin errors.
Everything runs pretty smoothly most of the time, we can carry lots of connections without any issues, server has plenty of free RAM and CPU is never overloaded even during peak hours. I have been told bastille firewall (iptables) is configured correctly, all ports that need to be open are open.
But about twice per day we have a MySQL "disconnect" that lasts about 30-60 seconds per incident. For example we will start getting the following message via e-mail, and it will amount to 20-30 e-mails like this during the 30-60 second incident. After that, the e-mails and problems will go away, until next time.
vBulletin Database Error! mysql_connect(): Can't connect to MySQL server on 'sql.datacolony.com' (4) /hsphere/local/home/site/site.com/forum/includes/class_core.php on line 274 MySQL Error : Error Number : Date : Saturday, January 26th 2008 @ 07:53:05 PM Script : [url] Referrer : [url] IP Address : 92.3.190.54 Username : Classname : vb_database
The server is running CentOS 4.6, PHP 4.4.7, MySQL 5.0.45. The vBulletin software has been patched to the latest version. The vBulletin people are not sure why this could be happening.
Well we never believed that the firewall was causing the issue BUT it only started happening on the day the new firewall was installed. So we tried stopping the firewall for a full week and the MySQL database errors stopped happening, completely. So I'm convinced the problem is the firewall blocking MySQL connections but I can't figure out why?
There are no entries in the MySQL .err logs pertaining to this. Can't figure this out, any help or ideas is appreciated since the people who installed the firewall are not sure either.
I hosted one aardvark topsite @ PluginSpace.com which consist of high traffics (3500+ unique visitors/day). I'm facing max_users_connection for quite number of times and I also have complain to my hosting but I still get no luck, they fix my problem for a short period and the problem appear again after some time.
I rent myself a dedicated server with 320GB Space and 3400GB Bandwith and 2GB RAM. Everytime I manage to get a lot of traffics to my topsite, maybe 100+ online users at a time, tracked by freeonlineusers[dot]com. I'll got these errors. Can anyone tell me what can I do? Can anyone tell me more about how much visitors can a normal dedicated server handle? Is it my script problem or my server problem?
(ERROR WILL POST IN NEXT POST)
can the dedicated server stand many visitors or suppose a normal dedicated server can handle so many visitors or my hosting maintenance is poor?
I have a site that is eating up my server resources and need to know what the best solution for this is. I'm thinking of getting another server just for mysql but do not know what specs the server should be to handle the current traffic/database load and have the site run smoothly without slowing down to a snail's pace.
An alternative is to get another server just for the videos being served and leave the database and html on the current server. This is where I'm stuck and don't know what route to take with this.
I've attached screenshots of top and bandwidth usage per day. Hopefully with this information you could tell me if I need another server or if there are any things I can do to the current server to help things move faster.
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND 7775 root 15 0 1344 1344 916 S 0.2 0.0 0:02 0 top 14220 root 15 0 1216 1216 920 R 0.2 0.0 0:00 1 top -c
I 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
I need some help my CPU (Intel Core 2 Duo 1.7 Ghz) always gets high usage from mysql sometimes its using 95% cpu etc... How can I modify my.cnf to use less CPU and more ram since I have 3gb ram installed?
I have a server with two cPanel accounts, each running gallery2. Load has been high:
[url] [url] [url]
Sar output (done at a less busy time):
[url]
Hardware: 1 Intel 2.4 GHz 1066FSB - Conroe Xeon 3060 (Dual Core) 2 Generic 1024 MB DDR2 667 ECC 1 Maxtor 250GB SATA 7200RPM MaxLine Plus II 1 Unknown Onboard SATA 1 Dell Single socket 1067FSB - Quad Core Capable PowerEdge 840
Howmuch %iowait is too much?
Since it's using less than 1GB Swap, there's enough RAM?
How do I get more specific than httpd as the cause of the load. The gallery script is the only thing running so is the execution time of PHP the bottleneck? If PHP and apache are single threaded would a quad core reduce load? Does lighttpd run PHP faster?
Whenever I run the "top" command, I see that some of the user "mysql" records always have very high CPU usage, anywhere from 20-80% on average (I've seen 100%). There are multiple records that show this, which means that the CPU is always above 100%. My cacti monitoring account also shows messed up CPU statistics.
I am not too knowledgeable with servers, but I believe that this is a problem. How much CPU usage is a mysqld command supposed to use on average? Here is a copy of the top part of a sample top command:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 21663 mysql 24 6 63032 30m 3004 R 66 1.5 38:24.06 mysqld 28483 mysql 24 6 63032 30m 3004 R 66 1.5 35:52.86 mysqld 20538 mysql 22 6 63032 30m 3004 S 15 1.5 40:29.74 mysqld 20837 mysql 24 6 63032 30m 3004 R 15 1.5 13:44.15 mysqld 1209 mysql 22 6 63032 30m 3004 S 11 1.5 32:46.26 mysqld 20591 mysql 22 6 63032 30m 3004 S 11 1.5 40:02.76 mysqld 20592 mysql 23 6 63032 30m 3004 S 6 1.5 40:48.29 mysqld 15301 apache 19 4 29928 15m 3608 S 3 0.8 0:00.60 httpd 15317 apache 19 4 28380 14m 3640 S 3 0.7 0:00.58 httpd 15249 apache 19 4 28612 14m 3732 S 1 0.7 0:01.49 httpd 15349 apache 19 4 28876 14m 3324 S 1 0.7 0:00.10 httpd 15319 apache 19 4 28368 14m 3528 S 1 0.7 0:00.34 httpd 15334 apache 19 4 28612 14m 3404 S 1 0.7 0:00.35 httpd 12343 root 15 0 2324 1088 804 R 1 0.1 0:03.31 top 15336 apache 19 4 28876 14m 3500 S 0 0.7 0:00.63 httpd 1 root 15 0 2064 588 508 S 0 0.0 1:01.28 init 2 root RT -5 0 0 0 S 0 0.0 0:09.39 migration/0 3 root 34 19 0 0 0 S 0 0.0 0:01.25 ksoftirqd/0 4 root RT -5 0 0 0 S 0 0.0 0:00.00 watchdog/0 =================================
I told my system manager about the cpu problem, and he said that he installed the cpulimit function on my server. I'm not sure if this is a good solution.
just noticed mysql cpu usage has shot up from its usual 5-10% cpu to 30%+. domains are as normal and no extraordinary php processes.
any way i can tell what users are most heavily running mysql? (as in number queries / slow queries etc?) i know mysqladmin processlist but that only gives me a snapshot, whereas i want to see over 24 hours, who used mysql the most...
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 ...........
MySQL 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?
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...
-- 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'.
I was wondering if anyone has had luck with 3ware 9650's in a RAID 10 to help bring down iowait?
I currently have a VPS node that see's spikes in iowait and i'd like to bring it down. I've been considering getting a 9650 in a JBOD setup (so then I don't need to reinstall the whole node) with a battery backup to enable the cache.
My two questions are:
- In JBOD, does the write cache enable at all? - Would the write cache help bring down the iowait much?
Drive wise we have 4 x 500GB drives in a raid10 (soft for now).
