indexes

Environment

I’m in the process of setting up a CentOS VPS running a moderately used D6 site (approximately 1000 visits/day). I’ve installed the entire stack including Apache, MySQL 5.5, and PHP 5.3. The VPS has a 3.6Ghz dedicated CPU, and 2.2GB Memory (provided through VPS.net)

I’ve installed Memcache and APC, which seem to be working fine. I’ve tuned MySQL using mysqltuner.pl and tuning-primer.sh. My current my.cnf is posted below. All tables are InnoDB.

Problem

My current issue is that when logged in, various pages tend to be very slow (occasionally taking over 10 seconds to generate). I have devel’s query log functionality enabled and it does not seem to be one specific query that causes the problem- it seems to be random queries that sometimes take several seconds. This especially seems to happen with admin pages.

Indexes?

After doing a bit of research, I believe I need to index various database tables. This site has 22 content types and 73 fields defined. My questions is how do I know what tables to index and what fields to index? I've looked into the DBtuner module, but that only shows core fields (as opposed to the CCK fields).

Does anyone have any tips or suggestions?

Below is my current my.cnf

[mysqld]

#added MH 1-17-12#comment this out after troubleshootinglog-queries-not-using-indexes

So I decided to create some code that will auto add indexes to your CCK fields where there are none. Works for me (using MySQL), may not work for you. Also at the bottom is code to add indexes elsewhere in Drupal.
<?php
$ret = array();
// Add indexes to CCK fields
$result = db_query("SHOW TABLES LIKE 'content_%'");
while ($table = db_result($result)) {
if ($table == 'content_group') {
db_add_index($ret, 'content_group', 'weight', array('weight'));

I've run into the problem of letting authenicated users access /sites/default/files/folder1/folder2 and see a full directory listing. Typically I solve this with htaccess (Options Indexes, IndexOptions +ScanHTMLTitles -IconsAreLinks FancyIndexing and other values). However, I see that Drupal's webroot/.htaccess does not play nicely.