Need help tunning Mysql for my server...( Chris ?)

[Sharg]

Hi :)
Sysem: 1gig Ram Dual p3-700

Here are my current settings, I would like optimal settings according to my server specs. Note that Vbulletin will not be the only application on my server, so it shouldn't use 100% of the ressources :)

(Notice, what is the [safe_mysqld] running ? I noticed on others mysql conf they don't have this variable).

---------------------------------
The mysql conf:

# Example mysql config file.

# You can copy this to one of:
# /etc/mysql/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# One can use all long options that the program supports.
# Run the program with --help to get a list of available options

# This will be passed to all mysql clients
[client]
#password = my_password
port = 3306
socket = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[safe_mysqld]
err-log = /var/log/mysql.err

[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
log = /var/log/mysql.log
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-locking
set-variable = key_buffer=16M
set-variable = max_allowed_packet=1M
set-variable = thread_stack=128K

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

[mysql]
no-auto-rehash

[isamchk]
set-variable = key_buffer=16M


[Edited by Benj on 10-30-2000 at 07:52 AM]

[Sharg]

ansi_mode | OFF |
| back_log | 50 |
| basedir | /usr/ |
| bdb_cache_size | 8388600 |
| bdb_home | /var/lib/mysql/ |
| bdb_logdir | |
| bdb_tmpdir | /tmp/ |
| character_set | latin1 |
| character_sets | latin1 big5 cp1251 cp1257 croat czech danish dec8 dos estonia euc_kr gb2312 gbk german1 greek hebrew hp8 hungarian koi8_ru koi8_ukr latin2 swe7 usa7 win1250 win1251 win1251ukr ujis sjis tis620 |
| connect_timeout | 5 |
| concurrent_insert | ON |
| datadir | /var/lib/mysql/ |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| join_buffer_size | 131072 |
| flush | OFF |
| flush_time | 0 |
| init_file | |
| interactive_timeout | 28800 |

key_buffer_size | 16773120 |
| language | /usr/share/mysql/english/ |
| locked_in_memory | OFF |
| log | ON |
| log_update | OFF |
| log_bin | OFF |
| log_slave_updates | OFF |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 1047552 |
| max_connections | 100 |
| max_connect_errors | 10 |
| max_delayed_threads | 20 |
| max_heap_table_size | 16777216 |
| max_join_size | 4294967295 |
| max_sort_length | 1024 |
| max_tmp_tables | 32 |
| max_write_lock_count | 4294967295 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| net_retry_count | 10 |
| pid_file | /var/run/mysqld/mysqld.pid |
| port | 3306 |
| protocol_version | 10 |
| record_buffer | 131072 |
| skip_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slow_launch_time | 2 |
| socket | /var/run/mysqld/mysqld.sock |
| sort_buffer | 2097144 |
| table_cache | 64 |
| table_type | MYISAM |
| thread_stack | 131072 |
| thread_cache_size | 0 |
| timezone | CEST |
| tmp_table_size | 1048576 |
| tmpdir | /tmp/ |
| version | 3.23.25-beta-log |
| wait_timeout | 28800 |

[Sharg]

Also, I heard a debate about if Mysql shoud be set or not to use sessions (persisant connexions ?) for each user.
I would like your thoughts on this as if I should enable it or disable it (and how) ? Or maybe this is done with the php setting ?

Thanks
Benj

[Edited by Benj on 10-30-2000 at 07:59 AM]

[Dave#]

cross posting really won't get you a response

[Sharg]

I don't cross post.
I deleted the other post as soon as this one was posted.. I felt it was more appropriate here in the mysql forum.

Benj

[Chris Schreiber]

Originally posted by Benj
(Notice, what is the [safe_mysqld] running ? I noticed on others mysql conf they don't have this variable).


safe_mysqld is just a shell script that starts up MySQL processes and makes sure they keep running properly. It is a good thing to have :)

In you my.cnf file I would replace this:

[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
log = /var/log/mysql.log
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-locking
set-variable = key_buffer=16M
set-variable = max_allowed_packet=1M
set-variable = thread_stack=128K

With this:

# The MySQL server
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-locking
set-variable = max_connections=250
set-variable = join_buffer=6M
set-variable = key_buffer=64M
set-variable = record_buffer=6M
set-variable = sort_buffer=6M
set-variable = table_cache=250
set-variable = max_allowed_packet=1M
set-variable = thread_stack=128K


Also, how many people do you expect to have concurrently on the vBulletin forums? What else (besides the basic web services) will be running on this machine?

-Chris

[Sharg]

Thanks a lot for all you answers chris.
We plan to have between 100-200 concurent users for the next couple of months.... however this might take a little while to reach (we should be under the 100 concurent users for still a few months).

We do plan to host a live chat and an NNTP server serving 400 newsgroups+ another light mysql usage from another forum.

Also what is your thought on mysql_pconnect and mysql_connect ? What should I set and where?

Benj

[Sharg]

set-variable = join_buffer=6M
set-variable = key_buffer=64M
set-variable = record_buffer=6M
set-variable = sort_buffer=6M
set-variable = table_cache=250
set-variable = max_allowed_packet=1M
set-variable = thread_stack=128K


Well I just thought about this:
Is there a particular reason to keep key buffer and other value so low ?

Considering I have 1 gig ram, why don't I just put 256M at key buffer ? Would it help or is there any reason to limit it to 64M ?

I looked at the mysql manual and they also recommend 64M but they don't tell exactly why we shouldn't put more.

Benj

[eva2000]

read the mysql optimisation guide which touches on that question in my sig by J.Eng...


The key buffer is a strange little item. It will start out small but over usage time, it will grow. It will to continue to grow until it peaks to your allocated key buffer size, and it doesn't shrink back down (even if there are key buffers that will never be used again). Here's where having good indexes comes into play. (Actually, you can shrink the buffer completely, with a flush command). ........

Linux has a dynamic disk cache. Allocate too much and you take away memory from the disk cache. When MySQL (or other applications) need to read some
disk data, it will have to go for direct disk access to get the data. Very bad. Also, other processes (e.g. system processes, daemons, cron jobs, admin tools, some user who wants to play a game, etc.) may be using or will need some unknown amount of memory. I enjoy math, but even I'm not going to calculate all the unknowns.

Generally, you want your settings to be enough to get the job done within a reasonable amount of time. Not too big, not too small. You can make them a little bigger so you can sleep better at night, but make things too big and you'll suffer the consequences - slow system performance or worse, nasty hate mail from your users.

[Sharg]

Hmmm, i was sure there was kind of specific reason like this :/

Thanks for the answer Eva.

Benj

[eva2000]

your welcome, the guide in my sig is great reading - http://vbulletin.com/forum/showthread.php?threadid=3452

i play around with my settings until i found the right settinsg for my forum based on concurrent visitors, and the mysql-extended variables

[Chris Schreiber]

Originally posted by Benj
Considering I have 1 gig ram, why don't I just put 256M at key buffer ? Would it help or is there any reason to limit it to 64M ?

Benj-

I would recommend starting out at 64M for the key_buffers and tune from there.... This is almost always enough, however you can slowly increase it from this value if you find you are having performance problems.

-Chris

[Sharg]

What about:
[isamchk]
set-variable = key_buffer=64M

I increased it to 64m from 16, to match Key buffer.
Is it a good thing ? Or should I put it back to 16 ?

Benj

[Chris Schreiber]

Originally posted by Benj
I increased it to 64m from 16, to match Key buffer.
Is it a good thing ? Or should I put it back to 16 ?

Nope that's ok... that will be used whenever you are running the isamchk utility.....

-Chris

[TopGeek]

where do I find the my.cnf? I can't locate it..
I found the ones in usr/share/mysql - but none of them were specifically my.cnf (there was big, medium, small)

also,

After implementing these changes do they take effect immediately or do I need to restart something?

If I do, what's the command - forgive me, I'm a drowning newbie to this.

I just turned on my board today and it's overloaded (300 or so vB users logged in within 20 minutes and now it's giving errors).. I'm trying to wade through the archives and see what I can do.

[Edited by TopGeek on 12-21-2000 at 01:17 PM]

[Freddie Bingham]

unix prompt> find / -name 'my.cnf'