In the previous post we had a look at the MySQL Cluster NDB API and how it enables direct access to the MySQL Cluster data nodes, and therefore also enables access through other protocols than SQL.

I've often asked myself: Since NDB is so great for MySQL Cluster, is there anything similar for MySQL Server (the not-cluster version...)? A couple of months ago Kazuho Oku did something like that and wrote in his blog about it.

The context for Kazuho's work is the social network use case: 1) You have users. 2) Some users are linked to each others as friends. 3) When a user logs in, he should see a timeline of events/messages from his friends. In a previous post he had already tested the difference between a …

In the previous post we had a look at the MySQL Cluster NDB API and how it enables direct access to the MySQL Cluster data nodes, and therefore also enables access through other protocols than SQL.

I've often asked myself: Since NDB is so great for MySQL Cluster, is there anything similar for MySQL Server (the not-cluster version...)? A couple of months ago Kazuho Oku did something like that and wrote in his blog about it.

The context for Kazuho's work is the social network use case: 1) You have users. 2) Some users are linked to each others as friends. 3) When a user logs in, he should see a timeline of events/messages from his friends. In a previous post he had already tested the difference between a …

In the previous post we had a look at the MySQL Cluster NDB API and how it enables direct access to the MySQL Cluster data nodes, and therefore also enables access through other protocols than SQL.

I've often asked myself: Since NDB is so great for MySQL Cluster, is there anything similar for MySQL Server (the not-cluster version...)? A couple of months ago Kazuho Oku did something like that and wrote in his blog about it.

The context for Kazuho's work is the social network use case: 1) You have users. 2) Some users are linked to each others as friends. 3) When a user logs in, he should see a timeline of events/messages from his friends. In a previous post he had already tested the difference between a …

Before I leave Grazr to work at Lycos, I've been tasked with documenting several things-- MySQL setup, including replication, nagios, UDFs we use, etc. I was describing on our wiki what using InnoDB means, and some basic things about settings. Here's a tidbit of it that I think is useful information.

Some important InnoDB Characteristics

InnoDB stores data and files in the same place whereas MyISAM has separate index and data files. InnoDB stores it's data in either a single tablespace file (ibdataN -- n being number) which containins all tables, or a single auto-extending tablespace file set by innodb_file_per_table (tablename.ibd), which is what we use for grazr because file-per-table allows OPTIMIZE TABLE to reclaim space from deletions, would could gives better performance with better optimized tables. Also allows you to restore backups of single tables without interrupting the use of the remaining InnoDB tables (per …

To have online backups of MySQL, We recently bought a license for InnoBase/Oracle's InnoDB Hot Backup Tool, ibbackup. This tool, used in conjunction with innobackup, has worked great in creating a nightly backup, with no downtime during the backup. Not even nagios messages!

I run innobackup/ibbackup on one of our slaves (well, it's also a dual master, but not used by apps). innobackup produces a backup in a directory that I specified, and when run results in a time-stamped directory, as show below:


ls -l 2008-09-17_03-00-03/
total 276272
-rw-r--r-- 1 root root 349 2008-09-17 03:00 backup-my.cnf
drwxr-x--- 2 root root 4096 2008-09-17 03:55 grazr
-rw-r--r-- 1 root root 27 2008-09-17 03:55 ibbackup_binlog_info
-rw-r----- 1 root root 186109952 2008-09-17 03:55 ibbackup_logfile
-rw-r----- 1 root root 10485760 2008-09-17 03:00 ibdata1
-rw-r----- 1 root root …

I just got back from vacation and noticed that two patchsets have been released that greatly improve Innodb performance! Maybe I need to take more breaks

Yasufumi Kinoshita's patches Percona recently released a patch which includes performance fixes developed by Yasufumi Kinoshita from NTT Comware. This helps diskbound applications quite significantly. Details at Bug #29413 Maximum performance of OLTP benchmark is not so scalable on multi-cpu. It looks like the bulk of performance improvements come from breaking up the lock guarding the buffer pool structures, and improvements in the IO code path. The "buf_pool->mutex" also gets quite hot when concurrency is not limited (via innodb_thread_concurrency) and …

I just got back from vacation and noticed that two patchsets have been released that greatly improve Innodb performance! Maybe I need to take more breaks

Yasufumi Kinoshita's patches Percona recently released a patch which includes performance fixes developed by Yasufumi Kinoshita from NTT Comware. This helps diskbound applications quite significantly. Details at Bug #29413 Maximum performance of OLTP benchmark is not so scalable on multi-cpu. It looks like the bulk of performance improvements come from breaking up the lock guarding the buffer pool structures, and improvements in the IO code path. The "buf_pool->mutex" also gets quite hot when concurrency is not limited (via innodb_thread_concurrency) and …

I just got back from vacation and noticed that two patchsets have been released that greatly improve Innodb performance! Maybe I need to take more breaks

Yasufumi Kinoshita's patches Percona recently released a patch which includes performance fixes developed by Yasufumi Kinoshita from NTT Comware. This helps diskbound applications quite significantly. Details at Bug #29413 Maximum performance of OLTP benchmark is not so scalable on multi-cpu. It looks like the bulk of performance improvements come from breaking up the lock guarding the buffer pool structures, and improvements in the IO code path. The "buf_pool->mutex" also gets quite hot when concurrency is not limited (via innodb_thread_concurrency) and …

In a previous post, I was trying to figure out the most optimal way to switch from two large innodb table space files to using innodb_file_per_table to take advantage of some of the benefits of this setting. I had one part of it solved, which was to stop MySQL, add innodb_file_per_table to the my.cnf, then restart, perform a "no-op" alter of "ALTER TABLE t1 ENGINE=InnoDB" which would cause the table to be re-created an it's own .ibd file. The remaining problem was how to be able to resize the huge table space files after converting all the tables to a smaller size (in my case from 10GB to 10MB).

Someone suggested a better way:

1. Alter all innodb tables to MyISAM
2. Stop the server
3. Add innodb_file_per_table to my.cnf
4. Change innodb_data_file_path to new settings (10MB tablespaces) in my.cnf
5. Move all innodb files (logs, data) to a backup directory
6. Restart MySQL
7. Alter …

Description:
The drupal_lookup_path function unnecessarily counts all rows of the url_alias table to determine if any aliases are defined. This can be expensive on a transactional database such as MySQL with the InnoDB Storage Engine or PostgreSQL when a website has a lot of aliases. This patch modifies the query to always only return one row, possible because any existing pid will be greater than 0. If no aliases are defined, it will return 0.

Status:
This patch has not been merged into any release of Drupal.

Patch

read more