[NA] Apache/MySQL server monitor

[CJones]

I'm running an AMP setup on Windows Server 2008. I need something that will monitor Apache and MySQL for bandwidth, requests, queries, server utilization, etc.

The free-er the better. Result presentation without a lot of tweaking is good as well.

Any suggestions?

 

[DaleB]

Webalizer (http://www.mrunix.net/webalizer/) will tell you most of what you wnt to know about your Web traffic. For anything and everything else, there's MRTG, RRDTool (http://oss.oetiker.ch/rrdtool/) or Cacti.

I've used RRDTool to monitor all manner of things, including motherboard temperatures, fan speeds, bandwidth, you name it. For seeing what MySQL is doing, try this.

 

[SkyHog]

Webalizer (http://www.mrunix.net/webalizer/) will tell you most of what you wnt to know about your Web traffic. For anything and everything else, there's MRTG, RRDTool (http://oss.oetiker.ch/rrdtool/) or Cacti.

I've used RRDTool to monitor all manner of things, including motherboard temperatures, fan speeds, bandwidth, you name it. For seeing what MySQL is doing, try this.

I second Webalizer. Its great for getting basically anything you'd ever want out of your server.

 

[CJones]

Webalizer (http://www.mrunix.net/webalizer/) will tell you most of what you wnt to know about your Web traffic. For anything and everything else, there's MRTG, RRDTool (http://oss.oetiker.ch/rrdtool/) or Cacti.

I've used RRDTool to monitor all manner of things, including motherboard temperatures, fan speeds, bandwidth, you name it. For seeing what MySQL is doing, try this.

I second Webalizer. Its great for getting basically anything you'd ever want out of your server.

Thanks for the suggestions, but it looks like Webalizer is *nix only and I'm running Windows Server 2008.

I'm trying to prove that my server running Apache/MySQL/PHP is capable enough to handle the expected traffic for this project and doesn't require a $1MM enterprise-edition platform. So far I have ~600 users on it and CPU utilization is running <1%, but I'd like to have some numbers that I can extrapolate out to 25,000 users. I know know... Extrapolation is evil (unless you are upper management )

I guess for web server hits, I could put a simple piece of code in my CodeIgniter main controller to save the requesting IP and page requested to a MySQL table and let it run for a week.

 

[DaleB]

Oh. Windows. Why?

Having said that, if you can get SNMP working you could still use RRDTool (running somewhere else) and pull down whatever stats you need.

Actually... I haven't ever looked into it, but I think it's all Perl anyway. If it's all Perl, you can just install Perl on the box and be done with it. Assuming Apache on Windows writes a standard log file. I dunno, never had the urge to run any of that on Windows.

 

[jesse]

Log parsing (which is what Webalizer is going to do) will give you some information about the traffic - but you're not going to get server vitals that way - nor are you going to know if it goes down.

I tend to default to Nagios for monitoring and then write nrpe plugins for some vital information.

I also use Cacti for pulling system data and for trending.

 

[DaleB]

There are dozens of free tools that will do anything you want, many of which are built upon the old MRTG and/or RRDTool framework. How much of that you can get to run on a Windows server... Cywin might be helpful too.

Of course there's always SCOM, if you don't mind swatting a $2 problem with a $200K flyswatter.

 

[CJones]

Oh. Windows. Why?

That's the box that IT provided. I had to install A/M/P on it myself - still amazed that they let me do it. Now they are so amazed at how well it is running with such little system utilization that they are actually thinking of not charging my team $1MM to 'upgrade' to a enterprise-level web platform. I just need to prove to them that this thing isn't going to increase in utilization exponentially when you add users like most of their other systems.

Based on the 600 or so users that are on it right now, I have a decent idea of how fast the database will grow (record-wise at least) relative to the number of users added. I just need to add some server-hits/timeframe, CPU utilization, and bandwidth numbers to go along with it.

 

[CJones]

Log parsing (which is what Webalizer is going to do) will give you some information about the traffic - but you're not going to get server vitals that way - nor are you going to know if it goes down.

I tend to default to Nagios for monitoring and then write nrpe plugins for some vital information.

I also use Cacti for pulling system data and for trending.

I'm not worried about the MS Server part going down - the 'box' is running on their high-availability VMWare platform, so if my system goes down, there is a good chance that a lot of other stuff is down as well. Besides, if MS Server goes down, they wouldn't let me touch it anyway.

Being able to know if Apache goes down would be nice, though. Considering I may have the only Apache server running anywhere on our company's servers, I would be the only point of support if it did go down.

I looked at Cacti - it seemed to have some decent 'meat' to it and could present it in a useful way without requiring a bunch of add-ons. I'll look into it more.

Thanks again for the suggestions...

 

[jesse]

I just need to prove to them that this thing isn't going to increase in utilization exponentially when you add users like most of their other systems.

It will. How many more users are you talking? Is the database indexed real well?

 

[TangoWhiskey]

Sounds like what you need is web traffic load testing software to simulate multiple concurrent connections at the profile you expect to see.

 

[denverpilot]

It will. How many more users are you talking? Is the database indexed real well?

Agreed. One row in MySQL is nothing. Grow a MySQL table to 300 GB and you'll be tuning it heavily or it will die. Massive indexing and really fast IO will save you for a little while.

Eventually you'll have to tear the fronted off of the backend too. MySQL will live on "bruiser" hardware and the (multiple) web front ends will attach to it.

First battle... See if they'll load a Linux VM for you. Running AMP on Windows is fine but it'll choke faster than Linux will, will be less stable, and will require downtime and reboots for really stupid/silly patches. Make it LAMP.

How many users and how much data? A single VM is NOT an enterprise-ready nor long-term maintainable solution for business. Two is a bare minimum with automated failover if the program is mission critical.

IT is about making money. If the software isn't running its not making (or saving) money. You must have a written recovery plan that states how long from when it goes down at noon on a Monday that you'll have it back online.

Just thoughts. What's the application?

 

[poadeleted21]

LoadRunner will tell you everything you need to know. It will bring your application to it's knees and tell you where it cried uncle. It's more of a what will happen vs what has happened piece of software, probably best to find someone who owns a copy and have them do the grunt work. IIRC LoadRunner is $$$$ might be some open source alternatives, but I've only needed to do this for a large financial institution in Alaska that was very concerned about Permanent fund day with new systems, fail over machines, load balancers etc.. they had installed and they didn't want to spare any expense.


EDIT.: Just checked looks like they have a free trial and a pay as you go cloud option, didn't look at much more than that.

 

[jesse]

Some more thoughts - which storage engine in MySQL are you using? Are you using transactions? Have you tuned MySQL? The default MySQL options are horrible and it will perform very badly with them.

 

[TangoWhiskey]

IT is about making money. If the software isn't running its not making (or saving) money. You must have a written recovery plan that states how long from when it goes down at noon on a Monday that you'll have it back online.

Written, and TESTED. Best when tested by someone unfamiliar with the environment, but technically savvy, to validate your instructions are clear, succinct, yet thorough, and can be executed by any available I.T. Staff.

If you haven't heard of the terms RPO (recovery point objective) and RTO (recovery time objective), or don't know the difference between the two, read up on them. They, defined in written form, should be part of your SLAs.

 

[CJones]

It will. How many more users are you talking? Is the database indexed real well?

If we put every user possible in the country on this one box it would be somewhere around 12,000 users.

I've tried to index everything that is getting queried heavily. If the project goes through, I'll be able to work on it 100% of the time and really work on tweaking the database and the queries.

First battle... See if they'll load a Linux VM for you. Running AMP on Windows is fine but it'll choke faster than Linux will, will be less stable, and will require downtime and reboots for really stupid/silly patches. Make it LAMP.

Won't happen. IT doesn't want to project to succeed in the first place. It was like pulling teeth to get a Windows box for us to use.

How many users and how much data? A single VM is NOT an enterprise-ready nor long-term maintainable solution for business. Two is a bare minimum with automated failover if the program is mission critical.

One database has been hosting 300 users for about a month now and so far that database has grown to a whopping ~150,000 rows and ~25MB.

This isn't a mission-critical app. We are basically replacing a paper-based process with a web-based process and the processes being replaced are not operationally critical. If the server dies, the sites go back to using paper until the system comes back up.

IT is about making money. If the software isn't running its not making (or saving) money. You must have a written recovery plan that states how long from when it goes down at noon on a Monday that you'll have it back online.

IT doesn't want this project to succeed in the first place. We (non-IT guys) wrote this app which is so stupid-simple that everyone that sees it, wants it. So far they have missed out on the development cost and if we can prove that we don't need Oracle Weblogic to run it, they won't get the money to setup and maintain it, either. They REALLY hate the fact that we came up with something that is so popular with the lowly 'operations' people.

Just thoughts. What's the application?

When you hear it, you'll laugh. We have developed a web-based way for floor associates to submit vacation requests and see the status of their requests from their forklifts or a kiosk. Another app allows them to do their forklift inspections on the web, which creates work requests that can be addressed by the mechanic from a kiosk.

It's so stupid simple that IT would never mess with developing it, but the associates LOVE it because they have more visibility into their vacation requests and supervisors love it because they don't have to deal with a huge stack of paper forklift inspection sheets or paper vacation requests.

 

[denverpilot]

Written, and TESTED.

Forgot that one just 'cause its ingrained in me so much. If I can't automate it, I wrote down how to do it with the intended audience not being me...

We're a small shop so we can't always break someone free to test but at least they'd have something to start from in the "hit by a bus" scenario.

 

[CJones]

Some more thoughts - which storage engine in MySQL are you using? Are you using transactions? Have you tuned MySQL? The default MySQL options are horrible and it will perform very badly with them.

That's not what I like to hear. ha!

Everything is running InnoDB. Like I said my last reply, if I can get the project approved then I should be able to work on it 100% of the time before it actually grows to a massive scale, so I can actually focus on designing it the way it should be.

 

[murphey]

Oh. Windows. Why?

Having said that, if you can get SNMP working you could still use RRDTool (running somewhere else) and pull down whatever stats you need.

Actually... I haven't ever looked into it, but I think it's all Perl anyway. If it's all Perl, you can just install Perl on the box and be done with it. Assuming Apache on Windows writes a standard log file. I dunno, never had the urge to run any of that on Windows.

I'm running Apache on Win7 with no problems. But I'm not running it as an enterprise server but as a development system. Still works the same.

 

[denverpilot]

Oops we posted at the same time.

Sounds like a worthy little project. One of those that will be highly visible too. The grousing when it's down will be very high.

And people *will forget* how to use the paper, as a backup if you succeed... It must be easier and faster than checking off a list on paper for all users, so a challenge. Grab your stopwatch. If it lags on the WAN it won't get used. Also some folks will resist anything tech. Do you have management buy-in to make one or the other the primary - and they'll make it stick with reluctant staff and assist with gathering their issues?

I can think of a number of accident scenarios where the data would be requested for legal purposes. You might think about asking legal how much data they want retained and for how long.

That or mimic the paper system and whatever procedures they're using and write them into the design doc if they're not already in writing.

If they toss those records after a period of time, toss yours too.

 

[dmspilot]

That's not what I like to hear. ha!

Everything is running InnoDB. Like I said my last reply, if I can get the project approved then I should be able to work on it 100% of the time before it actually grows to a massive scale, so I can actually focus on designing it the way it should be.

I believe PostgreSQL has better performance and reliability than InnoDB.

For benchmarking, try apachebench.

 

[CJones]

Just as a FYI for future searches, I ended up using Weblog Expert Lite to analyze the Apache access.log file. It does pretty good at breaking down visits/day, bandwidth usage, general page hit data from Apache's access.log. Doesn't do anything for MySQL, however.

Still waiting to see if the project gets 'official' funding from IT before going all-out with a database stress test. If they end up taking on the project, it is possible it will get moved to an Oracle platform and I can let the real DBA's manage it. If it doesn't get picked up by IT, I may not be around to care, anyway.