MySQL问题-完整的服务器超限！请指教 [英] MySQL problem - COMPLETE server overrun! Please advise
问题描述
这是我在这里的第一个问题，所以请不要对我强硬...:P
我通常倾向于使用Google搜索解决方案，但现在我很着急，已经用光了我所有的资源...
因为我的网站是商业网站，所以继续前进并付钱给我帮助是很公平的，但是...我自己遇到了一些财务问题，这是我向专用服务器付款的第三个月，距我的网站开通以来仅一个月...我自己完成了所有事情，因为我负担不起任何人的钱，而且也没有广告，因为我认为我现在不会得到任何收益，而广告只会没用...虽然我打算在我网站的后期使用广告，但我并没有从(至少出于我的预算)昂贵的服务器租金中赚到一毛钱...:(
这是我的问题: 我有一个专用的服务器，直到几天前流量开始上升之前，它一直运行得很好...几天来，我一直从服务器的cpu状态监视器中收到电子邮件... 就像卡在油门上一样，我试图找出原因并修复问题！...
我真的很绝望，请帮助我！
这是我的最新统计信息(尽管他们已经有4天了……)
08:02:35 up 12 days, 14:39, 0 users, load average: 25.82, 25.98, 25.92
我使用统计计数器，我认为它们非常准确(代码在每个页面上，所以这是我网站的全局结果)... 我的统计数据是:
Page Loads Unique Visits
10,367 1,843
我的主页是index.php和videos.php(我经营一个视频网站btw)
index和videos.php页面始终使用大约140-150-160％的CPU 我的意思是 他们使用率超过100％?喜欢借钱还是什么?
他们俩都做的是:
(主要是视频页面，索引只是它的简化版本)
检查-如果通过，则进行查询和页码.
查询数据库，从表中获取结果并显示
我将mysql_connect用于从mysql中获取的所有内容.
我听说mysql中的永久连接是对大量查询的可能解决方案，但同时也了解到由于安全问题而导致此方法的许多弊端...
请指教吗?
我的数据库是由myisam驱动的
我有2个主要表格:游戏和视频.
沿着它们大约有5-6000个用于存储评论的表...
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
其余表用于注释，但除id字段外，它们没有任何索引. 他们只保留评论和日期. 当我加载搜索页面时，所有数据均取自数据库: 搜索关键字，获取最相关的关键字，然后回显与匹配项有关的所有数据. 打开视频页面时，将加载来自特定数据库的所有评论，并将其显示在框中.
为了减少处理能力的使用，我能做些什么?
另外，我的支持团队也通过电子邮件给我发送了邮件:
我们已经将表缓存增加到12000，请现在检查它的运行方式.最好也获得一个最适合您的工作环境的my.cnf文件，此处mysql变量设置为默认值值.
我刚刚在Google上阅读了有关my.cnf文件的内容，但与我的案子没有任何关系...
有人可以告诉我我的my.cnf文件应该包含什么吗?与我的情况有关?
我在这里有我的服务器信息，但是要占用一些空间...我怎样才能以某种方式关闭它，而只有在想阅读的情况下才能打开?不使用链接?
我应该这样发布吗? 请，如果主持人阅读并可以某种方式将其折叠起来，请这样做！
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
----------------------------------------------------
我认为这是我所拥有的全部信息.
如果您对我有怜悯，请帮助！ 感谢十亿次！hot
index和videos.php页面始终使用大约140-150-160％的CPU，我是说吧?他们使用率超过100％?喜欢借钱还是什么?
否，负载表示为每个CPU一个单位，因此，如果您的服务器有四个CPU，则负载为1.00(或100％)表示一个CPU处于满负载，而其他三个则处于空闲状态(或两个为空载.如果负载为50％，另外两个负载为0％，或任何类似的组合)，负载为4.00(或400％)，则意味着所有CPU都已满负荷工作.
我将mysql_connect用于从mysql获取的所有内容.我听说mysql中的永久连接是对大量查询的一种可能的解决方案，但同时也了解到由于安全问题而导致此方法的许多弊端...请指教?
为什么应该是安全问题?如果使用不当，可能是一个性能问题，但绝对不是安全问题.
从您撰写的内容来看，您似乎对关系数据库或Web开发是一个新手，我建议的第一步是购买一本数据库书，并学习它们的工作方式和使用方法，然后再学习关于PHP的书.否则，您将巧合编程，并且将永远无法解决您的问题. /p>
顺便说一句，如果您是新手(并且知道)，则应该阅读常见问题解答，如何提问和编辑帮助.
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
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.
这篇关于MySQL问题-完整的服务器超限！请指教的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
