UBB.threads PHP Forum Software Community Forums UBB.threads 6 Version 6 Support System Load Guidance

I represent the webhost for a threads user and am looking for some guidance on the proper configuration settings/hardware for the current load. During this user's peak hours the processor is being 100% utilized, this currently only occurs during the users peak hours which happen to be 10am - noon on Saturday & Sunday. During this time the system memory and disk appear to be within acceptable ranges. During these peak times there appear to be approx. 300+ users online with a total of about 1100 users in the database. The average number of users usually falls in the 20 - 50 range. Page views appears to be 30,000/day. Primarily the system is barely used except these peak hours. Here are the system details:

Hardware:
1 - 500MHz Sparc processor
2GB RAM
1 - 18GB Drive

Software:
Solaris 8
Apache 1.3.12
php 4.2.3 configured as DSO module w/o zlib support
mysql 3.23.45

Here is the my.cnf config:

[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
set-variable = max_connections=75
set-variable = key_buffer=256M
set-variable = max_allowed_packet=16M
set-variable = table_cache=1024
set-variable = sort_buffer=10M
set-variable = record_buffer=7M
set-variable = thread_cache=256
# Try number of CPU's*2 for thread_concurrency
set-variable = thread_concurrency=2
set-variable = myisam_sort_buffer_size=64M
log-bin
server-id = 1

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

[mysql]
no-auto-rehash

[isamchk]
set-variable = key_buffer=256M
set-variable = sort_buffer=256M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

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


The php.ini is basically the default after compilation (not sure if there is anything that can help here).

Since this is a dedicated server I am using persistant connections.

I assume that I could add another disk to seperate the database files from the os but outside of that I'm not sure if there is anything I can do to optimize the site without upgrading the hardware. If I do have to upgrade the hardware can someone provide me with some suggestions for approx. sizing.

Thank you for any assistance!

My first recommendation would be to consult directly with the folks at MySQl.com as they could provide the most direct and competent assistance with that compontent of the application. They are the experts in that space.

Regards,

Brett Harris
Infopop Corporation

Ok...outside of the sql part...let's assume that my current configuration for sql is adeguate (I'm using many suggestions found on the threadsdev.com mysql forum). Does anyone have any imput they could provide on whether the current system hardware can support the number of users in question?

Why is your max connections only 75? Are you running out of connections? Do your logs indicate this?

How about httpd connections/children? What is this set to? Are you running out? Getting MaxConnections errors in httpd error logs?

Who is mysql running as? user "mysql" or? what is this user's ulimit? Running out of file descriptors? Need to change the ulimit?

What does the error log say?

Next time it's loaded log into mysql and run this: SHOW PROCESSLIST

What does that say? Run it from command line, not phpmyadmin. You'll want to see if you have a large number of tables locked and waiting.

I assume that 18g drive is SCSI and is keeping up? when you run top do you see a few processes in D wait state? Is it mysqld or httpd that's running the load up?

On the board itself, is it set to show all days by default or just the last two weeks? The lower it's set by default the less there is to sort.

How many posts do you have stored?

Can you update apache and/or mysql?

On it's face that would seem to be enough machine there, but there is a whole lot of information missing, a lot of blanks that need to be filled in before we can give you more advice.

Honor The Victims

I was gonna jump in here as it is my board with the problems, but after thinking it over I decied to delete my comments and keep my big mouth shut....I'll leave it to the experts.

Thanks David for ANY help you can give us.

Musky

I'll offer a few things on threads configuration that might help a bit. Will try to limit this to one post in this thread so it doesn't interfere with the other stuff going on as proper machine configuration is the fundamental piece.

When browsing your forums as the anonymous user I see that by default you have it set to show about 50 posts per page on the postlist screen. Also, well over 20 posts per page over showflat. This requires quite a bit of processing especially if you have quite a few anon users browsing around.

You might want to edit your settings and set the posts per page quite a bit lower. The defaults are 10 and 10 for both the showflat and postlist screens. Changing this will only affect your users that aren't logged in but will make quite a bit of difference as it will cut down on a good load of processing that is required when the forums are really active as it sounds like yours are during peak times.

If you have 100 unregistered users looking at the postlist screen that's 5000 posts that need to be processed. If you bring the defaults back down to you can reduce this drastically.

If the majority of your users are logged in then it could be that they are running with these defaults or possibly even higher. Wouldn't worry about them at this point but at least change the defaults for starters. We've made these suggestions for another very active forum with very good results.

-------------------
Rick Baker
UBBThreads developer

The max_connections was initially the default of 100, I tuned this down to monitor the system under load and see if the processor load could be kept from reaching the maximum levels. The board has never received any system error messages, the only indication of problems is slugishness and being unresponsive during peak times. During these times the processor is completely utilized.

Here are the httpd settings:

MinSpareServers 10
MaxSpareServers 20
StartServers 10
MaxClients 150
MaxRequestsPerChild 0

mysql is running as user 'mysql'. Here is mysql's ulimit:

time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 8192
coredump(blocks) unlimited
nofiles(descriptors) 256
memory(kbytes) unlimited

Where does the file descriptors report it's errors? I have no errors in the mysql logs, /var/log/syslog or /var/adm/messages. I will have to run the show processlist when I get a significant load on the machine, this is what it reports now:

mysql> show processlist;
+----+----------+-----------+-------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+----------+-----------+-------+---------+------+-------+------------------+
| 1 | muskyman | localhost | musky | Sleep | 21 | | NULL |
| 2 | muskyman | localhost | musky | Sleep | 17 | | NULL |
| 3 | muskyman | localhost | musky | Sleep | 33 | | NULL |
| 4 | muskyman | localhost | musky | Sleep | 93 | | NULL |
| 5 | muskyman | localhost | musky | Sleep | 23 | | NULL |
| 6 | muskyman | localhost | musky | Sleep | 1 | | NULL |
| 7 | muskyman | localhost | musky | Sleep | 0 | | NULL |
| 9 | muskyman | localhost | musky | Sleep | 16 | | NULL |
| 10 | muskyman | localhost | musky | Sleep | 17 | | NULL |
| 11 | muskyman | localhost | musky | Sleep | 75 | | NULL |
| 12 | muskyman | localhost | musky | Sleep | 14 | | NULL |
| 13 | muskyman | localhost | musky | Sleep | 14 | | NULL |
| 14 | muskyman | localhost | musky | Sleep | 77 | | NULL |
| 15 | muskyman | localhost | musky | Sleep | 17 | | NULL |
| 16 | muskyman | localhost | musky | Sleep | 15 | | NULL |
| 17 | muskyman | localhost | musky | Sleep | 5 | | NULL |
| 18 | muskyman | localhost | musky | Sleep | 154 | | NULL |
| 19 | muskyman | localhost | musky | Sleep | 61 | | NULL |
| 20 | muskyman | localhost | musky | Sleep | 11 | | NULL |
| 21 | muskyman | localhost | musky | Sleep | 149 | | NULL |
| 24 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+----------+-----------+-------+---------+------+-------+------------------+
21 rows in set (0.00 sec)


Here is the prstat on Solaris currently, during a heavy load I have seen mysqld at the top w/ approx. 30% of the processor, what really adds up is the httpd processes that average anything from .5% - 3%. Even when the system is heavily loaded the system still shows most of them in the sleep state. Once again, I will report an update of this command once I have a heavy load state but I haven't seen these in a D wait state.

PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
29652 mysql 269M 11M sleep 58 0 0:00.00 4.9% mysqld/26
23987 nobody 9208K 7736K sleep 18 0 0:51.29 1.4% httpd/1
23967 nobody 8712K 7544K sleep 52 0 0:50.04 1.4% httpd/1
23929 nobody 9272K 7712K sleep 58 0 0:51.41 1.3% httpd/1
23926 nobody 9232K 7760K sleep 58 0 0:51.18 1.2% httpd/1
818 nobody 8512K 7376K sleep 58 0 0:42.00 1.1% httpd/1
23952 nobody 8832K 7656K sleep 28 0 0:51.17 1.0% httpd/1
23986 nobody 8792K 7528K sleep 58 0 0:51.00 0.9% httpd/1
23932 nobody 8840K 7520K sleep 58 0 0:50.14 0.7% httpd/1
23925 nobody 9280K 7648K sleep 58 0 0:50.39 0.3% httpd/1
3341 root 1528K 1152K cpu0 48 0 0:00.00 0.2% prstat/1
23928 nobody 8888K 7664K sleep 58 0 0:50.49 0.2% httpd/1
23936 nobody 8720K 7680K sleep 58 0 0:51.26 0.2% httpd/1
23923 nobody 8848K 7584K sleep 58 0 0:50.47 0.1% httpd/1
3041 root 2456K 1952K run 48 0 0:00.00 0.0% bash/1
2711 root 3360K 2176K run 58 0 0:00.01 0.0% sshd/1
23924 nobody 8688K 7544K sleep 58 0 0:50.17 0.0% httpd/1
23985 nobody 9096K 7608K sleep 58 0 0:50.18 0.0% httpd/1
23935 nobody 9080K 7592K sleep 58 0 0:50.58 0.0% httpd/1
23930 nobody 8872K 7784K sleep 58 0 0:51.14 0.0% httpd/1
23927 nobody 8784K 7488K sleep 58 0 0:50.32 0.0% httpd/1
23968 nobody 8800K 7696K sleep 58 0 0:50.35 0.0% httpd/1
23969 nobody 8864K 7648K sleep 58 0 0:50.58 0.0% httpd/1
23931 nobody 8864K 7640K sleep 58 0 0:50.44 0.0% httpd/1
23922 root 4672K 3360K sleep 59 0 0:00.02 0.0% httpd/1


I've asked George to change his board's aging setting to only show the last 2 weeks after I discoved that while looking through this forum. He changed the most frequently used boards but not all of them, the rest are still set at "show all threads". Also, expire threads is set to "0". How do I see how many posts are stored?

I can update apache/mysql if need be. The current Apache version was used because it was the latest/stable offically supported version on Solaris 8. The version of mysql was chosen to expidite the installation of this server since it's the latest Solaris package available from sunfreeware.com without have to re-compile.

Thanks for the assistance, I will provide some updated stats when I can observe the machine under load again.

Your MySQL settings seem OK. As was stated, I'd run the max_connection count up a bit. However, that settings shouldn't affect CPU usage. It'll just cause errors if you go over the max.

The next time the system is under load, run a "top -bi" and pop the ouput here.

That command shows 'non-idle' tasks in batch mode (so you'll get the processes causing the problem and you'll be able to cut an paste a few runs of the command).

At the same time, I'd also run a "Show processlist" in mysql. Filter out any of the Idle connections.

Based on that info.

Doublecheck its mysql consuming CPU when the server is under load (not something else).

If it is MySQL, then show processlist should point out the queries that are causing the load. Once we have those queries, we'll be in a better position to optimise things.

Anyway, that's my 2 cents <img border="0" title="" alt="[Cool]" src="images/icons/cool.gif" />

Peter:

That sure seems a lot more than 2 cents worth.

Thanks. Hoepfully we can get this problem resolved.

Musky

Stop by and check out the BEST Sports Forum on the Web..

MuskymanSports Message Boards

ok...here are some stats on the system load:

There are 81 registered and 107 anonymous users online.


load averages: 14.17, 11.19, 7.92 10:52:36
64 processes: 61 sleeping, 2 running, 1 on cpu
CPU states: 0.0% idle, 75.1% user, 24.9% kernel, 0.0% iowait, 0.0% swap
Memory: 1024M real, 670M free, 507M swap in use, 1295M swap free

PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
3177 mysql 56 33 0 277M 22M run 58:42 43.40% mysqld
23928 nobody 1 38 0 10M 8576K sleep 78:24 1.80% httpd
12749 nobody 1 49 0 7704K 6328K sleep 0:01 1.74% httpd
23924 nobody 1 58 0 9664K 8432K sleep 79:27 1.68% httpd
23969 nobody 1 58 0 10M 8696K sleep 80:14 1.66% httpd
23936 nobody 1 56 0 9688K 8488K sleep 80:26 1.65% httpd
12668 nobody 1 44 0 7952K 6424K sleep 0:03 1.48% httpd
12726 nobody 1 58 0 8136K 6608K sleep 0:03 1.47% httpd
23923 nobody 1 53 0 10M 8464K sleep 80:24 1.43% httpd
23932 nobody 1 58 0 10M 8664K sleep 80:20 1.41% httpd
23926 nobody 1 38 0 10M 8536K run 80:19 1.40% httpd
23927 nobody 1 58 0 9376K 8424K sleep 79:20 1.32% httpd
23968 nobody 1 58 0 9664K 8496K sleep 80:20 1.30% httpd
12666 nobody 1 45 0 7944K 6424K sleep 0:04 1.29% httpd
12458 nobody 1 58 0 7936K 6344K sleep 0:12 1.28% httpd


and here are the mysql processes that are not in the "sleep" state, I'm not sure if the customer has changed any of the forum settings recommended by Rick Baker earlier in this thread:


mysql> show processlist;
+-----+----------+-----------+-------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+----------+-----------+-------+---------+------+----------------+----------------------------------------------------- |
| 2 | muskyman | localhost | musky | Query | 1 | Locked |
SELECT B_Number
FROM w3t_Posts
WHERE B_Main = '19723'
|
| 7 | muskyman | localhost | musky | Query | 0 | Sorting result |
SELECT U_Username,U_Totalposts
FROM w3t_Users
ORDER BY U_Totalposts DESC
LIMIT 0, |
|
| 10 | muskyman | localhost | musky | Query | 0 | Sorting result |
SELECT U_Username,U_Totalposts
FROM w3t_Users
ORDER BY U_Totalposts DESC
LIMIT 0, |
|
| 14 | muskyman | localhost | musky | Query | 0 | Sorting result |
SELECT Bo_Number,Bo_Title,Bo_Keyword,Bo_Cat,Bo_CatName,Bo_Sorter
FROM w3t_Board |
| 15 | muskyman | localhost | musky | Query | 0 | Sorting result |
SELECT U_Username,U_Totalposts
FROM w3t_Users
ORDER BY U_Totalposts DESC
LIMIT 0, |
|
| 139 | muskyman | localhost | musky | Query | 1 | Locked |
UPDATE w3t_Posts
SET B_Counter = B_Counter + 1
WHERE B_Main = 17253
|
| 195 | muskyman | localhost | musky | Query | 0 | Locked |
SELECT t1.B_Number,t1.B_Username,t1.B_Posted,t1.B_IP,t1.B_Subject,t1.B_Body,t1.B_File,t1.B_St |
|
| 197 | muskyman | localhost | musky | Query | 1 | Sorting result |
SELECT B_Number
FROM w3t_Posts
WHERE B_Topic = '1'
AND B_Boa |
|
| 219 | muskyman | localhost | musky | Query | 0 | Locked |
UPDATE w3t_Posts
SET B_Counter = B_Counter + 1
WHERE B_Main = 19898


| 227 | muskyman | localhost | musky | Query | 0 | Locked |
SELECT B_Main
FROM w3t_Posts
WHERE B_Number = '19899'
AND B_Board = 'UBB2 |

Here is my question:

Memory: 1024M real, 670M free, 507M swap in use, 1295M swap free


Why with 670 megs of, what I presume is free 'real' memory is your system (The OS) using a half gig of Swap??

Regards,

Brett Harris
Infopop Corporation

[This message was edited by Brett Harris on December 13, 2002 at 06:40 AM.]

Good question Brett, I have a few other mysql machines some managed by us and some managed by outside administrators but they all have high swap usage even when under little load. Here is the top output on the same barely used system before apache, before mysql and after. Notice a majority of the swap is taken by mysql but there isn't currently any processes being run...

before Apache:

24 processes: 23 sleeping, 1 on cpu

Memory: 1024M real, 862M free, 31M swap in use, 1773M swap free


PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
28208 root 1 50 0 1504K 1032K cpu 0:00 0.53% top
28088 root 1 58 0 3360K 2184K sleep 0:00 0.19% sshd
28179 root 1 48 0 2616K 2072K sleep 0:00 0.05% logit
28207 root 1 58 0 1016K 840K sleep 0:00 0.04% vmstat
28178 root 1 48 0 1032K 864K sleep 0:00 0.03% sh
28092 root 1 58 0 2464K 1960K sleep 0:00 0.03% bash
222 root 17 55 0 17M 16M sleep 1:22 0.00% nscd
1 root 1 58 0 792K 376K sleep 1:20 0.00% init
7621 root 1 58 0 7272K 6720K sleep 0:08 0.00% cron
300 root 1 59 0 3168K 1616K sleep 0:06 0.00% sendmail
209 root 10 58 0 3984K 2312K sleep 0:03 0.00% syslogd
49 root 5 22 0 1288K 808K sleep 0:00 0.00% syseventconfd
241 root 4 45 0 1392K 880K sleep 0:00 0.00% powerd
28090 jds 1 48 0 2408K 1880K sleep 0:00 0.00% bash
28091 root 1 48 0 312K 312K sleep 0:00 0.00% sh


before mysql:


CPU states: 94.6% idle, 4.6% user, 0.8% kernel, 0.0% iowait, 0.0% swap
Memory: 1024M real, 735M free, 181M swap in use, 1622M swap free

PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
818 nobody 1 28 0 11M 9312K sleep 114:47 0.88% httpd
23968 nobody 1 58 0 11M 9728K sleep 123:32 0.40% httpd
28143 root 1 58 0 2248K 1624K cpu 0:00 0.40% top
23967 nobody 1 58 0 11M 9616K sleep 122:57 0.37% httpd
23935 nobody 1 51 0 11M 9672K sleep 122:45 0.31% httpd
28140 root 1 41 0 2616K 2072K sleep 0:00 0.15% logit
28152 root 1 51 0 1800K 1304K sleep 0:00 0.09% netstat
23932 nobody 1 58 0 11M 9672K sleep 124:04 0.08% httpd
23928 nobody 1 58 0 11M 9656K sleep 122:06 0.07% httpd
28139 root 1 48 0 1032K 864K sleep 0:00 0.05% sh
1 root 1 58 0 792K 376K sleep 1:20 0.05% init
28088 root 1 58 0 3360K 2184K sleep 0:00 0.05% sshd
23952 nobody 1 58 0 11M 9712K sleep 124:36 0.04% httpd
23930 nobody 1 58 0 11M 9752K sleep 123:43 0.04% httpd
23985 nobody 1 58 0 11M 9640K sleep 123:04 0.04% httpd


after mysql:


CPU states: 99.8% idle, 0.2% user, 0.0% kernel, 0.0% iowait, 0.0% swap
Memory: 1024M real, 833M free, 326M swap in use, 1477M swap free

PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
28248 mysql 15 58 0 268M 7336K sleep 0:01 1.25% mysqld
28216 nobody 1 28 0 7248K 5744K sleep 0:00 0.74% httpd
28215 nobody 1 18 0 7984K 6152K sleep 0:00 0.49% httpd
28214 nobody 1 42 0 6816K 5584K sleep 0:00 0.26% httpd
28278 root 1 58 0 2240K 1616K cpu 0:00 0.24% top
28224 nobody 1 51 0 6928K 5776K sleep 0:00 0.23% httpd
28223 nobody 1 29 0 6384K 5312K sleep 0:00 0.12% httpd
28219 nobody 1 52 0 7040K 5664K sleep 0:00 0.08% httpd
28220 nobody 1 33 0 6352K 5296K sleep 0:00 0.07% httpd
28222 nobody 1 28 0 5920K 4872K sleep 0:00 0.06% httpd
28088 root 1 58 0 3360K 2184K sleep 0:00 0.05% sshd
28218 nobody 1 28 0 5896K 4816K sleep 0:00 0.04% httpd
28092 root 1 48 0 2464K 1960K sleep 0:00 0.04% bash
28217 nobody 1 52 0 5896K 4816K sleep 0:00 0.02% httpd
222 root 17 55 0 17M 16M sleep 1:22 0.00% nscd

From what I understand stuff in swap was pushed there when RAM ran out - if it's accessed again it should be MOVED back into RAM if available, if not, eventually it expires off swap and swap is released.

If swap is continuing to be used instead of RAM you may want to check to make sure that the RAM that was inserted after the machine left the factory (which looks like it could be a chunk of it) is not only being recognized by the OS but also used properly by the OS..

Regards,

Brett Harris
Infopop Corporation

Actually what is causing the large increase in swap usage is the configurations in the my.cnf file posted above. If I change those variables back to the defaults then it doesn't touch the swap virtually at all but if I use the my-medium.cnf or my-large.cnf it increases my swap usage based on the values in each configuration file. I have also verified this functionality on another machine. So my question would then be is mysql supposed to be claiming swap based on those free variables or should in grab free resources?

Here is an example, I will modify my key_buffer:

my-medium.cnf has a key_buffer = 16M

CPU states: 99.2% idle, 0.2% user, 0.6% kernel, 0.0% iowait, 0.0% swap
Memory: 1024M real, 837M free, 114M swap in use, 1711M swap free

PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
16917 root 1 58 0 2248K 1624K cpu 0:00 0.44% top
704 nobody 1 32 0 7824K 6536K sleep 4:26 0.23% httpd
16916 mysql 5 0 0 20M 2920K sleep 0:00 0.21% mysqld

and here it is with a key_buffer = 256M

CPU states: % idle, % user, % kernel, % iowait, % swap
Memory: 1024M real, 833M free, 357M swap in use, 1469M swap free

PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
16986 mysql 6 50 0 262M 6224K sleep 0:00 1.67% mysqld
714 nobody 1 32 0 8064K 6520K sleep 4:15 1.15% httpd

why have you got maxrequestsperchild set to 0? Unless you know something i don't get this whacked up... like 50 or 70 would be good.

>>edit... sorry just bothered to look up what this did... still it might be worth having them recycle anyhow or at least try it?<<

also upgrade apache... i had some random bug in an earlier version that ubb was agrevating really badly... it was causing child processes to terminate early hence the load on the box was actually being used up by opening and closing children.. as it were. Although you'd be able to see if it was happening in your httpd error log.

Sorry if non of this helps...

Bison - "Feel the joy!"

Did some messing around on my old old old SPARC server. It's running linux but nonetheless it always acts differently thean my intel does.

Bison hit something here. When I set maxrequestsperchild to 0 my apache children would never die. They would get stale, start sucking up memory, eventually a lot of memrory, but never die. I set it to 10000 and stopped having to restart apache every other day. My intel box running the same version of Linux and apache doesn't do this...

Anyhow, continually killing and starting new apache children isn't great either. Make sure you don't have too many of too few spare servers. You spare servers look ok to me since they are what I have <img border="0" title="" alt="[Smile]" src="images/icons/smile.gif" />

Honor The Victims

I looked at Bison's suggestion but based on his statement I'm not sure he was aware that "0" represented an unlimited number of maxrequestsperchild. Based on your tests David I have monitored my apache usage and am not seeing a memory leak that you are. During periods of unuse my processes always go back to the minspareservers setting. All of these are set to Sun's Apache defaults. Sun's engineering documents only recommend changing these for very large sites.

The biggest clue I can see is with the mysql config/memory usage from an earlier post by Brett Harris. Apache and the OS are consuming approx. 30% of the ram but all the memory allocated to mysql in my.cnf only grabs swap space rather than free memory. I'm not sure if this is a configuration problem or a platform/build issue. It would definately make sense that if the server was directly using free memory that quite a bit of processing and disk usage would be freed up. I have installed mysql on several solaris 8/sparc systems and see this same usage but I have it currently running on a couple redhat/intel system and I do not see this there. The only difference is that I am using a Redhat RPM on one and a PKG from sunfreeware.com on the others. Whether or not something at compilation time could be causing this. Any ideas on this? If someone had a known working Solaris 8/Sparc installation this would definately help.

That is an interesting point - I was discussing this topic with one of our network admins and his point was this:

When the RAM gets eaten up then it should go to Swap.. Once RAM is available again if the stuff in swap gets called again it's usually moved over to available RAM as it's faster.. Swap will usually be 'given back' as it times out and is not accessed again. (At least that is how I understood it - please feel to correct me any time I am wrong)

If this system is continuing to use Swap in lieu or RAM then that could be a problem at peak times for users whose sessions are in the Swap files and not RAM.

Is that a potential Unix flavor problem?? That I don't know.

Regards,

Brett Harris
Infopop Corporation

I threw our problem out to an aoutside source for thier opinion. Here is what they replied with.

Don't know if this has been tried or not.

I am sending the information forward — but in looking at what you sent
me, here is my first reaction:

The first thing I notice is that the max connections is

set to 75 so when you have 300 users, there will be a problem.

I suggest that they increase the max connections to 300 and see what happens. I think there is enough RAM but the processor could be a little slow.

Hope this he
lps.

Thanks.

Brett..

You are correct on the ram/swap thing. Now the interesting point I am seeing is this... These swap figures directly relate to the settings I make in my.cnf. In this case even If I turned everything on the site off (including apache) and start mysql it will still use swap only... no ram at all. If I set everything in my.cnf to consume a max of 16MB or 512MB it doesn't make any difference, it still only comes from swap while I have 800MB of free ram. I have seen this same thing on other Sun/sparc systems. Unfortunately I do not have anything else to test with at this time.

As for the other suggestion... Yes, the processor is overworked but we are trying to figure out if there is a configuration issue that could be causing some of this. The max connections of 75 is actually set at 125 and this only corresponds to open sql queries not to individual users. There could be several hundred users online without using up 100 connections. This also wouldn't cause slowness, just an sql error when the maximum number of connections had been reached.

I agree, the number of connections allowed doesn't correlate to the number of online users. Unless they ALL clicked a link at the same time. Pretty unlikely. <img border="0" title="" alt="[Wink]" src="images/icons/wink.gif" />

You CPU use is probably related to the fact that MySQL seems to be running entirely in swap.

Is there a way you can backup your current MySQL binary, grab a current Solaris MySQL binary from mysql.com and give it a try?

http://www.mysql.com/downloads/mysql-3.23.html

Honor The Victims

Wudaben:

Looks like you are making some progress. I just wanted to assure you that I have no intentions for charging you for this on the job training your are getting in Configurations for my site...Just consider it my belated Chrsitmas present to you.

Musky

PS:What's a "Wudaben"

The descriptions given above for how swap works actually only applies to Linux (real memory fills then it is swapped out to make room for more). Other Unix OS, notable Solaris and *BSD tend to be a little more intelligent about swap and frequently swap out unused stuff regardless of how much free RAM there is.

However, to get to the point of this post. On the Solaris Box - It is _likely_ that your /tmp directory is a "tmpfs memory filesystem" / aka Swap space.

MySQL defaults to /tmp for the temp tables it creates when doing _all_ queries. Thus queries are always going to be using tmpfs space - this is good since it means (usually) that your temp tables can be created and deleted in memory.

The amount of "swap space" available/used will also depend of the size of the /tmp directory.

Now Solaris defines Swap as the sum of physical memory not used + physical swap space. It is not just the disk based "space". Try the commands:
swap -l
df -k /tmp ; swap -s
To see just what your machine thinks is going on.

Hope this helps,

Paul.