HOWTO: Optimize MySQL 4.x

[loopforever]

Tweak MySQL

I've found that these configuration options posted by "aussie" on the Rackshack forums work well with MySQL 4.x on my machines. I suggest implementing them to alieviate the load MySQL may cause when you run big SQL sites on your server:


vi /etc/my.cnf [ENTER]

[mysqld]
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=500
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
key_buffer=16M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=2
myisam_sort_buffer_size=64M
log-bin
server-id=1

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

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

[mysqldump]
quick
max_allowed_packet=16M

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

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

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

[mysqlhotcopy]
interactive-timeout

/etc/rc.d/init.d/mysqld restart [ENTER]

[MQ-James]

thanks, this helps. How can I enable certain other host maks to access the server? I don't want all TLDs to be able to access, just ones on server and 2 other TLDs

[loopforever]

Directadmin can handle host access in the database area of the user control panel. You can also control this through phpMyAdmin under your MySQL root user.

Hope this helps.

[FarCry]

log into phpmyadmin with the username da_admin and your admin password (the original one)

[DirectAdmin Support]

Hello,

DirectAdmin does have the mysql hosts feature.. Just click your database name, and the "Access Hosts" table should be at the bottom.

John

[neorder]

i'm sorry but i don't find "/etc/my.cnf" in my server, is that how to compatible with DA?

do i just creat a new file and copy the content there?

btw, i've nothing at /var/log/mysql.log

is there anything wrong here at my server?

[existenz]

The my.cnf it not added during the default install. You can search the forums for more information about this but you can basically add what was posted as a new file called my.cnf.

[ProWebUK]

MySQL simply runs on defaults unless the my.cnf exists, there are actually 3 places it looks for the config with /etc/my.cnf just being the first... add them and it will override the default settings..

Chris

[neorder]

thanks, i will read more.

[Anzix]

Hello, I need some help:

[root]# /etc/rc.d/init.d/mysqld restart
Killing mysqld with pid 2812
Wait for mysqld to exit\c
.\c
.\c
.\c
.\c
.\c
done
[root]# /etc/rc.d/init.d/mysqld restart
No mysqld pid file found. Looked for /var/lib/mysql/purple.nemesis.net.pid.
[root]# Starting mysqld daemon with databases from /var/lib/mysql
040423 18:18:11 mysqld ended

[root]# service mysqld restart
No mysqld pid file found. Looked for /var/lib/mysql/purple.nemesis.net.pid.
[root]# Starting mysqld daemon with databases from /var/lib/mysql
040423 18:40:31 mysqld ended

any idea on how to fix it?

[loopforever]

Have you modified this my.cnf file at all? I think mysqld is looking for the pid file in one location that you have specified, but it is non-existant.

[sander815]

i had a problem with mysql server going down, because google bots tried to login a lot, causing many many cookies etc

we now blocked google from entering the forums, that solved it, but will this maybe prevent mysql going down?

[sander815]

in what way is this cnf better then the default 1?

[neorder]

Originally posted by sander815
in what way is this cnf better then the default 1?

good question, i shouldn't just copy blindly, i just read the original post from ev1, it looks for different server specs, the setting varies.

[neorder]

they recommand

suggest the following my.cnf changes for people with a single-processor server and 512MB RAM:

thread_cache_size=50
key_buffer=40M
table_cache=384
sort_buffer_size=768K
read_buffer_size=512K
read_rnd_buffer_size=512K
thread_concurrency=2

how are these values are calculated?

anyway, i did this optimization, it was a WOW when i see the out come, i have one site used to have 0.2 page generate speed, now it's only 0.08!

a small question is

/etc/rc.d/init.d/mysqld restart [ENTER]

doesn't look like work, i ran it, but it hangs...i don't know if it's restarted, i guess it did.

[sander815]

to m it seems this config is best suited if you have 1.5 gb ram

key_buffer_size = (record_buffer + sort_buffer)*max_connections
= 1500

[sander815]

on what thread is this onfo based upon? on ev1 servers

[ProWebUK]

Originally posted by sander815
on what thread is this onfo based upon? on ev1 servers

it doesn't matter who provided the server - mysql is simply - mysql and this will work on any DA server, from any provider. (providing it has mysql and uses the linux mysql setup - if it had DA it would ;) )

[sander815]

no, i mean, i wanted to know the link to the thread on rack shack forums where this info comes from, so i can check what peoples replys are there

[netswitch]

any idea of the values to change for a dual processor ?

[netswitch]

# Try number of CPU's*2 for thread_concurrency
thread_concurrency=2



ok, sometimes I am posting too fast ;-)

[intelliot]

When I try to save the file in vi, I get this error:

"/etc/my.cnf" E212: Can't open file for writing

my.cnf does not yet exist. How do I get permission to create it in /etc/?

I am accessing the server via SSH using the admin user/pass.

Edit: nevermind, I used su (to get root) and it seems to work. Is there a better way?

Hopefully this helps to decrease the load MySQL causes...

[sander815]

you should use su - to login as root
(note the - behind su)

[Thafusion]

hercules# /etc/rc.d/init.d/mysqld restart
/etc/rc.d/init.d/mysqld: Command not found.

And when i put any config in my my.cnf then mysql don't restart when i do a reboot . Also in the directadmin panel i can't use the start thing. Any help ?

[jmstacey]

Your using the cnf in this post and it won't start? Is there any output as to why it doesn't start or what happens? Is there anything that would give a hint to what it is in your error logs? It could be a typo or something in the cnf, might want to double check.

[Thafusion]

040810 20:36:00 mysqld started
040810 20:36:00 Can't start server : Bind on unix socket: No such file or directory
040810 20:36:00 Do you already have another mysqld server running on socket: /var/lib/mysql/mysql.sock ?
040810 20:36:00 Aborting

040810 20:36:00 /usr/local/mysql-4.0.17-unknown-freebsd5.1-i386/bin/mysqld: Shutdown Complete

040810 20:36:00 mysqld ended


I get that when i use the settings from the begin post and i found the config of John van directadmin somewhere got the same error. I just need to increase the max connection's because 100 is to little :P

[RosT]

[mysqld]
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=500
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
key_buffer=16M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=2
myisam_sort_buffer_size=64M
log-bin
server-id=1


What write if my server is Intel Pentium IV 2.6/8 Ghz with RAM 1024 Mb?

[jlasman]

On our servers with similar specifications we've not had to change the defaults.

Which doesn't mean you won't.

What it does mean is that there's no simple answer.

It depends on your server load, your MySQL load, the number of sites using MySQL, and lots of other variables.

You'll probably have to learn a bit about it and figure it out yourself over time, or hire someone who understands MySQL well to watch your server and adjust it for you as required.

Jeff

[vincenzobar]

I need some serious help.

For 3 months i have tried everything and my Database keeps getting slower and slower.

I need a straight answer from someone!!!!! PLEASE!!!

I have this

Processor Name Intel(R) Celeron(R) CPU 2.00GHz
Vendor ID GenuineIntel
Processor Speed (MHz) 1990.289
Total Memory 1022812 kB
Free Memory 15444 kB
Total Swap Memory 2040244 kB
Free Swap Memory 2038176 kB
System Uptime 7 Days, 0 Hours and 5 Minutes
Apache 1.3.31 Running
DirectAdmin 1.22.4 Running
Exim 4.24 Running
MySQL 4.0.20 Running
Named 9.2.1 Running
ProFTPd 1.2.9 Running
sshd Running
vm-Pop3d 1.1.7e Running

basically a intel celeron 2GHz with 1 gig of ram

i know its a celeron but thats all i can afford and this is our starter server we will upgrade when we start getting money back!

Anyway. I need my SQL optimized for this and fast. the site is www.innerearaudio.com then go to catalog and you can see the parse time at the bottom of each page. see the problem. its got almost 750, 000 total records. the latest my.cnf i am using is the one on this site and i think it actually slowed it down. before i was using a slightly modded small.cnf and it took 11-13 secs.


HEEEELLLLLLPPPPPPPPPPP!!!!!! MySQL documentation sux or maybe i am not looking in the right place. most likely the first one!

[intelliot]

Try optimizing the tables. BTW your current load time looks OK to me.

[vincenzobar]

Are you seeing what im seeing!!!!!!???????

15 secs is horrible !!!! and yes i optimize all tables after every day of major inserting and query testing.

So far this is what i have found and done and nothing is changing

+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_queries_in_cache | 47 |
| Qcache_inserts | 56 |
| Qcache_hits | 133 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 10 |
| Qcache_free_memory | 16708008 |
| Qcache_free_blocks | 4 |
| Qcache_total_blocks | 110 |
+-------------------------+----------+



back_log 50
basedir /
binlog_cache_size 32768
bulk_insert_buffer_size 8388608
character_set latin1
character_sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis...
concurrent_insert ON
connect_timeout 10
convert_character_set
datadir /var/lib/mysql/
default_week_format 0
delay_key_write ON
delayed_insert_limit 100
delayed_insert_timeout 300
delayed_queue_size 1000
flush OFF
flush_time 0
ft_boolean_syntax + -><()~*:""&|
ft_min_word_len 4
ft_max_word_len 254
ft_max_word_len_for_sort 20
ft_stopword_file (built-in)
have_bdb NO
have_crypt YES
have_innodb DISABLED
have_isam YES
have_raid NO
have_symlink YES
have_openssl NO
have_query_cache YES
init_file
innodb_additional_mem_pool_size 1048576
innodb_buffer_pool_size 8388608
innodb_data_file_path
innodb_data_home_dir
innodb_file_io_threads 4
innodb_force_recovery 0
innodb_thread_concurrency 8
innodb_flush_log_at_trx_commit 1
innodb_fast_shutdown ON
innodb_flush_method
innodb_lock_wait_timeout 50
innodb_log_arch_dir
innodb_log_archive OFF
innodb_log_buffer_size 1048576
innodb_log_file_size 5242880
innodb_log_files_in_group 2
innodb_log_group_home_dir
innodb_mirrored_log_groups 1
innodb_max_dirty_pages_pct 90
interactive_timeout 100
join_buffer_size 1044480
key_buffer_size 289406976
language /usr/share/mysql/english/
large_files_support ON
license GPL
local_infile ON
locked_in_memory OFF
log OFF
log_update OFF
log_bin ON
log_slave_updates OFF
log_slow_queries OFF
log_warnings ON
long_query_time 10
low_priority_updates OFF
lower_case_file_system OFF
lower_case_table_names 0
max_allowed_packet 16776192
max_binlog_cache_size 4294967295
max_binlog_size 1073741824
max_connections 500
max_connect_errors 10
max_delayed_threads 20
max_insert_delayed_threads 20
max_heap_table_size 16777216
max_join_size 4294967295
max_relay_log_size 0
max_seeks_for_key 4294967295
max_sort_length 1024
max_user_connections 0
max_tmp_tables 32
max_write_lock_count 4294967295
myisam_max_extra_sort_file_size 268435456
myisam_max_sort_file_size 2147483647
myisam_repair_threads 1
myisam_recover_options OFF
myisam_sort_buffer_size 75497472
net_buffer_length 16384
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
new OFF
open_files_limit 4096
pid_file /var/lib/mysql/server.Innerearaudio.com.pid
log_error
port 3306
protocol_version 10
query_alloc_block_size 8192
query_cache_limit 1048576
Variable_name Value
query_cache_size 16777216
query_cache_type ON
query_prealloc_size 8192
range_alloc_block_size 2048
read_buffer_size 10481664
read_only OFF
read_rnd_buffer_size 262144
rpl_recovery_rank 0
server_id 1
slave_net_timeout 3600
skip_external_locking ON
skip_networking OFF
skip_show_database OFF
slow_launch_time 2
socket /var/lib/mysql/mysql.sock
sort_buffer_size 1048568
sql_mode 0
table_cache 1024
table_type MYISAM
thread_cache_size 128
thread_stack 126976
tx_isolation REPEATABLE-READ
timezone EDT
tmp_table_size 33554432
tmpdir /tmp/
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
version 4.0.20-standard-log
version_comment Official MySQL RPM
version_compile_os pc-linux
wait_timeout 100


and now what i did on my.cnf

max_connections = 500
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
skip-innodb
skip-locking
query_cache_limit=1M
query_cache_size=16M
query_cache_type=1
key_buffer=276M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
sort_buffer_size=1M
record_buffer=16M
read_buffer_size=10M
myisam_sort_buffer_size=72M

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer=268M
sort_buffer_size=72M
read_buffer=1M
write_buffer=1M

[myisamchk]
key_buffer=268M
sort_buffer_size=72M
read_buffer=1M
write_buffer=1M

[mysqlhotcopy]
interactive-timeout


hows this look?!?!?!?!?!

[intelliot]

You're right, sometimes it is high. but what I had seen is this:
Parse Time: 0.195s

[vincenzobar]

ahhh you hit a page that was stored in cache in the database!!! lol

OS Commerce allows for page caching so if i go to it it loads in cache server/database side and then as long ass that query doesn't change it keeps it for the next user. or until it runs out of space!!!

lol I hate this crap sometimes!!!!!!!

Also if you go to the forum you will notice it loads fast as hell.

[intelliot]

Isn't it then a problem with unoptimized queries in OSCommerce?

[vincenzobar]

no others are running big databases with no problem

[lnguyen]

when trying to run this:

mysqlcheck -o -u root -p --all-databases


from that ev1 thread, my root password is not accepted, it says root@localhost has been denied.

Any ideas?

[Curtis]

Hi all,

Is that the "maxconnection" limit for whole services?
So anyone have idea to limit maxconnection by each account?
Thanks

[choon]

Originally posted by Thafusion
hercules# /etc/rc.d/init.d/mysqld restart
/etc/rc.d/init.d/mysqld: Command not found.

And when i put any config in my my.cnf then mysql don't restart when i do a reboot . Also in the directadmin panel i can't use the start thing. Any help ?
Try:
/etc/rc.d/init.d/mysql restart

[saman]

Originally posted by lnguyen
when trying to run this:

mysqlcheck -o -u root -p --all-databases


from that ev1 thread, my root password is not accepted, it says root@localhost has been denied.

Any ideas?

You should try to login as "da_admin". The password can be found at:

/usr/local/directadmin/scripts/setup.txt

[hehachris]

i afraid that i dont have my.cnf @@

[root@server root]# cat /etc/my.cnf
cat: /etc/my.cnf: No such file or directory

[choon]

Originally posted by hehachris
i afraid that i dont have my.cnf @@

[root@server root]# cat /etc/my.cnf
cat: /etc/my.cnf: No such file or directory
If you don't have it, just create it and put in whatever options which you think is necessary then restart mysql ;)

[jlasman]

You may...

What output do you get running as root:

locate my.cnf

Jeff

[Thafusion]

Originally posted by hehachris
i afraid that i dont have my.cnf @@

[root@server root]# cat /etc/my.cnf
cat: /etc/my.cnf: No such file or directory

correct just make the file nano my.cnf paste the info in it save it. Then reboot mysql.

[UndeaD]

not /etc/my.cnf ??

[saman]

Originally posted by UndeaD
not /etc/my.cnf ?? Yes, he meant:
nano /etc/my.cnf

[max2000]

try to find it:

cd /

find . -name 'my.cnf' -print

[jmstacey]

A my.cnf file is not created by default so that MySQL will use default settings ;)

[Zero]

These tweaks are fantastic, the difference in speed was BLAM-O!!! The only question I have is, what general system spec are these tweaks based on? And is there a guide/rule for creating a my.cnf for a 2.8HT w/1GB ram?

Thanks everyone!

[resolveit]

You need to look at your site again...


Warning: error_log(/home/innerear/domains/innerearaudio.com/logs/page_parse_time.log): failed to open stream: Permission denied in /home/innerear/domains/innerearaudio.com/public_html/catalog/includes/functions/database.php on line 41

Warning: error_log(/home/innerear/domains/innerearaudio.com/logs/page_parse_time.log): failed to open stream: Permission denied in /home/innerear/domains/innerearaudio.com/public_html/catalog/includes/functions/database.php on line 48


is repeated several times on the page http://www.innerearaudio.com/catalog/index.php

Regards,
Onno Vrijburg

PS. have you solved you loadtime problems yet?

[dbeames]

Originally posted by Zero
These tweaks are fantastic, the difference in speed was BLAM-O!!! The only question I have is, what general system spec are these tweaks based on? And is there a guide/rule for creating a my.cnf for a 2.8HT w/1GB ram?

Thanks everyone!

This cnf file is for a server just as yours. Its for 2-3Ghz single processors with 1-2gigs of ram. You cange change alot of that stuff if you have a slower system, or even if you have a faster one. However this cnf is pretty well adaptable to most servers.

[Chrysalis]

anyone here left out log-bin?

I recently removed it after it ended up using 30 gig hd space, I assume I have lost mysql database replication feature but otherwise looks fine.

[dbeames]

I have left it out. I see no reason to have it. Its only for database replication as you say. Dunno. No need for it for me.

[intelliot]

What should I change for a server with 4 GB RAM?

[NetJaro]

Hi!



Processor Name AMD Athlon(tm) XP 2600+
Vendor ID AuthenticAMD
Processor Speed (MHz) 1825.955
Total Memory 479648 kB
Free Memory 104492 kB
Total Swap Memory 1052248 kB
Free Swap Memory 1012652 kB



I must a optymalize my MySQL. Who help me?

[jmstacey]

Here's an example:
http://help.directadmin.com/item.php?id=44

[NetJaro]

And who help me :rolleyes:

[torp]

I just did the optimization, and it helped alot. However, lately some of the queries have taken a long time to parse. I wen't into my.cnf again and doubled a few of the values (all of my sites on this server runs MySQL heavy sites, and some Exim and Apache services, which do not have a huge load - general server resource usage is around 0.4-0.8). However, this didn't help at all.

Is there any possibility that I might have gone overkill on my.cnf. I only have 1/2 GB of RAM, running a slow Celeron 2GHz. As long as I have a low server load, I can keep upping the values in my.cnf to get more MySQL cache, right?

[jlasman]

Based on what you've written it's reasonable to presume you're running out of memory and your system is switching into swap memory.

If so, services will run extrmely slowly.

Jeff

[torp]

Thanks!

I'm not sure about this. MySQL is usually running on around 4% of memory. CPU on the other hand is jumping up and down.

[ank1t]

I run big SQL websites on my servers. I would further like to add that I have a very limited knowledge on MySQL Optimization on SERVER Front. My server keeps crashing due to SQL load.

I made a my.cnf file as mentioned over here:
http://help.directadmin.com/item.php?id=44

But even then the server fails to handle the excessive load.

Someone suggested to increase and decrease the values like - query_cache_limit and query_cache_size.

What are the suggested settings for tweaking MySQL.

I have two servers:
----------------------------
1. AMD 64 3800, 1024MB RAM, CentOS 4.x, Direct Admin

2. AMD Sempron 2600/2048MB RAM, CentOS 4.x, Direct Admin

Thanks in Advance

[new new]

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

[eymbo]

Originally posted by new new
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

The file is located at /etc/my.cnf

[new new]

Originally posted by eymbo
The file is located at /etc/my.cnf


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?

[Kestas]

Hello,
Was using http://help.directadmin.com/item.php?id=44 for some time, and noticed, that

log-bin

is not needed cause it logs all query, after one month, i've got about ~80 GB logs this is real **** :D

So plz just coment it, and fix it on help page :D

[chartmusic]

Buffer issue.

$ 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?

[eymbo]

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.

[chartmusic]

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

[jlasman]

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:
# uptime
It gives you the top line of top so will show you what we call the server load.

Jeff

[chartmusic]

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


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

[jlasman]

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

[eymbo]

@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. ;)

[jlasman]

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

[chartmusic]

Thanks for the replies again.



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.

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

[jlasman]

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

[computerlady911]

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?

[chartmusic]

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?

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.

[chartmusic]

I don't know any way of finding out more about memory usage.

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

After 24 hours here is free values.
free
total used free shared buffers cached
Mem: 2073516 1519256 554260 0 61064 1067596
-/+ buffers/cache: 390596 1682920
Swap: 2096472 0 2096472

[andyreed]

[B]I suggest implementing them to alieviate the load MySQL may cause when you run big SQL sites on your server:Allow me to share /etc/my.cnf file we use on our clients' MySQL servers:

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

[chartmusic]

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

[andyreed]

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.Yes, the directive should be set to 2X the number of processors in your machine for best performance. Thank you!

[chartmusic]

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

After 24 hours here is free values.
free
total used free shared buffers cached
Mem: 2073516 1519256 554260 0 61064 1067596
-/+ buffers/cache: 390596 1682920
Swap: 2096472 0 2096472


Day two

free
total used free shared buffers cached
Mem: 2073516 1799728 273788 0 59436 1268384
-/+ buffers/cache: 471908 1601608
Swap: 2096472 0 2096472

Not really happy that memory free has gone down since yesterday. Wondering if Swap will start to be used next and then perhaps site slowdown? Any thoughts?

[andyreed]

Day two

free
total used free shared buffers cached
Mem: 2073516 1799728 273788 0 59436 1268384
-/+ buffers/cache: 471908 1601608
Swap: 2096472 0 2096472There are many variables invloved in memory use and from what you posted, I don't see any problems. If you suspect that there is a memory leak, you should contact your Data Center to test your memory.

[whistler]

Should a dual core count as 2 processors? I just upgraded to a pentium dual core 2.8 GHz and 2 GB RAM.

I have some mysql intensive sites with joomla and so on. With my celeron mysql was crashing but this optimization helped alot.
http://help.directadmin.com/item.php?id=44

I am wondering how to optimize with upgrade.

[eymbo]

Should a dual core count as 2 processors? I just upgraded to a pentium dual core 2.8 GHz and 2 GB RAM.

I have some mysql intensive sites with joomla and so on. With my celeron mysql was crashing but this optimization helped alot.
http://help.directadmin.com/item.php?id=44

I am wondering how to optimize with upgrade.

Dual core means that there are two processors on one die so yes your Pentium D will count as two processors.

[sharebay]

how to optimize MySQL 5.x :p

[smtalk]

The same as MySQL 4.1 :) Edit /etc/my.cnf and optimize what you want.

[sharebay]

Any risks applying this configuration to MySQL 5.0.41 :)

http://help.directadmin.com/item.php?id=44

I currently have this conf.:
#DO NOT MODIFY THE FOLLOWING COMMENTED LINES!
#Created with ELS from www.servermonkeys.com
#els-build=5.0
[mysqld]
datadir=/var/lib/mysql
skip-locking
skip-innodb
skip-networking
safe-show-database
query_cache_limit=1M
query_cache_size=32M ## 32MB for every 1GB of RAM
query_cache_type=1
max_user_connections=200
max_connections=500
interactive_timeout=10
wait_timeout=20
connect_timeout=20
thread_cache_size=128
key_buffer=128M ## 128MB for every 1GB of RAM
join_buffer=1M
max_connect_errors=20
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=1M ## 1MB for every 1GB of RAM
read_buffer_size=1M ## 1MB for every 1GB of RAM
read_rnd_buffer_size=1M ## 1MB for every 1GB of RAM
thread_concurrency=2 ## Number of CPUs x 2
myisam_sort_buffer_size=64M
server-id=1
log_slow_queries=/var/log/mysql-slow-queries.log
long_query_time=2
collation-server=latin1_general_ci

[mysql.server]
user=mysql

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

[mysqldump]
quick
max_allowed_packet=16M

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

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

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

[mysqlhotcopy]
interactive-timeout

For Pentium 4 2.80GHz with 1GB Ram.

[Duboux]

Fantastic performance improvement :D

I have a dual core, but with hyperthreading so it thinks it has 4 of them or something.. anyway, used thread_concurrency=4
and the rest.

Also added:
log_slow_queries=/var/log/mysql-slow-queries.log
long_query_time=10
Because mysql> status
showed me I had 30 slow queries..

Unfortunately since I restarted MySql those queries were gone, so I haven't had the chance yet to see which queries on which clients are slowing the server down.

I guess I'll need to wait, but the speed of forums on my clients has defenately improved.

[Duboux]

Bugger...

# cat /var/log/mysql-slow-queries.log
cat: /var/log/mysql-slow-queries.log: No such file or directory

That went well... :(

I have no idea what the slow queries are, so I can't tackle them..
And I don't know why it didn't log those queries :(

[Duboux]

Interesting..

I think mysql didn't have permission to write to that logfile...

So I created it, and chown'd it..
# chown mysql:root /var/log/mysql-slow-queries.log
# chmod 660 /var/log/mysql-slow-queries.log

or, if you use the default filename:
# chown mysql:root /var/log/mysql-slow.log
# chmod 660 /var/log/mysql-slow.log

Let's wait and see...


btw, it's not recommended to turn these logs on, as they will slow down ur server..
But if you want to tackle these slow queries, than it's a good thing to use for a day or 2..

Next to that... adding memory ain't the best solution to speed up mysql.
It's usually the users that have crappy queries.. Not even 6Gb of ram would fix that..

So checking these slow queries and contacting the user abt it is a good way to go :)

[seachen]

may i know the server spec as below how to optimize it ?

Processor Speed (MHz) Pentium IV 3.0
Total Memory 1025712 kB

can somebody guide for me ?

thank you.

[jlasman]

seachen,

Optimization for MySQL is not cut and dried. You try recommendations and see if they help. And tweak them, as your server will run differently from others.

Look above in this thread; you'll find some suggestions.

In general the best way to speed up a server running 1 GB of memory is to add another 1 GB of memory.

Jeff

[welch]

I have found that if it is a heavy used database, often putting it on its own system and spreading it across as many spindles as possible with as much memory as possible is the best solution. This way your data is mostly loaded in memory, with occasional writes.

[morfargekko]

I have done the optimization on two of my boxes (CentOS) now and it went well so far. On my third box (Debian) the my.cnf is located at /etc/mysql/my.cnf and I wounder if this is the file to edit or if there should be a working my.cnf in /etc .
How can I check that the my.cnf that acctually is on the box is working its wounders? :confused:

[lkbryant]

having mysql on separate server mean latency time is going to slow down performance no?

[jlasman]

Yes, perhaps. But whether or not it will be noticeable depends on how much load it takes of your shared hosting server.

Jeff