title: MySQL problem - COMPLETE server overrun! Please advise
body: <p>This is my first question here so please don't be tough on me... :P<br>
  I usually tend to google my way to a solution but now I'm in a hurry and have used up all the resources I had...</p>
  
  <p>because my website is a commercial one, it would be fair to go ahead and pay someone to help me but... I have some financial problems myself, this was the 3rd month of payment to my dedicated server, still it was only the first month since my site was actually up... I have made it all by myself because I cannot afford paying anyone, and have no ads on it because I don't think I would be getting anything now and the ads would just be useless...although I intend in using ads later in my site's life, i don't get a dime from the (at least for my budget) expensive server rent... :(</p>
  
  <p>This is my problem:
  I have a dedicated server which has been going great until a few days ago when my traffic started going up...for a few days, i keep getting emails from my cpu status monitor from my server...
  it's like stuck at full throttle and I'm trying to find out why and repair the problem!...</p>
  
  <p>I'm really desperate so please help me!</p>
  
  <p>here are my latest stats (though they've been like this for like 4 days...)<br>
  <code>08:02:35 up 12 days, 14:39,  0 users,  load average: 25.82, 25.98, 25.92</code></p>
  
  <p>I use stat counter, I think they are pretty accurate (the code is on every page so these are my website's global results)...
  My stats are: </p>
  
  <pre><code>Page Loads  Unique Visits   
  10,367              1,843   
  </code></pre>
  
  <p>My main pages are <code>index.php</code> and <code>videos.php</code> (i run a video site btw)</p>
  
  <p>the index and videos.php pages are always using around 140-150-160% of the cpu
  i mean huh?
  they're using more than 100%? like loaning or what?</p>
  
  <p>what they both do is:<br>
  (mainly the videos page, the index is just a simplified version of it)  </p>
  
  <p>check -and take if passed- a query and a page number.<br>
  query a database, take results from a table and show them   </p>
  
  <p>I use <code>mysql_connect</code> for everything i take from mysql.
  I heard of permanent connections in mysql as being a possible solution to a large number of queries, but also read about a lot of cons to this method caused by security issues...
  please advise?</p>
  
  <p>my database is myisam engined<br>
  I have 2 main tables: games and videos.<br>
  Along them are around 5-6000 tables used for storing comments...    </p>
  
  <pre><code> games table:    9   columns    
   id - primary   
   name - fulltext - used by my search    
  
   videos table:    17 columns    
   indexes:   
   id-primary     
   title,tags-fulltext-used by search     
  </code></pre>
  
  <p>the rest of the tables are used for comments but they dont have any index except the id field.
  they only keep the comment and the date in themselves.
  when i load a search page, all data is taken from the db:
  search for keywords, take the most relative and then echo all data related to the matched items.
  when opening a video page, all comments from a certain db are loaded and displayed in a box.</p>
  
  <p>what is the best thing I can do to reduce processing power use?</p>
  
  <p>also, my support team just emailed me:</p>
  
  <blockquote>
    <p>we have increased the table-cache to 12000 , please check now how it goes .And also it is better to get a my.cnf file which is most suitable to your working environment , here the mysql variables are set to the default values .</p>
  </blockquote>
  
  <p>I just read something about the <code>my.cnf</code> file on google but nothing related to my case...
  Could someone please indicate me what should my <code>my.cnf</code> file contain? related to my sittuation?</p>
  
  <p>I have my server information here, but it takes some space... how can I close it somehow and you only open if you want to read? without using links?</p>
  
  <p>should i just post it here like this?
  Please, if a moderator reads this and can collapse it somehow, please do so!</p>
  
  <pre><code>Server Information:
  -------------------------------------------------------
  
  Processor Information
  Total processors: 6
  
  Processor #1
  
  Vendor
      AuthenticAMD
  
  Name
      AMD Phenom(tm) II X6 1055T Processor
  
  Speed
      2800.000 MHz
  
  Cache
      512 KB
  
  Processor #2
  
  Vendor
      AuthenticAMD
  
  Name
      AMD Phenom(tm) II X6 1055T Processor
  
  Speed
      2800.000 MHz
  
  Cache
      512 KB
  
  Processor #3
  
  Vendor
      AuthenticAMD
  
  Name
      AMD Phenom(tm) II X6 1055T Processor
  
  Speed
      2800.000 MHz
  
  Cache
      512 KB
  
  Processor #4
  
  Vendor
      AuthenticAMD
  
  Name
      AMD Phenom(tm) II X6 1055T Processor
  
  Speed
      2800.000 MHz
  
  Cache
      512 KB
  
  Processor #5
  
  Vendor
      AuthenticAMD
  
  Name
      AMD Phenom(tm) II X6 1055T Processor
  
  Speed
      2800.000 MHz
  
  Cache
      512 KB
  
  Processor #6
  
  Vendor
      AuthenticAMD
  
  Name
      AMD Phenom(tm) II X6 1055T Processor
  
  Speed
      2800.000 MHz
  
  Cache
      512 KB
  
  
  Memory Information
  
  Memory for crash kernel (0x0 to 0x0) notwithin permissible range
  Memory: 7917400k/8912896k available (2575k kernel code, 208164k reserved, 1304k data, 212k init)
  
  
  
  System Information
  
  Linux nrgysrv1.hostrprv1.com 2.6.18-194.32.1.el5 #1 SMP Wed Jan 5 17:52:25 EST 2011 x86_64 x86_64 x86_64 GNU/Linux
  
  
  Physical Disks
  
  SCSI device sda: 2930277168 512-byte hdwr sectors (1500302 MB)
  sda: Write Protect is off
  sda: Mode Sense: 00 3a 00 00
  SCSI device sda: drive cache: write back
  SCSI device sda: 2930277168 512-byte hdwr sectors (1500302 MB)
  sda: Write Protect is off
  sda: Mode Sense: 00 3a 00 00
  SCSI device sda: drive cache: write back
  sd 0:0:0:0: Attached scsi disk sda
  SCSI device sdb: 2930277168 512-byte hdwr sectors (1500302 MB)
  sdb: Write Protect is off
  sdb: Mode Sense: 00 3a 00 00
  SCSI device sdb: drive cache: write back
  SCSI device sdb: 2930277168 512-byte hdwr sectors (1500302 MB)
  sdb: Write Protect is off
  sdb: Mode Sense: 00 3a 00 00
  SCSI device sdb: drive cache: write back
  sd 3:0:0:0: Attached scsi disk sdb
  sd 0:0:0:0: Attached scsi generic sg0 type 0
  sd 3:0:0:0: Attached scsi generic sg1 type 0
  
  
  
  Current Memory Usage
  
               total       used       free     shared    buffers     cached
  Mem:       7920256    7873744      46512          0      61148    6980096
  -/+ buffers/cache:     832500    7087756
  Swap:      4192956        184    4192772
  Total:    12113212    7873928    4239284
  
  
  Current Disk Usage
  
  Filesystem            Size  Used Avail Use% Mounted on
  /dev/sda3             1.4T  636G  645G  50% /
  /dev/sda1              99M   18M   77M  19% /boot
  tmpfs                 3.8G     0  3.8G   0% /dev/shm
  /dev/sdb1             1.4T  291G 1016G  23% /backup
  /usr/tmpDSK           485M   11M  449M   3% /tmp
  
  
  ----------------------------------------------------
  </code></pre>
  
  <p>I think this is all the information I have.</p>
  
  <p>If you have mercy on me, please help!...
  thanks a million billion times!hot</p>