High Loads Due To MYSQL

May 11, 2007

Recently, we've been getting a lot of high loads (every 30 mins or so..) This is what shows in the warning email that we receive:

---------------------------------
12.0 04:31:35 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/swh.sellwebhost.com.pid --skip-external-locking
----------------------------------

We are using whm.. Any idea what might be causing this?

View 12 Replies


ADVERTISEMENT

Very High Loads

Dec 8, 2008

I have two vps's n ifferent hardware, after my hoster performed an update to node I am now seeing high loads on both vps's

When I reboot the problem goes for a while

My servers host very little websites and little activity and normally run fine

I have notice a process "unhide" running, what is this and where should I start looking for this issue

[root@vps ~]# top
top - 18:47:59 up 1 day, 6:51, 1 user, load average: 1.96, 1.95, 1.92
Tasks: 62 total, 2 running, 60 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.0% us, 2.8% sy, 1.1% ni, 96.0% id, 0.0% wa, 0.0% hi, 0.0% si
Mem: 1048576k total, 143908k used, 904668k free, 0k buffers
Swap: 0k total, 0k used, 0k free, 0k cached
Unknown command - try 'h' for help
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
23980 root 26 10 1872 876 316 S 2.0 0.1 4:40.86 unhide
1 root 15 0 1644 568 484 S 0.0 0.1 0:03.76 init
1982 root 17 0 6412 1828 1124 S 0.0 0.2 0:00.00 crond
1985 root 18 0 2120 900 784 S 0.0 0.1 0:00.00 run-parts
3228 root 18 0 3252 1028 896 S 0.0 0.1 0:00.00 01-rkhunter
3229 root 18 0 1888 548 460 S 0.0 0.1 0:00.00 awk
3665 apache 15 0 39700 18m 6036 S 0.0 1.8 0:05.14 httpd
5771 apache 15 0 39540 16m 4516 S 0.0 1.6 0:01.14 httpd
7837 root 26 10 11320 9272 976 S 0.0 0.9 0:07.64 rkhunter
9958 apache 15 0 39608 18m 6024 S 0.0 1.8 0:02.84 httpd
11611 root 15 0 8584 2532 1964 S 0.0 0.2 0:00.01 sshd
11831 root 16 0 6144 476 324 S 0.0 0.0 0:00.00 vzctl
11836 root 16 0 2184 1264 1048 S 0.0 0.1 0:00.00 bash
13789 root 16 0 1548 528 436 S 0.0 0.1 0:00.38 syslogd
13794 root 18 0 1484 368 308 S 0.0 0.0 0:00.00 klogd
14022 apache 15 0 39432 15m 3868 S 0.0 1.6 0:00.72 httpd
17612 root 18 0 4056 1052 756 S 0.0 0.1 0:00.00 sshd

View 4 Replies View Related

High Server Loads ...

May 6, 2008

I have a Quad Core Xeon server with 2 GB RAM. The server has some high traffic websites & at peak hours (4-5 hrs a day) the server load remains 10.0 to 50.0.

The sites on the server load very slow at these times. Here's the top result during heavy load

[url]

View 5 Replies View Related

Extremly High Loads And CPU %

Jan 2, 2008

I normally don't like too bother others with my problems but this time It seems I have no choice since I spent the whole afternoon trying to find out what the problem was and couldn't solve it "entirely".

Here's some hardware info:
Processor Information Processor #1 Vendor: GenuineIntel
Processor #1 Name: Intel(R) Xeon(R) CPU 3050 @ 2.13GHz
Processor #1 speed: 2133.418 MHz
Processor #1 cache size: 2048 KB

Processor #2 Vendor: GenuineIntel
Processor #2 Name: Intel(R) Xeon(R) CPU 3050 @ 2.13GHz
Processor #2 speed: 2133.418 MHz
Processor #2 cache size: 2048 KB

Memory Information Memory: 1031324k/1048320k available (2182k kernel code, 16308k reserved, 914k data, 240k init, 130816k highmem)

System Information Linux **************** 2.6.20-1.2320.fc5smp #1 SMP Tue Jun 12 19:40:16 EDT 2007 i686 i686 i386 GNU/Linux

Running Fedora Core 5 and working for over half year with almost no major problems, but today something totally weird just happened...

After waking up I logged to the server and realized how slow it was, I went to see more about that and for my surprise the loading was around 8 so I went to the mail queue to see if there were a few thousand mails, there were 16 mails... So I went to the see CPU % processes at WHM and I was stunted to see around 30 processes allegedly using over 11500% of the CPU or more!

My first thought was that cPanel smoked something before releasing a last update and I didn't realized it immediately so I downgraded from Release to Stable.
Nothing happened and I lost my time with this overheated overloaded and over-everything and I didn't changed any configuration.

I changed many old configs that... I don't know, might crashed with something that might changed... I was trying and looking for anything.

The load was getting up instead of down, it went up to around 60.

The final conclusion is that whenever I turn the MySQL on I got overwhelming loads otherwise the other services "only" give me around 1.5 of load but still very unstable.

I'm getting a little tired so my explanation may be a little confusing, sorry about that.

Anybody knows what this is? Did this ever happened to you? Extremely high loads without an obvious reason? If anyone could help me I would be thankful for that, I'm getting kind of desperate.

View 6 Replies View Related

High Server Loads

May 3, 2007

We are having a problem with our Linux dedicated server. At time we are getting high server loads witch is causing the server to go down and reboot. This problem has been going on for over a month now and still liquid web can not tell me what is the cause of the high loads. We have upgraded our RAM to 2 GB. This look to fix the problem for the first week but now we are back to having high loads and downtime.

I'm not a IT person so do not know how to find the problem. Can anyone tell me what I can do the locate the problem. I do know how to access and do some things in the root but do not know enough to find and fix this issue.

View 9 Replies View Related

Memory Loads Wierdly High

Mar 25, 2009

Two of my VPS's with a company are now showing very high memory loads, even after a reboot, both server are located on different nodes and they both shot up to high ram loads at the same time today(9amGMT) ....

View 4 Replies View Related

High Loads At Exactly The Same Time Everyday

Apr 30, 2009

has anyone else noticed an unusually high load occurring from 9 till 11 EEST time on their servers?

I've been getting high loads everyday this week at exactly these times.

I'm thinking that this is a crawler/bot that is causing the loads, i've already installed dos deflate and there hasn't been more than 100 connections from the same ip, so I'm ruling out any DOS attack ... besides, what kind of attack only takes place for 2 hours a day at exactly the same time?

I'm on a cpanel system and would like to know which logs to take a look at to determine the cause.

View 14 Replies View Related

Vps Tips, Apache High Loads

Apr 6, 2007

I currently have a vps server from GIGEServers, running at 512mb of dedicated and 1024mb spike it seems like apache is using alot of resources, does anyone know of a less hog, or tips on tweaking it, and the same for mysql server any tips on tweaking.

View 14 Replies View Related

Server Optimization Help Request - VERY High Sudden Loads

Mar 3, 2007

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

---------------------------------------------------------

View 3 Replies View Related

Server High Load/high Mysql Usage

Jul 2, 2009

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.

My current server stats:

Dual Xeon 5130
4GB RAM
250GB
50 Mbit/sec
CentOS 3.9

Website traffic stats:

15,000 visitors/day
150,000 pageviews/day
Serving videos

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.

View 13 Replies View Related

MYSQL High Load!

Aug 23, 2007

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?

View 14 Replies View Related

High Load From Mysql

Mar 11, 2008

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?

View 11 Replies View Related

High MySQL Load

Feb 15, 2007

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.

View 7 Replies View Related

MySql Die With High Load

Oct 17, 2007

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)

View 9 Replies View Related

MySQL High CPU Usage Put On RAM

Jan 30, 2007

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?

here is my.cnf

Code:
[mysqld]
back_log = 5
skip-innodb
max_connections = 400
key_buffer = 190M
myisam_sort_buffer_size = 156M
join_buffer_size = 4M
read_buffer_size = 4M
sort_buffer_size = 5M
table_cache = 1024
record_buffer = 1024
thread_concurrency = 2
long_query_time = 1
thread_cache = 1024
thread_cache_size = 992025
wait_timeout = 13
connect_timeout = 5
tmp_table_size = 64M
max_heap_table_size = 16M
max_allowed_packet = 16M
max_connect_errors = 500
read_rnd_buffer_size = 1024
bulk_insert_buffer_size = 32M
query_cache_limit = 1M
query_cache_size = 64M
query_cache_type = 1
query_prealloc_size = 163840
query_alloc_block_size = 32768
low_priority_updates=1
default-storage-engine = MyISAM

[mysqld_safe]
nice = -2
open_files_limit = 4096

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 32M
write_buffer = 32M

View 1 Replies View Related

MySQL Server Uage Very High

Jun 29, 2009

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:

=========

top - 06:54:50 up 92 days, 5:32, 1 user, load average: 3.82, 3.89, 4.24
Tasks: 181 total, 4 running, 176 sleeping, 0 stopped, 1 zombie
Cpu(s): 0.2%us, 18.6%sy, 80.9%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.3%si, 0.0%st
Mem: 2075600k total, 1905400k used, 170200k free, 331912k buffers
Swap: 4096564k total, 104k used, 4096460k free, 1187304k cached

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.

View 2 Replies View Related

Mysql Usage: High CPU %, How Do I Find Out Who's Using What

Aug 3, 2008

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...

View 6 Replies View Related

High Mysql Load After Upgrade

Mar 14, 2008

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 Related

Mysql Causing High Iowait

Apr 12, 2008

Server is Xeon 3060, 3GB Ram, 250GB SATA-II HDD. I followed the recommendations made by mysqlprimer tuner script.

top - 11:54:33 up 237 days, 15:01, 1 user, load average: 6.06, 2.80, 1.65
Tasks: 133 total, 2 running, 131 sleeping, 0 stopped, 0 zombie
Cpu(s): 13.0% us, 4.9% sy, 0.0% ni, 24.3% id, 57.8% wa, 0.0% hi, 0.0% si
Mem: 3114144k total, 3096468k used, 17676k free, 30220k buffers
Swap: 2096440k total, 32348k used, 2064092k free, 2515412k cached

PID USER PR NI %CPU TIME+ %MEM VIRT RES SHR S COMMAND
8610 nobody 16 0 28 1:37.73 0.7 78908 20m 16m S lsphp4
10520 nobody 16 0 3 0:01.97 0.4 78788 10m 6884 S lsphp4
26474 nobody 15 0 2 15:04.07 0.4 14196 11m 3216 S lshttpd
28275 mysql 22 6 1 131:28.83 7.4 733m 224m 2700 S /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pi

Code:
[mysqld]
max_connections=500
max_user_connections=300
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=64M
query_cache_type=1
interactive_timeout=60
wait_timeout=60
connect_timeout=6
thread_cache_size=128
key_buffer_size=640M
max_allowed_packet=16M
table_cache=2048
join_buffer=1M
sort_buffer=2M
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
safe-show-database
max_connect_errors=10
thread_concurrency=2
myisam_sort_buffer_size=64M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#log_slow_queries=/var/log/mysql/mysql_slow-queries.log
long_query_time=3
tmp_table_size=32M
max_heap_table_size=32M
#log-queries-not-using-indexes=/var/log/mysql/mysql_queries_no-indexes.log
join_buffer_size=2M
low_priority_updates=1
read_rnd_buffer_size=2M

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
var/lib/mysql/mysql.pid
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer=32M
sort_buffer=32M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=32M
sort_buffer=32M
read_buffer=16M
write_buffer=16M

[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.

View 12 Replies View Related

MySQL High Server Load

Dec 18, 2008

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?

View 8 Replies View Related

How To Resolve High MySQL Usage

Apr 20, 2008

on my server i have 104 accounts and the MySQL Usage for th cpus is 25.23
like this pic [url] is that danger? and how can i resolve it?

View 6 Replies View Related

HIGH LOAD Because Of MySQL. [merged]

Oct 24, 2007

my 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 |

View 13 Replies View Related

High Load And Mysql Errors

Jun 17, 2007

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

View 1 Replies View Related

Mysql High CPU Load - Totally Stumped

Jul 26, 2009

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 |
+-----------------------------------+----------+

View 14 Replies View Related

High Server Load ... With Mysql Optimization

Apr 21, 2008

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>

View 11 Replies View Related

Server Setup For MySQL + High Bandwidth

Jun 15, 2008

if I could get some input on this. Currently I'm running an Opteron 246, 2GB RAM with 2TB bandwidth/mo. I run a single site which is currently 99% HTML, along with an invision forum which is fairly popular. I get about 300-400k pageviews per day.

Right now the server is able to handle this stuff without much of a problem, and the average CPU load is between .5 and 3 (after a TON of tweaking!) - the only exception is when the forum db is being backed up/optimized, which isn't a huge problem since it happens during the off hours.

The problem is that I'm running out of bandwidth quickly, and need to come up with some kind of solution soon. My current provider offers bandwidth at $1/GB, which to me seems crazy compared to other providers, so that's not really an option.

I was thinking about switching to a more affordable provider and upgrading hardware, but I'm not sure what the best approach would be. I'm in the process of moving my HTML content over to a PHP-powered CMS, so I need to make sure whatever I do can handle that... What might be a decent setup for a site like mine which consumes a lot of bandwidth and will probably need more CPU power in the near future for PHP/MySQL stuff?

View 8 Replies View Related

Apache + MYSQL Optimization For High End Server

Jun 24, 2008

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 ....

View 11 Replies View Related

Automatically Restart Mysql After High Server Load

Jan 29, 2009

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 Related

Mysql Server Hanging On A High Traffic Site

Feb 28, 2008

My video sharing site has high traffic, alexa rate:3,000

My site has 2 servers to split the load. 2 servers share a mysql server. Using rrdns to load the balance.

Server A running mysql 5.0,lighttpd
Server B running lighttpd.
Server B connect to A's mysql database.

During peak time. B can not connect to A's mysql server. It says server not responding. But A still running fine.
When I check mysql log file.
/usr/libexec/mysqld: Forcing close of thread .....

And when run top, the load average is 20.

The spec of Server A
Intel(R) Xeon(TM) CPU 3.06GHz dual core.
2G Ram.

Here is the my.cnf

Quote:

[mysqld]
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
max_connections = 1000
wait_timeout=60
connect_timeout=10
interactive_timeout=120
join_buffer_size=1M
query_cache_size=128M
query_cache_limit=2M
max_allowed_packet=16M
table_cache=1024
sort_buffer_size=2M
read_buffer_size=2M

My question, do I need another maching C to run lighttpd, and just keep mysql on A.
Or I can do some mysql optimization on A.

Also, if my site keeps going, can I have 1 mysql server and 5 http servers?

View 11 Replies View Related

Server Optimisation Request/mysql High Memory

Jul 2, 2007

cpu speed/type single or dual cpus): Intel Xeon 2x3.6
how much memory installed: 2048MB
hard drive type/configuration: 2x160GB SATA
linux distributor or windows version: CentOS 4.5 x86_64
apache/IIS version: apache 1.3.37
PHP version: php 4.4.7
MySQL version: 4.1.22-standard

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
19017 teknoloj 16 0 0 0 0 Z 27 0.0 0:00.93 php <defunct>
17751 mysql 15 0 241m 55m 3148 S 25 2.8 0:53.73 mysqld
19025 root 18 0 16204 6104 1392 D 6 0.3 0:00.17 cpcpan_check_in
18968 sohbetgo 16 0 44596 7540 3556 S 5 0.4 0:00.44 php
18552 nobody 15 0 32636 4208 1204 S 2 0.2 0:00.28 httpd
18808 resimsit 17 0 46944 9960 3616 R 1 0.5 0:00.23 php
18896 resimsit 16 0 46944 9984 3616 D 1 0.5 0:00.08 php
18921 resimsit 17 0 46404 9444 3572 R 1 0.5 0:00.09 php
18819 nobody 17 0 32644 4176 1192 S 1 0.2 0:00.06 httpd
18868 haylazt 16 0 0 0 0 Z 1 0.0 0:00.48 php <defunct>
18871 nobody 15 0 32504 4056 1148 S 1 0.2 0:00.06 httpd
18892 root 16 0 6416 1180 756 R 1 0.1 0:00.24 top
18978 nobody 16 0 32504 4028 1132 S 1 0.2 0:00.02 httpd
11 root 5 -10 0 0 0 S 0 0.0 0:24.56 events/1
2638 root 0 -20 0 0 0 S 0 0.0 0:16.96 loop0
2981 named 18 0 88016 2328 1152 S 0 0.1 8:44.12 named
3592 mailman 16 0 50316 2320 1828 S 0 0.1 0:00.78 python2.4

my.cnf

[mysqld]
safe-show-database
skip-innodb
max_connections = 500
key_buffer = 80M
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 = 25
connect_timeout = 10
tmp_table_size = 128M
max_heap_table_size = 128M
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 = 96M
query_cache_type = 1
query_prealloc_size = 16384
query_alloc_block_size = 32768
ft_min_word_len=3
ft_max_word_len=25

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
ft_min_word_len=3
ft_max_word_len=25

[mysqlhotcopy]
interactive-timeout

MySQL 4.1.22-standard uptime 0 0:39:9 Sat Jun 30 21:33:29 2007

__ Key __________________________________________________ _______________
Buffer used 19.34M of 80.00M %Used: 24.17
Current 33.70M %Usage: 42.12
Write hit 32.57%
Read hit 98.45%

__ Questions __________________________________________________ _________
Total 63.26k 26.9/s
Slow 2 0.0/s %Total: 0.00 %DMS: 0.01
DMS 32.14k 13.7/s 50.80

__ Table Locks __________________________________________________ _______
Waited 40 0.0/s %Total: 0.08
Immediate 50.58k 21.5/s

__ Tables __________________________________________________ ____________
Open 486 of 1800 %Cache: 27.00
Opened 702 0.3/s

__ Connections __________________________________________________ _______
Max used 12 of 500 %Max: 2.40
Total 4.33k 1.8/s

__ Created Temp __________________________________________________ ______
Disk table 26 0.0/s
Table 2.23k 1.0/s
File 428 0.2/s


6-7 vbulletin sites-vbseo and other scripts online avarage;
120 registered user ve 500+ unregistered user
cookie timeout : 7200
unique hit : 15000-20000

KeepAlive ON
MaxKeepAliveRequests 100
KeepAliveTimeout 2
MinSpareServers 10
MaxSpareServers 15
StartServers 10
MaxClients 180
Maxrequestsperchild value 1000

[root@server ~]# uname -a

Linux server.xxxxx 2.6.9-55.ELsmp #1 SMP Wed May 2 14:04:42 EDT 2007 x86_64 x86_64 x86_64 GNU/Linux

[root@server ~]# ulimit -aH

core file size (blocks, -c) 1000000
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
pending signals (-i) 1024
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 4096
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 14335
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited

[root@server ~]# cat /proc/cpuinfo

processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 3.60GHz
stepping : 10
cpu MHz : 3600.322
cache size : 2048 KB
physical id : 0
siblings : 2
core id : 0
cpu cores : 1
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm pni monitor ds_cpl est tm2 cid cx16 xtpr
bogomips : 7207.08
clflush size : 64
cache_alignment : 128
address sizes : 36 bits physical, 48 bits virtual
power management:

processor : 1
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 3.60GHz
stepping : 10
cpu MHz : 3600.322
cache size : 2048 KB
physical id : 3
siblings : 2
core id : 3
cpu cores : 1
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm pni monitor ds_cpl est tm2 cid cx16 xtpr
bogomips : 7199.29
clflush size : 64
cache_alignment : 128
address sizes : 36 bits physical, 48 bits virtual
power management:

processor : 2
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 3.60GHz
stepping : 10
cpu MHz : 3600.322
cache size : 2048 KB
physical id : 0
siblings : 2
core id : 0
cpu cores : 1
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm pni monitor ds_cpl est tm2 cid cx16 xtpr
bogomips : 7509.03
clflush size : 64
cache_alignment : 128
address sizes : 36 bits physical, 48 bits virtual
power management:

processor : 3
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 3.60GHz
stepping : 10
cpu MHz : 3600.322
cache size : 2048 KB
physical id : 3
siblings : 2
core id : 3
cpu cores : 1
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm pni monitor ds_cpl est tm2 cid cx16 xtpr
bogomips : 7199.26
clflush size : 64
cache_alignment : 128
address sizes : 36 bits physical, 48 bits virtual
power management:

View 4 Replies View Related

Mysql Deleted Several Tables Under High Server Load

Apr 27, 2007

we have mysql 5 setup and this morning at around 10:07 tables started disappearing as they were being accessed by different clients.

Databases that had 40 tables now had 30, etc. Only the tables that were attempted to be accessed were gone. This is the first time something like this has happened.

The following output was given:

This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware.

We will try our best to scrape up some info that will hopefully help diagnose the
problem, but since we have already crashed, something is definitely wrong and this may fail.

key_buffer_size=8388600
read_buffer_size=131072
max_used_connections=208
max_connections=500
threads_connected=156
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1096188 K
bytes of memory

Hope that's ok; if not, decrease some variables in the equation.

thd=0xaf82930
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...

Cannot determine thread, fp=0xb143932c, backtrace may not be correct.

Stack range sanity check OK, backtrace follows:
0x816b1a0
0xaf1898
0x20
0x81ac49d
0x8182914
0x8189010
0x8189df1
0x818a738
0x818ae5c
0xaeb371
0x9c4ffe
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read [url]and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0xaf36620 = SELECT * FROM `sessions` WHERE `PHPSESSID` = '5e6775cd3c6f187d8c575127ba73be19'
thd->thread_id=113407
The manual page at [url]contains
information that should help you find out what is causing the crash.
mysqld: my_new.cc:51: int __cxa_pure_virtual(): Assertion `"Pure virtual method called." == "Aborted"' failed.

Number of processes running now: 0
070427 10:07:49 mysqld restarted
070427 10:07:50 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
070427 10:07:53 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 227822203.
InnoDB: Doing recovery: scanned up to log sequence number 0 227822203
070427 10:07:53 InnoDB: Started; log sequence number 0 227822203
070427 10:07:54 [Note] /usr/sbin/mysqld: ready for connections.

View 0 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved