Ramblings of a web guy

When I first started installing MySQL for myself, it was quite handy to have the example my.cnf files in the source package.  I was a noob to the MySQL configuration.  Even after I became more experienced, I would use them as a starting point.  However, I now find that they are so behind the times they are not as useful.  Here are some of the comments from the files.

my-small.cnf

# This is for a system with little memory (<= 64M) where MySQL is only used
# from time to time and it’s important that the mysqld daemon
# doesn’t use much resources.

my-medium.cnf

# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)

my-large.cnf

# This is for a large system with memory = 512M where the system runs mainly
# MySQL.

my-huge.cnf

# This is for a large system with memory of 1G-2G where the system runs mainly
# MySQL.

I end up using the large or huge files as a starting point for every server I set up by hand.  The small and medium should be renamed underpowered and teeny-tiny.  Who has less than 64MB of RAM on a server now?  Can you even buy sticks of memory that small in any modern system?  Most come with 256MB sticks minimum.  And they never come with just one stick.

I will use the large example as a starting point for a server that has 2GB of RAM and will be running an entire site on one server.  I use huge for any server that runs only MySQL.  And even then, most of them have 4GB of RAM or more.

I don’t know if anyone at MySQL has plans on tweaking these files or not.  Perhaps those good guys at the MySQL Performance Blog or Percona could create some example my.cnf files.  I could put some out there, but I fear their sole purpose would be for someone to point out what I am doing wrong. =P  Hey, they work for me.  Hmm, maybe this would make a good MySQL Forge section.  A whole area of user contributed my.cnf files.  They could be architecture specific and everything.  What runs best on Solaris?  Linux?  BSD?  Windows?  32-bit?  64-bit?

One thing I would for sure like to see is example files for InnoDB dominant servers.  Most of our servers all run primariy InnoDB tables.  None of these above examples covers InnoDB.  They have comments, but no preconfigured values.  I have seen more than one server using InnoDB tables without any custom configuration in their my.cnf.  In the end that is the fault of the server admin/owner no doubt.

What do you say?  Anyone up for a MySQL Forge section for my.cnf files?

When
Tuesday, March 11, 2008 at 12:00 PM

Where
BizTech
515 Sparkman Drive
Huntsville , AL 35816

Details
Brian Moon of dealnews.com will be discussing best practices for writing database backed web based applications. Many users teach themselves SQL and programming on the web. Other developers may have experience in enterprise desktop applications. No matter what your background, there are common mistakes made when deploying web based applications that use a database.

Also, at this event, we will be giving away two copies of NuSphere’s PhpED. Plus, everyone who attends can purchase any NuSphere product at 50% off.

Lunch will be served at this event.

I had mentioned a while back that I submitted three proposals for the 2008 MySQL Conference.  Well, two were accepted.

From one server to a cluster

In the last 10 years, dealnews.com has grown from a single shared hosting account to an entire rack of equipment. Luckily, we started using PHP and MySQL very early in the company’s history.

From the early days of growing a forum to surviving Slashdotting, Digging and even a Yahoo! front page mention, we have had to adapt both our hardware and software many times to keep up with the growth.

I will discuss the traps, bottlenecks, and even some big wins we have encountered along the way using PHP and MySQL. From the small scale to using replication and even some MySQL Cluster.  We have done many interesting things to give our readers (and our content team) a good experience when using our web site.

MySQL hacks and tricks to make Phorum fast

Phorum is the message board software used by MySQL. One reason they chose Phorum was because of its speed. We have to use some tricks and fancy SQL to make this happen. Things we will talk about in this session include:

• Using temporary tables for good uses.
• Why PHP and MySQL can be a bad mix with large data sets.
• What mysqlnd will bring to the table with the future of PHP and MYSQL.
• How Phorum uses full text indexing and some fancy SQL to make our search engine fast.
• Forcing MySQL to use indexes to ensure proper query performance.

You can find my conference page here.  (as Terry would say, me, me, me!)

The PHP manual basically tells you not to use Apache 2 with a threaded MPM and PHP as an Apache module. In general, it may be good advice. But, at dealnews.com, we have found it very valuable.

Apache threaded MPMs

Well, first, what is an MPM? It stands for Multi-Processing Module. It is the process model that Apache uses for its children process. Each request that comes in is handed to a child. Apache 1 used only one model for this, the prefork model. That uses one process per Apache child. The most commonly used threaded MPM is the Worker MPM. In this MPM, you have several processes that run multiple threads within it. This is the one I will be talking about. You can read more on Apache MPMs at the Apache web site.

Huge memory savings

With the Apache prefork or even FastCGI, each apache/php process allocates its own memory. Most healthy sites I have worked on use about 15MB of memory per apache process. Code that has problems will use even more than this. I have seen some use as much as 50MB of RAM. But, lets stick with healthy. So, a server with 1GB of RAM will only realistically be able to run 50 Apache processes or 50 PHP children for FastCGI if each uses 15MB or RAM. That is 750MB total. That leaves just 256MB for the OS and other applications. Now, if you are Yahoo! or someone else with lots of money and lots of equipment, you can just keep adding hardware. But, most of us can’t do that.

As I wrote above, the worker MPM apache uses children (processes) and threads. If you configure it to use 10 child processes, each with 10 threads you would have 100 total threads or clients to answer requests. The good news is, because 10 threads are in one process, they can reuse memory that is allocated by other threads in the same process. At dealnews, our application servers use 25 threads per child. In our experience, each child process uses about 35MB of RAM. So, that works out to about 1.4MB per thread. That is 10% the usage for a prefork server per client.

Some say that you will run out of CPU way before RAM. That was not what we experienced before switching to worker. Machines with 2GB of RAM were running out of memory before we hit CPU as a bottleneck due to having just 100 Apache clients running. Now, with worker, I am happy to say that we don’t have that problem.

Building PHP for best success with Worker

This is an important part. You can’t use radical extensions in PHP when you are using worker. I don’t have a list of extensions that will and won’t work. We stick with the ones we need to do our core job. Mainly, most pages use the mysql and memcached extension. I would not do any fancy stuff in a worker based server. Keep a prefork server around for that. Or better yet, do funky memory sucking stuff in a cron job and push that data somewhere your web servers can get to it.

Other benefits like static content

Another big issue you hear about with Apache and PHP is running some other server for serving static content to save resources. Worker allows you to do this without running two servers. Having a prefork Apache/PHP process that has 15MB of RAM allocated serve a 10k jpeg image or some CSS file is a waste of resources. With worker, like I wrote above, the memory savings negate this issue. And, from my benchmarks (someone prove me wrong) Apache 2 can keep up with the lighttpds and litespeeds of the world in terms of requests per second for this type of content. This was actually the first place we used the worker mpm. It may still be a good idea to have dedicated apache daemons running just for that content if you have lots of requests for it. That will keep your static content requests from over running your dynamic content requests.

Some issues we have seen

Ok, it is not without problems (but, neither was prefork). There are some unknown (meaning undiagnosed by us) things that will occasionally cause CPU spikes on the servers running worker. For example, we took two memcached nodes offline and the servers that were connected to them spiked their CPU. We restarted Apache and all was fine. It was odd. We had another issue where a bug in my PHP code that was calling fsockopen() without a valid host name and a long timeout would cause a CPU spike and would not seem to let go. So, it does seem that bad PHP code makes the server more sensitive. So, your mileage may vary.

As with any new technology, you need to test a lot before you jump in with both feet. Anyone else have experience with worker and want to share?

One last tip

We have adopted a technique that Rasmus Lerdorf had mentioned. We decide how many MaxClients a server can run and we configure that number to always run. We set the min and max settings of the Apache configuration the same. Of course, we are running service specific servers. If you only have one or two servers and they run Apache and MySQL and mail and dns and… etc. you probably don’t want to do that. But, then again, you need to make sure MaxClients will not kill your RAM/CPU as well. I see lots of servers that if MaxClients was actually reached, they would be using 20GB of RAM. And, these servers only have 2GB of RAM. So, check those settings. If you can, configure it to start up more (all if you can) Apache process rather than a few and make sure you won’t blow out your RAM.

Call it paranoia.  Call it being prepared.  Whatever your stance, we are considering using more than one data center for dealnews.com.  It is not a capacity issue.  We can keep growing our current data center without a problem.  But, stories of power outages and power outages we have experience have us wanting to explore the idea.

Here is the problem.  No one in our company has experience with this.  And, there does not seem to be any resources on the internet talking about this.  Our problems are not so much with managing the data between the two.  The problem is failover and how to deal with one data center being out.  Here are some of the ideas that have been thrown on to the wall.

Round Robin DNS

This was the first idea.  It seems simple enough.  We have two data centers.  We publish different DNS for each data center and traffic goes to each one.  The problem here is that it is, well, random.

Global Traffic Management

There are devices that “balance” traffic  across multiple different locations.  But, I am unsure how those deal with outages at one of the locations.  It seems like there is still one point of failure.

BGP Routing

This is the biggest mystery to me.  I know what it is.  I know what it means.  I have no idea how to deploy this type of solution.  I understand that you can “move” your IP addresses with routing changes.  But, that means running routers.  Where are these routers?  Does this happen at some provider?  Is there a provider that handles this?  Does that mean that all of our data centers are with one provider?  I think one more peace of mind feature of this is that we would not be tied to just one vendor.  So, if one vendor had major issues or there was some legal troubles (we lived through the dot come boom and bust) we would have security in knowing we had other equipment that was not affected.

Is there something else?  Are we being way paranoid?  Maybe it is not cost effective in the end.  I/we have no idea really.  Anyone out there that has knowledge on this subject?

Luckily, I don’t have personal experience with them. But, based on the 2 to 3 users per week that come to the Phorum support forums and IRC, they have the worst support of any host on the internet.

Example 1

A user comes to the forums having trouble with his Phorum install. In the user’s words, GoDaddy tells him “they couldnt help me costumize my scripts because it wasnt their job”. In this case “customize” meant filling in the MySQL permissions into the Phorum config files. In the end, GoDaddy had to move him to a Linux hosting account. They claimed that the Windows hosting accounts do not support PHP. However, they are clearly wrong about their own hosting as this all started because the user received a PHP error about not connecting to MySQL.

Example 2

This user found that GoDaddy is using MySQL 4 on their servers. Their web site does not mention a version anywhere. So, users are locked in to a hosting plan before knowing this.

Example 3

This happened today in IRC. It was much like the first example. In this case, GoDaddy support told him “permissions are set via ftp”. Um, MySQL permissions are set via FTP? The user had a MySQL server name. For some reason it did not exist. So, either he typed it wrong or they gave him the wrong server name. Either way, their support should recognize this and be able to help their clients.

Example 4

This is less a support issue and just plain crappy of them IMO. GoDaddy does not allow the creation of temporary tables. The Phorum search engine makes use of them to save lots of CPU and memory on the PHP side. Luckily for their users, Thomas felt sorry enough for them to make a module that used good old fashioned slow LIKE queries. So, that will work until their account is shut off because they have search queries clogging up the database servers.

So, if you are on GoDaddy, I feel for you. From where we sit, it really seems like they do not provide very good support. We end up having to support their users for things that GoDaddy should be able to answer.

So, I checked my email this morning and found this jewel:

I might use Phorum if you brain deads knew how to upload or download your files via FTP. Your documentation has no order to it, its all a mess. I even dropped a release level to see if it was just that release. Ill give you a clue, DONT TRANSFER YOUR FILES VIA AUTO, EXPECIALY YOUR TXT FILES. TRANSFER THEM IN ASCII MODE ONLY, THIS INCLUDES YOUR PHP FILES. Then you just f—ing* MIGHT get readable files. Now you might say hey wait a min, we have full documentation on our web site, but you forget, someone has to open the sample.config.php file and read the crap that resides there.

* edited for content

Should I respond?  If so, how?  I decided to respond in as nice a way as I could.

 I normally don’t answer direct support emails.  Neither do I normally answer very angry emails.  However, I view this as an educational experience.

Judging by your email, I would say you are using Notepad on Windows to edit and read files.  That is mistake number one.  Notepad only reads one file format: Windows text files.  Windows natively uses a CRLF for it’s line endings.  It is the only operating system that does so. Notepad is the only application on the Windows platform that only reads that format.  If you would use Wordpad instead, this would not have been a problem for you.  For some reading on the subject, you may want to read:

http://en.wikipedia.org/wiki/Newline
http://www.cs.toronto.edu/~krueger/csc209h/tut/line-endings.html

Because PHP scripts are most commonly deployed on a Linux platform, the Unix line feed (LF or \n) is best for PHP applications.  Here are some suggestions for some great text editors for Windows.

TextPad - http://www.textpad.com/
Metapad - http://www.liquidninja.com/metapad/
PSPad   - http://www.pspad.com/en/

I hope this has helped educate you on the world of new lines and how real programming works.  In the future, a kind word in the forums would be much more appreciated than an email like this.  Not all people would be as kind as I am being and want to help you grow.

What do you think?  Should have just let this guy go?  Should have been as ugly to him as he was to me?

My coworker Rob is building a little project at home.  After some bad experience with cheap black box servers, he got some used Dell’s.  Now, he has done this.  Heh, is pretty cool.

I listen to the MacBreak Weekly podcast every week.  I have liked Leo Laporte ever since The ScreenSaver days.  He has several good regulars on the show and mixes in topical guests as well.  However, I think there is a demographic of Mac user that the show is missing.

There is a growing audience of new Mac users in the tech sector.  Just to the O’Reilly Open Source Conference and take a count.  Mac OS X and the switch to the Intel platform has brought about the most stable, easy to use *nix based desktops and laptops the world has ever seen.  I was a long time Windows user.  I made fun of Mac users.  I even ran Linux on a Dell laptop for a while.  Boy, that was fun.  Nothing like waking up and having to edit X configurations so you can work.  Apple just got it right.  I can run my AMP stack on my MacBook Pro with no problems.  And the Mac UI is wonderful.  I am becoming a fan boy.

So, on this weeks MBW, Leo and the panel were talking about Leopard.  The subject came up about the best new feature for home users, power users and mac software developers.  There was neither anyone on the show that fit into my demographic of Mac user nor did anyone mention us.  No mention of Apache 2.2 or PHP 5.2.  No mention of a much improved Terminal.app.  No mention of a built in SSH Agent that works with your keychain.  If you work with Linux/BSD server, you use Terminal almost as much as any other application.

So, Leo, please include this growing Mac demographic into your discussions.  There has to be someone out there in our space that is as knowledgable as Andy Ihnatko and Scott Bourne are about their topics.  Merlin comes close when he is there, but I think he is still and old school Mac user that happens to have gotten into the geekier parts of Mac OS X.

Still, love the show.  Keep up the good work.

So, day two was the cool keynote day.  Day one keynotes were from Tim O’Reilly (not that he is not cool) and the vendors sponsors.  The Intel building blocks stuff was neat, but most of it was vendor stuff IMO.

Today we had the “cool thing to here and see, but I proabably won’t use it” keynote.  It was The Processing Development Environment.  It was really cool.  You can read more about it at processing.org.

The next keynote was hard for me to follow.  There were no slides he stood behind the podium the whole time.  Gnat  seemed to love it as he all told us in IRC.  You can read the guys blog at overcomingbias.com.  It was basically about overcoming the biases you have…. I think.

Interestingly, (speaking of bias) the next keynote was from Microsoft.  Coincidence?  According to the speaker, MS (or at least this guy) is really trying to make some Open Source stuff.  Time will tell.  Also, they are “working” with the OSI to get their licensing approved as Open Source licenses.  As somone in IRC said, its a win/win from them.  If they don’t get approved, they can just blame the OSI for being inflexible.  Nate kind of put him on the spot about patents after his talk.  He handled it well and kind of rode the fence.

The last keynote was, for me, the pay off keynote.  Its the one I will remember from this year the most.  It was about branding.  The poor guy did not have his slides due to technical issues and still did a great job.  You can read Steve’s blog at steve-yegge.blogspot.com.  Maybe he will post the slides.

I attended a couple of good sessions today.  One was about caching, mostly with APC.  But, if you stripped down the APC stuff and just took some of his concepts, you could apply some of it to lots of caching methods.  The talk was given by Gopal Vijayaraghavan of Yahoo! I don’t have a URL for the site where his slides may be.  If I find it, I will post it.

Another one was about legacy PHP code.  I didn’t agree with 100% of what he was saying, but if you are in the boat he described, anything is better than where you are.  The guys site is clintonrnixon.net.  Hopefully he will put of the slides and maybe a blog post about it.

The last talk that I want to tell you about was from Amy Hoy.   She gave the “When Interface Design Attacks!” again this year. Just like last year, it was brilliant.  There were new topics like web 2.0.  I was happy to see that the Phorum 5.2 template I have been working on (emerald) already included many of her recommendations.  I guess she rubbed off on me last year.  Amy has started her own consulting company.  If we need a usability and/or interface design help again (bleh, the last one was less than exciting) I will push for using her for sure.  Check out her site (linked above) for more stuff from her.

The day (and conference really) ended with parties.  We went to the Sourceforge Open Source Awards party.  phpBB won best tool for communication.  Gag me with a chicken bone.  I guess it has a large install base.  But, MySpace has lots of users too.  That does not mean its not a black eye on the internet.  Ok, MySpace is worse than phpBB for sure.  But, c’mon, I write Phorum.  I am biased (see above keynote =).  It was a popularity contest and I guess there are more kiddies to vote for them than say Pidgin which is what I voted for.  With all the trouble they have had with their name, I wonder if “Gaim” would have gotten more votes.  (see other keynote on branding =).  The phpBB team may need to see the branding keynote from this morning.  It talked about how it takes a generation to change perception about a brand.  Most people I talked to here have a negative reaction to the phpBB brand.

The rest of the night we just hung out at the party hosted by Jive Software. We use OpenFire from those guys.  I am not a big Java user on the server.  Its just one more different thing to admin in a company that is 99% GNU C apps on the servers.  But, Openfire does a damn good job with XMPP.

In closing, O’Reilly Open Source Convention was great.  I got some great ideas of stuff we should be doing.  I got confirmation of things that we are already doing.  And most important, IMO, we got to share with others how we solve problems.  As  Gopal said in his caching talk, sometimes is better to stop doing stuff and tell others what you are doing (paraphrase).