Tag Archives: MySql

Installing MySql Handlersocket in Ubuntu Natty for Ruby

Install

sudo apt-get install mysql-server -y;
sudo apt-get install handlersocket-doc -y;
sudo apt-get install handlersocket-mysql-5.1 -y;
sudo apt-get install libhsclient-dev -y;

Configure

#/etc/mysql/my.cnf -- under mysqld section
loose_handlersocket_port = 9998
loose_handlersocket_port_wr = 9999
loose_handlersocket_threads = 16
loose_handlersocket_threads_wr = 1
open_files_limit = 65535

Start

mysql -e "install plugin handlersocket soname 'handlersocket.so';"
sudo /etc/init.d/mysql restart

# should show lots of handlersocket processes
mysql -e "show processlist;"

Use

gem install handlersocket

require 'handlersocket'
h = HandlerSocket.new(:host => '127.0.0.1', :port => '9998')

# open PRIMARY index on widgets.user table, assign it ID #1
h.open_index(1, 'widgets', 'user', 'PRIMARY', 'user_name,user_email,created')
...

more of this can be found at ruby handlersocket introduction and ActiveRecord Handlersocket gem

MySql: 4 ways to lower the impact of TEXT columns

Starting point

Normally there is a table with some small stuff like id/type/xxx_id(~50B) and a text column(default 768B + x) that is not needed all the time, because most users enter less than 255 chars or most queries only need the smaller columns.

  • text bloats table size
  • text makes queries slow
  • text bloats mysql query cache
  • text is always loaded into buffer pool (consumes ram)

Alternative Row format

With row ROW_FORMAT=​DYNAMIC those default 768B+x can be reduced to a mere default 20B+x, but requires innodb_file​_format=Barracuda.

1 to 1 Relationship

Keep all small columns on the first model and pull in the text from another model e.g. using translated_attributes.
Generates some overhead in the model but is reasonably fast.

Varchar column as cache + 1 to 1 Relationship

Only fetches the text/adds the relationship when needed. Useful when only a few texts are larger then 255 chars and the content is often needed. e.g. on original model:

def text_truncated(to=255)
  text = if to > 255 and text_cache.size == 255
    1_to_1_relationship.text
  else
    text_cache
  end
  text.truncate(to)
end

(this needs truncate on string)
And e.g. in the UI the text could always show truncated to 255 chars and only on click it is really loaded from the db.

Using covering index

Make an index that covers all the normal columns except the text, and FORCE INDEX on the queries.

Performance

Source:
A lot more details on the 1-to-1-relation ship / covering index can be found here

Alter Column the Missing Migration Method for ActiveRecord

Changing many columns is not so easy, because change_column forces you to enter all attributes you want (default,limit,null…) again.
So to make less errors we just alter the attributes we want to change and leave the rest alone.

Install

#Inside your migration
def self.alter_column(table,column,text)
  execute("ALTER TABLE #{quote_table_name(table)} ALTER #{quote_column_name(column)} #{text}")
end

Usage

# to set the default to nil, without remembering all the other 
# stuff change_column needs
alter_column(:users,:name,"DROP DEFAULT")

So thats very basic, but i hope it helps anyway 🙂

MySql: DEFAULT does NOT prevent NULL

I just came across a serious bug/gotcha when using mysql.
Integer, default 0 -> set to NULL –> NULL and not 0

Remember
always set NOT_NULL and DEFAULT!

Example

change_column :order_items, :shipping, :boolean, :default=>false

#shipping is nil or false or true
OrderItem.find(:all,:conditions=>{:shipping=>false}) => [] 

change_column :order_items, :shipping, :boolean, :default=>false, :null=>false

OrderItem.find(:all,:conditions=>{:shipping=>false}) => [all]