Slow MySQL 5.5.22 database engine

16 05 2012

Slooooooow UPDATE query with MySQL 5.5

I am using a local MySQL server a lot to handle, prepare and restructure big research tables. Ubuntu Precise uses MySQL server 5.5 while the previous distros used 5.1. I thought that might be good until I tried to import a table with some dozen variables and some thousand rows with and UPDATE statement which took some seconds (10 min!!).

nick rulez on forum.mysql.com quantified this fact and revealed that the default database engine changed from “MyISAM” to “InnoDB” and that indeed InnoDB is considerably slower in this regard.

So I want MyISAM back.

To list the available and default engines:

show engines
which produces

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

Setting the database engine

When you create a new table, you can specify which storage engine to use by adding an ENGINE table option to the CREATE TABLE statement:

CREATE TABLE t (i INT) ENGINE = INNODB;

If you omit the ENGINE option, the default storage engine is used. Normally, this is MyISAM, but you can change it by using the –default-storage-engine server startup option, or by setting the default-storage-engine option in the my.cnf configuration file.

You can set the default storage engine to be used during the current session by setting the storage_engine variable:

SET storage_engine=MYISAM;

[…]

To convert a table from one storage engine to another, use an ALTER TABLE statement that indicates the new engine:

ALTER TABLE t ENGINE = MYISAM;

I want MyISAM all the time so I decided for the my.cnf option. But where is my.cnf? According to debianadmin:

sudo nano /etc/mysql/my.cnf

Now append
default-storage-engine = MyISAM
Safe and exit with Ctrl-o, Ctrl-x and restart the server.

sudo restart mysql

and MyISAM it is.

Advertisements




Move a table between MySQL databases

2 09 2011

Having organized a lot of datasets in severeal MySQL databases on the same (local)-server the I needed to move a table to another database. The posted solution is copy-paste from Eric Bergen on the MySQL forum:

Alter table can be used to move tables from one db to another.

alter table old_db.foo rename new_db.foo





Sane PATH variable in Emacs on Mac OS X

16 08 2011

On Mac OS X the system PATH variable is not recognized by emacs. This means that one can not simply type

mysql

in the emacs shell to get into the database. The emacs shell complains about “binary not found”.

Indeed

echo $PATH

reveals that emacs just looks into /bin, /usr/bin, /sbin and /usr/sbin.

To set the $PATH variable inside emacs one can append the following lines to the .emacs file (found on github, hattip Alex Payne):

; sane path
(setq path "/bin:/usr/bin:/sbin:/usr/sbin:/usr/local/bin:/usr/local/mysql/bin")
(setenv "PATH" path)

Next time Emacs starts one can go to the shell and

mysql

presents the database prompt.





Compressed backup of MySQL database

20 07 2011

Wrote several posts on this topic, but none was 100% right. The following is a blockquote from webcheatsheet.com and looks much better researched then my previous tries:

Back up your MySQL Database with Compress

If your mysql database is very big, you might want to compress the output of mysqldump. Just use the mysql backup command below and pipe the output to gzip, then you will get the output as gzip file.

$ mysqldump -u [uname] -p[pass] [dbname] | gzip -9 > [backupfile.sql.gz]
If you want to extract the .gz file, use the command below:

$ gunzip [backupfile.sql.gz]

Restoring your MySQL Database

Above we backup the Tutorials database into tut_backup.sql file. To re-create the Tutorials database you should follow two steps:

Create an appropriately named database on the target machine
Load the file using the mysql command:
$ mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]
Have a look how you can restore your tut_backup.sql file to the Tutorials database.

$ mysql -u root -p Tutorials < tut_backup.sql
To restore compressed backup files you can do the following:

gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]





MySQL tables must have indexes

16 06 2011

A newbee to MySQL myself I had a rather odd experience: A simple inner join of three research tables took severeal hours. I posted the problem on the forum.mysql.com and the following is the result of the post.

To give the conclusion first:

  1. Always create indexes for each key-variable in each and every MySQL table, no matter how small or big.
    ALTER TABLE MyTable ADD INDEX(MyID)
    where MyTable is a Table in the database and MyID is one of the key variables.

  2. Never use brackets in consecutive joint statements! The right way is:
    select * from Table_A A inner join Table_B B on A.id = B.id inner join Table_C on A.id = C.id;
    Brackets force the creation of temporary tables and increase execution time
  3. The first one is the most important. It reduced the time for a join from more then 10 hours to a couple of minutes.





MySQL backup

15 04 2011

Just for the record: How to combine mysqldump and zip to archive all MySQL databases on the host. I am using a simple MySQL database server on localhost, to organise research tables before analysis.

mysqldump --all-databases | zip -9 allDB_backup_110415.sql.zip -

mysqldump --all-databases writes the content of all databases into the pipe and
zip -9 Filename - compresses the standard input (note the dash ‘-‘ at the end!) to ‘Filename’ (-9 gives maximum compression).

The reverse following the man page of ‘mysqldump’:

You can load the dump file back into the server like this:

shell> mysql db_name < backup-file.sql

Or like this:

shell> mysql -e "source /path-to-backup/backup-file.sql" db_name





Emacs as MySQL frontend

18 01 2011

After working quite a time with some minor MySQL databases in the (Emacs-) Terminal I just looked up the preinstalled SQL related emacs functions. Just entered M-x sql TAB and indeed the autocompletion showed a function sql-mysql, as expected…

I gave it a try with
M-x sql-mysql
and after prompting for database, servername, username and password Emacs connected to the database and presented the MySQL shell. So I bound sql-mysql to some keyboard shortcut, BUT entering the whole connection parameters each and every time was not acceptable.

Atomized.org has a really nice post on Enhancing Emacs’ SQL Mode (you can have a look, but you cannot read the post before pasting the content to a text editor). There I found some excellent functions which would provide a solution:

(setq sql-connection-alist
'((pool-a
(sql-product 'mysql)
(sql-server "1.2.3.4")
(sql-user "me")
(sql-password "mypassword")
(sql-database "thedb")
(sql-port 3306))
(pool-b
(sql-product 'mysql)
(sql-server "1.2.3.4")
(sql-user "me")
(sql-password "mypassword")
(sql-database "thedb")
(sql-port 3307))))

(defun sql-connect-preset (name)
"Connect to a predefined SQL connection listed in `sql-connection-alist'"
(eval `(let ,(cdr (assoc name sql-connection-alist))
(flet ((sql-get-login (&rest what)))
(sql-product-interactive sql-product)))))

(defun sql-pool-a ()
(interactive)
(sql-connect-preset 'pool-a))

Now, you can just run sql-pool-a and get connected right away. Because the buffers have good names, you can easily fire up many connections.

I included it in my .emacs file and appended
(DefGlobKey "s-a" 'sql-pool-a)
and with a keystroke the database promt appears.

Thx, atomized.org, but what’s that webpage giving you an epileptic fit looking at it?!. Excellent page 🙂