Sunday, July 21, 2013

MySQL :: MySQL 5.1 Reference Manual :: 13.1.7.1 ALTER TABLE Partition Operations

MySQL :: MySQL 5.1 Reference Manual :: 13.1.7.1 ALTER TABLE Partition Operations:

Simply using a partition_options clause with ALTER TABLE on a partitioned table repartitions the table according to the partitioning scheme defined by the partition_options. This clause always begins withPARTITION BY, and follows the same syntax and other rules as apply to the partition_options clause forCREATE TABLE (see Section 13.1.17, “CREATE TABLE Syntax”, for more detailed information), and can also be used to partition an existing table that is not already partitioned. For example, consider a (nonpartitioned) table defined as shown here:
```
CREATE TABLE t1 (
    id INT,
    year_col INT
);
```
This table can be partitioned by HASH, using the id column as the partitioning key, into 8 partitions by means of this statement:
```
ALTER TABLE t1
    PARTITION BY HASH(id)
    PARTITIONS 8;
```
The table that results from using an ALTER TABLE ... PARTITION BY statement must follow the same rules as one created using CREATE TABLE ... PARTITION BY. This includes the rules governing the relationship between any unique keys (including any primary key) that the table might have, and the column or columns used in the partitioning expression, as discussed in Section 18.5.1, “Partitioning Keys, Primary Keys, and Unique Keys”. The CREATE TABLE ... PARTITION BY rules for specifying the number of partitions also apply to ALTER TABLE ... PARTITION BY.

ALTER TABLE ... PARTITION BY became available in MySQL 5.1.6.

The partition_definition clause for ALTER TABLE ADD PARTITION supports the same options as the clause of the same name for the CREATE TABLE statement. (See Section 13.1.17, “CREATE TABLE Syntax”, for the syntax and description.) Suppose that you have the partitioned table created as shown here:
```
CREATE TABLE t1 (
    id INT,
    year_col INT
)
PARTITION BY RANGE (year_col) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1995),
    PARTITION p2 VALUES LESS THAN (1999)
);
```
You can add a new partition p3 to this table for storing values less than 2002 as follows:
```
ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN (2002));
```
DROP PARTITION can be used to drop one or more RANGE or LIST partitions. This statement cannot be used withHASH or KEY partitions; instead, use COALESCE PARTITION (see below). Any data that was stored in the dropped partitions named in the partition_names list is discarded. For example, given the table t1 defined previously, you can drop the partitions named p0 and p1 as shown here:
```
ALTER TABLE t1 DROP PARTITION p0, p1;
```
Note

DROP PARTITION does not work with tables that use the NDBCLUSTER storage engine. See Section 18.3.1, “Management of RANGE and LIST Partitions”, and Section 17.1.6, “Known Limitations of MySQL Cluster”.

ADD PARTITION and DROP PARTITION do not currently support IF [NOT] EXISTS.

Renames of partitioned table are supported. You can rename individual partitions indirectly using ALTER TABLE ... REORGANIZE PARTITION; however, this operation makes a copy of the partition's data..

COALESCE PARTITION can be used with a table that is partitioned by HASH or KEY to reduce the number of partitions by number. Suppose that you have created table t2 using the following definition:
```
CREATE TABLE t2 (
    name VARCHAR (30),
    started DATE
)
PARTITION BY HASH( YEAR(started) )
PARTITIONS 6;
```
You can reduce the number of partitions used by t2 from 6 to 4 using the following statement:
```
ALTER TABLE t2 COALESCE PARTITION 2;
```
The data contained in the last number partitions will be merged into the remaining partitions. In this case, partitions 4 and 5 will be merged into the first 4 partitions (the partitions numbered 0, 1, 2, and 3).

To change some but not all the partitions used by a partitioned table, you can use REORGANIZE PARTITION. This statement can be used in several ways:
- To merge a set of partitions into a single partition. This can be done by naming several partitions in thepartition_names list and supplying a single definition for partition_definition.
- To split an existing partition into several partitions. You can accomplish this by naming a single partition forpartition_names and providing multiple partition_definitions.
- To change the ranges for a subset of partitions defined using VALUES LESS THAN or the value lists for a subset of partitions defined using VALUES IN.
- This statement may also be used without the partition_names INTO (partition_definitions) option on tables that are automatically partitioned using HASH partitioning to force redistribution of data. (Currently, onlyNDBCLUSTER tables are automatically partitioned in this way.) This is useful in MySQL Cluster NDB 7.0 and later where, after you have added new MySQL Cluster data nodes online to an existing MySQL Cluster, you wish to redistribute existing MySQL Cluster table data to the new data nodes. In such cases, you should invoke the statement with the ONLINE option; in other words, as shown here:
```
ALTER ONLINE TABLE table REORGANIZE PARTITION;
```
  You cannot perform other DDL concurrently with online table reorganization—that is, no other DDL statements can be issued while an ALTER ONLINE TABLE ... REORGANIZE PARTITION statement is executing. For more information about adding MySQL Cluster data nodes online, see Section 17.5.13, “Adding MySQL Cluster Data Nodes Online”.
  
  ALTER ONLINE TABLE ... REORGANIZE PARTITION does not work with tables which were created using theMAX_ROWS option, because it uses the constant MAX_ROWS value specified in the original CREATE TABLE statement to determine the number of partitions required, so no new partitions are created. Beginning with MySQL Cluster NDB 7.0.32 and MySQL Cluster NDB 7.1.21, you can use ALTER ONLINE TABLE ... MAX_ROWS=rows to increase the maxmimum number of rows for the table; after this, ALTER ONLINE TABLE ... REORGANIZE PARTITION can use this new, larger value to increase the number of partitions. The value of rows must be greater than the value specified for MAX_ROWS in the original CREATE TABLE statement for this to work.
  
  Attempting to use REORGANIZE PARTITION without the partition_names INTO (partition_definitions)option on explicitly partitioned tables results in the error REORGANIZE PARTITION without parameters can only be used on auto-partitioned tables using HASH partitioning.
Note

For partitions that have not been explicitly named, MySQL automatically provides the default names p0, p1, p2, and so on. As of MySQL 5.1.7, the same is true with regard to subpartitions.

For more detailed information about and examples of ALTER TABLE ... REORGANIZE PARTITION statements, see Section 18.3.1, “Management of RANGE and LIST Partitions”.

MySQL :: Re: How to create partition on existing table?

MySQL :: Re: How to create partition on existing table?:

you can use ALTER TABLE to add partitioning to the table,
keep in mind though that this will actually create the
new partitioned table first, then copy over all the
existing data, and finally drop the old unpartitioned
table. So this operation may take a while and will
temporarily use twice the disk space (which in the case
of InnoDB is not given back to the operating system ...)

MySQL :: Partitioning with Dates in MySQL 5.1

MySQL :: Partitioning with Dates in MySQL 5.1:

Partitioning with Dates in MySQL 5.1

Many of you have responded very positively on the first and second partitioning articles that I wrote some time back. It’s clear that a lot of you are excited about the upcoming MySQL 5.1 release, and in particular, the new data partitioning feature that’s being offered. That’s no surprise because, as we covered in the previous two articles, partitioning has some excellent performance and management benefits for anyone who’s building data warehouses or other large databases with MySQL.

However, I’ve gotten some feedback indicating a disappointment that MySQL 5.1 doesn’t support partitioning on a native date/time column. Given that many data warehouses and other large databases try and segment data by some date-based algorithm – especially via the RANGE partitioning style – it’s understandable that a number of you are wondering if partitioning in 5.1 will meet the needs of your particular business intelligence application that relies on time-based functions.

It’s true that in MySQL 5.1 the actual partitioning key (the element used by the MySQL partition engine to horizontally divide the rows across the various partitions defined on a table or index) must be an integer value. But that doesn’t mean you can’t partition by date in MySQL 5.1 – it just requires the proper selection between a couple of MySQL date functions and you’ll be in business.

Let’s take a look at how to properly partition by dates in MySQL 5.1 and also showcase how not to try and partition by date, so you won’t waste time and effort on something that doesn’t give you the results that you’re looking for.

How Not to Partition by Date

I always appreciate it when someone shows me where not to step, so let’s first see what you don’t want to try when it comes to partitioning by date in MySQL 5.1. If you try and define a table with a partition key that references a date datatype, MySQL will quickly kick back an error:

mysql>  create table rms (d date)
    ->  partition by range (d)
    -> (partition p0 values less than ('1995-01-01'),
    ->  partition p1 VALUES LESS THAN ('2010-01-01'));
ERROR 1064 (42000): VALUES value must be of same type as partition function near '),
 partition p1 VALUES LESS THAN ('2010-01-01'))' at line 3

Because MySQL DBAs and developers are very clever and innovative, the first thing they'll do is try a workaround for the integer-only restriction in MySQL 5.1 regarding the partition key. For example, here's one way to get around the restriction:

mysql> CREATE TABLE part_date1
-> ( c1 int default NULL,
-> c2 varchar(30) default NULL,
-> c3 date default NULL) engine=myisam
-> partition by range (cast(date_format(c3,'%Y%m%d') as signed))
-> (PARTITION p0 VALUES LESS THAN (19950101),
-> PARTITION p1 VALUES LESS THAN (19960101) ,
-> PARTITION p2 VALUES LESS THAN (19970101) ,
-> PARTITION p3 VALUES LESS THAN (19980101) ,
-> PARTITION p4 VALUES LESS THAN (19990101) ,
-> PARTITION p5 VALUES LESS THAN (20000101) ,
-> PARTITION p6 VALUES LESS THAN (20010101) ,
-> PARTITION p7 VALUES LESS THAN (20020101) ,
-> PARTITION p8 VALUES LESS THAN (20030101) ,
-> PARTITION p9 VALUES LESS THAN (20040101) ,
-> PARTITION p10 VALUES LESS THAN (20100101),
-> PARTITION p11 VALUES LESS THAN MAXVALUE );
Query OK, 0 rows affected (0.01 sec)

Bingo! MySQL took it. Here’s another possible workaround:

mysql> CREATE TABLE part_date2
    ->      (  c1 int default NULL,
    ->  c2 varchar(30) default NULL,
    ->  c3 date default NULL) engine=myisam
    ->      partition by range (YEAR(C3)*100+MONTH(C3))
    -> (PARTITION p0 VALUES LESS THAN (199501),
    -> PARTITION p1 VALUES LESS THAN (199601) ,
    -> PARTITION p2 VALUES LESS THAN (199701) ,
    -> PARTITION p3 VALUES LESS THAN (199801) ,
    -> PARTITION p4 VALUES LESS THAN (199901) ,
    -> PARTITION p5 VALUES LESS THAN (200001) ,
    -> PARTITION p6 VALUES LESS THAN (200101) ,
    -> PARTITION p7 VALUES LESS THAN (200201) ,
    -> PARTITION p8 VALUES LESS THAN (200301) ,
    -> PARTITION p9 VALUES LESS THAN (200401) ,
    -> PARTITION p10 VALUES LESS THAN (201001),
    -> PARTITION p11 VALUES LESS THAN MAXVALUE );
Query OK, 0 rows affected (0.02 sec)

MySQL took that too. At this point, you might be feeling pretty crafty as you’ve outwitted MySQL into partitioning by a date column. But watch what happens when we fill the tables with millions of rows of data and then see how each reacts with a standard range-based query:

mysql> explain partitions
    -> select count(*) from part_date1 where
    ->      c3 > date '1995-01-01' and c3 < date '1995-12-31'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: part_date1
   partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 8100000
        Extra: Using where
1 row in set (0.00 sec)

mysql> explain partitions
    -> select count(*) from part_date2 where
    ->      c3 > date '1995-01-01' and c3 < date '1995-12-31'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: part_date1
   partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 8100000
        Extra: Using where
1 row in set (0.00 sec)

Yikes! What happened? MySQL is doing a full table scan rather than just scanning the partition(s) that contain the data we need. The issue is that you’ve forgotten the second part of the equation when it comes to leveraging date-based partitioning (or any partitioning style really), which is the MySQL optimizer. Yes, you can divide your data using any number of date-to-integer conversion techniques, but that doesn’t mean you’ll get the big bang when it comes to using partitioning, which is partition pruning – the act where MySQL only references the partitions needed to fulfill a particular query. The optimizer’s partition pruning mechanism is the actual hero that accomplishes the performance speed-up’s in partitioned vs. non-partitioned tables, and without it, you might as well go back to using non-partitioned tables if it’s an increase in performance you’re looking for.

But take heart – there is a way to partition by date and get partition pruning.

How to Partition by Date

The MySQL optimizer will recognize two date-based functions for partition pruning purposes:

1.TO_DAYS()
2.YEAR()

If you partition date columns using either of these functions, you can both define a partitioned table by date and get the benefit of partition pruning from the optimizer (provided, of course, that your query is written in such a way so as to take advantage of the optimizer’s ability to prune partitions). For example, we can redefine the tables used in our previous example using both the TO_DAYS() and YEAR() date-based functions:

mysql> CREATE TABLE part_date3
    ->      (  c1 int default NULL,
    ->  c2 varchar(30) default NULL,
    ->  c3 date default NULL) engine=myisam
    ->      partition by range (to_days(c3))
    -> (PARTITION p0 VALUES LESS THAN (to_days('1995-01-01')),
    -> PARTITION p1 VALUES LESS THAN (to_days('1996-01-01')) ,
    -> PARTITION p2 VALUES LESS THAN (to_days('1997-01-01')) ,
    -> PARTITION p3 VALUES LESS THAN (to_days('1998-01-01')) ,
    -> PARTITION p4 VALUES LESS THAN (to_days('1999-01-01')) ,
    -> PARTITION p5 VALUES LESS THAN (to_days('2000-01-01')) ,
    -> PARTITION p6 VALUES LESS THAN (to_days('2001-01-01')) ,
    -> PARTITION p7 VALUES LESS THAN (to_days('2002-01-01')) ,
    -> PARTITION p8 VALUES LESS THAN (to_days('2003-01-01')) ,
    -> PARTITION p9 VALUES LESS THAN (to_days('2004-01-01')) ,
    -> PARTITION p10 VALUES LESS THAN (to_days('2010-01-01')),
    -> PARTITION p11 VALUES LESS THAN MAXVALUE );
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE part_date4
    ->      (  c1 int default NULL,
    ->  c2 varchar(30) default NULL,
    ->  c3 date default NULL) engine=myisam
    ->      PARTITION BY RANGE (year(c3))
    -> (PARTITION p0 VALUES LESS THAN (1995),
    -> PARTITION p1 VALUES LESS THAN (1996) ,
    -> PARTITION p2 VALUES LESS THAN (1997) ,
    -> PARTITION p3 VALUES LESS THAN (1998) ,
    -> PARTITION p4 VALUES LESS THAN (1999) ,
    -> PARTITION p5 VALUES LESS THAN (2000) ,
    -> PARTITION p6 VALUES LESS THAN (2001) ,
    -> PARTITION p7 VALUES LESS THAN (2002) ,
    -> PARTITION p8 VALUES LESS THAN (2003) ,
    -> PARTITION p9 VALUES LESS THAN (2004) ,
    -> PARTITION p10 VALUES LESS THAN (2010),
    -> PARTITION p11 VALUES LESS THAN MAXVALUE );
Query OK, 0 rows affected (0.02 sec)

If we fill both tables with the same amount of data as the tables we created with non-prunable partitioning keys and issue range-based queries against the newly loaded well-partitioned tables, we’ll find a big difference when it comes to what the MySQL optimizer does:

mysql> explain partitions
    -> select count(*) from part_date3 where
    ->      c3 > date '1995-01-01' and c3 < date '1995-12-31'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: part_date3
   partitions: p1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 808431
        Extra: Using where
1 row in set (0.00 sec)

mysql> explain partitions
    -> select count(*) from part_date4 where
    ->      c3 > date '1995-01-01' and c3 < date '1995-12-31'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: part_date4
   partitions: p1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 200000
        Extra: Using where
1 row in set (0.00 sec)

Notice only the p1 partition is being scanned in these two tables, whereas all partitions were being checked in the tables that had partition keys the MySQL optimizer couldn’t use for partition pruning. A quick test run shows partition pruning in action along with the resulting performance difference:

mysql> select count(*) from part_date3 where
    ->      c3 > date '1995-01-01' and c3 < date '1995-12-31';
+----------+
| count(*) |
+----------+
|   805114 |
+----------+
1 row in set (4.11 sec)

mysql> select count(*) from part_date1 where
    ->      c3 > date '1995-01-01' and c3 < date '1995-12-31';
+----------+
| count(*) |
+----------+
|   805114 |
+----------+
1 row in set (40.33 sec)

A 90% decrease in response time for the query referencing table part_date3 over the query requesting data from table part_date1 spells out pretty clearly why it’s important to use date functions that the MySQL optimizer can utilize when defining date-based partitioned tables.

Conclusion

While a restriction does exist in MySQL 5.1 that says partitioned tables must be partitioned via an integer-based value, it doesn’t mean you can’t partition tables by date or get the benefits of partition pruning. You must, however, ensure that you’re using date conversion functions in the defined partitioning key for the table that the MySQL optimizer can make use of.

For more information on partitioning in general, please check out our online reference manual at http://dev.mysql.com/doc/refman/5.1/en/partitioning.html. Also, don’t forget about our MySQL forums, which have a special section devoted to partitioning at http://forums.mysql.com/list.php?106.

Of course I’d be remiss if I didn’t encourage you to download the latest copy of MySQL 5.1 (which is now in beta) today and give partitioning a try. I’d certainly love to hear how you’re using partitioning and other new features in MySQL 5.1.

Thanks, as always, for supporting MySQL!

Monday, July 15, 2013

json - json_encode/json_decode - returns stdClass instead of Array in PHP - Stack Overflow

json - json_encode/json_decode - returns stdClass instead of Array in PHP - Stack Overflow:

Take a closer look at the second parameter of json_decode($json, $assoc, $depth) athttp://docs.php.net/json_decode

$arrayDecoded = json_decode($arrayEncoded, true);

gives you an array.

configuration - Is there an application to manage config files in the /etc folder with version control software? - Ask Ubuntu

configuration - Is there an application to manage config files in the /etc folder with version control software? - Ask Ubuntu:

The package etckeeper is specifically intended to manage your configuration files (located in the/etc directory) under a version control system(VCS).

What is etckeeper?:

etckeeper is a collection of tools to let /etc be stored in a git, mercurial, darcs, or bzr repository. It hooks into apt (and other package managers) to automatically commit changes made to /etc during package upgrades.

How to do I install etckeeper?:

To install the package, click on the link provided below to install in Ubuntu Software Center(USC), or runsudo apt-get install etckeeper from the terminal(Ctrl-Alt-t).

etckeeper

How do I change etckeeper from the default VCS(bzr) to use git?:

By default, etckeeper uses Bazaar(bzr) for version control management. This can be changed in the configuration file /etc/etckeeper/etckeeper.conf.

First, you will need to have git installed.

sudo apt-get install git
sudo apt-get install git-core

Now, you will need to edit your etckeeper.conf file to change your VCS. Here is what the first five lines of the default configuration should look like:

# The VCS to use.
# VCS="hg"
# VCS="git"
VCS="bzr"
# VCS="darcs"

This is where to switch to using git. Open a text editor (i.e. gksu gedit /etc/etc/keeper/etckeeper.conf) and change the file to look like this:

# The VCS to use.
# VCS="hg"
VCS="git"
# VCS="bzr"
# VCS="darcs"

Uncomment the line VCS="git" and comment out the line VCS="bzr"

Now, run sudo etckeeper init to initialize your repository.

As mentioned here:

This sets up version control for all files in /etc. You don't need to do anything else to manage etckeeper; you only need to interact with it when you want to do a version control operation, such as referring to older files. Files are automatically committed before and after each run of apt and every night (this is configurable).

Additional information on etckeeper:

See Ubuntu etckeeper documentation

also

Using etckeeper with git on Ubuntu

MySQL, Linux, and Thread Caching (by Jeremy Zawodny)

MySQL, Linux, and Thread Caching (by Jeremy Zawodny): "SET GLOBAL wait_timeout=60;"

Wow, it's been a busy week. I was totally swamped for several days dealing with the remember.yahoo.com MySQL servers and related stuff. And then I used a day or two to recover (sleep, shower, etc).

Anyway, I made some interesting discoveries along the way. The most surprising one had to do with thread caching on Linux when you have a busy MySQL server--busy in a particular way, mind you.

You see, we had a single master server which all the web servers could connect to (using PHP) whenever someone made a change. That includes creating a tile (there were several hundred thousand tiles created), approves a tile, marks one as "cool", and so on. All told, the master was quite busy.

Because there were between 20 and 45 front-end web servers during that time, and each could have had up to 70 apache processes that might have needed to connect, we faced a problem. That meant that the master needed to handle up to 3,150 connections in the worst case (that's 45 x 70). Most of the PHP code used mysql_pconnect() to hold persistent connections.

Rather than worry about how to do that, I made sure that the wait_timeout was set to a very low value: 15 seconds. That means MySQL would close any connection that was idle for more than 15 seconds. But I didn't realize the extent of the problem until I started getting reports from the web servers that the master was refusing connections. Why? Because I had set the maximum number of connections to a reasonable value in the master's my.cnf file:

set-variable = max_connections=180
set-variable = max_user_connections=140

And at that time, the wait_timeout was set to 600 seconds (10 minutes). Clearly that was a problem. There were a lot of idle clients holding connections and blocking out new clients from connecting and getting real work done.

What to do?

We could have stopped using mysql_pconnect(), but as you'll see, that wouldn't have solved the underlying problem.

I needed to adjust the settings. But I wasn't sure what values to use. And I really didn't want to keep stopping and starting the master. That would just suck. Then I remembered that we were running MySQL 4.0.4. I'd has a new feature that allows you to change most of the server settings on on the fly without restarting! Read about here, it in the on-line manual.

Excellent!

All I needed to do was execute a few variations on this command:

SET GLOBAL wait_timeout=60;

(with different values in the place of "60") to try and strike a balance between letting new clients in and kicking out already connected users too quickly.

Ultimately, I settled on a timeout of 15 seconds.

But that had an interesting and unanticipated side-effect. It meant that the Linux server was having to create new threads (MySQL is a multi-threaded server) at a very high rate. That sucks up a measurable amount of CPU time.

How much CPU time? By the time I got around to looking at the output of SHOW STATUS and seeing this:

| Threads_cached           | 0          |
| Threads_created          | 270194     |
| Threads_connected        | 46         |
| Threads_running          | 28         |

Things were pretty bad. The machine had very little idle CPU time--probably 5-10% at the most. But it really wasn't doing that much work--maybe 40 queries per second. I was a bit puzzled. But that Threads_created number jumped out at me. It was high and increasing rapidly.

Luckily I remembered the thread_cache setting. So I decided to investigate (using the new syntax for examining server variables):

mysql> SELECT @@global.thread_cache_size;
+---------------------+
| @@thread_cache_size |
+---------------------+
|                   0 |
+---------------------+
1 row in set (0.00 sec)

Uh oh. I never set the thread cache in my.cnf, so it has assumed the default. That's bad. It's like removing the pre-forking capabilities of Apache 1.3 and letting it get pounded on a busy web site. The "fork a new process for each new request" gets pretty expensive pretty quickly.

Ugh!

Luckily the thread cache is also tunable on the fly now. So all I had to do was this:

SET GLOBAL thread_cache_size=40;

I took a guess and figured that by caching 40 threads, we'd be saving a lot of work. And boy was I right!

In the other window, where I was running vmstat 1 I noticed a dramatic change. The idle CPU on the machine immediately went from 5-10% to 35-40%

If only I had thought of that sooner!

So the moral of the story is this: If you have a busy server that's getting a lot of quick connections, set your thread cache high enough that the Threads_created value in SHOW STATUS stops increasing. Your CPU will thank you.

I don't feel bad though. We were all going nuts to try and tune/optimize the code and servers while it was running and had very little sleep. Thread caching really wasn't the worst of our problems. But it became the worst after we had fixed all the bigger ones.

It was quite a learning experience.

tuning - MySQL lowering wait_timeout value to lower number of open connections - Server Fault

tuning - MySQL lowering wait_timeout value to lower number of open connections - Server Fault:

Lowering the value is pretty trivial without a mysql restart

Let's say you want to lower timeouts to 30 seconds

First, add this to my.cnf

[mysqld]
interactive_timeout=30
wait_timeout=30

Then, you can do something like this

mysql -uroot -ppassword -e"SET GLOBAL wait_timeout=30; SET GLOBAL interactive_timeout=30"

All DB Connections after this will timeout in 30 seconds

WARNING

Make sure to use explicitly use mysql_close. I do not trust Apache as most developers do. If not, sometimes, there is a race condition where Apache closes a DB Connection but does not inform mysqld and mysqld hold that connection open until it times out. Even worse, you may see TIME_WAITs more often. Choose your timeout values wisely.

UPDATE 2012-11-12 10:10 EDT

CAVEAT

After applying my posted suggestions, create a script called /root/show_mysql_netstat.sh with the following lines:

netstat | grep mysql > /root/mysql_netstat.txt
cat /root/mysql_netstat.txt | awk '{print $5}' | sed 's/:/ /g' | awk '{print $2}' | sort -u > /root/mysql_netstat_iplist.txt
for IP in `cat /root/mysql_netstat_iplist.txt`
do
        ESCOUNT=`cat /root/mysql_netstat.txt | grep ESTABLISHED | awk '{print $5}' | grep -c "${IP}"`
        TWCOUNT=`cat /root/mysql_netstat.txt | grep TIME_WAIT   | awk '{print $5}' | grep -c "${IP}"`
        IPPAD=`echo "${IP}..................................." | cut -b -35`
        (( ESCOUNT += 1000000 ))
        (( TWCOUNT += 1000000 ))
        ES=`echo ${ESCOUNT} | cut -b 3-`
        TW=`echo ${TWCOUNT} | cut -b 3-`
        echo ${IPPAD} : ESTABLISHED:${ES} TIME_WAIT:${TW}
done
echo ; echo
netstat -nat | awk '{print $6}' | sort | uniq -c | sort -n | sed 's/d)/d/'

When you run this, you should see something like this:

[root@*** ~]# /root/ShowConnProfiles.sh
10.48.22.4......................... : ESTABLISHED:00002 TIME_WAIT:00008
10.48.22.8......................... : ESTABLISHED:00000 TIME_WAIT:00002
10.64.51.130....................... : ESTABLISHED:00001 TIME_WAIT:00000
10.64.51.133....................... : ESTABLISHED:00000 TIME_WAIT:00079
10.64.51.134....................... : ESTABLISHED:00002 TIME_WAIT:00001
10.64.51.17........................ : ESTABLISHED:00003 TIME_WAIT:01160
10.64.51.171....................... : ESTABLISHED:00002 TIME_WAIT:00000
10.64.51.174....................... : ESTABLISHED:00000 TIME_WAIT:00589
10.64.51.176....................... : ESTABLISHED:00001 TIME_WAIT:00570


      1 established
      1 Foreign
     11 LISTEN
     25 ESTABLISHED
   1301 TIME_WAIT

If you still see a lot of mysql TIME_WAITs for any given web server, here are two escalation steps to take:

ESCALATION #1

service httpd stop
sleep 30
service httpd start

If necessary, do this to all the web servers

service httpd stop (on all web servers)
service mysql stop
sleep 120
service mysql start
service httpd start (on all web servers)

ESCALATION #2

You can force the OS to kill TIME_WAITs for mysql or any other app with the following:

SEC_TO_TIMEWAIT=1
echo ${SEC_TO_TIMEWAIT} > /proc/sys/net/ipv4/tcp_tw_recycle
echo ${SEC_TO_TIMEWAIT} > /proc/sys/net/ipv4/tcp_tw_reuse

This will make TIME_WAITs time out in 1 second.

Mustali Kachwala's Blog

Search This Blog