database snippets

How to install and use Redis with Ruby

Tagged redis, ruby, key-value, database, cat  Languages ruby

Redis and redis-rb example:

git clone git://github.com/ezmobius/redis-rb.git
cd redis-rb/
rake redis:install
rake dtach:install
rake redis:start &
rake install

cat <<EOF >>test.rb
require 'rubygems'
require 'redis'

r = Redis.new

r.delete('foo')

p'set foo to "bar"'
r['foo'] = 'bar'

p 'value of foo'
p r['foo']
EOF

ruby test.rb

If Redis is not started Ruby will exit at Redis.new for some reason without saying anything.

How to install Tokyo Tyrant, Tokyo Cabinet and rufus-tokyo

Tagged tokyo-tyrant, tokyo-cabinet, ruby, key-value, database  Languages ruby
git clone git://github.com/etrepum/tokyo-cabinet.git
git clone git://github.com/etrepum/tokyo-tyrant.git


cd tokyo-cabinet/
./configure
make
sudo make install

cd ..

cd tokyo-tyrant/
./configure
make
sudo make install

ttserver -port 45001 data.tch

sudo gem install rufus-tokyo

cat <<EOF >>test.rb

require 'rubygems'
require 'rufus/tokyo/tyrant'
require 'benchmark'


#db = Rufus::Tokyo::Cabinet.new('data.tch')

db = Rufus::Tokyo::Tyrant.new('localhost', 45001)
 
 seconds = Benchmark.realtime do
  for i in 1..1000000
    db['nada' + i.to_s] = 'surf' + i.to_s
  end
end

puts seconds
db.close

EOF

ruby test.rb

Gotchas

One gotcha I learned was that you should only use strings not symbols. If you use symbols you'll get this weird error:

Invalid String value

Another gotcha is that Tokyo Cabinet will lock a database. If you try to open the same database from another process it will wait for the other process to close the database.

Debian

On Debian you need to install bzlib before installing tokyo-cabinet:

apt-get install libbz2-dev

Fixing Incorrect information in file: '...'

Tagged mysql, corrupt, database  Languages 

If you get this MySQL error:

Incorrect information in file: '...'

you might have: * deleted the /tmp directory * disabled innodb or messed with my.cnf * corrupt database files * spilled coffee on the server???

I had accidentally deleted /tmp, so running these commands saved my day:

mkdir /tmp
chmod 1777 /tmp

Postgres Performance Tuning

Tagged database, performance, postgres, tuning  Languages sql

Tuning memory

To tune memory it helps to understand how much data fits into memory:

SELECT 
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit)  as heap_hit,
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM 
  pg_statio_user_tables;

If the cache hit rate is below 99%, try giving PG more memory.

Finding slow queries

Using the slow-query feature is one option. Telling Postgres to store statistics is easier:

CREATE extension pg_stat_statements;

-- View slowest queries
SELECT 
  (total_time / 1000 / 60) as total_minutes, 
  (total_time/calls) as average_time, 
  query 
FROM pg_stat_statements 
ORDER BY 1 DESC 
LIMIT 100;

Finding tables that are not indexed

SELECT 
  relname, 
  100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, 
  n_live_tup rows_in_table
FROM 
  pg_stat_user_tables
WHERE 
    seq_scan + idx_scan > 0 
ORDER BY 
  n_live_tup DESC;

Reference:

Mattermost database schema and design

Tagged communication, database, mattermost, schema, slack  Languages bash, sql

This review was done on Mattermost version 3.4.0.

Install mattermost

docker run --name mattermost-preview -d --publish 8065:8065 mattermost/mattermost-preview
docker exec -ti mattermost-preview /bin/bash
...
mysql -u root -p
use mattermost_test

Database password is mostest.

Schema

mysql> describe Posts;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| Id         | varchar(26)  | NO   | PRI | NULL    |       |
| CreateAt   | bigint(20)   | YES  | MUL | NULL    |       |
| UpdateAt   | bigint(20)   | YES  | MUL | NULL    |       |
| DeleteAt   | bigint(20)   | YES  |     | NULL    |       |
| UserId     | varchar(26)  | YES  |     | NULL    |       |
| ChannelId  | varchar(26)  | YES  | MUL | NULL    |       |
| RootId     | varchar(26)  | YES  | MUL | NULL    |       |
| ParentId   | varchar(26)  | YES  |     | NULL    |       |
| OriginalId | varchar(255) | YES  |     | NULL    |       |
| Message    | text         | YES  | MUL | NULL    |       |
| Type       | varchar(26)  | YES  |     | NULL    |       |
| Props      | text         | YES  |     | NULL    |       |
| Hashtags   | text         | YES  | MUL | NULL    |       |
| Filenames  | text         | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
14 rows in set (0.00 sec)

mysql> describe Channels;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| Id            | varchar(26)  | NO   | PRI | NULL    |       |
| CreateAt      | bigint(20)   | YES  |     | NULL    |       |
| UpdateAt      | bigint(20)   | YES  |     | NULL    |       |
| DeleteAt      | bigint(20)   | YES  |     | NULL    |       |
| TeamId        | varchar(26)  | YES  | MUL | NULL    |       |
| Type          | varchar(1)   | YES  |     | NULL    |       |
| DisplayName   | varchar(64)  | YES  |     | NULL    |       |
| Name          | varchar(64)  | YES  | MUL | NULL    |       |
| Header        | text         | YES  |     | NULL    |       |
| Purpose       | varchar(128) | YES  |     | NULL    |       |
| LastPostAt    | bigint(20)   | YES  |     | NULL    |       |
| TotalMsgCount | bigint(20)   | YES  |     | NULL    |       |
| ExtraUpdateAt | bigint(20)   | YES  |     | NULL    |       |
| CreatorId     | varchar(26)  | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+


mysql> describe ChannelMembers;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| ChannelId    | varchar(26) | NO   | PRI | NULL    |       |
| UserId       | varchar(26) | NO   | PRI | NULL    |       |
| Roles        | varchar(64) | YES  |     | NULL    |       |
| LastViewedAt | bigint(20)  | YES  |     | NULL    |       |
| MsgCount     | bigint(20)  | YES  |     | NULL    |       |
| MentionCount | bigint(20)  | YES  |     | NULL    |       |
| NotifyProps  | text        | YES  |     | NULL    |       |
| LastUpdateAt | bigint(20)  | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)


mysql> describe Teams;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| Id              | varchar(26)  | NO   | PRI | NULL    |       |
| CreateAt        | bigint(20)   | YES  |     | NULL    |       |
| UpdateAt        | bigint(20)   | YES  |     | NULL    |       |
| DeleteAt        | bigint(20)   | YES  |     | NULL    |       |
| DisplayName     | varchar(64)  | YES  |     | NULL    |       |
| Name            | varchar(64)  | YES  | UNI | NULL    |       |
| Email           | varchar(128) | YES  |     | NULL    |       |
| Type            | varchar(255) | YES  |     | NULL    |       |
| CompanyName     | varchar(64)  | YES  |     | NULL    |       |
| AllowedDomains  | text         | YES  |     | NULL    |       |
| InviteId        | varchar(32)  | YES  | MUL | NULL    |       |
| AllowOpenInvite | tinyint(1)   | YES  |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+

mysql> describe TeamMembers;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| TeamId   | varchar(26) | NO   | PRI | NULL    |       |
| UserId   | varchar(26) | NO   | PRI | NULL    |       |
| Roles    | varchar(64) | YES  |     | NULL    |       |
| DeleteAt | bigint(20)  | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)


mysql> describe Users;
+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| Id                 | varchar(26)  | NO   | PRI | NULL    |       |
| CreateAt           | bigint(20)   | YES  |     | NULL    |       |
| UpdateAt           | bigint(20)   | YES  |     | NULL    |       |
| DeleteAt           | bigint(20)   | YES  |     | NULL    |       |
| Username           | varchar(64)  | YES  | UNI | NULL    |       |
| Password           | varchar(128) | YES  |     | NULL    |       |
| AuthData           | varchar(128) | YES  | UNI | NULL    |       |
| AuthService        | varchar(32)  | YES  |     | NULL    |       |
| Email              | varchar(128) | YES  | UNI | NULL    |       |
| EmailVerified      | tinyint(1)   | YES  |     | NULL    |       |
| Nickname           | varchar(64)  | YES  |     | NULL    |       |
| FirstName          | varchar(64)  | YES  |     | NULL    |       |
| LastName           | varchar(64)  | YES  |     | NULL    |       |
| Roles              | varchar(64)  | YES  |     | NULL    |       |
| AllowMarketing     | tinyint(1)   | YES  |     | NULL    |       |
| Props              | text         | YES  |     | NULL    |       |
| NotifyProps        | text         | YES  |     | NULL    |       |
| LastPasswordUpdate | bigint(20)   | YES  |     | NULL    |       |
| LastPictureUpdate  | bigint(20)   | YES  |     | NULL    |       |
| FailedAttempts     | int(11)      | YES  |     | NULL    |       |
| Locale             | varchar(5)   | YES  |     | NULL    |       |
| MfaActive          | tinyint(1)   | YES  |     | NULL    |       |
| MfaSecret          | varchar(128) | YES  |     | NULL    |       |
+--------------------+--------------+------+-----+---------+-------+
23 rows in set (0.00 sec)

Data

mysql> select * from Channels;
+----------------------------+---------------+---------------+----------+----------------------------+------+-------------+--------------------------------------------------------+--------+---------+---------------+---------------+---------------+----------------------------+
| Id                         | CreateAt      | UpdateAt      | DeleteAt | TeamId                     | Type | DisplayName | Name                                                   | Header | Purpose | LastPostAt    | TotalMsgCount | ExtraUpdateAt | CreatorId                  |
+----------------------------+---------------+---------------+----------+----------------------------+------+-------------+--------------------------------------------------------+--------+---------+---------------+---------------+---------------+----------------------------+
| 1h3rs83b3bnwzn3xb1ow49rssy | 1474786289480 | 1474786289480 |        0 | chjgwwy9qbbsdcz4ry4a6fbsoy | O    | Town Square | town-square                                            |        |         | 1474786555810 |             2 | 1474786555808 |                            |
| 1muwbod1hjrmzcr6iukhzqk4dc | 1474786289482 | 1474786289482 |        0 | chjgwwy9qbbsdcz4ry4a6fbsoy | O    | Off-Topic   | off-topic                                              |        |         | 1474786565693 |             2 | 1474786555815 |                            |
| 3pocxn4za7rd58q6rzcycgerna | 1474786510558 | 1474786510558 |        0 | ehwmnuwmztrujxhtb65kpgq4sy | O    | Off-Topic   | off-topic                                              |        |         | 1474786524398 |             1 | 1474786510570 |                            |
| f3j13bk4fi8qb8hh4mzgfbmmjc | 1474786769364 | 1474786769364 |        0 |                            | D    |             | abkob7omkjynfqfsc8shkbwkfo__u7s1oanujifg8ywuirnmhk8r3a |        |         | 1474786777955 |             1 | 1474786769364 |                            |
| g46cuaw3u781bjfyowudtfso7y | 1474786510553 | 1474786510553 |        0 | ehwmnuwmztrujxhtb65kpgq4sy | O    | Town Square | town-square                                            |        |         | 1474786510566 |             0 | 1474786510565 |                            |
| wi87emmg6786inbhgxzbnfgj3r | 1474786711899 | 1474786711899 |        0 | chjgwwy9qbbsdcz4ry4a6fbsoy | P    | XXX         | XXX                                                    |        |         | 1474786723310 |             1 | 1474786719125 | abkob7omkjynfqfsc8shkbwkfo |
+----------------------------+---------------+---------------+----------+----------------------------+------+-------------+--------------------------------------------------------+--------+---------+---------------+---------------+---------------+----------------------------+

mysql> select * from Posts;
+----------------------------+---------------+---------------+----------+----------------------------+----------------------------+----------------------------+----------------------------+------------+---------------------------------------+-------------------+-------+----------+-----------+
| Id                         | CreateAt      | UpdateAt      | DeleteAt | UserId                     | ChannelId                  | RootId                     | ParentId                   | OriginalId | Message                               | Type              | Props | Hashtags | Filenames |
+----------------------------+---------------+---------------+----------+----------------------------+----------------------------+----------------------------+----------------------------+------------+---------------------------------------+-------------------+-------+----------+-----------+
| 1mmp5u8t8fgffkkysrgcddj9oh | 1474786565691 | 1474786565691 |        0 | u7s1oanujifg8ywuirnmhk8r3a | 1muwbod1hjrmzcr6iukhzqk4dc |                            |                            |            | -                                     |                   | {}    |          | []        |
| 37wm6h78j7fh8dtq6p66uohjyy | 1474786329527 | 1474786329527 |        0 | abkob7omkjynfqfsc8shkbwkfo | 1h3rs83b3bnwzn3xb1ow49rssy | 811nz3334pbg78338ebbjfd8yy | 811nz3334pbg78338ebbjfd8yy |            | -                                     |                   | {}    |          | []        |
| 5ed5ghxp33gbik9denmqg17w6y | 1474786510566 | 1474786510566 |        0 | u7s1oanujifg8ywuirnmhk8r3a | g46cuaw3u781bjfyowudtfso7y |                            |                            |            | hithere has joined the channel.       | system_join_leave | {}    |          | []        |
| 811nz3334pbg78338ebbjfd8yy | 1474786320981 | 1474786329530 |        0 | abkob7omkjynfqfsc8shkbwkfo | 1h3rs83b3bnwzn3xb1ow49rssy |                            |                            |            | Hello                                 |                   | {}    |          | []        |
| anr5wpaoa3yuibqgmruiw98a5o | 1474786555809 | 1474786555809 |        0 | u7s1oanujifg8ywuirnmhk8r3a | 1h3rs83b3bnwzn3xb1ow49rssy |                            |                            |            | hithere has joined the channel.       | system_join_leave | {}    |          | []        |
| ct7wcfon87rnxkna313esudzhw | 1474786719128 | 1474786719128 |        0 | abkob7omkjynfqfsc8shkbwkfo | wi87emmg6786inbhgxzbnfgj3r |                            |                            |            | hithere added to the channel by hello | system_add_remove | {}    |          | []        |
| dmt68m5b3fd9zr16g9ci4mpdqc | 1474786777954 | 1474786777954 |        0 | abkob7omkjynfqfsc8shkbwkfo | f3j13bk4fi8qb8hh4mzgfbmmjc |                            |                            |            | -                                     |                   | {}    |          | []        |
| kss8nustk3nqjnnf9dsrw914my | 1474786311271 | 1474786311271 |        0 | abkob7omkjynfqfsc8shkbwkfo | 1muwbod1hjrmzcr6iukhzqk4dc |                            |                            |            | Hello!                                |                   | {}    |          | []        |
| maa1ttmi9pri8xwb9jekyci3hw | 1474786723309 | 1474786723309 |        0 | abkob7omkjynfqfsc8shkbwkfo | wi87emmg6786inbhgxzbnfgj3r |                            |                            |            | WTF                                   |                   | {}    |          | []        |
| soizm5ey3ig3bqkkygxjngodya | 1474786289489 | 1474786289489 |        0 | abkob7omkjynfqfsc8shkbwkfo | 1h3rs83b3bnwzn3xb1ow49rssy |                            |                            |            | hello has joined the channel.         | system_join_leave | {}    |          | []        |
| yj8aayxz4ir38m9r6b97cxbgco | 1474786524397 | 1474786524397 |        0 | u7s1oanujifg8ywuirnmhk8r3a | 3pocxn4za7rd58q6rzcycgerna |                            |                            |            | Jebus                                 |                   | {}    |          | []        |
+----------------------------+---------------+---------------+----------+----------------------------+----------------------------+----------------------------+----------------------------+------------+---------------------------------------+-------------------+-------+----------+-----------+
11 rows in set (0.00 sec)

Notes

  • There are 3 types of channels: open, private groups, direct messages
  • Direct messages have no team members. The channel name contains the members, e.g., “user_1__user_2”.
  • Search uses database’s full-text search by default.
  • Reads are performed on a read-only slave for high-availability: s.GetReplica().Select(&posts, “SELECT * FROM Posts”)
  • Writes are performed on the master: s.GetMaster().Exec(“Update Channels SET DeleteAt
  • Unread count is calculated using SQL and the cached values (channel.TotalMsgCount - channel_member.MsgCount):
-- unread count for user
SELECT
  SUM(CASE
    WHEN c.Type = 'D' THEN (c.TotalMsgCount - cm.MsgCount)
    ELSE cm.MentionCount
  END)
FROM Channels c
INNER JOIN ChannelMembers cm
  ON cm.ChannelId = c.Id
  AND cm.UserId = :UserId
  
-- unread count for user and channel
SELECT
  SUM(CASE
    WHEN c.Type = 'D' THEN (c.TotalMsgCount - cm.MsgCount)
    ELSE cm.MentionCount
  END)
FROM Channels c
INNER JOIN ChannelMembers cm
  ON c.Id = :ChannelId
  AND cm.ChannelId = :ChannelId
  AND cm.UserId = :UserId
  • Unread count is updated, e.g., when a channel is viewed. This involves storing the “last viewed” timestamp:
-- set last viewed at
UPDATE ChannelMembers
SET MentionCount = 0,
    MsgCount = Channels.TotalMsgCount - (SELECT
      COUNT(*)
    FROM Posts
    WHERE ChannelId = :ChannelId
    AND CreateAt > :NewLastViewedAt),
    LastViewedAt = :NewLastViewedAt
FROM Channels
WHERE Channels.Id = ChannelMembers.ChannelId
AND UserId = :UserId
AND ChannelId = :ChannelId

-- update last viewed at (new post, edit post, mark channel unread, etc)
UPDATE ChannelMembers
SET MentionCount = 0,
    MsgCount = Channels.TotalMsgCount,
    LastViewedAt = Channels.LastPostAt,
    LastUpdateAt = Channels.LastPostAt
FROM Channels
WHERE Channels.Id = ChannelMembers.ChannelId
AND UserId = :UserId
AND ChannelId = :ChannelId

TODO