A megapixel is a unit of measurement for the resolution of digital cameras. Higher megapixel counts are the primary marketing indicator of a higher-quality digital camera. One megapixel is equal to one million pixels. This is about 25% better than 1024x768, a popular screen resolution for computer displays. The ever-increasing resolution on consumer digital cameras is primarily driven by small and smaller charged coupled device arrays getting smaller and cheaper to produce.
Today's average digital cameras typically boast a resolution of over 10 megapixels and can cost under US$150. This is enormously more resolution than the average family vacation photographer needs, and was just a few years ago only available in high-end professional digital cameras. A 1 MP camera easily has enough resolution to display pictures on a computer, the primary advantage to higher resolutions is for printing them. I find that as a rule of thumb, for each megapixel of resolution, a photograph can be very clearly printed on 1 inch of paper measured on the short side. That is, a 3 MP digital picture looks as good as a traditional photograph on 3x5" paper, a 4 MP camera looks good on 4x6" paper, and so forth. 10 MP is more than enough to fill an entire 8x10" frame. Lower resolutions can still obtain passable results on bigger paper, however, if you're willing to put up with slight blurring.
The disadvantage to ever-increasing digital camera resolutions is that the pictures themselves require more and more storage space. Depending on a number of factors, including JPG compression ratio and the amount of detail in the picture, a 10 MP image can take up several megabytes of storage space. When taking large numbers of pictures you can fill up memory cards surprisingly quickly that way. One of the advantages of digital photography is that you can take several times many more pictures than you plan to keep, deleting all but the very best ones. Taking images at higher resolutions than you need reduces your ability to do this. Furthermore, sharing the pictures via image sites such as Flickr and by emailing them to friends will take much longer, and can even fill up or overload a person's e-mailbox if taken to extremes.
There are several methods to keep the size of your images under control. Digital cameras all have the ability to take pictures at reduced resolutions, typically represented by a scale of one to four stars. The difference between the two highest quality settings are generally the amount of JPG compression, both being the same actual resolution. The second-highest setting will be smaller in file size, but equal in resolution, although it may have JPG compression artifacts under some circumstances. Going further down the scale, the camera begins reducing the actual resolution of the picture. Consult your user manual to find out how your camera behaves.
The problem is that most digital camera users don't really understand what they're doing with their cameras, preferring not to go into the menu to change the settings or read the finer points, if anything at all, in the manual. In general they can get away with this just fine, pointing and shooting and getting decent, if inefficient results. This is why you sometimes get 14 Megabyte emails from your aunt.
This is not to say that the average user will never have any reason to use the full 10 MP resolution. 10 MP photographs still look gorgeous printed on 8x10" and larger paper. Other uses include digital zoom (which was generally a joke until cameras hit about 5 MP) and capturing fine details in large images for later cropping. For general use, however, the camera is probably best set to a much lower setting.
Size isn't everything
It's important to realize that megapixel count is in no way an absolute measure of how good a picture will be. First of all, beyond a certain point, you simply can't notice any difference. No computer display in anyone's home office can display an entire 10 MP image at full resolution, so it's a complete waste if you don't want to crop or print it, and even printing a 10 MP image is a waste unless you're printing full-page images. Second, the quality of the camera itself makes a difference too. The lens, software that controls the auto focus and shutter speed, and the post-processing all make a huge difference in the final image quality. Finally, the ability of the photographer cannot be dismissed. Using the correct setting (e.g. macro, normal, action), judging whether or not to use the flash, and just plain holding the camera steady are factors that the camera simply can't account for no matter how good it is.
Digital cameras have gotten so small that they're even available in cell phones these days. Cameras in non-dedicated devices operate at much lower resolutions, of course, since the camera is a secondary feature of the phone. Even the highest-end camera phones tend to "only" be around 3 MP, but of course this was top of the line in consumer digital cameras just a few years ago.
The highest resolution digital camera I am aware of is NASA's Large Synoptic Survey Telescope, which will have over 3 billion pixels of resolution. Not bad, compared to the Hubble Space Telescope's original 800x800 pixel camera from the 1970s.