Monster Oasis

Share on Facebook

// —–

Using NGINX + PHP5-FPM
sudo apt-get install nginx php5-fpm php5-mysql
cd /etc/init.d/
sudo service apache2 stop
sudo update-rc.d -f apache2 remove

http://yoodey.com/how-install-configure-nginx-php-fpm-memcached-work-drupal-7-ubuntu-1010-maverick

Install Memcached
sudo apt-get install memcached libmemcached-dev php5-memcached php5-memcache

// ====================

Getting a new virtual machine up and running from the default AMI is easy.  Here’s my cookbook:
Install basic services

    yum install mysql mysql-server mysql-devel
    yum install httpd httpd-devel
    yum install php php-devel php-mysql php-gd php-dom php-pear php-json
    yum install svn

Configure those services to start at boot

    chkconfig –level 345 httpd on
    chkconfig –level 345 mysqld on
    chkconfig –list

Install APC

I couldn’t find a package, and this bug report explains why we have to use the beta.

    yum install gcc pcre-devel
    pecl install apc-beta
    echo extension=apc.so > /etc/php.d/apc.ini

Install memcached

    yum install libevent libevent-devel
    curl -O http://memcached.googlecode.com/files/memcached-1.4.5.tar.gz
    cd memcached-1.4.5
    ./configure
    make ; make install
    /usr/local/bin/memcached -u nobody -d -m 30 -l 127.0.0.1 -p 11211

// ----
<pre>
[root@ip-10-130-9-23 log]# cat /etc/fstab
#
LABEL=/     /           ext4    defaults,noatime  1   1
tmpfs       /dev/shm    tmpfs   defaults        0   0
devpts      /dev/pts    devpts  gid=5,mode=620  0   0
sysfs       /sys        sysfs   defaults        0   0
proc        /proc       proc    defaults        0   0
[root@ip-10-130-9-23 log]# cat /etc/mtab
/dev/xvda1 / ext4 rw,noatime 0 0
proc /proc proc rw 0 0
sysfs /sys sysfs rw 0 0
devpts /dev/pts devpts rw,gid=5,mode=620 0 0
tmpfs /dev/shm tmpfs rw 0 0
none /proc/sys/fs/binfmt_misc binfmt_misc rw 0 0

</pre>

Share on Facebook

1. Out of resources when opening file
在 my.cnf 加 open-files-limit = 6000

2. Too many partitions were defined

Ref. URL :  http://www.monster.com.tw/archives/2504

Share on Facebook

Share on Facebook

URL : http://dev.mysql.com/doc/refman/5.0/en/procedure-analyse.html

Continue reading ‘[memo] MySQL PROCEDURE ANALYSE / schema 分析’ »

Share on Facebook

Share on Facebook

download: http://dn.monster.tw/my/docs/mysql/mysql_perf_tuning.pdf

Share on Facebook

Mysql server version:

mysql> show variables like '%version%';
+--------------------------+------------------------------+
| Variable_name            | Value                        |
+--------------------------+------------------------------+
| protocol_version         | 10                           |
| version                  | 5.1.46-community             |
| version_comment          | MySQL Community Server (GPL) |
| version_compile_machine  | x86_64                       |
| version_compile_os       | unknown-linux-gnu            |
+--------------------------+------------------------------+
9 rows in set (0.01 sec)

Check things…

mysql> show variables like '%inno%';
+-----------------------------------------+------------------------+
| Variable_name                           | Value                  |
+-----------------------------------------+------------------------+
| have_innodb                             | YES                    |

mysql> show variables like '%part%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |

my schema:

--
CREATE TABLE `tbl_ts_ip` (
  `xdate` int(8) NOT NULL default 0,
  `xhour` varchar(2) NOT NULL default '00',
  `tag1` varchar(100) NOT NULL default '',
  `tag2` varchar(300) NOT NULL default '',
  `val` int(10) unsigned  NOT NULL default '0',
  KEY `ndx_xdate` (`xdate`),
  KEY `ndx_xhour` (`xhour`),
  KEY `ndx_tag1` (`tag1`),
  KEY `ndx_tag2` (`tag2`)
) ENGINE=InnoDB
partition by range(xdate) (
  partition p201003 values less than (20100400),
  partition p201004 values less than (20100500),
  partition p201005 values less than (20100600),
  partition p201006 values less than (20100700)
);

testing

--
insert into tbl_ts_ip values (20100301,'01','tag1','tag2',1);
insert into tbl_ts_ip values (20100401,'01','tag1','tag2',1);
insert into tbl_ts_ip values (20100501,'01','tag1','tag2',1);
insert into tbl_ts_ip values (20100601,'01','tag1','tag2',1);
insert into tbl_ts_ip values (20100701,'01','tag1','tag2',1);
--

result:

--
mysql> insert into tbl_ts_ip values (20100301,1,'tag1','tag2',1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbl_ts_ip values (20100401,1,'tag1','tag2',1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbl_ts_ip values (20100501,1,'tag1','tag2',1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbl_ts_ip values (20100601,1,'tag1','tag2',1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbl_ts_ip values (20100701,1,'tag1','tag2',1);
ERROR 1526 (HY000): Table has no partition for value 20100701
mysql> select * from tbl_ts_ip;
+----------+-------+------+------+-----+
| xdate    | xhour | tag1 | tag2 | val |
+----------+-------+------+------+-----+
| 20100301 |     1 | tag1 | tag2 |   1 |
| 20100401 |     1 | tag1 | tag2 |   1 |
| 20100501 |     1 | tag1 | tag2 |   1 |
| 20100601 |     1 | tag1 | tag2 |   1 |
+----------+-------+------+------+-----+
4 rows in set (0.00 sec)
--

data files :

[root@ stat]# ls -l
total 676
-rw-rw---- 1 mysql mysql     65 May 18 17:02 db.opt
-rw-rw---- 1 mysql mysql   8682 May 18 17:07 tbl_ts_ip.frm
-rw-rw---- 1 mysql mysql     52 May 18 17:07 tbl_ts_ip.par
-rw-rw---- 1 mysql mysql 163840 May 18 17:09 tbl_ts_ip#P#p201003.ibd
-rw-rw---- 1 mysql mysql 163840 May 18 17:09 tbl_ts_ip#P#p201004.ibd
-rw-rw---- 1 mysql mysql 163840 May 18 17:09 tbl_ts_ip#P#p201005.ibd
-rw-rw---- 1 mysql mysql 163840 May 18 17:09 tbl_ts_ip#P#p201006.ibd

add partitions:

--
alter table tbl_ts_ip
add partition (
  partition p201007 values less than (20100800),
  partition p201008 values less than (20100900),
  partition p201009 values less than (20101000),
  partition p201010 values less than (20101100),
  partition p201011 values less than (20101200),
  partition p201012 values less than (20110100)
);
--

explain partitions:

--
mysql> explain partitions select * from tbl_ts_ip where xdate=20100501;
+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+-------+
| id | select_type | table     | partitions | type | possible_keys | key       | key_len | ref   | rows | Extra |
+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+-------+
|  1 | SIMPLE      | tbl_ts_ip | p201005    | ref  | ndx_xdate     | ndx_xdate | 4       | const |    1 |       |
+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+-------+
--
--
--
mysql> explain partitions select * from tbl_ts_ip where xdate>20100101 and xdate<20100501 and xhour=1 and tag1='x1' and tag2='x2';
+----+-------------+-----------+-------------------------+------+---------------------------------------+-----------+---------+-------+------+-------------+
| id | select_type | table     | partitions              | type | possible_keys                         | key       | key_len | ref   | rows | Extra       |
+----+-------------+-----------+-------------------------+------+---------------------------------------+-----------+---------+-------+------+-------------+
|  1 | SIMPLE      | tbl_ts_ip | p201003,p201004,p201005 | ref  | ndx_xdate,ndx_xhour,ndx_tag1,ndx_tag2 | ndx_xhour | 4       | const |    3 | Using where |
+----+-------------+-----------+-------------------------+------+---------------------------------------+-----------+---------+-------+------+-------------+
--

######################

sample schema:

create table t1 (

  d date

) engine=innodb partition by range ( to_days(d) ) (

  partition p1 values less than (to_days(’1999-01-01′)),

  partition p2 values less than (to_days(’2000-01-01′))

);

– drop old partition

alter table tablename

  drop partition p1;

######

--
CREATE TABLE `tbl_ts_ip` (
  `xdate` int(8) NOT NULL default 0,
  `xhour` int(2) NOT NULL default 0,
  `tag1` varchar(100) NOT NULL default '',
  `tag2` varchar(300) NOT NULL default '',
  `val` int(10) unsigned  NOT NULL default '0',
  KEY `ndx_xdate` (`xdate`),
  KEY `ndx_xhour` (`xhour`),
  KEY `ndx_tag1` (`tag1`),
  KEY `ndx_tag2` (`tag2`)
) ENGINE=InnoDB
partition by range(xdate) (
  partition p200901 values less than (20090200),
  partition p200902 values less than (20090300),
  partition p200903 values less than (20090400),
  partition p200904 values less than (20090500),
  partition p200905 values less than (20090600),
  partition p200906 values less than (20090700),
  partition p200907 values less than (20090800),
  partition p200908 values less than (20090900),
  partition p200909 values less than (20091000),
  partition p200910 values less than (20091100),
  partition p200911 values less than (20091200),
  partition p200912 values less than (20100100),
  partition p201001 values less than (20100200),
  partition p201002 values less than (20100300),
  partition p201003 values less than (20100400),
  partition p201004 values less than (20100500),
  partition p201005 values less than (20100600),
  partition p201006 values less than (20100700),
  partition p201007 values less than (20100800),
  partition p201008 values less than (20100900),
  partition p201009 values less than (20101000),
  partition p201010 values less than (20101100),
  partition p201011 values less than (20101200),
  partition p201012 values less than (20110100),
  partition p201101 values less than (20110200),
  partition p201102 values less than (20110300),
  partition p201103 values less than (20110400),
  partition p201104 values less than (20110500),
  partition p201105 values less than (20110600),
  partition p201106 values less than (20110700),
  partition p201107 values less than (20110800),
  partition p201108 values less than (20110900),
  partition p201109 values less than (20111000),
  partition p201110 values less than (20111100),
  partition p201111 values less than (20111200),
  partition p201112 values less than (20120100)
);
--

###

一天一個 partition …

--
CREATE TABLE `tbl_ts_ip` (
  `xdate` date NOT NULL DEFAULT '0000-00-00',
  `xhour` int(2) NOT NULL DEFAULT '0',
  `tag1` varchar(100) NOT NULL DEFAULT '',
  `tag2` varchar(300) NOT NULL DEFAULT '',
  `val` int(10) unsigned NOT NULL DEFAULT '0',
  KEY `ndx_xdate` (`xdate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE ( to_days(xdate) )
(PARTITION p0 VALUES LESS THAN ( to_days('2005-1-1') ) );
--
--
alter table tbl_ts_ip
add partition (
  partition p20100401 values less than ( to_days('20100401')+1 )
);

--

###

產生 alter table … add partition 的 code

$start_date = mktime( 0,0,0,4,1,2010);
$day_count = 365;

for ( $i=0; $i < $day_count; $i++ ) {
  echo "
    alter table tbl_ts_ip
    add partition (
      partition p".date("Ymd",$start_date)." values less than ( to_days('".date("Ymd",$start_date)."')+1 )
    );
  ";
  echo "
    alter table tbl_ts_member
    add partition (
      partition p".date("Ymd",$start_date)." values less than ( to_days('".date("Ymd",$start_date)."')+1 )
    );
  ";
  $start_date += 86400;
}

###

--
mysql> explain partitions select * from tbl_ts_ip where xdate>20100510 and xdate<20100517 and tag2='114.39.237.77'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl_ts_ip
   partitions: p0,p20100511,p20100512,p20100513,p20100514,p20100515,p20100516
         type: ALL
possible_keys: ndx_xdate
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 37706064
        Extra: Using where
1 row in set (0.01 sec)

mysql> select * from tbl_ts_ip where xdate>20100510 and xdate<20100517 and tag2='114.39.237.77';
+------------+-------+--------------------+---------------+-----+
| xdate      | xhour | tag1               | tag2          | val |
+------------+-------+--------------------+---------------+-----+
| 2010-05-16 |    23 | 37043600380A380C3E | 114.39.237.77 |   2 |
+------------+-------+--------------------+---------------+-----+
1 row in set (24.26 sec)
--

###

—

用 mysql partition table 要注意的事:

Check resources!

• InnoDB
  
  check disk space (uses more than MyISAM)

  check CPU usage

• MyISAM
  
  use 2 file handles per partition

  If you use more than one partitioned table, count total file handles

• Archive
  
  check CPU usage

  check memory usage

Share on Facebook

Share on Facebook

Share on Facebook

重要參數1 = key_buffer_size 這個值只針對 MYISAM engine 有作用 , 這個值用來設定 index 使用多少 memory , mysql 把 index 儘可能的放在 memory 中, 這 key_buffer_size 決定 index 的使用效率 , 效率高的話就是多數的 索引 index 都是存在 memory 中, 那就 select 會快, 一般這個值是 16M , 這兩個狀態值可以用來判斷: Key_reads , Key_read_requests

看以下的例子:
DBM53 的 
show global status like ‘Key_read%’
Key_read_requests 36,479,348,961 
Key_reads 1,009,874,537 

Key_reads / Key_read_requests = 0.02 這樣偏高, 不太好 , 這個值正常是 0.01 , 0.001 最佳

好朋友 phpmyadmin 裡面有很好的解釋:

這台的 show global variables like ‘key_%’ 是 128MB
key_buffer_size 134,217,728

set global key_buffer_size=1024M

—

重要參數2 = table_cache

show global status like ‘Open%’

Open_tables 2,048 

若等於

show global variables like ‘%table%cache%’

table_cache 2,048 

並且 Opened_tables 一直增加 , 那要增加 table_cache 的值 , server 1G RAM 推薦 table_cache 128 ~ 256

沒錯, phpmyadmin 指示

那麼就來修改 table_cache 吧!

set global table_cache=3000;

not finish!

參考網址:

• http://www.slideshare.net/ligaya/performance-tuning

Share on Facebook

• mysql 的 innodb 重裝或改了 innodb_log_file_size 後, 發現 xxx/yyy.frm 壞了 , 解決辦法是 把 /var/lib/mysql/ib_logfile* 砍了, 再 restart mysql
• 在 http://dev.mysql.com/doc/refman/5.1/en/alter-table.html 中提到….

  若要大量 bluk 作 insert 動作前, 下 ALTER TABLE tbl_name DISABLE KEYS , 這樣可以讓 insert 加快,

  但是作完 insert 後還是得 enable keys , 把 missing 的 indexs 補回來, 我想這時也是非常耗時間吧!!

  另外 enable / disable keys 對於 mysql 5.1.1 以前的 partition table 沒有用

Share on Facebook

mysql> explain
    -> select SQL_CALC_FOUND_ROWS tbl_item.g_no, tbl_item.ctrl_rowid ,tbl_item.rank , g_priority_order
    -> from tbl_item
    -> where
    -> match(g_name_key) against('+AA +BB CC' in boolean mode)
    -> and tbl_item.g_end_int > 1249953400
    -> and tbl_item.g_class not like '0025%'
    -> order by g_priority_order desc, tbl_item.rank desc,tbl_item.g_post_time desc limit 0, 30\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl_item
         type: fulltext
possible_keys: ndx_g_end_int,ndx_g_name_key
          key: ndx_g_name_key
      key_len: 0
          ref:
         rows: 1
        Extra: Using where; Using filesort
1 row in set (0.00 sec)

Share on Facebook

參考: http://dev.mysql.com/doc/refman/5.0/en/optimizing-database-structure.html

7.4.1. Make Your Data as Small as Possible
7.4.2. Column Indexes
7.4.3. Multiple-Column Indexes
7.4.4. How MySQL Uses Indexes
7.4.5. The MyISAM Key Cache
7.4.6. MyISAM Index Statistics Collection
7.4.7. How MySQL Opens and Closes Tables
7.4.8. Disadvantages of Creating Many Tables in the Same Database

減少 record structure 的大小 – Numeric Types 參考表
(用 MEDIUMINT 3bytes 比 INT 4bytes 好 , 若資料內容不可能有負值那加上 UNSIGNED , 則數值範圍可多一倍!)

Share on Facebook

http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

The name index is an index over the last_name and first_name columns. The index can be used for queries that specify values in a known range for last_name, or for both last_name and first_name. Therefore, the name index is used in the following queries:

SELECT * FROM test WHERE last_name='Widenius';

SELECT * FROM test
  WHERE last_name='Widenius' AND first_name='Michael';

SELECT * FROM test
  WHERE last_name='Widenius'
  AND (first_name='Michael' OR first_name='Monty');

SELECT * FROM test
  WHERE last_name='Widenius'
  AND first_name >='M' AND first_name < 'N';

However, the name index is not used in the following queries, 以下的 query 用不到 index —> 多重 index 有先後區別.

SELECT * FROM test WHERE first_name='Michael';

SELECT * FROM test
  WHERE last_name='Widenius' OR first_name='Michael';

Share on Facebook

相關新聞 :  http://news.cnet.com/8301-11424_3-10223448-90.html

Share on Facebook

可以用
Using Per-Table Tablespaces : http://dev.mysql.com/doc/refman/5.1/en/multiple-tablespaces.html

Store each InnoDB table and its indexes in its own file. This feature is called “multiple tablespaces” because in effect each table has its own tablespace.

Using multiple tablespaces can be beneficial to users who want to move specific tables to separate physical disks or who wish to restore backups of single tables quickly without interrupting the use of other InnoDB tables.

[mysqld]
innodb_file_per_table

那在把不同的 innodb file 放到不同 disk partition 去, 達到分散實體 IO 的目標, 不過, mysql 對於 path 部份要特別處理, 避免 drop table 或 alter table 時, 它會發生無法預期的結果, 翻了 web , 看到有人這麼作:

改 mysys/mf_format.c

if (!lstat(to,&stat_buff))  /* Check if it's a symbolic link */
    if (S_ISLNK(stat_buff.st_mode) && realpath(to,buff))
成

if (realpath(to,buff))

試看看吧, 我沒改過!

Share on Facebook

可以用 sqlldr 來處理

http://www.orafaq.com/wiki/SQL*Loader_FAQ

Share on Facebook

看到一個有用的套件

for the Sun Solaris Operating System(TM)

http://cooltools.sunsource.net/coolstack/

Cool Stack includes several packages in the SVR4 package format, so you can install just the ones you need. Some of the applications in Cool Stack already ship with Solaris, but these are either older versions and/or not built with full optimization. Further, Cool Stack has been pre-configured to have the most popular applications (Apache, PHP, MySQL) to work seamlessly out of the box.
Deploying PHP From Cool Stack in Sun Java System Web Server

Configuring Cool Stack PHP With Web Server

Next, do the following:

1. Go to the Cool Stack PHP 5 installation location. Type:

cd /opt/coolstack/php5

In that directory is a script called setup-ws7-php.sh.

2. Run setup-ws7-php.sh. Type:

./setup-ws7-php.sh

This message is displayed:

Usage:
This script will configure Coolstack PHP with Sun Java System Web Server
7. Here, you will need to provide the top level location of your Web Server
7 installation and your Web Server 7 instance_name name to which this
script should configure to run PHP scripts.

Enter your Web Server installation location(/sun/webserver7):

3. Type the full path for your Web Server installation.

The script then prompts you to type a Web Server instance name. That name is the path to a directory in your installation location—one that contains all the configuration files for running your Web site. If you are using Web Server in Sun Java Enterprise System 5, your instances are under /var/opt/SUNWwbsvr7.

For the example in this article, cite the instance https-coolstack that you created previously. To enable a different instance for PHP, type that instance name. Your instance is then ready for PHP, which you can deploy with Cool Stack 1.2 PHP on Web Server.

Finally, do the following:

1. Start Web Server. Type:

/sun/webserver7/https-coolstack/bin/startserv

2. Create sample PHP files under /sun/webserver7/https-coolstack/docs.

Share on Facebook

我的實驗機是裝在 vmware  裡的

Continue reading ‘Monster All-In-One LAMP Pack 0726A ( package )’ »

Share on Facebook