Convert InnoDB > MyISAM and Back Again...
Friday, 28 December 2007
Convert Typo InnoDB > MyISAM
Simple script to convert your Typo database tables from InnoDB to MyISAM format.
use mytypo_production;
ALTER TABLE articles_tags ENGINE = MyISAM;
ALTER TABLE blacklist_patterns ENGINE = MyISAM;
ALTER TABLE blogs ENGINE = MyISAM;
ALTER TABLE categories ENGINE = MyISAM;
ALTER TABLE categorizations ENGINE = MyISAM;
ALTER TABLE contents ENGINE = MyISAM;
ALTER TABLE feedback ENGINE = MyISAM;
ALTER TABLE notifications ENGINE = MyISAM;
ALTER TABLE page_caches ENGINE = MyISAM;
ALTER TABLE pings ENGINE = MyISAM;
ALTER TABLE redirects ENGINE = MyISAM;
ALTER TABLE resources ENGINE = MyISAM;
ALTER TABLE schema_info ENGINE = MyISAM;
ALTER TABLE sessions ENGINE = MyISAM;
ALTER TABLE sidebars ENGINE = MyISAM;
ALTER TABLE sitealizer ENGINE = MyISAM;
ALTER TABLE tags ENGINE = MyISAM;
ALTER TABLE text_filters ENGINE = MyISAM;
ALTER TABLE triggers ENGINE = MyISAM;
ALTER TABLE users ENGINE = MyISAM;
Now What?
Change mytypo_production to your Typo database's name.
Save it as convert.sql
Run it via,
mysql -u root
source convert.sql
You will get a load of check messages and hopefully no errors or warnings. If all goes ok you should now be over to MyISAM.
Convert Typo MyISAM > InnoDB
use mytypo_production;
ALTER TABLE articles_tags ENGINE = INNODB;
ALTER TABLE blacklist_patterns ENGINE = INNODB;
ALTER TABLE blogs ENGINE = INNODB;
ALTER TABLE categories ENGINE = INNODB;
ALTER TABLE categorizations ENGINE = INNODB;
ALTER TABLE contents ENGINE = INNODB;
ALTER TABLE feedback ENGINE = INNODB;
ALTER TABLE notifications ENGINE = INNODB;
ALTER TABLE page_caches ENGINE = INNODB;
ALTER TABLE pings ENGINE = INNODB;
ALTER TABLE redirects ENGINE = INNODB;
ALTER TABLE resources ENGINE = INNODB;
ALTER TABLE schema_info ENGINE = INNODB;
ALTER TABLE sessions ENGINE = INNODB;
ALTER TABLE sidebars ENGINE = INNODB;
ALTER TABLE sitealizer ENGINE = INNODB;
ALTER TABLE tags ENGINE = INNODB;
ALTER TABLE text_filters ENGINE = INNODB;
ALTER TABLE triggers ENGINE = INNODB;
ALTER TABLE users ENGINE = INNODB;
Also...
Also make sure you Tune your MySQL Install...
And optimize the Tables,
Optimize Typo Tables
Thursday, 27 December 2007
Quick article on Optimizing your Typo MySQL database, does take a bit and only works on MyISAM and BDB Tables.
mysql -u root
use mytypo_db;
show tables;
Now the beef;
optimize table articles_tags, blacklist_patterns, blogs, categories, categorizations, contents, feedback, notifications, page_caches, pings, redirects, resources, schema_info, sessions, sidebars, sitealizer, tags, text_filters, triggers, users;
Does take a while but should clean up your Typo 4.1.1 Database Tables.
Rake Migrations and Custom Tasks
Sunday, 16 December 2007
One cool thing about Rails is it provides you with so many helper functions and commands, one of these being RAKE.
RAKE is similar to the MAKE compiler command used to build binaries from source, with Rails it's useful when building your apps database.
Database Work
Create Migration
script/generate migration users
This will create a Rails database migration you can use to specify the contents of the users table for instance.
/myapp/db/migrate/001_create_users.rb
Create Database
Once that's defined you can create the database with,
rake db:create
Compile Pending Migrations
Then run any pending database migrations.
rake db:migrate
All of which will build into a schema located at,
/myapp/db/schema.rb
Custom Tasks
You can also write custom RAKE tasks to do other things,
What to put in them
Here's a simple rake task,
task :sayHello do
puts "Hello World"
end
Where to save your Scripts
Simple, in the tasks directory,
/myapp/lib/tasks
e.g.
/myapp/lib/tasks/sayhello.rake
And to Run ?
rake sayHello
Returns...
Hello World
More Info at...
Installing MySQL on Leopard
Friday, 14 December 2007
Bit complex, but here's how...
Downloads
Terminal Work
Now the hard bit...
Open the mysql .dmg and install & run the mysql package (the startup one won't work, or the preference pane addon, so skip those).
So far you can only startup MySQL from the Terminal, so open a Terminal window...
sudo /usr/local/mysql/bin/safe_mysqld
Now open another Terminal window and do...
sudo mkdir /var/mysql/
Creating a symbolic link to your ports file, by running...
sudo ln -s /tmp/mysql.sock /var/mysql/mysql.sock
All done...
Start with...
Now you can start MySQL by running...
sudo /usr/local/mysql/bin/safe_mysqld
When you need to,
CocoaMySQL Settings
When opening CocoaMySQL, specify your settings as...
host = localhost
user = root
password = blank
Funky Autostarts with Launchctl
To handle Startup jobs, like cron, OSX and Leopard come with a new tool called Lauchctl which makes setting up startup tasks better.
First off you'll need this config file...
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
<key>KeepAlive</key>
<true/>
<key>Label</key>
<string>com.mysql.mysqld</string>
<key>Program</key>
<string>/usr/local/mysql/bin/mysqld_safe</string>
<key>RunAtLoad</key>
<true/>
</dict>
</plist>
Save this into a text file, but give it the name & extension...
com.mysql.mysqld.plist
Now drag / copy it into your HardDrive's /Library/LaunchDaemons folder
Now you need to set it's owner as root, so in Terminal run...
sudo chown root /Library/LaunchDaemons/com.mysql.mysqld.plist
Now make it autoload by running...
sudo launchctl load /Library/LaunchDaemons/com.mysql.mysqld.plist
Job done, next time you boot your MacBook running Leopard it'll autostart MySQL Server.
Thanks to...
Now running Gutsy + NGINX
Friday, 07 December 2007
Took some doing to make the install cleaner, but yes; this site and my other baby app atompad are now proudly running under NGINX.
NGINX is an extremely lightweight and very memory efficient webserver for Linux, and is absolutely ideal for Ruby on Rails applications; plus it isn't that hard to setup.
So glad I made the move away from Apache, it's good but not ideal for Rails apps.
Along with this change I've move my XEN box onto Ubuntu Gutsy 7.10, MySQL 5 (InnoDB), 2 efficient clusters, load balancer, and upgraded Rails to the fresh new 2.0 release out today.
So far all running well and much better than before.
I'll post what I did to make the move in a follow-up article but for now, I'm gonna put my feet up and watch Heroes.
All the best,
John.
MySQL and Apache Tuning
Monday, 03 December 2007
Thanks to PickledOnion and SliceHost for posting this on their tutorials site, here's a quick example to improve your MySQL speed.
Tuning your Install
Remote onto your box via SSH, and...
sudo nano /etc/mysql/my.cnf
Go down till you see a section for the [mysqld] settings, then add...
default-storage-engine = MyISAM
This will set MySQL to use the MyISAM storage engine (lighter than InnoDB) for all newly created databases.
Now look for anything called...
#skip-innodb
delete the # to make skip loading the InnoDB engine, if you're not using it, changing it to...
skip-innodb
NOTE: Ubuntu Gutsy MySQL 5 uses InnoDB by default (i've found) , so if you haven't set the type and your on Ubuntu Gutsy with a database already on there; then chances are it's under InnoDB. so skip this one for now (see bottom).
Now navigate to the Fine Tuning section and change the values to:
# * Fine Tuning
#
key_buffer = 16K
max_allowed_packet = 1M
thread_stack = 64K
thread_cache_size = 4
Add these also,
sort_buffer = 64K
net_buffer_length = 2K
Save the file and restart MySQL with...
sudo /etc/init.d/mysql restart
Job done, you can run:
top
To see your memory usage (exit with 'q')
Convert Tables to MyISAM
Log into your MySQL server via...
mysql -u root
Now type,
show databases;
Choose your database,
use my_db;
Now see what tables are around,
show tables;
Now you can run a command to convert your tables over to the MyISAM format,
ALTER TABLE my_table ENGINE = MyISAM;
More here...
Restart MySQL
Easily done via,
sudo /etc/init.d/mysql restart
And If All Fails
Now for me those settings weren't completely ideal for Typo5's performance so I switched them back to the default settings, with my other apps they were fine (think they're good for memory-starved situations).
The default settings are...
# * Fine Tuning
#
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 8
#sort_buffer = 64K
#net_buffer_length = 2K
#max_connections = 100
#table_cache = 64
#thread_concurrency = 10
#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 16M
Tuning Options from RimuHosting
# if your are not using the innodb table manager, then just skip it to save some memory
#skip-innodb
innodb_buffer_pool_size = 16k
key_buffer_size = 16k
myisam_sort_buffer_size = 16k
query_cache_size = 1M
Courtesy of RimuHosting
Apache Tuning
Open your Apache Config
sudo nano /etc/apache2/apache2.conf
Suggested by Emergent Properties, this may lower the amount of memory Apache consumes,
StartServers 1
MinSpareServers 1
MaxSpareServers 5
MaxClients 5
MaxRequestsPerChild 300
And original settings...
StartServers 5
MinSpareServers 5
MaxSpareServers 10
MaxClients 20
MaxRequestsPerChild 0
Apache Finer Tuning
Best to turn off Hostname Lookups,
HostnameLookups Off
Lower your timeouts from 300 to 45
Timeout 45
Increase your MaxKeepAlives for High-Loads from 100 to 200
MaxKeepAliveRequests 200
Lower your KeepAliveTimouts from 45 to 2-3
KeepAliveTimeout 2
Source here
Restart Apache
sudo apache2ctl graceful
MySQL dumps with DateStamp's
Tuesday, 30 October 2007
Quickie here, below is a simple command to backup your MySQL database to a fully datestamped file...
mysqldump -u user mydbname > "backup_`date +%Y%m%d_%H%M`.sql"
Replace 'user' with your MySQL username and mydbname with the name of the database your making a dump of.
The > pipes the output to the file.
The 'date' part uses Linux's date function to append date digits inside the filename. The ' embeds it inside the filename.
So in the end you should end up with a file something like...
backup_20071030_1700.sql
Neat!
MySQL 5 + Storage Engines
Friday, 07 September 2007
MySQL is a very fast and very flexible multi-user / multi-threaded database system used most popularly for Web Applications and offers the simple startup a good platform to build from at very little cost.
Despite other more pedigree Database Systems (SQL Server, Oracle), it offers some fairly advanced functions to the developer and has gained a good reputation for it’s large support base and adherence to standards.
Supporting the ANSI 99 query set, stored prodedures, cursors, triggers, updatable views, text indexing, SSL and even Database Clustering, it’s grown up a bit from it’s relatively meager beginnings.
It can be expanded upon and can quite successfully grow as your company grows with famous users like YouTube , Adobe and Flickr most of which handling millions of transactions within a single hour. Okay the unicode support isn’t completely there, but it’s on the way and release 6 isn’t that far off so watch this space.
MySQL Storage Engines
On top of all this, MySQL offers different ways to handle and manage your data on top of the standard collation types. Allowing you to basically choose an engine that closely fits your company needs.
So if you’re a large archive house that only needs to query old records you can opt for the ARCHIVE engine or if your accessing many remote sources you can choose the FEDERATED engine. The key here is that each one is designed around a specific daily function, allowing you to optimize your hardware for the best performance.
Here’s a look at what’s offered,
MyISAM
(default storage engine, best performance overall)
- Default install: Yes
- Data limitations: None
- Index limitations: 64 indexes per table (32 pre 4.1.2); max 16 columns per index
- Transaction support: No
- Locking level: Table
MERGE
(allows to combine a number of identical tables into one)
- Data limitations: Underlying tables must be MyISAM
- Index limitations: N/A
- Transaction support: No
- Locking level: Table
MEMORY
(stores all data in memory, if power failure, you lose it all good for quick access, calculations, rapid temp tables)
- Data limitations: BLOB and TEXT types not supported
- Index limitations: None
- Transaction support: No
- Locking level: Table
FEDERATED
(allows remote data access, combining many sources into one system)
- Data limitations: Limited by remote database
- Index limitations: N/A
- Transaction support: No
- Locking level: No
ARCHIVE
(insert & select only supported, compressed, good for logs, old data)
- Data limitations: Data can only be inserted (no updates)
- Index limitations: N/A
- Transaction support: No
- Locking level: N/A
CSV
(stores as comma-separated data, good for data transport)
- Data limitations: None
- Index limitations: Indexing is not supported
- Transaction support: No
- Locking level: Table
BLACKHOLE
(allows you to test out possible data structures, schemas)
- Data limitations: No data is stored, but statements are written to the binary log (and therefore distributed to slave databases)
- Index limitations: N/A
- Transaction support: No
- Locking level: N/A
ISAM
(original engine, included only for backwards compatibility)
- Data limitations: Limited maximum database size (4GB)
- Index limitations: Maximum 16 indexes per table, 16 parts per key
- Transaction support: No
- Locking level: Table
BERKELEY DB
(hash-based storage engine, very quick to access & recover great for accessing data that does not change much, due to it’s table locking)
- Data limitations: None
- Index limitations: Max 31 indexes per table, 16 columns per index; max key size 1024 bytes
- Transaction support: Yes
- Locking level: Page (8192 bytes)
INNO DB
(based on myisam + adds database cacheing & indexing, in memory and disk, very fast recovery, less table-locking issues, speeds up recovery & storage. there is a management overhead with InnoDB that requires your system to be optimised to use it but great if you go that extra mile)
- Data limitations: None
- Index limitations: None
- Transaction support: Yes (ACID compliant)
- Locking level: Row
EXAMPLE
(used as a blueprint to create your own storage engine)
Uses
Having a lot of different storage engines available is a major bonus, but just because I’m nice I’ll let you into what I use. It’s really a mixture but mainly InnoDB (major apps) because it’s less limiting and I like to have total control, but I do have a couple of sites I run for friend’s using the Berkeley DB engine (blogs).
Enjoy,
MySQL Commands
Sunday, 08 July 2007
Here’s some useful commands for using MySQL from the command line, and breakdowns of the most common SQL query syntax.
Command Line
mysql -u root@localhost
p. login to mysql server with username ‘root’, host = localhost, this will drop you into a sql console where you can fire off common SQL queries & commands (e.g. select * from users, create table users…)
mysqladmin -u root password [mysqlpassword]
p. change the ‘root’ password
mysqladmin -u root create sessions_development
p. using ‘root’ account, create database ‘sessions_development’
mysqladmin -u root drop sessions_development
p. using ‘root’ account, delete database ‘sessions_development’
mysqldump -u root -ppassword —opt >/all.sql
p. backup all databases to disk
mysqldump -u root mydb > mydb.sql
p. backup only database ‘mydb’ to disk
mysql -u username -ppassword mydb < /mydb.sql
p. restore database mydb from disk
Console Queries
CREATE TABLE new_tbl SELECT * FROM orig_tbl;
p. create one table from the results of a SELECT query
CREATE TABLE people (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
fullname VARCHAR );
p. creates a new table ‘people’ with an auto-incrementing (AUTO_INCREMENT) ‘id’ field that is the primary key (PRIMARY KEY) and can’t be null (NOT NULL), along with a ‘fullname’ variable text string field
INSERT INTO goods (price) VALUES (1.99);
p. insert a new record into goods with the field ‘price’ of 1.99
UPDATE goods SET price = 2.99 WHERE name = ‘shampoo’;
p. update ‘price’ value for record with ‘name’ of shampoo in ‘goods’ table
DROP TABLE IF EXISTS goods;
p. conditionally only delete the table ‘goods’ if it exists
SHOW databases;
p. list all databases on server
USE mydb;
p. switch to another database
DESC goods;
p. show table definition for ‘goods’ table
SHOW CREATE TABLE goods;
p. show the sql syntax for creating the ‘goods’ table
DESCRIBE goods;
p. to see all of table ‘goods’ field formats
FLUSH PRIVILEGES;
p. update all database permissions & privileges
COMMIT;
p. commit all pending transactions
ROLLBACK;
p. rollback previous transaction
Mephisto!
Friday, 18 May 2007
God what a week it’s been, what with too many late nights, an unexploded bomb outside my station and a million email’s asking for help; I think I really deserve that bottle of JD sitting on the counter.
… Anyway, enough of my moaning, to work people and today’s task is to build a production-class ruby-on-rails application with Mephisto. Now if you don’t know, Mephisto is a ruby app much like Wordpress that provides you with the basis of running your blog-on-rails.
To build Mephisto, first you’ll need to do a couple of things,
First, install ruby, rails, mongrel and the like (see previous guide)
On my ibook (macbook’s in the shop getting repaired)
1. open cocoamysql, and create a database called ‘mephisto’
…if your following from the previous guide where we built mysql on our machine you’ll need to set the host → ‘localhost’, username → ‘root’, there is no password, so leave the rest blank and cocoamysql should connect to your local db server.
2. open terminal, it’ll drop into your user root dir, that’s fine, type
svn co http://svn.techno-weenie.net/projects/mephisto/trunk mephisto
…that’ll download the latest build of mephisto and copy it all to the dir ‘mephisto’
3. type,
cd mephisto
svn update
…first changes you to the mephisto dir, next verifies the svn version num so your running bleedin’ edge material
4. type,
rake rails:freeze:edge
sudo gem install tzinfo —remote
… the first makes rails use the edge gem (required), the next installs the tzinfo gem (for this you have to append ‘sudo’ to the beginning to give it admin privelidges otherwise it can’t install tzinfo on your mac)
5. next go to mephisto/config/ and rename ‘database.example.yml’ to database.yml
6. edit database.yml, changing the ‘production’ database name to ‘mephisto’
7. open terminal again, type cd mephisto, and run
rake db:bootstrap RAILS_ENV=production
…this makes sure the production database is used.
8. run,
script/server —e
…to get the Rails web server going.
9. open your web browser and login to your administration at ‘http://0.0.0.0:3000/admin’ with the username: admin and password: test
In the end, you should now have a fully running Mephisto install, go play!
…more at → mephisto stiki
Drop me if you get stuck,
All the best,
John
Ruby Cheat Sheet's
Monday, 14 May 2007
Thought people needed a bit more help to get rolling, and knowing people’s fear of buying too many books (and then the impending doom of having to read them…), below are a whole load of Ruby on Rails cheat sheets to help you create some really wicked stuff!
Enjoy…
ActiveRecord Relationships cheat sheet
building ruby, rails, subversion, mysql and mongrel
What Goes Where – ruby on rails cheat sheet
Thanks goes to all the original writers, the fabulous Amy Hoy, the stunning WhyTheLuckyStiff and many more…
Enjoy the guides and take care…
… also available in the DOWNLOADS section,
… John
Cocoa MySQL
Thursday, 10 May 2007
Ok, by now i’m reckoning your getting a little tired of using phpMySQL to add and edit your database tables?
So help is on the way for you Mac users with the aptly named CocoaMySQL.
This provides you with a friendly and OSX-like MySQL GUI for manipulating your MySQL databases, tables, queries, and the lot under the MAC OSX operating system.
It’s free and can be downloaded here.
Enjoy, till next time,
“Good night and good luck”
