Announcement

**JamesUS** · Sat 17 Mar '01, 12:40am

I think more RAM is probably a good idea - those load averages are pretty high.
Have you optimised your configuaration before? If not post in the Server Config forum with those specs and hopefully someone there will be able to help you optimise it.
If optimisation doesn't help then certainly RAM is the way to go.

**George L** · Sat 17 Mar '01, 1:16am

you're with rackspace which means you have a AMD Duron 650 mhz cpu not a P3 650 mhz cpu

also looking at your slow queries from mysql you're table_cache may not be optimised. also your swap file usage is kinda high so more ram might be needed

I was with rackspace once with a AMD K6/2 500 , 384mb ram and had all sorts of problems

get the extended status script i have in my sig and put it on your server and let's see what's going on with mysql

also what settings do you have in your /etc/my.cnf file ?

**theprof** · Sat 17 Mar '01, 1:25am

THanks for all your help. I'll post info when I can get it. However, where can I find the my.cnf file? It doesnt seem to be in /etc

**Chris Schreiber** · Sat 17 Mar '01, 1:35am

The following command should show any my.cnf files on your system:

Code:

find / -name my.cnf -print

It's possible there isn't one if it wasn't installed or modified. Also, can you post the output of the following commands:

Code:

mysqladmin -uroot -ppassword variables
mysqladmin -uroot -ppassword extended-status

**theprof** · Sat 17 Mar '01, 1:35am

The URL of my status script. Thanks! I really appreciate your advice. Please tell me what I should do now.

http://www.adolescentadulthood.com/status.php

**George L** · Sat 17 Mar '01, 1:39am

Originally posted by theprof
The URL of my status script. Thanks! I really appreciate your advice. Please tell me what I should do now.

http://www.adolescentadulthood.com/status.php

the status script basically shows the same info as the command chris suggested

mysqladmin -uroot -ppassword extended-status

seems in the status.php script you forgot to specify your mysql root password in it - this line

$mysqllogin = "-uroot -pyourrootpassword";

yourrootpassword = you root password

**theprof** · Sat 17 Mar '01, 1:45am

+----------------------------+---------------------------+
| Variable_name | Value |
+----------------------------+---------------------------+
| back_log | 5 |
| connect_timeout | 5 |
| basedir | / |
| datadir | /var/lib/mysql/ |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| join_buffer | 131072 |
| flush_time | 0 |
| key_buffer | 8388600 |
| language | /usr/share/mysql/english/ |
| log | OFF |
| log_update | OFF |
| long_query_time | 10 |
| low_priority_updates | OFF |
| max_allowed_packet | 1048576 |
| max_connections | 100 |
| max_connect_errors | 10 |
| max_delayed_insert_threads | 20 |
| max_join_size | 4294967295 |
| max_sort_length | 1024 |
| max_write_lock_count | 4294967295 |
| net_buffer_length | 16384 |
| pid_file | /var/lib/mysql/mysqld.pid |
| port | 3306 |
| protocol_version | 10 |
| record_buffer | 131072 |
| skip_locking | ON |
| skip_networking | OFF |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer | 2097144 |
| table_cache | 64 |
| thread_stack | 65536 |
| tmp_table_size | 1048576 |
| tmpdir | /tmp/ |
| version | 3.22.32 |
| wait_timeout | 28800 |
+----------------------------+---------------------------+

+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 66 |
| Aborted_connects | 80 |
| Created_tmp_tables | 3854503 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 744260 |
| Handler_read_first | 8257499 |
| Handler_read_key | 308770342 |
| Handler_read_next | 183190153 |
| Handler_read_rnd | 2268463733 |
| Handler_update | 8403001 |
| Handler_write | 71105485 |
| Key_blocks_used | 7822 |
| Key_read_requests | 803927325 |
| Key_reads | 777823 |
| Key_write_requests | 12240031 |
| Key_writes | 10541403 |
| Max_used_connections | 100 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 63 |
| Open_files | 91 |
| Open_streams | 0 |
| Opened_tables | 719452 |
| Questions | 206236451 |
| Running_threads | 25 |
| Slow_queries | 183880 |
| Uptime | 9430083 |
+--------------------------+------------+

**theprof** · Sat 17 Mar '01, 1:47am

Ok, i've changed it. My bad

http://www.adolescentadulthood.com/status.php

**George L** · Sat 17 Mar '01, 1:53am

okay looks like you're using mysql default settings - you could tweak it some but with 100 max connections being used already you might wanna upgrade the memory to 512mb (that's gonna be expensive at rackspace

)

do you have a /etc/my.cnf file ?

things you can do before a ram upgrade

1. upgrade mysql from 3.22.32 to 3.23.32 (ask Chris about that he helped me with that when i was on rackspace

)

2. now in your my.cnf file located at /etc/my.cnf - you should be able to log into webmin's file manager to edit the my.cnf as i did it that way

in it

set-variable = max_connections=150
set-variable = join_buffer=3M
set-variable = key_buffer=16M
set-variable = record_buffer=4M
set-variable = sort_buffer=5M
set-variable = table_cache=256

now i'll pulling these out of the air from trial and error - not sure if 256mb ram would be okay with upping those settings in my.cnf

**George L** · Sat 17 Mar '01, 1:56am

Originally posted by theprof
Ok, i've changed it. My bad

http://www.adolescentadulthood.com/status.php

woah 111+ days uptime nice

forgot to mention when finished changing the my.cnf file restart mysql

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

**grumpy** · Sat 17 Mar '01, 4:26am

Since upgrading to beta 3, we've had countless thousands of the same types of errors on both of our forums!

It's slowing down now. We've worked with suggestions from here as well as an unusually helpful tech from our host. I'll list what all we've done, in hopes it may help you and anyone else that has experienced this. I'll copy our tech's comments for you.

....................
First, we increased max connections from 100 to 256.

....................

We then received errors such as this:
mysql error: Can't create/write to file '/var/tmp/#sql6e84_2021_0.MYD'
(Errcode: 9)

vB said "It is probably in /var/mysql and /var is generally partitioned to something smallish"

So our tech then did this:

"I just moved your MySQL databases from /var/db/mysql to /usr/local/db/mysql -
effectively eliminating the problem with space on the /var partition. Also, I've set your mysqld process to run as root again... this will set all file descriptor limits to infinite. We'll see if that solves it =)"

.......................

The problems continued (although much less frequent) but connection problems occurred again. I reported this to our tech and he said this:

"Funky, indeed. I purged out the /var/tmp directory... we'll see if that helps.

The problems with connectivity to the MySQL DB might very well be related to the PHP version. I can stick the most recent version of PHP on there, and that
might cure it. PHP devel sites and threads seem to indicate that the fix is in the newer version.."

He then installed PHP 4.0.5-dev. At that moment, our forums ceased functioning due to a conflict between the vB script and this version of PHP. vB suggested removing 'connection_timeout() or ' from line 175 in functions.php and then it worked perfectly.

.........................

The next day, the errors began again. Our technician did this:

"I've set an hourly timer to clear out /var/tmp, and converted your table formats to the new MYISAM style that is used in 3.23 SQL."

We've been error free for over 36 hours now.

**George L** · Sat 17 Mar '01, 4:31am

in theprof's case he can't set max_connections too high as he doesn't have enough ram at 256mb

setting it to 150 as i suggested to start would be okay - set it too high and you'd be swapping to disk more with 100+ connections theprof gets and only 256mb ram

**grumpy** · Sat 17 Mar '01, 4:46am

We only have 256 mg ram as well. We're on a unix with dual 800mhz processors. Have we done a bad thing by increasing our max connections?

**George L** · Sat 17 Mar '01, 4:56am

Originally posted by grumpy
We only have 256 mg ram as well. We're on a unix with dual 800mhz processors. Have we done a bad thing by increasing our max connections?

it all depends on your load, max_connections use (shown in the mysql status scrip) , if you're eating into your swap file, etc

it's all trial and error and i use the mysql status script as a guide to what i need to change my settings to depending on my load, visitors, max connections etc

i.e. currently for me i have a dual p3 866, 512mb ram and 18gb 10k scsi hdd

my.cnf for me is

set-variable = max_connections=200
set-variable = join_buffer=6M
set-variable = key_buffer=16M
set-variable = record_buffer=6M
set-variable = sort_buffer=8M
set-variable = table_cache=256
set-variable = max_allowed_packet=1M
set-variable = thread_stack=128K

my extended status is

Sat Mar 17 04:52:40 PST 2001
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 16067 |
| Aborted_connects | 3 |
| Bytes_received | 298655964 |
| Bytes_sent | 1477122224 |
| Connections | 17123 |
| Created_tmp_disk_tables | 27 |
| Created_tmp_tables | 39632 |
| Created_tmp_files | 6 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 10536 |
| Handler_read_first | 48648 |
| Handler_read_key | 14349746 |
| Handler_read_next | 61413235 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 16283748 |
| Handler_read_rnd_next | 404950093 |
| Handler_update | 172662 |
| Handler_write | 1849719 |
| Key_blocks_used | 15582 |
| Key_read_requests | 47246358 |
| Key_reads | 63872 |
| Key_write_requests | 149016 |
| Key_writes | 135303 |
| Max_used_connections | 50 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 83 | 32% of table_cache in use
| Open_files | 128 |
| Open_streams | 0 |
| Opened_tables | 89 |
| Questions | 2067554 |
| Select_full_join | 577 |
| Select_full_range_join | 0 |
| Select_range | 153356 |
| Select_range_check | 0 |
| Select_scan | 553789 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 1 | (execution time > 10 secs)
| Sort_merge_passes | 3 |
| Sort_range | 209058 |
| Sort_rows | 21922475 |
| Sort_scan | 347132 |
| Threads_cached | 0 |
| Threads_created | 17122 |
| Threads_connected | 15 |
| Threads_running | 1 |
| Uptime | 236556 | 2 days 17 hrs 42 mins 36 secs
+--------------------------+------------+

Key Reads/Key Read Requests = 0.001352 (Cache hit = 99.998648%)
Key Writes/Key Write Requests = 0.907976
Connections/second = 0.072 (/hour = 260.584)
KB received/second = 1.233 (/hour = 4438.533)
KB sent/second = 6.098 (/hour = 21952.549)
Temporary Tables Created/second = 0.168 (/hour = 603.135)
Opened Tables/second = 0.000 (/hour = 1.354)
Slow Queries/second = 0.000 (/hour = 0.015)
% of slow queries = 0.000%
Queries/second = 8.740 (/hour = 31464.830)

key stuff is in bold
and is explained in some of the links in my sig ie

http://vbulletin.com/forum/showthread.php?threadid=3452

http://www.mysql.com/information/pre...719/index.html

and variables are explained at

http://www.mysql.com/documentation/m...SHOW_VARIABLES

Announcement

Excessive database errors with beta2, suggestions?"

Excessive database errors with beta2, suggestions?"

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment