SQLite uses a file for every database you create, which means that it is very easy to keep track of your data, particularly if you want to back up and restore information. However, it also means that this file must be easily available, preferably local - using remote file systems, such as NFS, is not recommended.
SQLite makes sense if you want to store some data, but do not need all the hassle of a client/server setup. In these situations, using an advanced DBMS like MySQL or Sybase just seems like overkill, and it is - this is where SQLite wins. On the performance front, SQLite is not as good as a true database server, and cannot really ever be. The SQLite proudly proclaims that SQLite is up to twice as fast as MySQL, but I've consistently found it to be less than that - there are dozens of optimisations that MySQL can do that SQLite cannot do, such as storing data in memory across requests and processes. What's more, many SQLite benchmarks test only one user at a time - a situation that, naturally, a flat file system handles very well.