# mass Alter even on small tables kills MYSQL

**URL:** <https://forums.percona.com/t/mass-alter-even-on-small-tables-kills-mysql/1262>\
**Category:** Other MySQL® Questions\
**Created:** [September 17, 2009, 12:00pm UTC](https://forums.percona.com/t/mass-alter-even-on-small-tables-kills-mysql/1262 "2009-09-17T12:00:41Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![prouders](https://avatars.discourse-cdn.com/v4/letter/p/ad7895/32.png) [@prouders](https://forums.percona.com/u/prouders)\
**Post date:** [September 17, 2009, 12:00pm UTC](https://forums.percona.com/t/mass-alter-even-on-small-tables-kills-mysql/1262/1 "2009-09-17T12:00:41Z")

</div>

I have dual 8 core Xeon (16 cores total), hardware RAID-1 with 15K rpm SAS, 8G of the memory.

I use only InnoDB engine. Structure of the my mysql server is around 14 databases with 2100 tables at each. Currently, tables are quite small (less than thousand of the records at each), most of the empty. Such structure is necessary for one highload project, it stores splitted mirrored user’s information.

Sometimes during development it’s neccesary to run mass Alter procedure, when we need to add some new fields for example. Alter commands were sent one by one without parralelizm. For example today I ran such procedure for 100 tables at each 14 databases, 99% of them were empty.

Ok, here is my issue, when I run this mass Alter - Mysql becomes slow like the old floppy disk, it looks like when he has even 1 alter command, he puts all resources on it, all other queries are executed with lowest priority.

First of all each Alter tooks 1-2s!!!

BUT CPU load is almost 0, IO stat is OK, no hdd high load.  
Linux 2.6.27.7-9-default (server3) 09/17/09 _x86\_64_

avg-cpu: %user %nice %system %iowait %steal %idle  
8.59 0.00 2.27 0.11 0.00 89.05

Device: tps Blk\_read/s Blk\_wrtn/s Blk\_read Blk\_wrtn  
sda 9.61 49.15 1782.88 506005411 18354603253  
sda1 0.79 11.49 11.26 118243500 115926880  
sda2 1.06 7.82 26.70 80495233 274914754  
sda3 7.76 29.84 1744.92 307240182 17963761619

When I am trying to execute any most simple query using primary key to other tables with max ten records, it executes it 0.5-1.5s!!! even I am trying to run it again and again (looks like it doesn’t use read disk cache absolutely).

Mysql ver. is 5.0.51a

site\_o\_id - is PK, number of the records is less than 15;

mysql\> SELECT site\_o\_name, project\_o\_id FROM db\_2.u144\_sites\_o WHERE site\_o\_id=30;  
±---------------------±-------------+  
| site\_o\_name | project\_o\_id |  
±---------------------±-------------+  
| ??? ?? | 2 |  
±---------------------±-------------+  
1 row in set (0.88 sec)

mysql\> SELECT site\_o\_name, project\_o\_id FROM db\_2.u144\_sites\_o WHERE site\_o\_id=30;  
±---------------------±-------------+  
| site\_o\_name | project\_o\_id |  
±---------------------±-------------+  
| ??? ?? | 2 |  
±---------------------±-------------+  
1 row in set (0.56 sec)

mysql\> explain SELECT site\_o\_name, project\_o\_id FROM db\_2.u144\_sites\_o WHERE site\_o\_id=30;  
±—±------------±-------------±------±--------------± --------±--------±------±-----±------+  
| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |  
±—±------------±-------------±------±--------------± --------±--------±------±-----±------+  
| 1 | SIMPLE | u144\_sites\_o | const | PRIMARY | PRIMARY | 3 | const | 1 | |  
±—±------------±-------------±------±--------------± --------±--------±------±-----±------+  
1 row in set (0.53 sec)

When alter queries are finished, this query tooks 0.00 as usual.

Because each alter query tooks 1-2s or sometimes more, the total alter time for 1400 tables tooks more than 1 hour.

I should notice that same operation(altering of the 1400 tables at 14 databases) at our test office server intel Q6600 (4 core), 8G RAM, software raid between 2 SATA 7200rpm hdds tooks less than 2 mins!!! with same my.cnf!

MySQL 5.0.51a.

OS - OpenSUSE 11.1, Linux server3 2.6.27.7-9-default #1 SMP 2008-12-04 18:10:04 +0100 x86\_64 x86\_64 x86\_64 GNU/Linux, file system is XFS.

office test server: Debian Etch, Linux level7 2.6.24-etchnhalf.1-amd64 #1 SMP Tue Dec 2 17:21:26 UTC 2008 x86\_64 GNU/Linux, file system is XFS

The total size of the database is around 14G.

Here is my my.cnf:

[mysqld]

basedir = /usr/local/mysql/  
datadir = /usr/local/mysql/data  
user = mysql  
port = 3306  
socket = /usr/local/mysql/mysql.sock  
bind-address = 10.77.0.78  
skip-locking

skip-external-locking  
skip-name-resolve

character-set-server = cp1251  
default-character-set = cp1251

character-set-client = cp1251

read\_buffer\_size = 128K  
read\_rnd\_buffer\_size = 32M

sort\_buffer\_size = 32M  
join\_buffer\_size = 4M

key\_buffer = 64M  
tmp\_table\_size = 32M  
max\_heap\_table\_size = 32M

max\_allowed\_packet = 16M  
thread\_stack = 128K  
thread\_cache\_size = 256

max\_connections = 256  
table\_cache = 128000

thread\_concurrency = 0  
open\_files\_limit = 256000  
query\_cache\_limit = 0M  
query\_cache\_size = 0M  
skip-bdb

innodb\_open\_files = 65535

binlog\_cache\_size = 1M  
innodb\_additional\_mem\_pool\_size = 32M  
innodb\_buffer\_pool\_size = 4G  
innodb\_data\_home\_dir = /usr/local/mysql/data

innodb\_data\_file\_path = ibdata1:100M:autoextend

innodb\_log\_group\_home\_dir = /usr/local/mysql/data  
innodb\_log\_arch\_dir = /usr/local/mysql/data  
innodb\_file\_io\_threads = 16

innodb\_thread\_concurrency = 0  
innodb\_concurrency\_tickets = 4096

innodb\_flush\_log\_at\_trx\_commit = 0  
innodb\_log\_buffer\_size = 8M  
innodb\_log\_file\_size = 1024M  
innodb\_log\_files\_in\_group = 2  
innodb\_max\_dirty\_pages\_pct = 70  
innodb\_lock\_wait\_timeout = 120  
innodb\_thread\_sleep\_delay = 0

innodb\_flush\_method=O\_DIRECT  
innodb\_support\_xa=0

innodb\_file\_per\_table  
#memlock

server-id = 100  
log\_slow\_queries = /usr/local/mysql/3-slow.log  
long\_query\_time = 5

master-host=10.77.0.77  
master-port=3308  
master-user=\*\*\*\*  
master-password=\*\*\*\*\*  
read\_only=0

Status values of the problem Xeon server:

mysql\> SHOW STATUS;  
±----------------------------------±-----------+  
| Variable\_name | Value |  
±----------------------------------±-----------+  
| Aborted\_clients | 8 |  
| Aborted\_connects | 3 |  
| Binlog\_cache\_disk\_use | 0 |  
| Binlog\_cache\_use | 0 |  
| Bytes\_received | 111 |  
| Bytes\_sent | 5909 |  
| Com\_admin\_commands | 0 |  
| Com\_alter\_db | 0 |  
| Com\_alter\_table | 0 |  
| Com\_analyze | 0 |  
| Com\_backup\_table | 0 |  
| Com\_begin | 0 |  
| Com\_call\_procedure | 0 |  
| Com\_change\_db | 0 |  
| Com\_change\_master | 0 |  
| Com\_check | 0 |  
| Com\_checksum | 0 |  
| Com\_commit | 0 |  
| Com\_create\_db | 0 |  
| Com\_create\_function | 0 |  
| Com\_create\_index | 0 |  
| Com\_create\_table | 0 |  
| Com\_create\_user | 0 |  
| Com\_dealloc\_sql | 0 |  
| Com\_delete | 0 |  
| Com\_delete\_multi | 0 |  
| Com\_do | 0 |  
| Com\_drop\_db | 0 |  
| Com\_drop\_function | 0 |  
| Com\_drop\_index | 0 |  
| Com\_drop\_table | 0 |  
| Com\_drop\_user | 0 |  
| Com\_execute\_sql | 0 |  
| Com\_flush | 0 |  
| Com\_grant | 0 |  
| Com\_ha\_close | 0 |  
| Com\_ha\_open | 0 |  
| Com\_ha\_read | 0 |  
| Com\_help | 0 |  
| Com\_insert | 0 |  
| Com\_insert\_select | 0 |  
| Com\_kill | 0 |  
| Com\_load | 0 |  
| Com\_load\_master\_data | 0 |  
| Com\_load\_master\_table | 0 |  
| Com\_lock\_tables | 0 |  
| Com\_optimize | 0 |  
| Com\_preload\_keys | 0 |  
| Com\_prepare\_sql | 0 |  
| Com\_purge | 0 |  
| Com\_purge\_before\_date | 0 |  
| Com\_rename\_table | 0 |  
| Com\_repair | 0 |  
| Com\_replace | 0 |  
| Com\_replace\_select | 0 |  
| Com\_reset | 0 |  
| Com\_restore\_table | 0 |  
| Com\_revoke | 0 |  
| Com\_revoke\_all | 0 |  
| Com\_rollback | 0 |  
| Com\_savepoint | 0 |  
| Com\_select | 1 |  
| Com\_set\_option | 0 |  
| Com\_show\_binlog\_events | 0 |  
| Com\_show\_binlogs | 0 |  
| Com\_show\_charsets | 0 |  
| Com\_show\_collations | 0 |  
| Com\_show\_column\_types | 0 |  
| Com\_show\_create\_db | 0 |  
| Com\_show\_create\_table | 0 |  
| Com\_show\_databases | 0 |  
| Com\_show\_errors | 0 |  
| Com\_show\_fields | 0 |  
| Com\_show\_grants | 0 |  
| Com\_show\_innodb\_status | 0 |  
| Com\_show\_keys | 0 |  
| Com\_show\_logs | 0 |  
| Com\_show\_master\_status | 0 |  
| Com\_show\_ndb\_status | 0 |  
| Com\_show\_new\_master | 0 |  
| Com\_show\_open\_tables | 0 |  
| Com\_show\_privileges | 0 |  
| Com\_show\_processlist | 0 |  
| Com\_show\_slave\_hosts | 0 |  
| Com\_show\_slave\_status | 0 |  
| Com\_show\_status | 2 |  
| Com\_show\_storage\_engines | 0 |  
| Com\_show\_tables | 0 |  
| Com\_show\_triggers | 0 |  
| Com\_show\_variables | 0 |  
| Com\_show\_warnings | 0 |  
| Com\_slave\_start | 0 |  
| Com\_slave\_stop | 0 |  
| Com\_stmt\_close | 0 |  
| Com\_stmt\_execute | 0 |  
| Com\_stmt\_fetch | 0 |  
| Com\_stmt\_prepare | 0 |  
| Com\_stmt\_reset | 0 |  
| Com\_stmt\_send\_long\_data | 0 |  
| Com\_truncate | 0 |  
| Com\_unlock\_tables | 0 |  
| Com\_update | 0 |  
| Com\_update\_multi | 0 |  
| Com\_xa\_commit | 0 |  
| Com\_xa\_end | 0 |  
| Com\_xa\_prepare | 0 |  
| Com\_xa\_recover | 0 |  
| Com\_xa\_rollback | 0 |  
| Com\_xa\_start | 0 |  
| Compression | OFF |  
| Connections | 6489 |  
| Created\_tmp\_disk\_tables | 0 |  
| Created\_tmp\_files | 5 |  
| Created\_tmp\_tables | 2 |  
| Delayed\_errors | 0 |  
| Delayed\_insert\_threads | 0 |  
| Delayed\_writes | 0 |  
| Flush\_commands | 1 |  
| Handler\_commit | 0 |  
| Handler\_delete | 0 |  
| Handler\_discover | 0 |  
| Handler\_prepare | 0 |  
| Handler\_read\_first | 0 |  
| Handler\_read\_key | 0 |  
| Handler\_read\_next | 0 |  
| Handler\_read\_prev | 0 |  
| Handler\_read\_rnd | 0 |  
| Handler\_read\_rnd\_next | 227 |  
| Handler\_rollback | 0 |  
| Handler\_savepoint | 0 |  
| Handler\_savepoint\_rollback | 0 |  
| Handler\_update | 0 |  
| Handler\_write | 358 |  
| Innodb\_buffer\_pool\_pages\_data | 94423 |  
| Innodb\_buffer\_pool\_pages\_dirty | 356 |  
| Innodb\_buffer\_pool\_pages\_flushed | 101322 |  
| Innodb\_buffer\_pool\_pages\_free | 166439 |  
| Innodb\_buffer\_pool\_pages\_latched | 0 |  
| Innodb\_buffer\_pool\_pages\_misc | 1282 |  
| Innodb\_buffer\_pool\_pages\_total | 262144 |  
| Innodb\_buffer\_pool\_read\_ahead\_rnd | 66 |  
| Innodb\_buffer\_pool\_read\_ahead\_seq | 96 |  
| Innodb\_buffer\_pool\_read\_requests | 1153408769 |  
| Innodb\_buffer\_pool\_reads | 75886 |  
| Innodb\_buffer\_pool\_wait\_free | 0 |  
| Innodb\_buffer\_pool\_write\_requests | 4854649 |  
| Innodb\_data\_fsyncs | 71498 |  
| Innodb\_data\_pending\_fsyncs | 0 |  
| Innodb\_data\_pending\_reads | 0 |  
| Innodb\_data\_pending\_writes | 0 |  
| Innodb\_data\_read | 1363513344 |  
| Innodb\_data\_reads | 84884 |  
| Innodb\_data\_writes | 120353 |  
| Innodb\_data\_written | 3696776704 |  
| Innodb\_dblwr\_pages\_written | 101322 |  
| Innodb\_dblwr\_writes | 3137 |  
| Innodb\_log\_waits | 3 |  
| Innodb\_log\_write\_requests | 741365 |  
| Innodb\_log\_writes | 31370 |  
| Innodb\_os\_log\_fsyncs | 33065 |  
| Innodb\_os\_log\_pending\_fsyncs | 0 |  
| Innodb\_os\_log\_pending\_writes | 0 |  
| Innodb\_os\_log\_written | 375879680 |  
| Innodb\_page\_size | 16384 |  
| Innodb\_pages\_created | 29761 |  
| Innodb\_pages\_read | 83089 |  
| Innodb\_pages\_written | 101322 |  
| Innodb\_row\_lock\_current\_waits | 0 |  
| Innodb\_row\_lock\_time | 718664 |  
| Innodb\_row\_lock\_time\_avg | 209 |  
| Innodb\_row\_lock\_time\_max | 1616 |  
| Innodb\_row\_lock\_waits | 3434 |  
| Innodb\_rows\_deleted | 9532 |  
| Innodb\_rows\_inserted | 45354 |  
| Innodb\_rows\_read | 1248787734 |  
| Innodb\_rows\_updated | 1035839 |  
| Key\_blocks\_not\_flushed | 0 |  
| Key\_blocks\_unused | 214339 |  
| Key\_blocks\_used | 9 |  
| Key\_read\_requests | 3862 |  
| Key\_reads | 9 |  
| Key\_write\_requests | 0 |  
| Key\_writes | 0 |  
| Last\_query\_cost | 0.000000 |  
| Max\_used\_connections | 38 |  
| Not\_flushed\_delayed\_rows | 0 |  
| Open\_files | 19 |  
| Open\_streams | 0 |  
| Open\_tables | 10865 |  
| Opened\_tables | 0 |  
| Prepared\_stmt\_count | 0 |  
| Qcache\_free\_blocks | 0 |  
| Qcache\_free\_memory | 0 |  
| Qcache\_hits | 0 |  
| Qcache\_inserts | 0 |  
| Qcache\_lowmem\_prunes | 0 |  
| Qcache\_not\_cached | 0 |  
| Qcache\_queries\_in\_cache | 0 |  
| Qcache\_total\_blocks | 0 |  
| Questions | 2058021 |  
| Rpl\_status | NULL |  
| Select\_full\_join | 0 |  
| Select\_full\_range\_join | 0 |  
| Select\_range | 0 |  
| Select\_range\_check | 0 |  
| Select\_scan | 2 |  
| Slave\_open\_temp\_tables | 0 |  
| Slave\_retried\_transactions | 0 |  
| Slave\_running | ON |  
| Slow\_launch\_threads | 0 |  
| Slow\_queries | 0 |  
| Sort\_merge\_passes | 0 |  
| Sort\_range | 0 |  
| Sort\_rows | 0 |  
| Sort\_scan | 0 |  
| Table\_locks\_immediate | 2335122 |  
| Table\_locks\_waited | 0 |  
| Tc\_log\_max\_pages\_used | 0 |  
| Tc\_log\_page\_size | 0 |  
| Tc\_log\_page\_waits | 0 |  
| Threads\_cached | 6 |  
| Threads\_connected | 32 |  
| Threads\_created | 38 |  
| Threads\_running | 1 |  
| Uptime | 24816 |  
| Uptime\_since\_flush\_status | 24816 |  
±----------------------------------±-----------+  
226 rows in set (0.01 sec)

Please, advice … I googled everywhere and didn’t find anyting.

At furture, we will need sometimes to alter some tables. At now when we do altering server is almost die. It’s unacceptable for us to so slow DB at live system.

---

<div class="post-metadata">

**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\
**Post date:** [September 17, 2009, 2:21pm UTC](https://forums.percona.com/t/mass-alter-even-on-small-tables-kills-mysql/1262/2 "2009-09-17T14:21:57Z")

</div>

You could try [http://dev.mysql.com/doc/refman/5.1/en/multiple-tablespaces](http://dev.mysql.com/doc/refman/5.1/en/multiple-tablespaces). html

---

<div class="post-metadata">

**Author:** ![prouders](https://avatars.discourse-cdn.com/v4/letter/p/ad7895/32.png) [@prouders](https://forums.percona.com/u/prouders)\
**Post date:** [September 17, 2009, 2:51pm UTC](https://forums.percona.com/t/mass-alter-even-on-small-tables-kills-mysql/1262/3 "2009-09-17T14:51:02Z")

</div>

if you will take a look at my my.cnf, that I already use it ) because I know that by using this option I could be more flexible with altering, but in fact I am not (

---

<div class="post-metadata">

**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\
**Post date:** [September 17, 2009, 2:59pm UTC](https://forums.percona.com/t/mass-alter-even-on-small-tables-kills-mysql/1262/4 "2009-09-17T14:59:55Z")

</div>

Oh I’m sorry. Do you also use that option on your other server? Is your disk almost full?

If the server has nothing else to do, and an alter table of a table just a few MB in size takes more than a second, something’s got to be wrong.

---

<div class="post-metadata">

**Author:** ![prouders](https://avatars.discourse-cdn.com/v4/letter/p/ad7895/32.png) [@prouders](https://forums.percona.com/u/prouders)\
**Post date:** [September 17, 2009, 3:14pm UTC](https://forums.percona.com/t/mass-alter-even-on-small-tables-kills-mysql/1262/5 "2009-09-17T15:14:06Z")

</div>

disk is almost free.  
test office server is same situation and configuration is same too (per file for innodb).

I broken my head what’s the reason for death of the mysql at most modern Xeon only during altering…