Just Upgraded My Server But Still Getting Errors

[ibeblunt]

Ok, I finally got a new server. [See specs below] However, my users are still getting errors that the DB is busy or the dreadful error message. If anyone can help me tweek my system, much appreciated. Today I had about 175 users on at once.

vBulletin 2.2.1 Settings:

Users: 67,706
Posts: 474,561 [Today I'm pruning...]

Simultaneous Sessions: 300
*NIX Load Limit: 5.00
GZIP compression level: 1

Note: Attachments are turned off.

[config.php]
$usepconnect = 0;

Hardware:

1 GHZ PIII
512 MB RAM

Software:
Linux 6.2
Apache 1.3.22
PHP 4.0.6
MySQL 3.23.36

Other:
GZip Enabled
Zend Optimizer
my_phpinfo.php
Mysql extended status script

I will post my info below this thread...

[ibeblunt]

top

8:33pm up 2 days, 20:48, 1 user, load average: 0.69, 1.13, 1.60
87 processes: 86 sleeping, 1 running, 0 zombie, 0 stopped
CPU states: 6.0% user, 4.0% system, 0.0% nice, 3.2% idle
Mem: 523864K av, 464592K used, 59272K free, 0K shrd, 28608K buff
Swap: 136512K av, 0K used, 136512K free 274716K cached

mysqladmin -uroot -p variables

| Variable_name           | Value
                                                                                                       |
| ansi_mode               | OFF
                                                                                                       |
| back_log                | 50
                                                                                                       |
| basedir                 | /
                                                                                                       |
| binlog_cache_size       | 32768
                                                                                                       |
| character_set           | latin1
                                                                                                       |
| concurrent_insert       | ON
                                                                                                       |
| connect_timeout         | 5
                                                                                                       |
| datadir                 | /var/lib/mysql/
                                                                                                       |
| delay_key_write         | ON
                                                                                                       |
| delayed_insert_limit    | 100
                                                                                                       |
| delayed_insert_timeout  | 300
                                                                                                       |
| delayed_queue_size      | 1000
                                                                                                       |
| flush                   | OFF
                                                                                                       |
| flush_time              | 0
                                                                                                       |
| have_bdb                | NO
                                                                                                       |
| have_gemini             | NO
                                                                                                       |
| have_innobase           | NO
                                                                                                       |
| have_isam               | YES
                                                                                                       |
| have_raid               | NO
                                                                                                       |
| have_ssl                | NO
                                                                                                       |
| init_file               |
                                                                                                       |
| interactive_timeout     | 28800
                                                                                                       |
| join_buffer_size        | 131072
                                                                                                       |
| key_buffer_size         | 268431360
                                                                                                       |
| language                | /usr/share/mysql/english/
                                                                                                       |
| large_files_support     | ON
                                                                                                       |
| locked_in_memory        | OFF
                                                                                                       |
| log                     | OFF
                                                                                                       |
| log_update              | OFF
                                                                                                       |
| log_bin                 | ON
                                                                                                       |
| log_slave_updates       | OFF
                                                                                                       |
| long_query_time         | 10
                                                                                                       |
| low_priority_updates    | OFF
                                                                                                       |
| lower_case_table_names  | 0
                                                                                                       |
| max_allowed_packet      | 1047552
                                                                                                       |
| max_binlog_cache_size   | 4294967295
                                                                                                       |
| max_binlog_size         | 1073741824
                                                                                                       |
| max_connections         | 500
                                                                                                       |
| max_connect_errors      | 10
                                                                                                       |
| max_delayed_threads     | 20
                                                                                                       |
| max_heap_table_size     | 16777216
                                                                                                       |
| max_join_size           | 4294967295
                                                                                                       |
| max_sort_length         | 1024
                                                                                                       |
| max_user_connections    | 0
                                                                                                       |
| max_tmp_tables          | 32
                                                                                                       |
| max_write_lock_count    | 4294967295
                                                                                                       |
| myisam_recover_options  | OFF
                                                                                                       |
| myisam_sort_buffer_size | 67108864
                                                                                                       |
| net_buffer_length       | 16384
                                                                                                       |
| net_read_timeout        | 30
                                                                                                       |
| net_retry_count         | 10
                                                                                                       |
| net_write_timeout       | 60
                                                                                                       |
| open_files_limit        | 0
                                                                                                       |
| pid_file                | /var/lib/mysql/www.sohh.com.pid
                                                                                                       |
| port                    | 3306
                                                                                                       |
| protocol_version        | 10
                                                                                                       |
| record_buffer           | 1044480
                                                                                                       |
| query_buffer_size       | 0
                                                                                                       |
| safe_show_database      | OFF
                                                                                                       |
| server_id               | 1
                                                                                                       |
| skip_locking            | ON
                                                                                                       |
| skip_networking         | OFF
                                                                                                       |
| skip_show_database      | OFF
                                                                                                       |
| slow_launch_time        | 2
                                                                                                       |
| socket                  | /var/lib/mysql/mysql.sock
                                                                                                       |
| sort_buffer             | 1048568
                                                                                                       |
| table_cache             | 256
                                                                                                       |
| table_type              | MYISAM
                                                                                                       |
| thread_cache_size       | 8
                                                                                                       |
| thread_stack            | 65536
                                                                                                       |
| transaction_isolation   | READ-COMMITTED
                                                                                                       |
| timezone                | EST
                                                                                                       |
| tmp_table_size          | 1048576
                                                                                                       |
| tmpdir                  | /tmp/
                                                                                                       |
| version                 | 3.23.36-log
                                                                                                       |
| wait_timeout            | 28800
                                                                                                       |

[ibeblunt]

mysqladmin -uroot -p extended-status

+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| Aborted_clients          | 6          |
| Aborted_connects         | 4          |
| Bytes_received           | 463562634  |
| Bytes_sent               | 3667424512 |
| Connections              | 126123     |
| Created_tmp_disk_tables  | 190        |
| Created_tmp_tables       | 49350      |
| Created_tmp_files        | 2          |
| Delayed_insert_threads   | 0          |
| Delayed_writes           | 0          |
| Delayed_errors           | 0          |
| Flush_commands           | 1          |
| Handler_delete           | 22106      |
| Handler_read_first       | 683        |
| Handler_read_key         | 19761847   |
| Handler_read_next        | 314434210  |
| Handler_read_prev        | 1373       |
| Handler_read_rnd         | 25109264   |
| Handler_read_rnd_next    | 860099256  |
| Handler_update           | 469921     |
| Handler_write            | 2056224    |
| Key_blocks_used          | 12511      |
| Key_read_requests        | 113722912  |
| Key_reads                | 10325      |
| Key_write_requests       | 260912     |
| Key_writes               | 188239     |
| Max_used_connections     | 149        |
| Not_flushed_key_blocks   | 0          |
| Not_flushed_delayed_rows | 0          |
| Open_tables              | 256        |
| Open_files               | 307        |
| Open_streams             | 0          |
| Opened_tables            | 755        |
| Questions                | 2755810    |
| Select_full_join         | 61         |
| Select_full_range_join   | 0          |
| Select_range             | 507728     |
| Select_range_check       | 0          |
| Select_scan              | 485391     |
| Slave_running            | OFF        |
| Slave_open_temp_tables   | 0          |
| Slow_launch_threads      | 36         |
| Slow_queries             | 167        |
| Sort_merge_passes        | 1          |
| Sort_range               | 536989     |
| Sort_rows                | 25741534   |
| Sort_scan                | 258349     |
| Table_locks_immediate    | 2945139    |
| Table_locks_waited       | 8647       |
| Threads_cached           | 7          |
| Threads_created          | 1482       |
| Threads_connected        | 1          |
| Threads_running          | 1          |
| Uptime                   | 96460      |
+--------------------------+------------+

mysqladmin -uroot -p --version
mysqladmin Ver 8.18 Distrib 3.23.36, for pc-linux-gnu on i686

my.cnf
my.cnf
I used the basic my-large.cnf that came with MySQL. I choose this one because it said for servers with 512MB RAM use this one. And I added the following lines to it:

set-variable = max_connections=500

I hope I've given everyone enough to work with.

[George L]

2 problems you have is you set your key_buffer too high 250+MB and your table_cache too low

try replaceing my.cnf with

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = max_connections=450
set-variable = key_buffer=16M
set-variable = join_buffer=4M
set-variable = record_buffer=4M
set-variable = sort_buffer=6M
set-variable = table_cache=1024
set-variable = thread_cache_size=256

[myisamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=16M
set-variable = write_buffer=16M

restart mysql

[ibeblunt]

Before I do this, should I just copy what you put and save it, or change each variable using vi?

[George L]

Originally posted by ibeblunt
Before I do this, should I just copy what you put and save it, or change each variable using vi?

just delete existing my.cnf file and create a new one in /etc/my.cnf and copy and paste what i recommended

[ibeblunt]

Done.

Thanks.

I will let you know how this works out.

Also, I know you didn't mention these settings, but should I change any of my vBulletin settings, like:

Simultaneous Sessions: 300
*NIX Load Limit: 5.00
GZIP compression level: 1

Note: Attachments are turned off.

$usepconnect = 0;

I've read somewhere on this site that the the use of persistant connections work well on dedicated servers but not very well on shared space.

Once again thanks!

[George L]

personally, i'd turn of session limiting and enable pconnects=1

see how that would work

[ibeblunt]

brief summary

Ok, I turned off session limiting and turned persistant connections. I setthe *NIX Load Limit at 10.00 [I hope this means that any request that takes over 10 seconds to process will be turned away.]

At 4:13pm (peek time) I got the following stats:

5.43, 5.85, 5.06 / 218

5.71, 5.53, 5.06 / 227

5.07, 5.39, 5.03 / 226

I have my server sending me stats every hour on the hour as well. I noticed the following stats immediately:

BEFORE THE MOD TO MY.CNF

12:00pm up 3 days, 12:14, 0 users, load average: 1.46, 1.16, 1.19
96 processes: 95 sleeping, 1 running, 0 zombie, 0 stopped
CPU states: 8.9% user, 3.9% system, 0.0% nice, 2.1% idle
Mem: 523864K av, 445128K used, 78736K free, 0K shrd, 21488K buff
Swap: 136512K av, 0K used, 136512K free 249552K cached

1:00pm up 3 days, 13:14, 1 user, load average: 1.42, 1.57, 1.53
131 processes: 129 sleeping, 1 running, 1 zombie, 0 stopped
CPU states: 9.2% user, 3.9% system, 0.0% nice, 2.8% idle
Mem: 523864K av, 495948K used, 27916K free, 0K shrd, 13564K buff
Swap: 136512K av, 0K used, 136512K free 248260K cached

AFTER THE MOD TO MY.CNF

2:00pm up 3 days, 14:14, 1 user, load average: 1.81, 1.96, 2.14
211 processes: 210 sleeping, 1 running, 0 zombie, 0 stopped
CPU states: 9.6% user, 4.0% system, 0.0% nice, 3.2% idle
Mem: 523864K av, 452104K used, 71760K free, 0K shrd, 12292K buff
Swap: 136512K av, 0K used, 136512K free 246860K cached

3:00pm up 3 days, 15:14, 1 user, load average: 1.91, 1.63, 1.80
277 processes: 276 sleeping, 1 running, 0 zombie, 0 stopped
CPU states: 10.0% user, 4.0% system, 0.0% nice, 3.7% idle
Mem: 523864K av, 519540K used, 4324K free, 0K shrd, 12212K buff
Swap: 136512K av, 0K used, 136512K free 238212K cached

I guess it's inevitable to get some users turned away, but I look at HardForum and get jealous.

Do you have a wish list at Amazon or anything?

[George L]

Glad i could help

don't have a Amazon wish list at the moment but do have a Donate button on my forums http://animeboards.com/index.php?s=

[ibeblunt]

I'd take it since you didn't tell me to adjust anything that those stats posted above are good. What should I keep an eye on?

[George L]

all you need to keep an eye on is this link http://www.sohh.com/apps/mysql.php

- memory swap usage
and the following from that page

Key Reads/Key Read Requests = 0.000324 (Cache hit = 99.999676%)
Key Writes/Key Write Requests = 0.779213
Connections/second = 0.721 (/hour = 2595.004)
KB received/second = 8.068 (/hour = 29043.096)
KB sent/second = 135.033 (/hour = 486119.242)
Temporary Tables Created/second = 0.848 (/hour = 3054.516)
Opened Tables/second = 0.020 (/hour = 73.127)
Slow Queries/second = 0.009 (/hour = 31.293)
% of slow queries = 0.018%
Queries/second = 47.023 (/hour = 169281.362)

Max_used_connections
Open_tables
Open_files
Open_streams
Opened_tables
Slow_queries
Threads_cached
Threads_created
Threads_connected

take a read of some of my sig links especially this one http://i4net.tv/marticle/get.php?act...le&articleid=3

and

http://www.mysql.com/doc/O/p/Optimising_the_Server.html

[ibeblunt]

Yea! Things are rocking now. I still haven't beat my peek of 339 users on at once, but nevertheless the pages are coming up MEGA FAST now.

Not like I know how off-hand, but what if a script was made to tweek mySQL...Like a person could enter thier info and then the proper my.cnf could be generated? I could produce the script if someone could produce the math.

[George L]

Originally posted by ibeblunt
Yea! Things are rocking now. I still haven't beat my peek of 339 users on at once, but nevertheless the pages are coming up MEGA FAST now.

Not like I know how off-hand, but what if a script was made to tweek mySQL...Like a person could enter thier info and then the proper my.cnf could be generated? I could produce the script if someone could produce the math.

glad to hear it's working

unfortunately there ain't no real formula it's trial and error and from experience and depends on a persons hardware and how mysql is used

[TurboFC3S]

Hey! You have all the extra stuff in your script too .. http://www.sohh.com/apps/mysql.php

Mind sharing your script?