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:
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.
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.
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.
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.
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.
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:
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.
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.
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 |
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...
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..
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?
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.
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" />
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.
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?
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.
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.