New dedicated server - need tune up and compile advice

[Steve_S]

Hello,
I would be gratefull for some tuneup advice :) from the experts.

New dedicated server at venturesonline.com with WHM/Cpanel

hardware:
CPU1: 866mhz Pentium III (Default)
HardDrive Bay1: 40 GB IDE Drive
HardDrive Bay2: 40 GB IDE Drive (backup drive)
Memory: 1 GB RAM
Top:
<No traffic...........heheheh >
4:00pm up 3:11, 0 users, load average: 0.00, 0.00, 0.00
67 processes: 66 sleeping, 1 running, 0 zombie, 0 stopped
Mem: 1016752K av, 158728K used, 858024K free, 0K shrd, 38792K buff
Swap: 2052208K av, 0K used, 2052208K free 65156K cached
-----------------
php and mysql data at http://www.newshogs.com/phpinfo.php
Note: For some strange reason this shows mysql 3.23.49 but thats not true. Why? Edit: figured it out with the FAQ at Cpanel which says:

<<The API version you see in phpinfo is the builtin mysql api
included in php. If the buildapache application used the
libraries and headers of mysql installed on the server itself,
apache would not be able to work if the mysql version was
changed. Since mysql rpms are updated in cpanel whenever
redhat releases updates, this could automatically break
thousands of servers in a matter of a few hours, hence
apache and php will always be built with the -builtin option.>>

/etc/my.cnf from Geogies/eva2000 tweaks on other server:
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = max_connections=350
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=2M
set-variable = table_cache=1024
set-variable = thread_cache_size=256
set-variable = wait_timeout=3600
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10
[safe_mysqld]
open_files_limit=8192
[mysqldump]
quick
set-variable = max_allowed_packet=16M
[myisamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=16M
set-variable = write_buffer=16M
*******************************************

httpd.conf settings:
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 10
StartServers 5
MaxClients 150
******************************
Set up - tweak for:
- My current vB 2.4 which I will upgrade to 2.30 and eventually 3.x
load: users online: about 20 - 40 but sometimes spikes to 70 because of forum archive which uses mod_mime
- phpadsnew - will serve about 25K impressions per day
- PowerPhlogger - logs about 3K uniques per day

Note: I think I need gd ? and the compile of php looks funky so if you could please provide tweaks and compile strings that would be great.
Thanks in advance for the help :)

<wew>

[Steve_S]

and :)

data from shell:
mysqladmin variables

see: http://www.newshogs.com/mysql-data.htm

[Steve_S]

Where is the server geek :) help please :)

BTW, my current vB on another dedicated server uses mySQL 3.23.48 and the "dump" is 270 MB. Small potatoes compared to some of you guys.

Anything special I need to do when I set up the new db on the new box under mySQL 4.0.13 ?

Thanks in advance.

[eva2000]

hey steve how ya doing ? long time no see :D

1. regarding phpinfo's reported mysql libraries, what i've always done after running easyapache or when cpanel auto updates mysql to a new version such as 4.0.13, is to recompile php again with --with-mysql=/usr

if you ran /scripts/easyapache you should have a directory at /home/cpapachebuild/buildapache/

with a folder to your current php version i.e.

/home/cpapachebuild/buildapache/php-4.3.1

go into that directory and type


rm -rf config.cache
make clean


then to recompile php with mysql 4.0.13 client libraries type


./configure --with-apxs=/usr/local/apache/bin/apxs --with-xml --enable-bcmath --enable-calendar --enable-ftp --enable-magic-quotes --with-mysql=/usr --with-pear --enable-sockets --enable-track-vars --enable-versioning --with-zlib --with-gd


wait until it finishes then type


make

then wait until it finishes and type

make install

once it finishes, then restart apache and phpinfo should report gd installed and mysql 4.0.13 client libraries

2. then replace current my.cnf with below and restart mysql


[mysqld]
max_connections = 500
key_buffer = 16M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 128
wait_timeout = 7600
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
skip-innodb

[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

[Steve_S]

Eva2000 GOD has arrived and said: :)

< 1. regarding phpinfo's reported mysql libraries, what i've always done after running easyapache or when cpanel auto updates mysql to a new version such as 4.0.13, is to recompile php again with --with-mysql=/usr

if you ran /scripts/easyapache you should have a directory at /home/cpapachebuild/buildapache/ >

Thanks so much and I'm still learning the ropes with the WHM/Cpanel box.

Issue: Iv'e never run easyapache and neither has VO when they set the server up. Also, I do not have a /home/cpapachebuild directory.

Any ideas or help would be great?

[eva2000]

you'll have to ask VO about that since IIRC, VO doesn't use /scripts/easyapache at all, they use to have their own apachekit to recompile apache and php.. not sure if they still do that

[Steve_S]

Thanks much G :)

What the heck I said to myself as I accidently ran /scripts/easyapache

<Holding Breath> Interesting :)

Then I emailed the other REAL GEEK in my life and lets see what happens in about one hour :)

Sometimes I amaze myself with the dumb mistakes I make :)

[Steve_S]

OK Dr. Root aka Eva2000 :)

Things are working dandy except when I use your my.cnf in this thread. mySQL will not restart via WHM when I use your my.cnf ??

I am stumped. Ideas?

Current data:

http://www.newshogs.com/phpinfo.php

http://www.newshogs.com/mysql-data-rev-2.htm

my current my.cnf

cat /etc/my.cnf
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = max_connections=350
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=2M
set-variable = table_cache=1024
set-variable = thread_cache_size=256
set-variable = wait_timeout=3600
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10
[safe_mysqld]
open_files_limit=8192
[mysqldump]
quick
set-variable = max_allowed_packet=16M
[myisamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=16M
set-variable = write_buffer=16M

:)

[eva2000]

1. how are you restarting mysql
2. try adding these 3 lines back into my recommended my.cnf's [mysqld] group

port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking

3. also check your mysql error log to see what the error is, as root use in ssh telnet type

tail -150 /var/lib/mysql/yourhostname.err

4. try typing in ssh telnet as root user

/etc/rc.d/init.d/mysql stop

wait for a few secs then type

/etc/rc.d/init.d/mysql start

[Steve_S]

Much thanks G. The stop and start did the trick. No errors this time and all apears to be fine.

I used:

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
max_connections = 500
key_buffer = 16M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 128
wait_timeout = 7600
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
skip-innodb
[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

My new variables:

http://www.newshogs.com/mysql-data-rev-3.htm

Looks OK? All I need is a DB :)

[eva2000]

looks good to me :)

[Steve_S]

Thanks :)

Just in case this might help another geek:

Importing the vB dump of 280MB of 2.2.4 took about 45 minutes. Natch, their is no traffic on this server and loads never passed 1.8

HTH :)

[Steve_S]

Thanks for the great work. I have a question. Persistent connection for vB are OFF and the Forums are performing very nicely.
Current Data:
1:00pm up 9 days, 21:05, 0 users, load average: 0.09, 0.06, 0.01
103 processes: 102 sleeping, 1 running, 0 zombie, 0 stopped
Mem: 1016752K av, 867900K used, 148852K free, 0K shrd, 168408K buff
Swap: 2052208K av, 27220K used, 2024988K free 550344K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
11663 nobody 16 0 1056 1056 832 R 8.1 0.1 0:00 top

Http processes currently running = 14
Mysql processes currently running = 42
Netstat information summary
2 FIN_WAIT2
3 ESTABLISHED
3 SYN_RECV
20 TIME_WAIT
34 LISTEN
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| Aborted_clients | 30 |
| Aborted_connects | 4 |
| Bytes_received | 262182001 |
| Bytes_sent | 618535799 |
| Com_admin_commands | 0 |
| Com_alter_table | 133 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 79057 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 1 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 34 |
| Com_delete | 4340 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 13 |
| Com_flush | 38 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 13839 |
| Com_insert_select | 145 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 63 |
| Com_optimize | 13 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 499 |
| Com_replace_select | 13 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_select | 468435 |
| Com_set_option | 522 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 522 |
| Com_show_databases | 167 |
| Com_show_fields | 546 |
| Com_show_grants | 4 |
| 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_processlist | 2039 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 447 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 26 |
| Com_show_variables | 622 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 63 |
| Com_update | 351226 |
| Connections | 82567 |
| Created_tmp_disk_tables | 353 |
| Created_tmp_tables | 30291 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 13425 |
| Handler_read_first | 28932 |
| Handler_read_key | 4771816 |
| Handler_read_next | 6704685 |
| Handler_read_prev | 605352 |
| Handler_read_rnd | 1068685 |
| Handler_read_rnd_next | 265690315 |
| Handler_rollback | 0 |
| Handler_update | 353510 |
| Handler_write | 1283139 |
| Key_blocks_used | 15586 |
| Key_read_requests | 12990223 |
| Key_reads | 18212 |
| Key_write_requests | 52193 |
| Key_writes | 38045 |
| Max_used_connections | 35 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 76 | 7% of table_cache in use
| Open_files | 134 |
| Open_streams | 0 |
| Opened_tables | 466 |
| Questions | 1664414 |
| Qcache_queries_in_cache | 3948 |
| Qcache_inserts | 467359 |
| Qcache_hits | 659078 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1060 |
| Qcache_free_memory | 28101960 |
| Qcache_free_blocks | 2102 |
| Qcache_total_blocks | 10056 |
| Rpl_status | NULL |
| Select_full_join | 1006 |
| Select_full_range_join | 325 |
| Select_range | 51879 |
| Select_range_check | 0 |
| Select_scan | 131809 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 24 | (execution time > 10 secs)
| Sort_merge_passes | 0 |
| Sort_range | 49093 |
| Sort_rows | 141916863 |
| Sort_scan | 54498 |
| Table_locks_immediate | 1107924 |
| Table_locks_waited | 155 |
| Threads_cached | 33 |
| Threads_created | 36 |
| Threads_connected | 3 |
| Threads_running | 1 |
| Uptime | 608467 | 7 days 1 hr 1 min 7 secs
+--------------------------+-----------+

Key Reads/Key Read Requests = 0.001402 (Cache hit = 99.998598%)
Key Writes/Key Write Requests = 0.728929
Connections/second = 0.136 (/hour = 488.508)
KB received/second = 0.421 (/hour = 1514.845)
KB sent/second = 0.993 (/hour = 3573.796)
Temporary Tables Created/second = 0.050 (/hour = 179.217)
Opened Tables/second = 0.001 (/hour = 2.757)
Slow Queries/second = 0.000 (/hour = 0.142)
% of slow queries = 0.001%
Queries/second = 2.735 (/hour = 9847.519)
************************************************** ***
----> ISSUE: About 5 days ago AdvertPro was installed and we are trying to get mod_fastcgi to work properly. We keep loosing the mod_fastcgi socket every 5 to 10 hours.
The AdvertPro developer wants to change the value for wait_timeout in my.cnf which is currently set to 7600 back up to the default 28800. Would this be OK? Any drawbacks? Thanks in advance for your help. :)

[eva2000]

should be fine with 28800 for wait_timeout .. might want to try 14400 as well as 28800

[Steve_S]

A belated Thank you :) very much

[eva2000]

no probs Steve :D