Posts Tagged ‘MySQL’

InnoDB engine disabled when specifying a buffer pool size too high

Friday, March 12th, 2010

The InnoDB engine will (silently) not start if innodb_buffer_pool_size too big.

You modified your my.cnf to increase the performance of your DB by tweaking the InnoDB variables.
Then you restarted MySQL for the changes to take effect, everything seems fine, no error message.

Except your InnoDB tables are no longer working.

mysql> SELECT VERSION();
+-----------------------+
| VERSION()             |
+-----------------------+
| 5.1.37-1ubuntu5.1-log |
+-----------------------+
1 row in set (0.00 sec)

mysql>
mysql> SHOW CREATE TABLE users;
ERROR 1286 (42000): Unknown table engine 'InnoDB'
mysql>

You can’t believe your eyes and start double checking for confirmation:

mysql> SHOW VARIABLES LIKE 'have_innodb';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_innodb   | NO    |
+---------------+-------+
1 row in set (0.00 sec)
mysql>
mysql> SHOW ENGINES \G
*************************** 1. row ***************************
      Engine: MyISAM
     Support: DEFAULT
     Comment: Default engine as of MySQL 3.23 with great performance
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
7 rows in set (0.00 sec)

mysql>

Yep, InnoDB is definitely gone.

If you try to create an InnoDB table unfortunately the command will not fail:
MySQL will create a MyISAM table behind your back without telling you it couldn’t create an InnoDB table. Ouch.

Examining your /etc/mysql/my.cnf and you see you typed some extra zeros:

innodb_buffer_pool_size=10000M

Probably you were thinking assigning 1G to the buffer pool when in fact it was 10GB, and your server only has 2GB of memory.
Change that to:

innodb_buffer_pool_size=1000M

Restart MySQL and you get your InnoDB tables working again.

mysql> SHOW VARIABLES LIKE 'have_innodb';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_innodb   | YES   |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

It’s amazing but MySQL will actually not complain about a value too high when you restart it.
It looked like this to me when the value was too high:

bash# /etc/init.d/mysql restart
 * Stopping MySQL database server mysqld    [ OK ]
 * Starting MySQL database server mysqld    [ OK ]
 * Checking for corrupt, not cleanly closed and upgrade needing tables.
bash#

On an Amazon EC2 Standard Default Small Instance running Ubuntu server, I was surprised to find the limit is very low.
It’s a 32-bit platform with 1.7 GB of memory, 1 EC2 Compute Unit (1 virtual core with 1 EC2 Compute Unit), and 160 GB of local instance storage.

InnoDB could not start when innodb_buffer_pool_size was set to 512M.
However it worked with 256M.

Setting up MySQL replication

Wednesday, March 10th, 2010

In this article the Master is 200.126.187.013 and the Slave is 200.126.187.066

On the Master, edit /etc/mysql/my.cnf:

server-id        = 126187013  # choose a unique integer
log_bin          = /var/log/mysql/mysql-bin.log
bind-address   = 0.0.0.0  # listen on all interfaces (by default 127.0.0.1 is used)
binlog_do_db   = mydb    # replicate only the 'mydb' database

Test your new configuration for syntax error:

bash# mysqld --help

Make sure the skip-networking option is not enabled.
Make sure server-id is unique. You can for instance use the last 3 octets of the IP address of the server.

Create a replication user on the Master using the IP address of the Slave that will access it (200.126.187.66):

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.1.37-1ubuntu5.1 |
+-------------------+
1 row in set (0.00 sec)
mysql> CREATE USER 'replication'@'200.126.187.66' IDENTIFIED BY 'slavepass';
mysql> GRANT REPLICATION SLAVE ON mydb.users TO 'replication'@'200.126.187.66';
mysql> GRANT SELECT ON mydb.users TO replication;

Restart MySQL for the changes to take effect:

bash# /etc/init.d/mysql restart

On the Slave, edit /etc/mysql/my.cnf to set the server-id:

server-id  = 126187066    # choose a unique integer

Copy the current database on the Master with mysqldump and load it on the Slave.

Check the current status of the Master by retrieving its log file name and position:

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |    23577 | mydb         |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql>

Start the replication on the Slave.

mysql> CHANGE MASTER TO MASTER_HOST='200.126.187.013', MASTER_USER='replication', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=0;
mysql> START SLAVE;

We did take some shortcuts here: the proper way would have been to:
1/ prevent the Master from committing anything
2/ backup the DB on the Master
3/ check the log file name and log position on the Master (‘SHOW MASTER STATUS’)
4/ load the backup on the Slave
5/ start replication using the log file name and position on the Master
6/ unlock the Master so the commits can happen again

A nightmare if the Master is already a live production server because it means from step 1/ to 6/ the Master is dead not committing anything.

As we did a ‘dirty’ backup, the replication will bump into errors that will stop the Slave (mainly because of entries already on the Slave, so we can safely skip them).
List them on the Slave with ’show slave status \G’ (look at the ‘Last_Error’) and if you can skip it, do it this way:

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> START SLAVE;

Continue skipping the failed insert (if they can be skipped of course) until the slave completely catches up with the Master.

How do I know when the Slave is done catching up with the Master?

Issue a ‘SHOW SLAVE STATUS’ on the Slave:

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 200.126.187.013
                Master_User: replication
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000003
        Read_Master_Log_Pos: 339528
             Relay_Log_File: prod1-relay-bin.000004
              Relay_Log_Pos: 339665
      Relay_Master_Log_File: mysql-bin.000003
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 339528
            Relay_Log_Space: 339665
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

mysql>

You can see everything is fine because:
- ‘Seconds Behind Master’ reports 0, so the Slave is done catching up (it’s in sync with the Master)
- Both Slave IO and Slave SQL are running
- The Slave is just waiting for new data to come in: ‘Waiting for master to send event’

Source

Dumping InnoDB tables without causing the slave to fall behind

Sunday, February 28th, 2010

Use a combination of non-locking table arguments for mysqdlump and the “nice” command to give mysqldump lower priority over other processes.

bash# nice -n 19 mysqldump --single-transaction --quick -uroot -p app3 > app3.2010022801.sql

–single-transaction doesn’t lock the tables when they are being dumped (by default -lock-tables would be enabled)

nice is a Unix command to tweak the priority of a process, from -20 (highest priority) to 19 (lowest priority)

You also probably want to compress the file to speed up transfer across the network to a backup server:

bash# tar -czvf app3.2010011801.sql.tar.gz app3.2010011801.sql

Using nice on complex queries that would actually eat up all the CPU works for every SQL query, not only mysqldump.
If you have such a complex query, instead of trying to run it from inside a mysql shell, run it from the bash as follows:

bash# nice -n 19 mysql -e "use mydb; select * from purchase where id in (select id from users where sex='female');"

… and compare with running it directly from the mysql prompt.

This is particularly handy for complex queries using JOINs between big tables.

ON DUPLICATE KEY do nothing

Monday, October 27th, 2008

Sometimes you just don’t want to do anything if a key already exists in the DB.

mysql> INSERT INTO `table` SET id=5, name=`arthur` ON DUPLICATE KEY UPDATE 0+0;

Source

Permission required to run ’show slave status’

Friday, October 17th, 2008

When checking how far behind from the master your slave has fallen, you may encounter this error:

ERROR 1227 (42000): Access denied; you need the SUPER,REPLICATION CLIENT privilege for this operation

Grant the SUPER privilege to your user and it will work:

mysql> GRANT SUPER ON *.* TO 'root'@'10.1.19.179' IDENTIFIED BY 'password';
mysql> SHOW SLAVE STATUS\G
******************** 1. row *********************
       Slave_IO_State: Waiting for master to send event
          Master_Host: localhost
          Master_User: root
          Master_Port: 3306
        Connect_Retry: 3
      Master_Log_File: gbichot-bin.005
  Read_Master_Log_Pos: 79
       Relay_Log_File: gbichot-relay-bin.005
        Relay_Log_Pos: 548
Relay_Master_Log_File: gbichot-bin.005
     Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
      Replicate_Do_DB:
  Replicate_Ignore_DB:
           Last_Errno: 0
           Last_Error:
         Skip_Counter: 0
  Exec_Master_Log_Pos: 79
      Relay_Log_Space: 552
      Until_Condition: None
       Until_Log_File:
        Until_Log_Pos: 0
   Master_SSL_Allowed: No
   Master_SSL_CA_File:
   Master_SSL_CA_Path:
      Master_SSL_Cert:
    Master_SSL_Cipher:
       Master_SSL_Key:
Seconds_Behind_Master: 8

Load data from a plain CSV text file

Wednesday, August 20th, 2008

Use the LOAD DATA query:

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

To store a query to a CSV file:

mysql> select * from my_table INTO OUTFILE '/tmp/my_table' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

Note that mysql cannot write anywhere in the file system, just in /tmp/ for security reasons, so just move the file afterwards.

Pushing MySQL updates to Memcache using SQL triggers

Tuesday, August 19th, 2008

How to make MySQL trigger cache updates after an INSERT or UPDATE on the DB.

CREATE TRIGGER tr1 AFTER INSERT ON table1 FOR EACH ROW DO memcache_set("localhost:11211",NEW.name,NEW.value);
CREATE TRIGGER tr1 AFTER DELETE ON table1 FOR EACH ROW DO memcache_delete("localhost:11211",OLD.name);

Source

Add a key to speed up queries

Friday, August 1st, 2008
ALTER TABLE items ADD KEY amount_lookup (amount);

Speed test: ON DUPLICATE KEY UPDATE statement vs (DELETE then INSERT)

Thursday, July 24th, 2008

Is it faster to issue two SQL statements (DELETE then INSERT) or craft a single query using ON DUPLICATE KEY UPDATE?

Single query:

INSERT INTO items SET user_id=3463574, item_id=1, amount = 1 ON DUPLICATE KEY UPDATE amount = amount + 1;

Multiple queries:

DELETE FROM items WHERE user_id=3463574 AND item_id=1;
INSERT INTO items SET user_id=3463574 , item_id=1, amount = 66;

Difference in time (using microtime() in PHP):
ON DUP – 0.00023600000000001
ON DUP – 0.00022699999999998
ON DUP – 0.00014599999999998
ON DUP – 0.00014899999999998
DEL AND INSERT – 0.000336
ON DUP – 0.00015000000000004
DEL AND INSERT – 0.00046600000000002

The del/insert is 3 times slower than the ON DUPLICATE statement.

Change a column type along with the table engine in one query

Wednesday, July 23rd, 2008

You have a table to migrate from MyISAM to InnoDB, and while you’re at it, you want to modify the definition of some columns.

For instance we want to change the engine of the following table, along with changing the “amount” column to an unsigned int:

mysql> show create table items;
+-------+--------------------------------------+
| Table | Create Table                                                                                                                                                                                                                          |
+-------+--------------------------------------+
| items | CREATE TABLE `items` (
  `user_id` bigint(20) NOT NULL,
  `item_id` tinyint(4) NOT NULL,
  `amount` int(11) default '0',
  `created_at` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`user_id`,`item_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

You could do that in two queries…:

mysql> ALTER TABLE items MODIFY COLUMN amount INT UNSIGNED;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE items ENGINE InnoDB;
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

… but in practice it will take twice as long as a single query, as every time MySQL will have to make a copy of the whole table anyway.
So here’s the single query:

mysql> ALTER TABLE items MODIFY COLUMN amount INT UNSIGNED, ENGINE InnoDB;

This operation is extremely slow so keep that in mind on your production server.

For instance it took 6 minutes to execute on a staging server with only 700k rows.

The production DB has 8 Million rows…