Aussie thanks for that, I should have done this a long time ago, it has dropped my memory usage from 10% to 1% for mysql.

I hope the collaboration of minds and those that posted in this thread really helped you.

Maybe I am wrong but my goal is to get mysql to use MORE memory less harddrive. I am not sure using less ram is a good thing unless your starving other applications. My mysql runs a search engine so we want to minimize calls to the HD as much as possible.

Ram is faster by a factor of 500x's than hd seeks. Useing all my ram is important to me.

Shouldn't it be to everyone?

You are right about memory; the box I optimized was running about 60% and lately 73% average memory usage, the optimization cut the server load as well. But it did also cut memory usage as well for me. I know there are some issues where the stats show a lot of memory use (thread stacks issue) but even checking swap it appeared to be using a good amount of memory. I do understand the main issue is server load, which as I mentioned above it did cut down.

My issues were probably more of not looking and optimizing towards the entire system.

How to optimize my mysql? Server: Dual Xeon 2.8Ghz with 2GB RAM. I use MySQL 3x. Big thanks. my.cnf now:

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable = connect_timeout=15
set-variable = interactive_timeout=100
set-variable = join_buffer_size=1M
set-variable = key_buffer=128M
set-variable = max_allowed_packet=16M
set-variable = max_connections=500
set-variable = max_connect_errors=10
set-variable = myisam_sort_buffer_size=64M
set-variable = table_cache=1024
set-variable = thread_cache_size=100
set-variable = thread_concurrency=4
set-variable = wait_timeout=300
skip-innodb

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 16M
sort_buffer_size = 8M
read_buffer_size = 4M
write_buffer_size = 4M

[myisamchk]
key_buffer = 16M
sort_buffer_size = 8M
read_buffer_size = 4M
write_buffer_size = 4M

[mysqlhotcopy]
interactive-timeout

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

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
set-variable = open_files_limit = 8192

Cheers for the great Howto, Aussie. I upgraded to MySQL 4.0.21 the other night and used your my.cnf file.

Ran into a BIT of a problem in that although my very busy Invisionboard was working VERY fast, MySQL was running away with swap - too many mysqld processes running - each allegedly using 50M of memory (and THAT was when the forum was very quiet!) See below!

So I changed Key_buffer to 64 (from 16) and so far, so good. It's a 1 Gb server running virtually nothing EXCEPT a very busy Invisionboard. Any other suggestions from anyone on ways of optimising further for a busy forum?

Below is what TOP looked like before changing Key_buffer to 64M


1:04am up 1 day, 15:24, 1 user, load average: 0.05, 0.04, 0.03
135 processes: 133 sleeping, 2 running, 0 zombie, 0 stopped
CPU states: 0.1% user, 0.9% system, 0.0% nice, 98.8% idle
Mem: 1023128K av, 998320K used, 24808K free, 0K shrd, 33536K buff
Swap: 2048276K av, 182880K used, 1865396K free 523356K cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
5791 apache 9 0 70868 57M 2956 S 0.0 5.7 12:40 httpd
5794 apache 9 0 56852 51M 2964 S 0.0 5.2 12:08 httpd
5789 apache 17 0 59368 50M 2936 S 0.0 5.0 12:21 httpd
3777 mysql 9 0 53088 50M 1220 S 0.0 5.0 0:10 mysqld
3779 mysql 9 0 53088 50M 1220 S 0.0 5.0 0:00 mysqld
3780 mysql 9 0 53088 50M 1220 S 0.0 5.0 0:25 mysqld
3789 mysql 9 0 53088 50M 1220 S 0.0 5.0 5:05 mysqld
3823 mysql 9 0 53088 50M 1220 S 0.0 5.0 5:19 mysqld
3828 mysql 9 0 53088 50M 1220 S 0.0 5.0 5:13 mysqld
3918 mysql 9 0 53088 50M 1220 S 0.0 5.0 4:56 mysqld
4040 mysql 9 0 53088 50M 1220 S 0.0 5.0 4:59 mysqld
4041 mysql 9 0 53088 50M 1220 S 0.0 5.0 5:04 mysqld
9584 mysql 9 0 53088 50M 1220 S 0.0 5.0 1:17 mysqld
9585 mysql 9 0 53088 50M 1220 S 0.0 5.0 1:24 mysqld
9586 mysql 9 0 53088 50M 1220 S 0.0 5.0 1:20 mysqld
9588 mysql 9 0 53088 50M 1220 S 0.0 5.0 1:31 mysqld
9591 mysql 9 0 53088 50M 1220 S 0.0 5.0 1:28 mysqld
9596 mysql 9 0 53088 50M 1220 S 0.0 5.0 1:15 mysqld
9597 mysql 9 0 53088 50M 1220 S 0.0 5.0 1:31 mysqld
9598 mysql 9 0 53088 50M 1220 S 0.0 5.0 1:23 mysqld
9599 mysql 9 0 53088 50M 1220 S 0.0 5.0 1:17 mysqld
9600 mysql 9 0 53088 50M 1220 S 0.0 5.0 1:19 mysqld
9602 mysql 9 0 53088 50M 1220 S 0.0 5.0 1:14 mysqld
9604 mysql 9 0 53088 50M 1220 S 0.0 5.0 1:22 mysqld
9605 mysql 9 0 53088 50M 1220 S 0.0 5.0 1:24 mysqld
9606 mysql 9 0 53088 50M 1220 S 0.0 5.0 1:22 mysqld
9609 mysql 9 0 53088 50M 1220 S 0.0 5.0 1:21 mysqld
10367 mysql 9 0 53088 50M 1220 S 0.0 5.0 1:03 mysqld
10368 mysql 9 0 53088 50M 1220 S 0.0 5.0 1:07 mysqld
12973 mysql 9 0 53088 50M 1220 S 0.0 5.0 0:31 mysqld
12974 mysql 9 0 53088 50M 1220 S 0.0 5.0 0:35 mysqld
12976 mysql 9 0 53088 50M 1220 S 0.0 5.0 0:39 mysqld
12977 mysql 9 0 53088 50M 1220 S 0.0 5.0 0:35 mysqld
12978 mysql 9 0 53088 50M 1220 S 0.0 5.0 0:32 mysqld
12979 mysql 9 0 53088 50M 1220 S 0.0 5.0 0:29 mysqld
12982 mysql 9 0 53088 50M 1220 S 0.0 5.0 0:30 mysqld
12987 mysql 9 0 53088 50M 1220 S 0.0 5.0 0:30 mysqld
12988 mysql 9 0 53088 50M 1220 S 0.0 5.0 0:30 mysqld
12989 mysql 9 0 53088 50M 1220 S 0.0 5.0 0:32 mysqld
12990 mysql 9 0 53088 50M 1220 S 0.0 5.0 0:32 mysqld
12999 mysql 9 0 53088 50M 1220 S 0.0 5.0 0:29 mysqld
13000 mysql 9 0 53088 50M 1220 S 0.0 5.0 0:32 mysqld
13001 mysql 9 0 53088 50M 1220 S 0.0 5.0 0:32 mysqld
13002 mysql 9 0 53088 50M 1220 S 0.0 5.0 0:29 mysqld
13003 mysql 9 0 53088 50M 1220 S 0.0 5.0 0:25 mysqld
13004 mysql 9 0 53088 50M 1220 S 0.0 5.0 0:34 mysqld
13005 mysql 9 0 53088 50M 1220 S 0.0 5.0 0:30 mysqld
13008 mysql 9 0 53088 50M 1220 S 0.0 5.0 0:33 mysqld
13010 mysql 9 0 53088 50M 1220 S 0.0 5.0 0:32 mysqld
13012 mysql 9 0 53088 50M 1220 S 0.0 5.0 0:35 mysqld
13014 mysql 9 0 53088 50M 1220 S 0.0 5.0 0:31 mysqld
13016 mysql 9 0 53088 50M 1220 S 0.0 5.0 0:40 mysqld
13018 mysql 9 0 53088 50M 1220 S 0.0 5.0 0:31 mysqld

Thanks aussie for what I think the most valuable howto.

saddleup I am having this *problem* of mysqld 74M+ each after upgrading MySQL. But after alot of panicking, I found that ALL mysqld processes are taking 50M not each of them. Above, you have 25 processes with 50M each. This is 2.5G (which you do not have - you have 1G).

Exactly. As I said on the other thread, I was panicking until I found this on the MySQL list:



"Linux implements threads by forking separate processes with shared address space. If you have 50 processes each with 20MB rss, it's still only using 20MB of memory.

Or - 25 threads SHOWING as using 50 MB each - but actually ONLY using 50 MB in total!

OR - as my server is showing at this moment: 45 MySQLD threads using 88 MB!

Mmmmm -

Last night I increased tmp_table_size to tmp_table_size=64M because Created_tmp_disk_tables was rather large.

With the forum very busy, top shows:


1:23pm up 13:43, 1 user, load average: 0.57, 0.74, 0.75
203 processes: 200 sleeping, 3 running, 0 zombie, 0 stopped
CPU states: 18.5% user, 3.7% system, 0.0% nice, 77.6% idle
Mem: 1023128K av, 967440K used, 55688K free, 0K shrd, 22204K buff
Swap: 2048276K av, 92140K used, 1956136K free 670612K cached

Previously, my free memory would have been MUCH lower at this time of day. Swap still higher than I'd like - but threads_created; treads_cached are nearly 3 times higher than they've ever been previously.
:confused:

If I could work out how to reduce swap used I think I'd be happy.

Can you help me PLEASE !! I have Dual Xeon with 2 GB RAM but my load is extensivly high

Here is top

23:46:42 up 20 days, 9:03, 2 users, load average: 2,37, 2,39, 2,74
189 processes: 188 sleeping, 1 running, 0 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 32,5% 0,0% 11,2% 0,0% 0,8% 10,6% 44,6%
cpu00 24,9% 0,0% 10,5% 0,0% 1,9% 10,7% 51,7%
cpu01 35,2% 0,0% 15,1% 0,3% 0,0% 10,7% 38,4%
cpu02 36,8% 0,0% 13,9% 0,0% 1,5% 6,3% 41,2%
cpu03 33,0% 0,0% 5,3% 0,0% 0,0% 14,5% 47,0%
Mem: 2055464k av, 1896248k used, 159216k free, 0k shrd, 50336k buff
1428196k actv, 242676k in_d, 26684k in_c
Swap: 2048276k av, 207500k used, 1840776k free 1226084k cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
17197 nobody 16 0 16076 15M 10100 S 6,4 0,7 0:02 0 httpd
17162 nobody 19 0 16148 15M 10040 S 5,2 0,7 0:05 1 httpd
13753 mysql 16 0 66476 61M 1228 S 5,1 3,0 2:16 1 mysqld
1591 radio 15 0 6408 4312 604 S 3,5 0,2 631:06 1 sc_serv
15408 mysql 16 0 66476 61M 1228 S 2,8 3,0 1:29 1 mysqld
13238 mysql 17 0 66476 61M 1228 S 2,5 3,0 2:45 3 mysqld
25553 root 16 0 26356 18M 1808 S 2,4 0,9 0:50 3 spamd
13406 mysql 16 0 66476 61M 1228 S 2,1 3,0 2:07 1 mysqld
13751 mysql 16 0 66476 61M 1228 S 2,1 3,0 2:03 1 mysqld
12646 mysql 16 0 66476 61M 1228 S 1,5 3,0 3:11 3 mysqld
17186 nobody 15 0 21968 20M 9980 S 1,4 1,0 0:02 2 httpd
17173 nobody 15 0 15828 14M 9668 S 1,1 0,7 0:05 0 httpd
17271 nobody 15 0 14608 13M 8764 S 1,0 0,6 0:02 3 httpd
17287 nobody 15 0 15356 14M 9812 S 1,0 0,7 0:00 0 httpd
17189 nobody 15 0 20140 19M 10128 S 0,9 0,9 0:02 3 httpd
17344 nobody 15 0 13924 12M 8336 S 0,9 0,6 0:01 1 httpd
21592 root 15 0 24452 17M 1800 S 0,8 0,8 2:25 0 spamd
13407 mysql 15 0 66476 61M 1228 S 0,1 3,0 2:55 0 mysqld
16296 root 15 0 1344 1344 844 R 0,1 0,0 0:09 2 top
16549 mailman 15 0 4172 4172 1376 S 0,1 0,2 0:00 2 python2
1 root 18 0 508 476 448 S 0,0 0,0 0:37 2 init
2 root RT 0 0 0 0 SW 0,0 0,0 0:00 0 migration/0
3 root RT 0 0 0 0 SW 0,0 0,0 0:00 1 migration/1
4 root RT 0 0 0 0 SW 0,0 0,0 0:00 2 migration/2
5 root RT 0 0 0 0 SW 0,0 0,0 0:00 3 migration/3
6 root 15 0 0 0 0 SW 0,0 0,0 0:03 3 keventd
7 root 34 19 0 0 0 SWN 0,0 0,0 0:00 0 ksoftirqd/0
8 root 34 19 0 0 0 SWN 0,0 0,0 0:00 1 ksoftirqd/1
9 root 34 19 0 0 0 SWN 0,0 0,0 0:00 2 ksoftirqd/2
10 root 34 19 0 0 0 SWN 0,0 0,0 0:00 3 ksoftirqd/3
13 root 15 0 0 0 0 SW 0,0 0,0 0:06 1 bdflush
11 root 15 0 0 0 0 SW 0,0 0,0 17:59 3 kswapd
12 root 15 0 0 0 0 SW 0,0 0,0 20:08 2 kscand
14 root 16 0 0 0 0 SW 0,0 0,0 5:51 2 kupdated
15 root 25 0 0 0 0 SW 0,0 0,0 0:00 2 mdrecoveryd
25 root 15 0 0 0 0 SW 0,0 0,0 15:30 0 kjournald
79 root 25 0 0 0 0 SW 0,0 0,0 0:00 1 khubd
601 root 15 0 0 0 0 SW 0,0 0,0 0:00 3 kjournald
602 root 15 0 0 0 0 SW 0,0 0,0 7:51 1 kjournald
1640 root 15 0 584 560 500 S 0,0 0,0 1:41 2 syslogd
1644 root 15 0 448 392 388 S 0,0 0,0 0:00 3 klogd




And here is my.cnf


[mysqld]
datadir=/var/lib/mysql
skip-locking
skip-innodb
max_allowed_packet=16M
max_connections=150
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
join_buffer=1M
key_buffer=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=1M
read_buffer_size=1M
max_connect_errors=10
thread_concurrency=4
log-slow-queries
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
myisam_sort_buffer_size=64M
log-bin


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

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates

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

[mysqlhotcopy]
interactive-timeout


What am i doing wrong that i have so big load ?? Any ideas ?? im confused

i think that your cache is not high enough.
I have also a Dual Xeon 2.4GHz 2Gb ram, with 200 processes, but my averange load is under 1.0 ...
Try my own my.conf, i tuned some time on it, and i'm pretty happy with the current config :

[mysqld]
socket=/home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock
datadir=/var/lib/mysql
max_connections=500
max_user_connections=250
key_buffer=128M
join_buffer=1M
myisam_sort_buffer_size=32M
join_buffer_size=2M
read_buffer_size=2M
sort_buffer_size=3M
read_rnd_buffer_size=1M
tmp_table_size=32M
table_cache=1024
record_buffer=1M
thread_cache_size=128
wait_timeout=10000
connect_timeout=100
max_allowed_packet=16M
max_connect_errors=10000
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
skip-innodb
skip-locking
thread_concurrency=2
interactive_timeout=30
log-bin
server-id=1

[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

[isamchk]
key_buffer=48M
sort_buffer=48M
read_buffer=16M

[mysqlhotcopy]
interactive-timeout

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

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock


Think to restart MySQL after this. Let us know if you have some results.
Regards
Jean

don't be affraid with the 128Mb cache, MySQL use only 1 time 128Mb, not on every session
Jean

With your config it looks now like this

00:11:58 up 20 days, 9:28, 2 users, load average: 1,43, 1,84, 2,18
178 processes: 175 sleeping, 3 running, 0 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 26,1% 0,0% 9,5% 0,1% 1,0% 7,3% 55,6%
cpu00 28,4% 0,0% 12,3% 0,3% 3,7% 6,7% 48,3%
cpu01 24,4% 0,0% 9,7% 0,3% 0,3% 5,9% 59,0%
cpu02 24,2% 0,0% 8,3% 0,0% 0,0% 8,1% 59,3%
cpu03 27,6% 0,0% 7,7% 0,0% 0,1% 8,3% 56,0%
Mem: 2055464k av, 1985592k used, 69872k free, 0k shrd, 49916k buff
1468348k actv, 282264k in_d, 32008k in_c
Swap: 2048276k av, 153304k used, 1894972k free 1292800k cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
17156 nobody 17 0 22788 21M 15740 S 8,9 1,0 0:25 0 httpd
18929 nobody 17 0 10544 9904 5412 R 8,8 0,4 0:01 3 httpd
1591 radio 15 0 6408 4420 604 S 2,1 0,2 631:37 2 sc_serv
18883 mysql 16 0 29300 28M 1444 S 1,7 1,4 0:05 3 mysqld
18913 mysql 15 0 29300 28M 1444 S 1,6 1,4 0:03 1 mysqld
18916 nobody 15 0 12880 11M 7220 S 1,6 0,5 0:00 1 httpd
18912 mysql 16 0 29300 28M 1444 R 1,5 1,4 0:06 0 mysqld
18380 nobody 15 0 22636 21M 12192 S 0,8 1,0 0:07 1 httpd
18889 mysql 16 0 29300 28M 1444 S 0,8 1,4 0:04 2 mysqld
17179 nobody 15 0 24388 23M 15036 S 0,7 1,1 0:24 0 httpd
17176 nobody 15 0 25516 24M 16928 S 0,5 1,2 0:21 1 httpd
18914 mysql 15 0 29300 28M 1444 S 0,5 1,4 0:01 0 mysqld
18931 mysql 15 0 29300 28M 1444 S 0,4 1,4 0:01 2 mysqld
18956 nobody 16 0 8900 8256 5492 S 0,4 0,4 0:00 3 httpd
17154 nobody 15 0 25376 24M 17488 S 0,3 1,2 0:20 2 httpd
17177 nobody 16 0 28544 27M 16560 D 0,3 1,3 0:21 3 httpd
18705 mazda 15 0 5656 5656 3056 S 0,3 0,2 0:03 0 cppop
18937 nobody 15 0 10312 9660 5728 S 0,3 0,4 0:00 1 httpd
17153 nobody 15 0 26572 25M 16340 S 0,2 1,2 0:21 0 httpd
17782 nobody 15 0 20628 19M 14104 S 0,2 0,9 0:24 1 httpd
18374 nobody 15 0 22804 21M 13980 S 0,2 1,0 0:09 1 httpd
18880 mysql 15 0 29300 28M 1444 S 0,2 1,4 0:06 2 mysqld
18940 nobody 15 0 8972 8324 5292 S 0,2 0,4 0:00 1 httpd
17160 nobody 15 0 22644 21M 15616 S 0,1 1,0 0:22 3 httpd
17178 nobody 15 0 26872 25M 17860 S 0,1 1,2 0:17 3 httpd
18933 mysql 15 0 29300 28M 1444 S 0,1 1,4 0:01 2 mysqld
18949 root 15 0 1268 1268 844 R 0,1 0,0 0:00 2 top
1 root 15 0 508 476 448 S 0,0 0,0 0:37 3 init
2 root RT 0 0 0 0 SW 0,0 0,0 0:00 0 migration/0
3 root RT 0 0 0 0 SW 0,0 0,0 0:00 1 migration/1
4 root RT 0 0 0 0 SW 0,0 0,0 0:00 2 migration/2

In poland its 12AM so i will see how it handles greater load hmm at 18-20 PM so i must wait and see but its all the time strange that u all have load bellow 1 and i have most of the time load above 2

And i think you have a mistake

thread_concurrency=2 shouldn't it be 4 ??

yes, that's correct. I disables the HT, so i put it back at 2.
You should use 4.
Let your server run some time, and you will see that the MySQL sessions will take more and more RAM, but it should stop at 128Mb.
Did you also tune Apache ?
If not, tell us what your're running (Ensim, Plesk, ...)
Jean

I have Cpanel and in apache i have mod gzip enabled and MaxKeepAlive On
MaxKeepAliveRequests 1000
MaxKeepAliveTimeout 10
Timeout 60
MaxClients 200

These are the only things i;ve changed in apache

Crank up your keybuffer too.

If you use a lot of php mysql very little html go ahead and dedicate some ram to it. I set mine to 750M.

Set some timeouts on your shoutcast server. That is what is bleeding you memory.

You shouldn't be using any swap but your using a ton of it. You have a massive memory leak in one of your programs.

hmmm what you mean about massive memory leak in program, what program ??

Strange things are happening, why my mysql processess are larger then 128 MB i use config which gave me rs-6422

root@server [/home/misiek]# top
07:41:22 up 20 days, 16:57, 1 user, load average: 1,15, 1,10, 0,83
144 processes: 140 sleeping, 4 running, 0 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 24,2% 0,0% 6,5% 0,0% 0,1% 1,6% 67,2%
cpu00 29,4% 0,0% 10,1% 0,3% 0,7% 2,3% 56,7%
cpu01 27,2% 0,0% 6,3% 0,0% 0,0% 1,3% 64,9%
cpu02 20,1% 0,0% 5,3% 0,0% 0,0% 1,1% 73,3%
cpu03 19,9% 0,0% 4,3% 0,0% 0,0% 1,7% 73,9%
Mem: 2055464k av, 2030124k used, 25340k free, 0k shrd, 81108k buff
1424132k actv, 270580k in_d, 31712k in_c
Swap: 2048276k av, 184520k used, 1863756k free 1265320k cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
19601 mysql 17 0 163M 150M 1372 S 8,6 7,5 2:38 3 mysqld
30362 nobody 17 0 19516 18M 13196 S 8,5 0,9 0:14 0 httpd
31193 nobody 17 0 10504 9680 5504 R 5,7 0,4 0:01 0 httpd
30359 nobody 16 0 19008 17M 12588 R 1,9 0,8 0:05 2 httpd
19478 mysql 16 0 163M 150M 1372 S 0,9 7,5 3:24 1 mysqld
24577 mysql 16 0 163M 150M 1372 S 0,9 7,5 1:16 0 mysqld
31187 nobody 15 0 12356 11M 7060 S 0,5 0,5 0:00 3 httpd
30356 nobody 15 0 21828 20M 11880 S 0,4 1,0 0:05 2 httpd
30441 nobody 15 0 20200 18M 11148 S 0,4 0,9 0:07 2 httpd
30363 nobody 15 0 19460 18M 13232 S 0,3 0,9 0:14 3 httpd
30427 nobody 15 0 18336 17M 11884 S 0,3 0,8 0:06 0 httpd

User Domain %CPU %MEM Mysql Processes
mysql 118.66 1030.00 0.0
Top Process %CPU 6.5 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/planet.server4us.net.pid --skip-locking --socket/var/lib/mysql/mysql.sock
Top Process %CPU 6.3 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/planet.server4us.net.pid --skip-locking --socket/var/lib/mysql/mysql.sock
Top Process %CPU 4.1 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/planet.server4us.net.pid --skip-locking --socket/var/lib/mysql/mysql.sock

Memory usage 1030 wow why can this happen

What is skip locking
How can i disable it

ANyone please help me my processes keeps growing Here is my my.cnf

[mysqld]
datadir=/var/lib/mysql
max_connections=500
max_user_connections=250
key_buffer=64M
join_buffer=1M
myisam_sort_buffer_size=64M
join_buffer_size=2M
read_buffer_size=2M
sort_buffer_size=2M
read_rnd_buffer_size=1M
tmp_table_size=32M
table_cache=512
record_buffer=1M
thread_cache_size=128
wait_timeout=5
connect_timeout=10
max_allowed_packet=40M
max_connect_errors=10000
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
skip-innodb
skip-locking
thread_concurrency=4
interactive_timeout=30
log-bin
server-id=1

[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

[isamchk]
key_buffer=48M
sort_buffer=48M
read_buffer=16M

[mysqlhotcopy]
interactive-timeout

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

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


I have a problem processes are starting to grow from 32MB to 72 after some time they grow to 150 MB and so on how can i stop this process ??

I tried to make key-buffer 128 MB but the situation is still the same anoyone ??

22:23:41 up 34 min, 2 users, load average: 2,01, 1,85, 1,66
183 processes: 179 sleeping, 3 running, 1 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 30,3% 0,0% 9,3% 0,0% 0,2% 0,2% 59,8%
cpu00 28,5% 0,0% 4,7% 0,0% 0,9% 0,0% 65,7%
cpu01 20,1% 0,0% 13,4% 0,0% 0,0% 0,0% 66,3%
cpu02 40,3% 0,0% 9,6% 0,0% 0,0% 0,0% 50,0%
cpu03 32,3% 0,0% 9,5% 0,0% 0,0% 0,9% 57,1%
Mem: 2055464k av, 1909744k used, 145720k free, 0k shrd, 56036k buff
1259792k active, 428428k inactive
Swap: 2048276k av, 0k used, 2048276k free 1093848k cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
3679 nobody 16 0 23700 23M 16252 R 16,2 1,1 0:14 0 httpd
4675 mysql 16 0 88904 86M 1448 S 13,3 4,3 0:30 1 mysqld
4669 mysql 16 0 88904 86M 1448 S 2,6 4,3 0:30 1 mysqld
3708 nobody 15 0 26852 26M 17160 S 2,3 1,3 0:15 3 httpd
4619 mysql 16 0 88904 86M 1448 S 1,9 4,3 0:11 1 mysqld
4633 mysql 16 0 88904 86M 1448 R 1,1 4,3 0:50 3 mysqld
3732 nobody 15 0 25292 24M 18216 S 0,7 1,2 0:25 1 httpd
5824 root 20 0 1168 1168 832 R 0,7 0,0 0:00 2 top
3742 nobody 15 0 25776 25M 15400 S 0,2 1,2 0:18 0 httpd
5519 nobody 15 0 20172 19M 10492 S 0,2 0,9 0:05 0 httpd


Here is top after 34 minutes of working

your swap see better than before...
I think that the next step is to tune Apache.
Anybody here to help Misiek ?
Regards
Jean

Swap looks better but after one hour of working my server is starting swapping

Is there any help for me

P4 2.4, Ram 1.5

Sun Jan 9 19:31:34 EET 2005


top - 19:31:37 up 18 days, 3:08, 1 user, load average: 35.73, 30.89, 29.69
Tasks: 726 total, 1 running, 722 sleeping, 0 stopped, 3 zombie
Cpu(s): 26.9% user, 7.3% system, 0.2% nice, 65.6% idle
Mem: 1550980k total, 1516824k used, 34156k free, 147932k buffers
Swap: 2080368k total, 4392k used, 2075976k free, 473644k cached



Http processes currently running = 515
Mysql processes currently running = 160

Netstat information summary
1 LAST_ACK
2 SYN_SENT
4 CLOSE_WAIT
4 CLOSING
18 LISTEN
69 SYN_RECV
77 FIN_WAIT1
272 FIN_WAIT2
342 ESTABLISHED
1378 TIME_WAIT

+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| Aborted_clients | 322 |
| Aborted_connects | 7 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 63229450 |
| Bytes_sent | 124199439 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 1 |
| Com_change_db | 298330 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 1 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 3926 |
| 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 | 58483 |
| Com_insert_select | 3 |
| 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 | 132298 |
| 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_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 | 3 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 8 |
| Com_show_variables | 3 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 66056 |
| Com_update_multi | 0 |
| Connections | 124129 |
| Created_tmp_disk_tables | 23 |
| Created_tmp_files | 11 |
| Created_tmp_tables | 40856 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 6876 |
| Handler_discover | 0 |
| Handler_read_first | 1618 |
| Handler_read_key | 255103 |
| Handler_read_next | 7030808 |
| Handler_read_prev | 515 |
| Handler_read_rnd | 72831 |
| Handler_read_rnd_next | 39033676 |
| Handler_rollback | 0 |
| Handler_update | 65805 |
| Handler_write | 942094 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 101692 |
| Key_blocks_used | 14288 |
| Key_read_requests | 2541739 |
| Key_reads | 16739 |
| Key_write_requests | 502227 |
| Key_writes | 440569 |
| Max_used_connections | 165 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 360 |
| Open_streams | 0 |
| Open_tables | 256 | 100% of table_cache in use
| Opened_tables | 517 |
| Qcache_free_blocks | 1874 |
| Qcache_free_memory | 28684480 |
| Qcache_hits | 38771 |
| Qcache_inserts | 128778 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 3516 |
| Qcache_queries_in_cache | 3206 |
| Qcache_total_blocks | 8417 |
| Questions | 721836 |
| Rpl_status | NULL |
| Select_full_join | 41 |
| Select_full_range_join | 0 |
| Select_range | 1285 |
| Select_range_check | 0 |
| Select_scan | 27919 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 71 | (execution time > 3 secs)
| Sort_merge_passes | 4 |
| Sort_range | 18302 |
| Sort_rows | 1411055 |
| Sort_scan | 25009 |
| Table_locks_immediate | 274777 |
| Table_locks_waited | 6476 |
| Threads_cached | 127 |
| Threads_connected | 19 |
| Threads_created | 270 |
| Threads_running | 1 |
| Uptime | 2471 | 41 mins 11 secs
+--------------------------+-----------+


Key Reads/Key Read Requests = 0.006586 (Cache hit = 99.993414%)
Key Writes/Key Write Requests = 0.877231
Connections/second = 50.234 (/hour = 180843.545)
KB received/second = 24.989 (/hour = 89959.207)
KB sent/second = 49.085 (/hour = 176704.492)
Temporary Tables Created/second = 16.534 (/hour = 59523.108)
Opened Tables/second = 0.209 (/hour = 753.217)
Slow Queries/second = 0.029 (/hour = 103.440)
% of slow queries = 0.010%
Queries/second = 292.123 (/hour = 1051642.898)
MySQL Query Cache hits = 38771/171065(23%)



[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
skip-networking

key_buffer = 128M
table_cache = 512
read_buffer_size = 1M
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
wait_timeout = 50
max_connections = 710
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
sort_buffer_size = 1M
thread_cache_size = 80
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
interactive_timeout=100




httpd.conf
-------------
Timeout 180
KeepAlive On
MaxKeepAliveRequests 2048
KeepAliveTimeout 5
MinSpareServers 32
MaxSpareServers 75
StartServers 100
MaxClients 512
MaxRequestsPerChild 20000

How do I fix this problem as noted in the mysqld.log file? Can anybody shed some light on this?

[root@srv1 root]# cat /var/log/mysqld.log
050628 01:08:24 mysqld ended

050628 01:09:19 mysqld started
050628 1:09:19 InnoDB: Out of memory in additional memory pool.
InnoDB: InnoDB will start allocating memory from the OS.
InnoDB: You may get better performance if you configure a bigger
InnoDB: value in the MySQL my.cnf file for
InnoDB: innodb_additional_mem_pool_size.
050628 1:09:19 InnoDB: Started
/usr/libexec/mysqld: ready for connections

Hi guys,
I'm having a problem with the load on my server. When the mysql "table_cache in use" reaches 99%, the mysql performance goes down the drain. I start getting slow queries and the load goes up like mad. By restarting the mysql daemon it gets cleared and everything is back to normal. Is there a setting to set the time the cache clears or resets in the config file?

BTW, this is my config on a Dual Xeon 2.8ghz with 1G of ram (any tips welcome)


EDIT: Using the script mentioned before I can se a lot of Table_locks_waited.



Please help :confused:
Thanks
-d

| Key_read_requests | 144333765 |
| Key_reads | 120084 |
| Key_write_requests | 2039 |
| Key_writes | 2037 |


Is it ok ?

I followed the upgrade instructions in thread Upgrade PHP to 4.3.10 and mySQL to 4.02 without breaking plesk and then followed up with the modifications outlined by aussie in this thread and taylored to our 515k Celeron.

Since then the cpu has been running at around 100% the number of mysqld threads has crept up over the day to the point where the power switch becomes the only option to get going again.

Much hair and sleep has been lost over this but I've managed a work around by including 'log_slow_queries' in my.cnf. I can see no reason why this would make a difference but the system is now running consistantly at a 0 or 0.1 load average rather than building to 30+.

Before anyone asks it's not logging any slow queries either.

If anyone has seen this behaviour or has any thoughts I'd be delighted to hear them.


my.cnf now looks like:

[mysqld]
# max_connections=500
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=200
interactive_timeout=50
wait_timeout=50
connect_timeout=10
thread_cache_size=50
key_buffer=150M
join_buffer=1M
max_allowed_packet=16M
table_cache=348
record_buffer=1M
sort_buffer_size=768K
read_buffer_size=512K
read_rnd_buffer_size=512K
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=2
myisam_sort_buffer_size=32M
log_slow_queries=/var/log/mysqld-slow.log

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

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates

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

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

[mysqlhotcopy]
interactive-timeout

Hoping one of the gurus here can help me-
server is
P4 - 3.06Ghz - Redhat Enterprise - Ensim Pro - 2-120GBHD
- 1 GB RAM
server had been running ok, but here the last 3 weeks it's been
hitting 30's and above-
Any advice on which settings need to be change with the above server specs?


httpd.conf


my.conf


the output from the stat.php
=========
top - 14:39:31 up 1 day, 2:08, 2 users, load average: 16.29, 14.28, 8.53
Tasks: 228 total, 1 running, 227 sleeping, 0 stopped, 0 zombie
Cpu(s): 8.4% us, 2.7% sy, 0.1% ni, 82.4% id, 6.0% wa, 0.1% hi, 0.3% si
Mem: 1026556k total, 1008108k used, 18448k free, 4544k buffers
Swap: 2096472k total, 1117692k used, 978780k free, 37020k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
7465 mysql 15 0 137m 38m 1516 D 0.0 3.8 1:01.70 mysqld
22882 mysql 15 0 137m 38m 1516 D 0.0 3.8 0:00.16 mysqld
22894 mysql 15 0 137m 38m 1516 D 0.0 3.8 0:00.28 mysqld
22903 mysql 15 0 137m 38m 1516 D 0.0 3.8 0:00.24 mysqld
25180 mysql 17 0 137m 38m 1516 D 0.0 3.8 0:00.03 mysqld

1 CLOSE_WAIT
1 ESTABLISHED
1 FIN_WAIT2
4 TIME_WAIT
21 LISTEN

+----------------------------+-----------+
| Variable_name | Value |
+----------------------------+-----------+
| Aborted_clients | 22 |
| Aborted_connects | 8 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 299004444 |
| Bytes_sent | 551002376 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 82 |
| Com_change_db | 48520 |
| Com_change_master | 0 |
| Com_check | 1 |
| Com_checksum | 0 |
| Com_commit | 82 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 12457 |
| 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 | 17378 |
| Com_insert_select | 234 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 4 |
| Com_optimize | 1546 |
| 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 | 1314 |
| 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 | 388534 |
| Com_set_option | 751 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 13 |
| Com_show_charsets | 124 |
| Com_show_collations | 124 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 252 |
| Com_show_databases | 43 |
| Com_show_errors | 0 |
| Com_show_fields | 582 |
| Com_show_grants | 71 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 99 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 1 |
| Com_show_privileges | 0 |
| Com_show_processlist | 1 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 11 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 8982 |
| Com_show_variables | 339 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 4 |
| Com_update | 260185 |
| Com_update_multi | 0 |
| Connections | 42236 |
| Created_tmp_disk_tables | 4401 |
| Created_tmp_files | 13 |
| Created_tmp_tables | 13822 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 15257 |
| Handler_discover | 0 |
| Handler_read_first | 24935 |
| Handler_read_key | 6834699 |
| Handler_read_next | 40035765 |
| Handler_read_prev | 302577 |
| Handler_read_rnd | 349398 |
| Handler_read_rnd_next | 101383555 |
| Handler_rollback | 0 |
| Handler_update | 1474737 |
| Handler_write | 689940 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 27309 |
| Key_blocks_used | 30681 |
| Key_read_requests | 24194351 |
| Key_reads | 55497 |
| Key_write_requests | 154904 |
| Key_writes | 88076 |
| Max_used_connections | 45 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 1823 |
| Open_streams | 0 |
| Open_tables | 1024 | 100% of table_cache in use
| Opened_tables | 7274 |
| Qcache_free_blocks | 2278 |
| Qcache_free_memory | 7030832 |
| Qcache_hits | 377617 |
| Qcache_inserts | 376589 |
| Qcache_lowmem_prunes | 9877 |
| Qcache_not_cached | 11293 |
| Qcache_queries_in_cache | 4811 |
| Qcache_total_blocks | 13741 |
| Questions | 1161944 |
| Rpl_status | NULL |
| Select_full_join | 598 |
| Select_full_range_join | 0 |
| Select_range | 32775 |
| Select_range_check | 0 |
| Select_scan | 152544 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slave_retried_transactions | 0 |
| Slow_launch_threads | 2 |
| Slow_queries | 173 | (execution time > 10 secs)
| Sort_merge_passes | 13 |
| Sort_range | 11534 |
| Sort_rows | 1462905 |
| Sort_scan | 27671 |
| Table_locks_immediate | 794061 |
| Table_locks_waited | 2113 |
| Threads_cached | 0 |
| Threads_connected | 45 |
| Threads_created | 45 |
| Threads_running | 44 |
| Uptime | 94027 | 1 day 2 hrs 7 mins 7 secs
+----------------------------+-----------+


Key Reads/Key Read Requests = 0.002294 (Cache hit = 99.997706%)
Key Writes/Key Write Requests = 0.568584
Connections/second = 0.449 (/hour = 1617.084)
KB received/second = 3.105 (/hour = 11179.614)
KB sent/second = 5.723 (/hour = 20601.708)
Temporary Tables Created/second = 0.147 (/hour = 529.201)
Opened Tables/second = 0.077 (/hour = 278.499)
Slow Queries/second = 0.002 (/hour = 6.624)
% of slow queries = 0.015%
Queries/second = 12.358 (/hour = 44487.205)
MySQL Query Cache hits = 377617/765499(49%)
==========

had to power off lastnight today as it hit well over 60% for some reason
Thanks a million in advance!
TB.

Whats your swap like? You must have a fairly large swap % with that config.



Change it to;

Timeout 8
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
StartServers 5
MinSpareServers 5
MaxSpareServers 10
MaxClients 150
MaxRequestsPerChild 500




Change it to;

[mysqld]
datadir=/var/lib/mysql
safe-show-database
back_log = 75
skip-locking
skip-innodb
max_connections = 500
key_buffer = 16M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
read_rnd_buffer_size = 524288
table_cache = 1800
thread_cache_size = 384
interactive_timeout = 100
wait_timeout = 180
connect_timeout = 10
tmp_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 10
thread_concurrency = 2
query_cache_limit = 1M
query_cache_size = 64M
query_cache_type = 1
query_prealloc_size = 16384
query_alloc_block_size = 16384

[mysqld_safe]
err-log=/var/log/mysqld.log
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

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

Swap size =

if looking at this correct
2048 MB

made the changes to the httpd.conf


should i even mess with this part

Hello,

I've read this thread for the most part and it quite interesting, i made a couple of changes and additions to my.cnf. I'm wondering if i have done the right thing.

I've have a server with 512mb ram. My my.cnf looks like:


I have installed mtop too:

The free command shows:

My scripts do connect very frequent to the mysql database (over 5 to 10 times in a second) and most of the time execute 1 select query.

My sites stay responsive even with a constant load of 3.0. I'm quite happy with that.

But sometimes apache gets to handle many requests and that pushes the load up, and then mysql gives me a error that there are no connections available anymore.

I looked at max_connections and the default is 100. Should i increase this number? Or should i make more use of my free memory (see output of 'free')?

Twan, you really should put something in there for the number of connections. Why don't you copy and paste what i posted above for Ntburchf with the following changes based on 512meg of memory.

back_log = 50
table_cache = 1500
tmp_table_size = 32M
query_cache_size = 16M

You want to reduce swapping as much as possible and i wouldn't go higher than this based on 512megs. Then restart MySQL.

who was this directed to / for?
mine just went from .03 to 31.00 in a matter of secs.
was hoping someone could help with my settings-

here's what I have currently after trying to gleam some info from this thread


thanks

I already posted the my.cnf for you to try. Your loads may have gone to 31.00 but it could be a host of many things, not just MySQL that is pushing up your loads. Why dont you try the my.cnf that i posted? The reply I posted was for Twan.

well, was not sure who you were posting to due to the fact that the my.cnf you quoted was not the one I posted, so I assumed that it was the guy that either posted above me the my.cnf was from kiwibob post- that's the reason -

your quoted my httpd.conf, but kiwibob's my.cnf So I assumed you were talking to 2 people in 1 post/reply
Still not sure what you would like to to try, as you were basing your changes on someone else my.cnf --

thanks!
TB.

Here is what i replied as a possible solution;

Change it to;

Timeout 8
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
StartServers 5
MinSpareServers 5
MaxSpareServers 10
MaxClients 150
MaxRequestsPerChild 500

[mysqld]
datadir=/var/lib/mysql
safe-show-database
back_log = 75
skip-locking
skip-innodb
max_connections = 500
key_buffer = 16M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
read_rnd_buffer_size = 524288
table_cache = 1800
thread_cache_size = 384
interactive_timeout = 100
wait_timeout = 180
connect_timeout = 10
tmp_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 10
thread_concurrency = 2
query_cache_limit = 1M
query_cache_size = 64M
query_cache_type = 1
query_prealloc_size = 16384
query_alloc_block_size = 16384

[mysqld_safe]
err-log=/var/log/mysqld.log
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

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

Aussie,
Thanks many times over--!!

Made the changes you sugested- Seems to running great. Will let you know if have any issues-
Once again Thanks
TB.

==

after about 24hrs of making changes Aussie sugested running great-
peaks are now what I can live with

will be installing another gig mem in server this week which should help a lot also.

Thanks, but it didn't work out very well. At the first flood the server went up to load averages of 60-70, i had major trouble getting everything at normal levels again Now i'm back at the normal load averages levels of 1.5 at normal hours till 3.0 at high traffic hours. Could you please explain why the table_cache is 1500, it's currently 384 in my configuration. The difference seems to be quite big.

Ausie,
Added another gig of ram for a total of 2 gigs now
current my.cnf


and this


What would be the recommended settings now for 2 gigs vs the 1 gig
top is showing after 9 hrs


Thanks in advance!

hi how do i optimize my.cnf on a server with dual opteron 248 and 4gig ram?

aussie you mind helping me out on this one?

This is a very useful thread but makes me a little puzzle.

My current 1G ram server uses key_buffer=16M but according to this thread, some 1G/2G ram server uses 16M, while others uses 128 or even 256M. I just checked the my-large.cnf file and its value is 256M and this is what I will use in my 3G ram server:

key_buffer=256M

Another puzzle is no body mention read_rnd_buffer_size.



Here is my settings:

join_buffer_size = 1M //I do not use any JOIN sql in my php
read_buffer_size = 1M
sort_buffer_size = 3M //or maybe I should use 1M or 2M?
read_rnd_buffer_size = 4M

And the following settings are accepted by most people who joined this thread:

[mysqld]
skip-locking
skip-innodb
log-bin

old_passwords=1 // what's this? It's in my original my.cnf

query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1

myisam_sort_buffer_size=64M

table_cache = 1024
thread_cache_size = 128

max_connections = 500 //I also truned mysql.allow_persistent = Off in php.ini
max_allowed_packet = 16M
max_connect_errors = 10
connect_timeout = 10
wait_timeout=100
interactive_timeout=100

And what's record_buffer?

thread_concurrency=2 - Is this ok for a Dual Xeon Single Core Server?

I have add my root password to the end of the script as follows:

mysql_fix_privilege_tables 'password'

But always got error

This scripts updates the mysql.user, mysql.db, mysql.host and the
mysql.func tables to MySQL 3.22.14 and above.

This is needed if you want to use the new GRANT functions,
CREATE AGGREGATE FUNCTION or want to use the more secure passwords in 3.23

If you get 'Access denied' errors, you should run this script again
and give the MySQL root user password as an argument!
Converting all privilege tables to MyISAM format
ERROR 1045: Access denied for user: 'root@localhost' (Using password: YES)

If your tables are already up to date or partially up to date you will
get some warnings about 'Duplicated column name'. You can safely ignore these!
ERROR 1045: Access denied for user: 'root@localhost' (Using password: YES)

Creating Grant Alter and Index privileges if they don't exists
You can ignore any Duplicate column errors
ERROR 1045: Access denied for user: 'root@localhost' (Using password: YES)

Adding columns needed by GRANT .. REQUIRE (openssl)
You can ignore any Duplicate column errors
ERROR 1045: Access denied for user: 'root@localhost' (Using password: YES)

Creating the new table and column privilege tables
ERROR 1045: Access denied for user: 'root@localhost' (Using password: YES)
Changing name of columns_priv.Type -> columns_priv.Column_priv
You can ignore any Unknown column errors from this
ERROR 1045: Access denied for user: 'root@localhost' (Using password: YES)

Fixing the func table
You can ignore any Duplicate column errors
ERROR 1045: Access denied for user: 'root@localhost' (Using password: YES)

Adding new fields used by MySQL 4.0.2 to the privilege tables
You can ignore any Duplicate column errors
ERROR 1045: Access denied for user: 'root@localhost' (Using password: YES)
ERROR 1045: Access denied for user: 'root@localhost' (Using password: YES)
ERROR 1045: Access denied for user: 'root@localhost' (Using password: YES)
ERROR 1045: Access denied for user: 'root@localhost' (Using password: YES)

Hellow folks i have a big problem i have a dual xeon machine 2,4 with 4 GB of ram and even when i have small traffic my server its up all my ram and start swapping could somebody help ??

15:07:38 up 14:55, 1 user, load average: 2,03, 1,82, 1,64
152 processes: 150 sleeping, 2 running, 0 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 28,2% 0,0% 4,8% 0,1% 1,2% 2,9% 62,3%
cpu00 28,8% 0,0% 3,7% 0,7% 4,1% 0,9% 61,3%
cpu01 26,6% 0,0% 3,3% 0,0% 0,0% 5,5% 64,3%
cpu02 28,4% 0,0% 10,1% 0,0% 0,7% 0,5% 60,0%
cpu03 29,0% 0,0% 2,1% 0,0% 0,1% 4,7% 63,7%
Mem: 4051028k av, 3955688k used, 95340k free, 0k shrd, 158952k buff
3010240k actv, 585456k in_d, 56180k in_c
Swap: 2048276k av, 151804k used, 1896472k free 3079772k cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
27108 mysql 15 0 56116 54M 1912 S 0,0 1,3 0:07 0 mysqld
27109 mysql 15 0 56116 54M 1912 S 0,0 1,3 0:00 1 mysqld
27110 mysql 15 0 56116 54M 1912 S 0,0 1,3 0:12 0 mysqld
27111 mysql 15 0 56116 54M 1912 S 0,0 1,3 1:52 0 mysqld
27112 mysql 16 0 56116 54M 1912 S 0,5 1,3 2:12 0 mysqld
27113 mysql 15 0 56116 54M 1912 S 0,0 1,3 2:16 0 mysqld
27114 mysql 15 0 56116 54M 1912 S 0,0 1,3 2:11 1 mysqld
27120 mysql 15 0 56116 54M 1912 S 0,0 1,3 2:19 2 mysqld
27121 mysql 16 0 56116 54M 1912 S 0,0 1,3 1:55 2 mysqld
27123 mysql 16 0 56116 54M 1912 S 0,7 1,3 1:58 2 mysqld
27125 mysql 16 0 56116 54M 1912 S 0,1 1,3 2:25 2 mysqld
27126 mysql 15 0 56116 54M 1912 S 0,0 1,3 2:08 3 mysqld
27127 mysql 16 0 56116 54M 1912 S 1,5 1,3 2:04 1 mysqld
27128 mysql 15 0 56116 54M 1912 S 0,0 1,3 1:43 1 mysqld
27135 mysql 15 0 56116 54M 1912 S 0,0 1,3 1:42 0 mysqld
27136 mysql 15 0 56116 54M 1912 S 0,0 1,3 2:08 0 mysqld
27137 mysql 15 0 56116 54M 1912 S 0,9 1,3 1:59 2 mysqld
754 mysql 15 0 56116 54M 1912 S 0,0 1,3 1:00 1 mysqld
755 mysql 15 0 56116 54M 1912 S 0,0 1,3 1:07 1 mysqld
756 mysql 15 0 56116 54M 1912 S 0,0 1,3 1:06 0 mysqld
761 mysql 15 0 56116 54M 1912 S 0,0 1,3 1:02 0 mysqld
762 mysql 15 0 56116 54M 1912 S 0,3 1,3 1:02 0 mysqld
763 mysql 15 0 56116 54M 1912 S 0,0 1,3 1:06 1 mysqld
764 mysql 15 0 56116 54M 1912 S 0,1 1,3 1:04 0 mysqld
765 mysql 16 0 56116 54M 1912 S 0,4 1,3 1:11 2 mysqld
825 mysql 16 0 56116 54M 1912 S 2,5 1,3 0:56 2 mysqld
826 mysql 15 0 56116 54M 1912 S 0,0 1,3 1:02 1 mysqld
1958 mysql 15 0 56116 54M 1912 S 0,0 1,3 0:58 2 mysqld
1984 mysql 15 0 56116 54M 1912 S 0,0 1,3 0:55 1 mysqld
2065 mysql 15 0 56116 54M 1912 S 0,0 1,3 0:55 2 mysqld
2066 mysql 15 0 56116 54M 1912 S 0,0 1,3 0:39 1 mysqld
2067 mysql 15 0 56116 54M 1912 S 1,0 1,3 1:14 2 mysqld
24769 nobody 15 0 38176 37M 17684 S 0,1 0,9 3:28 2 httpd

EXTENDED STATUS:

+----------------------------+------------+
| Variable_name | Value |
+----------------------------+------------+
| Aborted_clients | 1913 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 181243793 |
| Bytes_sent | 3544160437 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 161 |
| Com_change_db | 211672 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 158 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 9316 |
| 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 | 10317 |
| Com_insert_select | 235 |
| 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 | 49 |
| 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 | 344714 |
| Com_set_option | 35 |
| 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 | 1 |
| 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 | 29 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 58 |
| Com_show_variables | 1 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 46667 |
| Com_update_multi | 0 |
| Connections | 60047 |
| Created_tmp_disk_tables | 2828 |
| Created_tmp_files | 306 |
| Created_tmp_tables | 27748 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 9392 |
| Handler_discover | 0 |
| Handler_read_first | 5027 |
| Handler_read_key | 86850802 |
| Handler_read_next | 84153010 |
| Handler_read_prev | 8935287 |
| Handler_read_rnd | 1178404 |
| Handler_read_rnd_next | 349595772 |
| Handler_rollback | 0 |
| Handler_update | 53830063 |
| Handler_write | 16672581 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 0 |
| Key_blocks_used | 14497 |
| Key_read_requests | 99530797 |
| Key_reads | 1060466 |
| Key_write_requests | 368295 |
| Key_writes | 65608 |
| Max_used_connections | 29 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 1202 |
| Open_streams | 0 |
| Open_tables | 684 |
| Opened_tables | 690 |
| Qcache_free_blocks | 3244 |
| Qcache_free_memory | 10019712 |
| Qcache_hits | 713099 |
| Qcache_inserts | 325490 |
| Qcache_lowmem_prunes | 20036 |
| Qcache_not_cached | 19223 |
| Qcache_queries_in_cache | 6624 |
| Qcache_total_blocks | 16993 |
| Questions | 1412690 |
| Rpl_status | NULL |
| Select_full_join | 1796 |
| Select_full_range_join | 1 |
| Select_range | 13390 |
| Select_range_check | 0 |
| Select_scan | 52807 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 153 |
| Sort_range | 16033 |
| Sort_rows | 7361527 |
| Sort_scan | 36212 |
| Table_locks_immediate | 678038 |
| Table_locks_waited | 624 |
| Threads_cached | 28 |
| Threads_connected | 1 |
| Threads_created | 29 |
| Threads_running | 1 |
| Uptime | 8658 |
+----------------------------+------------+


MY.CNF

[mysqld]
log-slow-queries
datadir=/var/lib/mysql
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=26M
query_cache_type=1
max_connections=300
max_connect_errors=3
max_allowed_packet=16M
interactive_timeout=5
#wait_timeout=10
wait_timeout=2
#timeout=10
#connect_timeout=5
connect_timeout=2
thread_cache_size=128
key_buffer=16M
#key_buffer=128M
join_buffer=1M
myisam_sort_buffer_size=64M
table_cache=1024
record_buffer=1M
sort_buffer=2M
thread_concurrency=4
#log-bin
#serverid=1
#interactive_timeout=100
read_buffer_size=1M
read_rnd_buffer_size=768K
#default-character-set=latin2
[client]
#default-character-set=latin2
#character-set=latin2
#default-collation=latin2_bin
[mysql.server]
user=mysql
basedir=/var/lib
[mysqldump]
quick
max_allowed_packet=64M
[mysql]
no-auto-rehash
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit=8192
[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M


HTTPD.CONF

Timeout 20
KeepAlive Off
MaxKeepAliveRequests 200
KeepAliveTimeout 20
MinSpareServers 5
MaxSpareServers 10
StartServers 4
MaxClients 200
MaxRequestsPerChild 0

key_buffer=16M
and you have 4Gb ram ???
I have 512Mb key buffer, and our server's load is really ok.
The more key buffer you give to MySQL, the better he can do the job.
Also, don't hink that every MySQL process will take 512Mb, that's wrong !
MySQL will use 512Mb + a little more, but for every processes.
So, in my case, MySQL don't eat more then +- 768Mb
;-)
Regards
Jean

I will try that but what about mysql is consumming all my memmory ?

For what i know, MySQL will not eat ALL your memory, but what you configure.
But, if you don't give enough, then MySQL will not work like he should.
If you have sites with forums, or php and MySQL requests, then you have to deal with the fact that MySQL can take 15 or 20% of your memory.
But you have enough memory, why should MySQL don't use it ???
Regards
Jean

Thats why

23:40:17 up 23:27, 1 user, load average: 2,01, 2,16, 2,37
166 processes: 160 sleeping, 6 running, 0 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 26,5% 0,0% 5,4% 0,1% 0,5% 3,1% 64,1%
cpu00 28,1% 0,0% 6,1% 0,3% 2,1% 5,1% 57,9%
cpu01 36,3% 0,0% 6,7% 0,1% 0,0% 0,3% 56,2%
cpu02 17,2% 0,0% 6,5% 0,0% 0,0% 6,7% 69,3%
cpu03 24,4% 0,0% 2,1% 0,0% 0,0% 0,1% 73,1%
Mem: 4051028k av, 3914416k used, 136612k free, 0k shrd, 126844k buff
2971200k actv, 568856k in_d, 65876k in_c
Swap: 2048276k av, 433584k used, 1614692k free 2934372k cached


He is using swap

hmmm, i don't think that the solution is to lower the MySQL key_buffer
What are the process using your memory ?
I think that Apache or any other process eat your memory...
Make a copy of your my.cnf, and try my settings, only to see what's going on.
After that you pasted my settings, stop MySQL, and see if yous swap is always used. If not, then restart MySQL. If yes, then i suggest to try to reboot ... and see what is using your memory.

PS : Don't paste ALL my settings ! Use only the variables, and don't use my path, and locations ! This my.cnf is for a Ensim 4.1.0 and CentOS 4.3 server, running a single P4 3GHz and 4Gb ram.



[mysqld]
socket=/home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock
datadir=/var/lib/mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
#
connect_timeout=30
interactive_timeout=90
join_buffer=2M
join_buffer_size=2M
key_buffer=512M
max_allowed_packet=2M
max_connect_errors=10000
max_connections=256
max_user_connections=128
myisam_sort_buffer_size=64M
query_cache_limit=8M
query_cache_size=32M
query_cache_type=1
read_buffer_size=2M
read_rnd_buffer_size=8M
record_buffer=2M
sort_buffer_size=2M
table_cache=1500
thread_cache_size=128
tmp_table_size=16M
wait_timeout=100
skip-innodb
skip-locking
thread_concurrency=4
log-bin
server-id=1

[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

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M

[mysqlhotcopy]
interactive-timeout

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

[client]
socket=/home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock



This one is probably not at 100% perfect for your server, but you can try to tune your own settings :-)
PS : Try also to tune your apache settings, it can make wonders
Regards
Jean