MySQL problem - COMPLETE server overrun! Please ad

This is my first question here so please don't be tough on me... :P
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...

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... :(

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!...

I'm really desperate so please help me!

here are my latest stats (though they've been like this for like 4 days...)
08:02:35 up 12 days, 14:39, 0 users, load average: 25.82, 25.98, 25.92

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:

Page Loads  Unique Visits   
10,367              1,843

My main pages are index.php and videos.php (i run a video site btw)

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?

what they both do is:
(mainly the videos page, the index is just a simplified version of it)

check -and take if passed- a query and a page number.
query a database, take results from a table and show them

I use mysql_connect 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?

my database is myisam engined
I have 2 main tables: games and videos.
Along them are around 5-6000 tables used for storing comments...

 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

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.

what is the best thing I can do to reduce processing power use?

also, my support team just emailed me:

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 .

I just read something about the my.cnf file on google but nothing related to my case... Could someone please indicate me what should my my.cnf file contain? related to my sittuation?

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?

should i just post it here like this? Please, if a moderator reads this and can collapse it somehow, please do so!

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


----------------------------------------------------

I think this is all the information I have.

If you have mercy on me, please help!... thanks a million billion times!hot

回答1:

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?

No, the load is expressed as one unit per CPU, so if your server has four CPUs, a load of 1.00 (or 100%) means that one CPU is at full load and the other three are idling (or two are at 50% load and the other two at 0%, or any similar combination) and a load of 4.00 (or 400%) would mean that all the CPUs are at full capacity.

I use mysql_connect 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?

Why should it be a security issue? Maybe it can be a performance issue if misused, but definitely not a security issue.

From what you're writing it seems you're quite a novice on relational database and perhaps web development, the first step I suggest is to buy a database book and learn how they work and how to use them, then a good book on PHP. Otherwise, you will be programming by coincidence and will never resolve your issues.

By the way, if you're new (and you know it) you should give a read to the FAQs, How to ask and Editing help.

回答2:

First, isolate the problem. There's no point in tuning MySQL if the root cause of the problem is in your php code.

Your support said they "increased the table-cache to 12000", so they probably increased the value for table_open_cache from 400 (default) to 12000. (You probably shouldn't have 6000 tables. Reconsider that design.)

For MySQL, study these sections of the MySQL docs.

Tuning Server Parameters

When tuning a MySQL server, the two most important variables to configure are key_buffer_size and table_open_cache. You should first feel confident that you have these set appropriately before trying to change any other variables.

Preconfigured Option Files

MySQL provides a number of preconfigured option files that can be used as a basis for tuning the MySQL server. Look for files such as my-small.cnf, my-medium.cnf, my-large.cnf, and my-huge.cnf, which are sample option files for small, medium, large, and very large systems.

Server System Variables

Server Status Variables

You should also have a local or remote system for testing.

回答3:

I think the problem is you have a separate table for each video. All the comments (or whatever) should be stored in the SAME table with a video_id or similar attached to them to link them back to the video in question.