Serial Port (DB9) signals
Most of the fancy signals are left over from external telephone modem control.  This is back when a computer had to talk to some external box that was then in turn connected to the phone line.  Think about most of the other signals as not just transferring data, but controlling a computer-interfaced telephone.

About the only signals beyond RX and TX you are likely to encounter today are RTS and CTS.  These are for flow control, and can still be useful today.  However, the vast majority of today's devices that still use RS-232 use only the basic RX and TX lines.

Some microcontrollers have UARTs that can optionally use RTS and CTS, because out of band flow control can be a useful thing.  I have a project going on right now where I've got two PICs on the same board that need to send data back and forth between each other, and I connected RX, TX, RTS, and CTS.  One of the PICs implements this flow control in hardware, so that is particularly easy.  On the other I had to do it in firmware since that UART has no such hardware capability.