HOWTO: Optimize MySQL 4.x

where exactly do you put this my.cnf in direct admin?

do i use it to upload it via ftp,or do i have to put it somewhere else?

please help
 
new new said:
where exactly do you put this my.cnf in direct admin?

do i use it to upload it via ftp,or do i have to put it somewhere else?

please help
Click to expand...

The file is located at /etc/my.cnf
 
eymbo said:
The file is located at /etc/my.cnf
Click to expand...


thanks for the reply i did find it now when i paste the info and try to save i get this error

E45: 'readonly' option is set (add ! to override)

got any idea what that means?
 
Buffer issue.

Code: 
$ free
             total       used       free     shared    buffers     cached
Mem:       1033216     656008     377208          0       1888      58356
-/+ buffers/cache:     595764     437452
Swap:      2096472      55468    2041004

The value in the buffers above drops when the site is going slow.
Which setting in my.cnf do I amend?
 
My theory of buffers after long observation is that the cache things. When you restart MySQL the buffers will drop but after a few hours if not a day the buffers will rise again unless there are some intense IO going on with MySQL (lets say optimizing a 2Gig table). Buffers are to help IO, so if your site becomes terribly slow your bottleneak will be your harddrives.

Run iostat 1 or vmstat 1 and paste it here when the buffers are low.
 
Just had a server overload.

Am using the recommend my.cnf http://help.directadmin.com/item.php?id=44

Was not able to get many stats at time of overload. Even logging into SSH was a problem as key response was dire.
What I was able to do was the free as soon as I got in.
Code: 
Free:        total       used       free     shared    buffers     cached
Mem:       1033192    1015976      17216          0        924      17996
-/+ buffers/cache:     997056      36136
Swap:      2096472    1437116     659356

The next thing I did was a top, obviously bad idea, because top just never responded and had to then end the SSH session.

So I take it the memory is low and it's swapping to disk which is why it's then so slow. Can anyone confirm that?
 
It appears you have two gigabytes of swap memory and over one gigabyte of swap memory in use.

I've always maintained that you should never have that much swap memory in place because your server will become unusable long before you use it.

It appears to me you need more memory.

You can also try:
Code: 
# uptime
It gives you the top line of top so will show you what we call the server load.

Jeff
 
jlasman said:
It appears you have two gigabytes of swap memory and over one gigabyte of swap memory in use.

I've always maintained that you should never have that much swap memory in place because your server will become unusable long before you use it.
Click to expand...
Yes, Jeff. Indeed the server becomes unusable, that is what happens.

So how do I reduce swap memory?

I am currently using the recommended http://help.directadmin.com/item.php?id=44

Uptime currently shows this.
17:30:13 up 23:31, 1 user, load average: 0.28, 0.24, 0.19
Click to expand...

Below is 'free' right now. Note that swap used is low. Also the 'free' posted above on 01-09-2007 shows the swap used at a more normal 55468.
Code: 
             total       used       free     shared    buffers     cached
Mem:       1033192    1017400      15792          0      17440     516080
-/+ buffers/cache:     483880     549312
Swap:      2096472        144    2096328
I take it because used memory is almost equal to free memory then this is good reason to have more memory?

Is the only solution in your view to upgrade the RAM which is currently 1GB or can it be done with better config?
Would reducing number of max_connections help recover much memory for example?
 
Last edited:
Used memory is almost always equal to free memory because when Linux manages memory it's listed as used even though it's only use is to be available for immediate deployment.

The indication of not having enough memory is when swap memory is heavily used.

Sure you can play with your configuration to use less memory, but only at the expense of server usability. I'd go the more memory route :) .

Jeff
 
@chartmusic: There is a way to disable swap. There are advantages and disadvantages of disabling swap. Personally if you run a stable server or you want to experiment with this I'd recommend doing it at a off peak hour. I run production servers that run very stable and tweaked websites and sometimes Linux resorts to swap even though there is a few hundred megabytes RAM available in the cache available. So I turn off swap so that the applications would be forced to use only what is left in the RAM. Bare in mind that the applications I run aren't memory sensitive and can adjust it's memory usage according to how many memory is available.

To disable swap run:

swapoff -a

To re-enable swap run:

swapon -a

These two commands can be done easily without reboots. ;)
 
chartmusic's swap documented (in this thread) swap use has gone as high as almost 1.4 Gig. Under those circumstances I wouldn't trust running his server without at least another gigabyte of memory.

I fear he might end up not being able to log into his server remotely and needing a hard reboot.

Jeff
 
Thanks for the replies again.



jlasman said:
Used memory is almost always equal to free memory because when Linux manages memory it's listed as used even though it's only use is to be available for immediate deployment.
Click to expand...
Jeff is there any way of determining actual memeory free, or actual memory used rather then the figure of what is ready for deployment.

I'm wanting to upgrade the memory, but someone who pays the bills considers it pointless because there is no proof that memory is an issue. The swap memory is high in the above posts because possibly it's due to an SQL lock issue, which called for more and more memory.

Kinda looking for some more information to pin point it either way.
Thanks chartmusic
 
I don't know any way of finding out more about memory usage. I think Onno may know of a program that can figure it out; perhaps he'll respond.

However I'll point out that no matter why the swap memory is high; if it's high, it's high :) .

When we added memory we saw immediate improvement.

Jeff
 
There is not my.cnf under /etc/. When I created one using the recommendations in this thread, mysql would not start.

I notice that the recommendations for the my.cnf settings are very old. Are they still valid? If so, why did my mysql fail to start?
 
computerlady911 said:
There is not my.cnf under /etc/. When I created one using the recommendations in this thread, mysql would not start.

I notice that the recommendations for the my.cnf settings are very old. Are they still valid? If so, why did my mysql fail to start?
Click to expand...

Regarding starting mysql with new configuration.

Within DirectAdmin I use the Service Monitor menu ( /CMD_SHOW_SERVICES )
Just click STOP mysqld and then within a second or two click START. May not be the cleanest way, but it works for me.
 
jlasman said:
I don't know any way of finding out more about memory usage.
Click to expand...

Good news, now have upgraded to 2GB of RAM.

After 24 hours here is free values.
Code: 
free
             total       used       free     shared    buffers     cached
Mem:       2073516    1519256     554260          0      61064    1067596
-/+ buffers/cache:     390596    1682920
Swap:      2096472          0    2096472
 
loopforever said:
I suggest implementing them to alieviate the load MySQL may cause when you run big SQL sites on your server:
Click to expand...
Allow me to share /etc/my.cnf file we use on our clients' MySQL servers:

Code: 
[mysqld]
connect_timeout=15
datadir=/var/lib/mysql
interactive_timeout=50
join_buffer=1M
key_buffer=384M
max_allowed_packet=1M
max_connect_errors=10
max_connections=350
max_user_connections=25
myisam_sort_buffer_size=64M
old-passwords = 1
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
read_buffer_size=2M
record_buffer=1M
server-id=1
skip-innodb
skip-locking
socket=/var/lib/mysql/mysql.sock
sort_buffer_size=2M
table_cache=512
thread_cache_size=8
thread_concurrency=2
wait_timeout=50

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

[safe_mysqld]
open_files_limit=8192
pid-file=/var/lib/mysql/mysql.pid

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash

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

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

[mysqlhotcopy]
interactive-timeout

You need to change the directive:
thread_concurrency=2
to match the number of CPUs on your server. This directive is for a server with 2 CPUs.
 
ServerTune said:
Allow me to share /etc/my.cnf file we use on our clients' MySQL servers:

You need to change the directive:
thread_concurrency=2
to match the number of CPUs on your server. This directive is for a server with 2 CPUs.
Click to expand...
Thanks ServerTune .
Shouldn't the thread_concurrency be twice the number of your processors then?
That was the previous advice. I have 1 processor and thread_concurrency=2 seems to work fine.
 
chartmusic said:
Thanks ServerTune .
Shouldn't the thread_concurrency be twice the number of your processors then?
That was the previous advice. I have 1 processor and thread_concurrency=2 seems to work fine.
Click to expand...
Yes, the directive should be set to 2X the number of processors in your machine for best performance. Thank you!
 
You must log in or register to reply here.
Back
Top