PDA

View Full Version : Help with mySQL and server optimiztion :)

thenetbox
Sat 11th Jan '03, 7:54pm
I just changed servers and now i'm on a Celeron 1.3 with 1024 MB RAM and a 60 GB HD

My VB has 16500+ members and the database is about 1500 MB.. it is VB 2.2.8

Users are having problems uploading and viewing attachments :(

MySQL version is - 3.23.41
PHP is 4.2.2
http://www.darkforum.com/phpinfo.php

top stats:

3:26am up 3 days, 11:35, 1 user, load average: 0.20, 0.18, 0.25
132 processes: 128 sleeping, 4 running, 0 zombie, 0 stopped
CPU states: 17.6% user, 3.1% system, 3.1% nice, 75.9% idle
Mem: 1023108K av, 1009684K used, 13424K free, 0K shrd, 52492K buff
Swap: 1044216K av, 11488K used, 1032728K free 781600K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
23689 apache 15 0 11672 11M 9548 S 10.3 1.1 0:00 httpd
23583 apache 16 0 12468 12M 9556 R 6.1 1.2 0:00 httpd
23715 mysql 26 10 19312 18M 1792 R N 1.9 1.8 0:00 mysqld
23684 apache 15 0 11112 10M 9496 S 1.3 1.0 0:00 httpd
23709 root 15 0 1112 1112 836 R 0.7 0.1 0:00 top
21051 apache 15 0 11800 11M 9532 S 0.3 1.1 0:14 httpd
7410 mysql 26 10 19312 18M 1792 S N 0.1 1.8 0:08 mysqld
21054 apache 15 0 11892 11M 9540 S 0.1 1.1 0:14 httpd
23586 apache 15 0 11412 11M 9536 S 0.1 1.1 0:00 httpd
1 root 15 0 512 460 436 S 0.0 0.0 0:04 init
2 root 15 0 0 0 0 SW 0.0 0.0 0:00 keventd
3 root 15 0 0 0 0 SW 0.0 0.0 0:00 kapmd
4 root 34 19 0 0 0 SWN 0.0 0.0 0:00 ksoftirqd_CPU0
5 root 15 0 0 0 0 SW 0.0 0.0 0:08 kswapd
6 root 15 0 0 0 0 SW 0.0 0.0 0:10 bdflush
7 root 15 0 0 0 0 SW 0.0 0.0 0:00 kupdated
8 root 25 0 0 0 0 SW 0.0 0.0 0:00 mdrecoveryd
12 root 15 0 0 0 0 SW 0.0 0.0 0:23 kjournald
89 root 15 0 0 0 0 SW 0.0 0.0 0:00 khubd
212 root 15 0 0 0 0 SW 0.0 0.0 0:00 kjournald
686 root 15 0 0 0 0 SW 0.0 0.0 0:00 eth0
930 named 15 0 3232 3012 1984 S 0.0 0.2 0:00 named
932 named 15 0 3232 3012 1984 S 0.0 0.2 0:00 named
934 named 15 0 3232 3012 1984 S 0.0 0.2 0:01 named
935 named 15 0 3232 3012 1984 S 0.0 0.2 0:00 named
936 named 15 0 3232 3012 1984 S 0.0 0.2 0:00 named
953 root 16 0 1456 1316 1228 S 0.0 0.1 0:01 sshd
973 root 19 0 1256 1040 1040 S 0.0 0.1 0:00 sshd
1007 root 15 0 908 752 712 S 0.0 0.0 0:00 xinetd
1248 root 15 0 2476 2432 984 S 0.0 0.2 0:00 proftpd
1318 root 15 0 1260 1088 984 S 0.0 0.1 0:05 bandwidth_manag
1326 root 15 0 1024 620 600 S 0.0 0.0 0:00 urchinwebd
1327 nobody 17 0 1108 680 680 S 0.0 0.0 0:00 urchinwebd
1328 nobody 17 0 1108 680 680 S 0.0 0.0 0:00 urchinwebd
1329 nobody 17 0 1108 680 680 S 0.0 0.0 0:00 urchinwebd
1330 nobody 17 0 1108 680 680 S 0.0 0.0 0:00 urchinwebd
1331 nobody 17 0 1108 680 680 S 0.0 0.0 0:00 urchinwebd
1333 nobody 15 0 132 116 64 S 0.0 0.0 0:00 urchind
1350 root 15 0 672 604 564 S 0.0 0.0 0:00 crond
1398 daemon 15 0 588 544 524 S 0.0 0.0 0:00 atd
1436 root 16 0 428 368 368 S 0.0 0.0 0:00 mingetty
1437 root 16 0 428 368 368 S 0.0 0.0 0:00 mingetty
1438 root 16 0 428 368 368 S 0.0 0.0 0:00 mingetty
1439 root 16 0 428 368 368 S 0.0 0.0 0:00 mingetty
1440 root 16 0 428 368 368 S 0.0 0.0 0:00 mingetty
1441 root 15 0 476 416 416 S 0.0 0.0 0:00 agetty
1492 root 16 0 428 368 368 S 0.0 0.0 0:00 mingetty
2377 root 15 0 644 632 520 S 0.0 0.0 0:02 syslogd
2383 root 15 0 1116 496 452 S 0.0 0.0 0:00 klogd
2865 root 15 0 4596 2780 1608 S 0.0 0.2 0:00 psad
2867 root 15 0 2320 1472 1312 S 0.0 0.1 0:00 kmsgsd
2869 root 15 0 2464 1608 1328 S 0.0 0.1 0:00 diskmond
2872 root 15 0 2524 1620 1328 S 0.0 0.1 0:00 psadwatchd



/etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


mysqladmin variables


+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 121 |
| Aborted_connects | 110 |
| Bytes_received | 1305492516 |
| Bytes_sent | 3377163702 |
| Connections | 46242 |
| Created_tmp_disk_tables | 21 |
| Created_tmp_tables | 16864 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 5362 |
| Handler_read_first | 33866 |
| Handler_read_key | 7777292 |
| Handler_read_next | 21954780 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 9468656 |
| Handler_read_rnd_next | 419312520 |
| Handler_update | 2701865 |
| Handler_write | 1789606 |
| Key_blocks_used | 7793 |
| Key_read_requests | 38451744 |
| Key_reads | 27369 |
| Key_write_requests | 4468854 |
| Key_writes | 79203 |
| Max_used_connections | 40 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 64 |
| Open_files | 104 |
| Open_streams | 0 |
| Opened_tables | 1596 |
| Questions | 933925 |
| Select_full_join | 2950 |
| Select_full_range_join | 0 |
| Select_range | 201894 |
| Select_range_check | 0 |
| Select_scan | 122045 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 2 |
| Slow_queries | 251 |
| Sort_merge_passes | 0 |
| Sort_range | 194892 |
| Sort_rows | 10762076 |
| Sort_scan | 61287 |
| Table_locks_immediate | 982317 |
| Table_locks_waited | 1745 |
| Threads_cached | 0 |
| Threads_created | 46241 |
| Threads_connected | 1 |
| Threads_running | 1 |
| Uptime | 253323 |
+--------------------------+------------+


httpd stuff

KeepAlive On
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 10
StartServers 5
MaxClients 150
thenetbox
Sat 11th Jan '03, 8:00pm
Originally posted by thenetbox
I just changed servers and now i'm on a Celeron 1.3 with 1024 MB RAM and a 60 GB HD

My VB has 16500+ members and the database is about 1500 MB.. it is VB 2.2.8

Users are having problems uploading and viewing attachments :(

MySQL version is - 3.23.41
PHP is 4.2.2
http://www.darkforum.com/phpinfo.php

top stats:


3:26am up 3 days, 11:35, 1 user, load average: 0.20, 0.18, 0.25
132 processes: 128 sleeping, 4 running, 0 zombie, 0 stopped
CPU states: 17.6% user, 3.1% system, 3.1% nice, 75.9% idle
Mem: 1023108K av, 1009684K used, 13424K free, 0K shrd, 52492K buff
Swap: 1044216K av, 11488K used, 1032728K free 781600K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
23689 apache 15 0 11672 11M 9548 S 10.3 1.1 0:00 httpd
23583 apache 16 0 12468 12M 9556 R 6.1 1.2 0:00 httpd
23715 mysql 26 10 19312 18M 1792 R N 1.9 1.8 0:00 mysqld
23684 apache 15 0 11112 10M 9496 S 1.3 1.0 0:00 httpd
23709 root 15 0 1112 1112 836 R 0.7 0.1 0:00 top
21051 apache 15 0 11800 11M 9532 S 0.3 1.1 0:14 httpd
7410 mysql 26 10 19312 18M 1792 S N 0.1 1.8 0:08 mysqld
21054 apache 15 0 11892 11M 9540 S 0.1 1.1 0:14 httpd
23586 apache 15 0 11412 11M 9536 S 0.1 1.1 0:00 httpd
1 root 15 0 512 460 436 S 0.0 0.0 0:04 init
2 root 15 0 0 0 0 SW 0.0 0.0 0:00 keventd
3 root 15 0 0 0 0 SW 0.0 0.0 0:00 kapmd
4 root 34 19 0 0 0 SWN 0.0 0.0 0:00 ksoftirqd_CPU0
5 root 15 0 0 0 0 SW 0.0 0.0 0:08 kswapd
6 root 15 0 0 0 0 SW 0.0 0.0 0:10 bdflush
7 root 15 0 0 0 0 SW 0.0 0.0 0:00 kupdated
8 root 25 0 0 0 0 SW 0.0 0.0 0:00 mdrecoveryd
12 root 15 0 0 0 0 SW 0.0 0.0 0:23 kjournald
89 root 15 0 0 0 0 SW 0.0 0.0 0:00 khubd
212 root 15 0 0 0 0 SW 0.0 0.0 0:00 kjournald
686 root 15 0 0 0 0 SW 0.0 0.0 0:00 eth0
930 named 15 0 3232 3012 1984 S 0.0 0.2 0:00 named
932 named 15 0 3232 3012 1984 S 0.0 0.2 0:00 named
934 named 15 0 3232 3012 1984 S 0.0 0.2 0:01 named
935 named 15 0 3232 3012 1984 S 0.0 0.2 0:00 named
936 named 15 0 3232 3012 1984 S 0.0 0.2 0:00 named
953 root 16 0 1456 1316 1228 S 0.0 0.1 0:01 sshd
973 root 19 0 1256 1040 1040 S 0.0 0.1 0:00 sshd
1007 root 15 0 908 752 712 S 0.0 0.0 0:00 xinetd
1248 root 15 0 2476 2432 984 S 0.0 0.2 0:00 proftpd
1318 root 15 0 1260 1088 984 S 0.0 0.1 0:05 bandwidth_manag
1326 root 15 0 1024 620 600 S 0.0 0.0 0:00 urchinwebd
1327 nobody 17 0 1108 680 680 S 0.0 0.0 0:00 urchinwebd
1328 nobody 17 0 1108 680 680 S 0.0 0.0 0:00 urchinwebd
1329 nobody 17 0 1108 680 680 S 0.0 0.0 0:00 urchinwebd
1330 nobody 17 0 1108 680 680 S 0.0 0.0 0:00 urchinwebd
1331 nobody 17 0 1108 680 680 S 0.0 0.0 0:00 urchinwebd
1333 nobody 15 0 132 116 64 S 0.0 0.0 0:00 urchind
1350 root 15 0 672 604 564 S 0.0 0.0 0:00 crond
1398 daemon 15 0 588 544 524 S 0.0 0.0 0:00 atd
1436 root 16 0 428 368 368 S 0.0 0.0 0:00 mingetty
1437 root 16 0 428 368 368 S 0.0 0.0 0:00 mingetty
1438 root 16 0 428 368 368 S 0.0 0.0 0:00 mingetty
1439 root 16 0 428 368 368 S 0.0 0.0 0:00 mingetty
1440 root 16 0 428 368 368 S 0.0 0.0 0:00 mingetty
1441 root 15 0 476 416 416 S 0.0 0.0 0:00 agetty
1492 root 16 0 428 368 368 S 0.0 0.0 0:00 mingetty
2377 root 15 0 644 632 520 S 0.0 0.0 0:02 syslogd
2383 root 15 0 1116 496 452 S 0.0 0.0 0:00 klogd
2865 root 15 0 4596 2780 1608 S 0.0 0.2 0:00 psad
2867 root 15 0 2320 1472 1312 S 0.0 0.1 0:00 kmsgsd
2869 root 15 0 2464 1608 1328 S 0.0 0.1 0:00 diskmond
2872 root 15 0 2524 1620 1328 S 0.0 0.1 0:00 psadwatchd



/etc/my.cnf


[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


mysqladmin variables



+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 121 |
| Aborted_connects | 110 |
| Bytes_received | 1305492516 |
| Bytes_sent | 3377163702 |
| Connections | 46242 |
| Created_tmp_disk_tables | 21 |
| Created_tmp_tables | 16864 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 5362 |
| Handler_read_first | 33866 |
| Handler_read_key | 7777292 |
| Handler_read_next | 21954780 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 9468656 |
| Handler_read_rnd_next | 419312520 |
| Handler_update | 2701865 |
| Handler_write | 1789606 |
| Key_blocks_used | 7793 |
| Key_read_requests | 38451744 |
| Key_reads | 27369 |
| Key_write_requests | 4468854 |
| Key_writes | 79203 |
| Max_used_connections | 40 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 64 |
| Open_files | 104 |
| Open_streams | 0 |
| Opened_tables | 1596 |
| Questions | 933925 |
| Select_full_join | 2950 |
| Select_full_range_join | 0 |
| Select_range | 201894 |
| Select_range_check | 0 |
| Select_scan | 122045 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 2 |
| Slow_queries | 251 |
| Sort_merge_passes | 0 |
| Sort_range | 194892 |
| Sort_rows | 10762076 |
| Sort_scan | 61287 |
| Table_locks_immediate | 982317 |
| Table_locks_waited | 1745 |
| Threads_cached | 0 |
| Threads_created | 46241 |
| Threads_connected | 1 |
| Threads_running | 1 |
| Uptime | 253323 |
+--------------------------+------------+


httpd stuff


KeepAlive On
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 10
StartServers 5
MaxClients 150
oh.. and I'm using Zend Optimizer and Mod_gzip
thenetbox
Sat 11th Jan '03, 8:16pm
Originally posted by thenetbox
oh.. and I'm using Zend Optimizer and Mod_gzip


all of a sudden the load sky rocketted!! :(




4:03am up 3 days, 12:12, 1 user, load average: 5.04, 2.98, 1.36
129 processes: 126 sleeping, 3 running, 0 zombie, 0 stopped
CPU states: 37.5% user, 10.7% system, 4.9% nice, 46.8% idle
Mem: 1023108K av, 1006348K used, 16760K free, 0K shrd, 54024K buff
Swap: 1044216K av, 11488K used, 1032728K free 786096K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
25705 apache 15 0 11844 11M 9540 S 7.5 1.1 0:10 httpd
25739 apache 16 0 11532 11M 9532 S 7.1 1.1 0:06 httpd
25742 apache 15 0 11768 11M 9532 S 6.9 1.1 0:08 httpd
21055 apache 16 0 12152 11M 9528 S 1.7 1.1 0:27 httpd
25263 root 16 0 1144 1144 836 R 0.5 0.1 0:06 top
26940 mysql 26 10 18628 18M 1796 R N 0.3 1.8 0:00 mysqld
1 root 15 0 512 460 436 S 0.0 0.0 0:04 init
2 root 15 0 0 0 0 SW 0.0 0.0 0:00 keventd
3 root 15 0 0 0 0 SW 0.0 0.0 0:00 kapmd
4 root 34 19 0 0 0 SWN 0.0 0.0 0:00 ksoftirqd_CPU0
thenetbox
Sat 11th Jan '03, 9:03pm
and now after a few minutes it came down.. currently the only site on this server is darkforum.com and there are only 40 people online at this moment.


4:50am up 3 days, 12:59, 1 user, load average: 0.72, 0.76, 1.04
113 processes: 110 sleeping, 2 running, 1 zombie, 0 stopped
CPU states: 7.3% user, 2.1% system, 1.1% nice, 89.2% idle
Mem: 1023108K av, 996360K used, 26748K free, 0K shrd, 110796K buff
Swap: 1044216K av, 11480K used, 1032736K free 683584K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
5080 apache 15 0 14080 13M 11968 S 7.1 1.3 0:02 httpd
4453 root 15 0 1104 1104 836 R 0.7 0.1 0:04 top
5083 apache 15 0 0 0 0 Z 0.1 0.0 0:02 httpd <defunct>
1 root 15 0 512 460 436 S 0.0 0.0 0:04 init
2 root 15 0 0 0 0 SW 0.0 0.0 0:00 keventd
3 root 15 0 0 0 0 SW 0.0 0.0 0:00 kapmd
4 root 34 19 0 0 0 SWN 0.0 0.0 0:00 ksoftirqd_CPU0
5 root 15 0 0 0 0 SW 0.0 0.0 0:09 kswapd
6 root 15 0 0 0 0 SW 0.0 0.0 0:10 bdflush
7 root 15 0 0 0 0 SW 0.0 0.0 0:00 kupdated
8 root 25 0 0 0 0 SW 0.0 0.0 0:00 mdrecoveryd
12 root 15 0 0 0 0 SW 0.0 0.0 0:24 kjournald
89 root 15 0 0 0 0 SW 0.0 0.0 0:00 khubd
212 root 15 0 0 0 0 SW 0.0 0.0 0:00 kjournald
686 root 15 0 0 0 0 SW 0.0 0.0 0:00 eth0
930 named 15 0 3228 3008 1980 S 0.0 0.2 0:00 named
932 named 15 0 3228 3008 1980 S 0.0 0.2 0:00 named
934 named 15 0 3228 3008 1980 S 0.0 0.2 0:01 named
eva2000
Mon 13th Jan '03, 8:30pm
celery cpu could also be not handling the load, but try this first, replace my.cnf with below one and restart mysql


[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable = max_connections=450
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=64
set-variable = wait_timeout=14400
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10

[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
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
dzeanah
Mon 13th Jan '03, 8:43pm
What problem are you having?

I was having an issue with 2.2.9 where attachments simply wouldn't appear. PHP was not running in safe mode, but the only way I could make it work was to place a /tmp directory under /admin (under the forum root wouldn't work) chmod 777 and use that for safe mode uploads.

Worked fine then.
eva2000
Tue 14th Jan '03, 12:09pm
Originally posted by dzeanah
What problem are you having?

I was having an issue with 2.2.9 where attachments simply wouldn't appear. PHP was not running in safe mode, but the only way I could make it work was to place a /tmp directory under /admin (under the forum root wouldn't work) chmod 777 and use that for safe mode uploads.

Worked fine then. problem right now is his mysql configuration isn't optimised.. but running on default values which isn't adequate
thenetbox
Tue 14th Jan '03, 4:44pm
Thank you Thank you Thank you :)

I just made the changes and restarted :)

Yes problems are that its not optimized.. and some people were complaining of slow speeds.

Should I wait and see if the performance is better now? :)
thenetbox
Tue 14th Jan '03, 4:54pm
hmm.. after I restarted it.. the Load when to 2.56

top showed Apache going crazy.. 2 instances taking up 45% each..

then I turned off apache.. and turned it back on.. and now load is 0.19 :-/

Could Zend Optimizer be doing this?
thenetbox
Tue 14th Jan '03, 5:04pm
now there are 26 people on.. and its 0.56 :(


edit:
now its 0.08.. heh..
eva2000
Tue 14th Jan '03, 10:35pm
Originally posted by thenetbox
hmm.. after I restarted it.. the Load when to 2.56

top showed Apache going crazy.. 2 instances taking up 45% each..

then I turned off apache.. and turned it back on.. and now load is 0.19 :-/

Could Zend Optimizer be doing this? remove zend optimiser and replace it with php accelerator - install guide at http://i4net.tv/marticle/get.php?action=getarticle&articleid=31 :)