MySQL的my.cnf优化实例
我从网上找到的,我刚看了一下,还算不错,发在这里,大家看看,最好有牛人补充完善
然后,再整理整理!
========================================
在Apache, PHP, MySQL的体系架构中,MySQL对于性能的影响最大,也是关键的核心部分。对于Discuz!论坛程序也是如此,MySQL的设置是否合理优化,直接影响到论坛的速度和承载量!同时,MySQL也是优化难度最大的一个部分,不但需要理解一些MySQL专业知识,同时还需要长时间的观察统计并且根据经验进行判断,然后设置合理的参数。
下面我们了解一下MySQL优化的一些基础,MySQL的优化我分为两个部分,一是服务器物理硬件的优化;二是MySQL自身(my.cnf)的优化。
(1) 服务器硬件对MySQL性能的影响
a) 磁盘寻道能力(磁盘I/O),以目前高转速SCSI硬盘(7200转/秒)为例,这种硬盘理论上每秒寻道7200次,这是物理特性决定的,没有办法改变。MySQL每秒钟都在进行大量、复杂的查询操作,对磁盘的读写量可想而知。所以,通常认为磁盘I/O是制约MySQL性能的最大因素之一,对于日均访问量在100万PV以上的Discuz!论坛,由于磁盘I/O的制约,MySQL的性能会非常低下!解决这一制约因素可以考虑以下几种解决方案:
使用RAID-0+1磁盘阵列,注意不要尝试使用RAID-5,MySQL在RAID-5磁盘阵列上的效率不会像你期待的那样快; 抛弃传统的硬盘,使用速度更快的闪存式存储设备。经过Discuz!公司技术工程的测试,使用闪存式存储设备可比传统硬盘速度高出6-10倍左右。
b) CPU 对于MySQL应用,推荐使用S.M.P.架构的多路对称CPU,例如:可以使用两颗Intel Xeon 3.6GHz的CPU。
c) 物理内存对于一台使用MySQL的Database Server来说,服务器内存建议不要小于2GB,推荐使用4GB以上的物理内存。
(2) MySQL自身因素当解决了上述服务器硬件制约因素后,让我们看看MySQL自身的优化是如何操作的。对MySQL自身的优化主要是对其配置文件my.cnf中的各项参数进行优化调整。下面我们介绍一些对性能影响较大的参数。
由于my.cnf文件的优化设置是与服务器硬件配置息息相关的,因而我们指定一个假想的服务器硬件环境:
CPU: 2颗Intel Xeon 2.4GHz
内存: 4GB DDR
硬盘: SCSI 73GB
下面,我们根据以上硬件配置结合一份已经优化好的my.cnf进行说明:
# vi /etc/my.cnf
以下只列出my.cnf文件中[mysqld]段落中的内容,其他段落内容对MySQL运行性能影响甚微,因而姑且忽略。
[mysqld]
port = 3306
serverid = 1
socket = /tmp/mysql.sock
skip-locking
# 避免MySQL的外部锁定,减少出错几率增强稳定性。
skip-name-resolve
禁止MySQL对外部连接进行DNS解析,使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意,如果开启该选项,则所有远程主机连接授权都要使用IP地址方式,否则MySQL将无法正常处理连接请求!
back_log = 384
指定MySQL可能的连接数量。当MySQL主线程在很短的时间内接收到非常多的连接请求,该参数生效,主线程花费很短的时间检查连接并且启动一个新线程。
back_log参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。 如果系统在一个短时间内有很多连接,则需要增大该参数的值,该参数值指定到来的TCP/IP连接的侦听队列的大小。不同的操作系统在这个队列大小上有它自己的限制。
试图设定back_log高于你的操作系统的限制将是无效的。默认值为50。对于Linux系统推荐设置为小于512的整数。
key_buffer_size = 256M
# key_buffer_size指定用于索引的缓冲区大小,增加它可得到更好的索引处理性能。
对于内存在4GB左右的服务器该参数可设置为256M或384M。
注意:该参数值设置的过大反而会是服务器整体效率降低!
max_allowed_packet = 4M
thread_stack = 256K
table_cache = 128K
sort_buffer_size = 6M
查询排序时所能使用的缓冲区大小。注意:该参数对应的分配内存是每连接独占!如果有100个连接,那么实际分配的总共排序缓冲区大小为100 × 6 = 600MB。所以,对于内存在4GB左右的服务器推荐设置为6-8M。
read_buffer_size = 4M
读查询操作所能使用的缓冲区大小。和sort_buffer_size一样,该参数对应的分配内存也是每连接独享!
join_buffer_size = 8M
联合查询操作所能使用的缓冲区大小,和sort_buffer_size一样,该参数对应的分配内存也是每连接独享!
myisam_sort_buffer_size = 64M
table_cache = 512
thread_cache_size = 64
query_cache_size = 64M
指定MySQL查询缓冲区的大小。可以通过在MySQL控制台执行以下命令观察:
# > SHOW VARIABLES LIKE '%query_cache%';
# > SHOW STATUS LIKE 'Qcache%';
# 如果Qcache_lowmem_prunes的值非常大,则表明经常出现缓冲不够的情况;
如果Qcache_hits的值非常大,则表明查询缓冲使用非常频繁,如果该值较小反而会影响效率,那么可以考虑不用查询缓冲;Qcache_free_blocks,如果该值非常大,则表明缓冲区中碎片很多。
tmp_table_size = 256M
max_connections = 768
指定MySQL允许的最大连接进程数。如果在访问论坛时经常出现Too Many Connections的错误提 示,则需要增大该参数值。
max_connect_errors = 10000000
wait_timeout = 10
指定一个请求的最大连接时间,对于4GB左右内存的服务器可以设置为5-10。
thread_concurrency = 8
该参数取值为服务器逻辑CPU数量×2,在本例中,服务器有2颗物理CPU,而每颗物理CPU又支持H.T超线程,所以实际取值为4 × 2 = 8
skip-networking
开启该选项可以彻底关闭MySQL的TCP/IP连接方式,如果WEB服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项!否则将无法正常连接!
摘自:http://bbs.chinaunix.net/archiver/?tid-809159.html
2010年4月20日 星期二
Image Cache using phpThumb and Mod_Rewrite
Generate thumbs by visiting a URL such as your.com/thumbs/images/image.50x50.jpg. This will create a 50x50px thumbnail of your.com/images/image.jpg.
The thumb will be stored on your server at your.com/thumbs/images/image.50x50.jpg so the next request for the same image will be loaded without loading php for ultra fast image cache.
Introduction
About a year ago I came across a fantastic script called phpThumb. It is an open source project used to resize images. Sure you can do the same thing with tools such as GD2 or imagemagick (or magickwand), however its much nicer to not have to worry about those things and just focus on getting the right image with ease.
It was as easy as
The problems started to arise on high-volume servers when apache had to get PHP to parse the phpThumb code for every image requested. Sure it has caching but it still has to load PHP to decide if it should use the cache or not.
In the past I have seen this issue solved using mod_rewrite to redirect non-existent images to a script where they can be generated. As a proof-of-concept I will provide the basic information required to get this running.
What you need
Apache
mod_rewrite
PHP
These things usually come with dedicated and shared hosting servers by default, however installation is beyond the scope of this article.
Ok, just tell me how to do it!
Upload phpThumb
Download phpThumb from here:
http://phpthumb.sourceforge.net/
Upload phpThumb to yoursite.com/phpthumb
Setup Mod_Rewrite
Create yoursite.com/thumbs/.htaccess
RewriteEngine on
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*)$ index.php?thumb=$1 [L,QSA]
Create the Thumbnail Generator
Create yoursite.com/thumbs/index.php
/**
* Create a thumbnail
*
* @author Brett @ Mr PHP
*/
// define allowed image sizes
$sizes = array(
'50x50',
'100x100',
'250x250',
);
// ensure there was a thumb in the URL
if (!$_GET['thumb']) {
error('no thumb');
}
// get the thumbnail from the URL
$thumb = strip_tags(htmlspecialchars($_GET['thumb']));
// get the image and size
$thumb_array = explode('.',$thumb);
$image = '../';
foreach($thumb_array as $k=>$thumb_part){
if ($k != count($thumb_array)-2) {
$image .= $thumb_part . '.';
}
}
$image = substr($image,0,-1);
$size = $thumb_array[count($thumb_array)-2];
list($width,$height) = explode('x',$size);
// ensure the size is valid
if (!in_array($size,$sizes)) {
error('invalid size');
}
// ensure the image file exists
if (!file_exists($image)) {
error('no source image');
}
// generate the thumbnail
require('../phpthumb/phpthumb.class.php');
$phpThumb = new phpThumb();
$phpThumb->setSourceFilename($image);
$phpThumb->setParameter('w',$width);
$phpThumb->setParameter('h',$height);
$phpThumb->setParameter('f',substr($thumb,-3,3)); // set the output format
//$phpThumb->setParameter('far','C'); // scale outside
//$phpThumb->setParameter('bg','FFFFFF'); // scale outside
if (!$phpThumb->GenerateThumbnail()) {
error('cannot generate thumbnail');
}
// make the directory to put the image
if (!mkpath(dirname($thumb),true)) {
error('cannot create directory');
}
// write the file
if (!$phpThumb->RenderToFile($thumb)) {
error('cannot save thumbnail');
}
// redirect to the thumb
// note: you need the '?new' or IE wont do a redirect
header('Location: /thumbs/'.$thumb.'?new');
// basic error handling
function error($error) {
header("HTTP/1.0 404 Not Found");
echo '
echo '
echo "
exit();
}
//recursive dir function
function mkpath($path, $mode){
is_dir(dirname($path)) || mkpath(dirname($path), $mode);
return is_dir($path) || @mkdir($path,0777,$mode);
}
?>
Test it out!
Upload an image to yoursite.com/images/myimage.jpg
Open your web browser to yoursite.com/thumbs/images/myimage.100x100.jpg
Check in your thumbs folder, the file should actually be there now. Next time it is requested PHP will not be loaded.
Link to your thumbs like this:
Clearing Cache
This is a small script I use to delete all the cached thumbs.
Upload an image to yoursite.com/thumbs/flush.php
delete_dir('./images');
echo 'done';
function delete_dir($path) {
$files = glob($path.'/*');
foreach($files as $file) {
if(is_dir($file) && !is_link($file)) {
delete_dir($file);
}
else {
unlink($file);
}
}
if ($path!='./images') rmdir($path);
}
?>
摘自:http://mrphp.com.au/code/image-cache-using-phpthumb-and-modrewrite
The thumb will be stored on your server at your.com/thumbs/images/image.50x50.jpg so the next request for the same image will be loaded without loading php for ultra fast image cache.
Introduction
About a year ago I came across a fantastic script called phpThumb. It is an open source project used to resize images. Sure you can do the same thing with tools such as GD2 or imagemagick (or magickwand), however its much nicer to not have to worry about those things and just focus on getting the right image with ease.
It was as easy as
The problems started to arise on high-volume servers when apache had to get PHP to parse the phpThumb code for every image requested. Sure it has caching but it still has to load PHP to decide if it should use the cache or not.
In the past I have seen this issue solved using mod_rewrite to redirect non-existent images to a script where they can be generated. As a proof-of-concept I will provide the basic information required to get this running.
What you need
Apache
mod_rewrite
PHP
These things usually come with dedicated and shared hosting servers by default, however installation is beyond the scope of this article.
Ok, just tell me how to do it!
Upload phpThumb
Download phpThumb from here:
http://phpthumb.sourceforge.net/
Upload phpThumb to yoursite.com/phpthumb
Setup Mod_Rewrite
Create yoursite.com/thumbs/.htaccess
RewriteEngine on
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*)$ index.php?thumb=$1 [L,QSA]
Create the Thumbnail Generator
Create yoursite.com/thumbs/index.php
/**
* Create a thumbnail
*
* @author Brett @ Mr PHP
*/
// define allowed image sizes
$sizes = array(
'50x50',
'100x100',
'250x250',
);
// ensure there was a thumb in the URL
if (!$_GET['thumb']) {
error('no thumb');
}
// get the thumbnail from the URL
$thumb = strip_tags(htmlspecialchars($_GET['thumb']));
// get the image and size
$thumb_array = explode('.',$thumb);
$image = '../';
foreach($thumb_array as $k=>$thumb_part){
if ($k != count($thumb_array)-2) {
$image .= $thumb_part . '.';
}
}
$image = substr($image,0,-1);
$size = $thumb_array[count($thumb_array)-2];
list($width,$height) = explode('x',$size);
// ensure the size is valid
if (!in_array($size,$sizes)) {
error('invalid size');
}
// ensure the image file exists
if (!file_exists($image)) {
error('no source image');
}
// generate the thumbnail
require('../phpthumb/phpthumb.class.php');
$phpThumb = new phpThumb();
$phpThumb->setSourceFilename($image);
$phpThumb->setParameter('w',$width);
$phpThumb->setParameter('h',$height);
$phpThumb->setParameter('f',substr($thumb,-3,3)); // set the output format
//$phpThumb->setParameter('far','C'); // scale outside
//$phpThumb->setParameter('bg','FFFFFF'); // scale outside
if (!$phpThumb->GenerateThumbnail()) {
error('cannot generate thumbnail');
}
// make the directory to put the image
if (!mkpath(dirname($thumb),true)) {
error('cannot create directory');
}
// write the file
if (!$phpThumb->RenderToFile($thumb)) {
error('cannot save thumbnail');
}
// redirect to the thumb
// note: you need the '?new' or IE wont do a redirect
header('Location: /thumbs/'.$thumb.'?new');
// basic error handling
function error($error) {
header("HTTP/1.0 404 Not Found");
echo '
Not Found
';echo '
The image you requested could not be found.
';echo "
An error was triggered: $error
";exit();
}
//recursive dir function
function mkpath($path, $mode){
is_dir(dirname($path)) || mkpath(dirname($path), $mode);
return is_dir($path) || @mkdir($path,0777,$mode);
}
?>
Test it out!
Upload an image to yoursite.com/images/myimage.jpg
Open your web browser to yoursite.com/thumbs/images/myimage.100x100.jpg
Check in your thumbs folder, the file should actually be there now. Next time it is requested PHP will not be loaded.
Link to your thumbs like this:
Clearing Cache
This is a small script I use to delete all the cached thumbs.
Upload an image to yoursite.com/thumbs/flush.php
delete_dir('./images');
echo 'done';
function delete_dir($path) {
$files = glob($path.'/*');
foreach($files as $file) {
if(is_dir($file) && !is_link($file)) {
delete_dir($file);
}
else {
unlink($file);
}
}
if ($path!='./images') rmdir($path);
}
?>
摘自:http://mrphp.com.au/code/image-cache-using-phpthumb-and-modrewrite
How to check if checkbox is checked using jQuery
There are 3 ways in jQuery to find a check box is checked or not.
view plaincopy to clipboardprint?
// First way
$('#checkBox').attr('checked');
// Second way
$('#edit-checkbox-id').is(':checked');
// Third way for jQuery 1.2
$("input[@type=checkbox][@checked]").each(
function() {
// Insert code here
}
);
// Third way == UPDATE jQuery 1.3
$("input[type=checkbox][checked]").each(
function() {
// Insert code here
}
);
First two methods return true or false. True if that particular checkbox was checked or false if it is not checked. The third method iterates though all checkboxes with checked attribute defined and performs some action.
摘自:http://cfmitrah.com/blog/post.cfm/how-to-check-if-checkbox-is-checked-using-jquery
view plaincopy to clipboardprint?
// First way
$('#checkBox').attr('checked');
// Second way
$('#edit-checkbox-id').is(':checked');
// Third way for jQuery 1.2
$("input[@type=checkbox][@checked]").each(
function() {
// Insert code here
}
);
// Third way == UPDATE jQuery 1.3
$("input[type=checkbox][checked]").each(
function() {
// Insert code here
}
);
First two methods return true or false. True if that particular checkbox was checked or false if it is not checked. The third method iterates though all checkboxes with checked attribute defined and performs some action.
摘自:http://cfmitrah.com/blog/post.cfm/how-to-check-if-checkbox-is-checked-using-jquery
2010年4月11日 星期日
解決setcookie後,需要refresh / reload頁面才能取得cookie的值問題
setcookie('cookie_var','cookie_value',time()+604800,"/");
$_COOKIE['cookie_var'] = 'cookie_value'; //<-- 寫多這一行即可
$_COOKIE['cookie_var'] = 'cookie_value'; //<-- 寫多這一行即可
2010年4月6日 星期二
IE problem about javascript setTimeout class method
if( document.all ){ // IE
var me = this;
this.var_timeout = setTimeout(function() { me.startAnimateAdsSWFTwinClass() }, 1000);
}
var me = this;
this.var_timeout = setTimeout(function() { me.startAnimateAdsSWFTwinClass() }, 1000);
}
2010年4月5日 星期一
改动office outlook 2007默认文件夹的存放位置
office outlook 2007 数据文件默认的存放位置是C盘,因为经常动C盘,且时常用影子系统,所以里面的数据一天到晚会清空,导出不是个办法,所以想让它存到其他盘,但在帐号数据文件里只能看不能改文件路径,请问有没有什么方法能够实现。我现在的笨办法是把C:\Document s and Setting\user name\Local Settings\Application Data\Microsoft\Outlook文件夹剪切到其它盘,然后打开Outlook会说找不到pst文件,再定位到新的路径,但另外的E- mail帐号还是会保存到C盘,请问有什么方法能够解决?谢谢。
控制面板→邮件→数据文件,添加一个新的,然后再删除旧的
控制面板→邮件→数据文件,添加一个新的,然后再删除旧的
訂閱:
文章 (Atom)