Mycohl wrote:Dr Neo Lao wrote:I realise that I misread your post.
No worries. I, in turn, apologize for jumping to a conclusion and replying in a snarky manner.
Kisai wrote:if mySQL is kill'ed for any reason, it trashes the tables
Why?? Whenever mysql stops, the tables go away? What happens when you reboot? That's insane and has to be indicative of a more profound problem.
I am sure you are much more experienced than I, and you have the insight of having designed and implemented the system yourself. All I can do is ask the proverbial blind man's inane questions. If you don't want to be bothered, I completely understand.
Why are you logging to mysql? So that you don't have to rotate logs? Does it have something to do with autokeen and how it handles data? If you have webalizer fetching log data from the db, that's going to eat cycles like candy and strain mysql as well. Rather than invest in moving everything to a new server, why not just set up a dedicated mysql box and lock it down? I'm uncertain as to whom you are having security problems with regarding mysql. Is CG's apache/boa/mysql setup being shared with some other group of people? Is the box shared with other keenspace-related services? (I assume it is, which would explain why upgrading is not an option.)
I really don't know who/what "keenspace" is, how many machines/clusters they have or how many they have allocated for CG's use. With as much strain as I imagine CG puts on the db back-end, I can't imagine that running mysql alongside apache, boa, ftp and a continuously forking perl/bash script is healthy. Especially if the machine has intermittent postfix hangs. The only thing that I can (ignorantly) recommend is getting mysql on a separate box/cluster, getting it stable and then getting your dependant services working. With a mysql box dedicated to CG's use, that eliminates both the security problems and (hopefully) the stability problems.
As I stated, the machine is dual processor, and running threaded apache, therefor it can not log to the hard drive.
Conventional apache, prefork mode, writes one line at a time, in order, to one master log file, that remains locked the entire time. This is fine on a very small web server where it doesn't fill up fast, but to give you a reference point, 40GB of logs in 5 days. You simply can't rotate logs fast enough to let webalizer process it. Plus they need to be split on a per domain basis.
Second it's also dynamicaly generated virtual hosting, Apache's static virtual hosting tops out at about 300 sites, we have 25 times that. So there is no provision to simply create one log file per site, as it would require shutting down apache for hours to rotate the logs.
Every logging system I tried, was not thread safe, and would eventually hang apache after a while. I eventually just wrote a non-thread-safe logging script that each thread calls and dumps straight to mysql. This solves the problem of log files remaining locked or becoming out of order. MySQL has one table per day, and drops the oldest table on every new day. So this gets rid of the rotating logs issue, and then when webalizer is supposed to run, it's piped through webalizer so no temporary files are created. It really can not be made any more efficient.
Having a physically separate mySQL server is out of the question. The only thing being replicated between the forum server and the main server is the user tables for phpbb and autokeen. None of the logging or anything.
As for why we have not received a new server, we keep asking and we keep getting told 'soon'. There is nothing left to optimize on the existing server, it's getting limited by disk bandwidth.
LET THE MASS SHAVING BEGIN!!!
