如何优化这个网站。 需要一般性的建议

Question

this is my first question here, which is regarding a specific website optimization. A few moths ago, we launched www.kissid.ro for one of our clients which is some kind of community website.

Everything works great, but now this website is getting bigger and it shows some slowness when the pages are loading.

The server specs:
- PHP 5.2.1 (i think we need to upgrade on 5.3 to make use of the new garbage collector)
- Apache 2.2
- Quad Core Xeon Processor @ 2,8 Ghz and 4 GB DDR 3 RAM.
- XCACHE 1.3 (we added this a few months ago)
- Mysql 5.1 (we are using innodb as engine)
- Codeigniter framework

Here is what we did so far and what we intend to do further :

Beside xcache, we don't really use a caching mechanism because most of the content comes live and beside this, we didn't wanted to optimize prematurely because we didn't know what to expect as far as the traffic flow. On the other hand, we have installed memcached and we want to implement a cache system based on memcached.

Regarding the database structure, we have reached 3NF with most of our tables, and yes we have some slow queries(which we plan to optimize) but i think because the tables that produce slow queries are the one for blog comments(~44,408 rows) / user logs tracking (~725,837 rows) / user comments (~698,964 rows) etc which are quite big tables. The entire database is 697.4 MB in size for now.

Also, here are some stats for January 2011:
Monthly unique visitors: - 127.124
Monthly unique views: 4.829.252
Monthly unique visits: 242.708

Daily average:
Unique new visitors: 7.533
Unique new views : 179.680

Just let me know if you need more details.
Any advice is highly appreciated.

Thank you.

Answer 1

When it come to performance issue, there is no golden rule or labelled sticky note that first tell that is related to database. Maybe what i could suggest is to do performance profiling and there are many free and paid tools over the Internet that allows you to do so.

First start of with web server layer, make sure everything is done correctly and optimized as what is be possible.

Then move on to next layer (which i assume is your database). Normally from layman perspective whenever someone mentioned InnoDB MySQL, we assume there are indexes being created to optimize and search operations. The usage of indexes also quite important because you don't want to indexing something wrong and make things worse. My advise to this is to get a DBA equivalent personnel to troubleshoot using a staging environment.

Another tricks you could possibility look at is the contents, from web page contents to database data, make sure you show/keep data where is needed only, do no store unnecessary information into database and using smart layout on the webpage. A cut down of a seconds or two might do a big difference in terms of usability and response time.

It is very hard to explain the detail here unless we have in-depth information about your application, its architecture and your environment, but above are some commonly used direction people use to troubleshoot such incident.

Good luck!