MySQL Cluster: Generating Partition Reports

If you're using MySQL Cluster and have recently added a nodegroup, you can use the following code to generate a report of what tables are currently partitioned across the nodes as you apply ALTER ONLINE TABLE ... REORGANIZE PARTITION to them to get them partitioned:

select count(p.table_name) as partitions, p.table_schema, p.table_name from tables t join partitions p on (t.table_schema=p.table_schema and t.table_name=p.table_name) where t.engine='ndbcluster' group by p.table_schema, p.table_name order by partitions desc;

You'll get a nice clean report of all your tables, what schema they belong to, and how many partitions they have. Use in combination with my method of finding the largest tables in MySQL and you'll ensure smooth growth of your cluster!

Find Largest Tables in MySQL

If you're trying to find what tables in your MySQL deployment are consuming the most amount of space, you can use the following query to find this information directly from the information schema.

SELECT CONCAT(table_schema, '.', table_name), CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows, CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA, CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size, ROUND(index_length / data_length, 2) idxfrac, engine FROM information_schema.TABLES ORDER BY data_length + index_length DESC LIMIT 25;

You'll get a list of the top 25 tables by total size (index size + data size), how many rows they have, and the engine they are using to be stored. Being able to see what engine is being used is especially helpful when running MySQL Cluster.

Coding Contest: Shortest Full-featured CMS, BB, or Blog

There's a large number of Content Management System, Bulletin Board, and Blog solutions available, all with amazing functionality that simply can't be missed on today's rapidly advancing internet(s).

Examples
CMS: Joomla, with around 280,000 lines of code.
BB: phpBB, with around 150,000 lines of code.
Blogs: WordPress, with around 170,000 lines of code.

My challenge is this:
What is the smallest full-featured CMS, BB, or Blog that you can create?

Contest submissions must include the following features:

• User Accounts
• Article Posts (or "Topics" in BB-land)
• Comment System

Submissions will be accepted in any language, so long as the content can be served up over HTTP. To submit, comment on this post with a link to your project!

Good luck and happy coding!

Tuning Large phpBB3 Forums

One of the sites I own and run (RolePlayGateway) has a pretty large forum, with several customizations and features that I've added on to the base install of phpBB3. As time went on, we continued upgrading the servers (moving from GoDaddy shared hosting to GoDaddy virtual dedicated servers, then onward to MediaTemple's (gs), and now we're on the second tier of MediaTemple's (dv) hosting) in an effort to keep the hardware moving as fast as possible.

As I'm sure you know, hardware can be pretty expensive! One month, while on MediaTemple's Grid Server, we racked up $600 in CPU time overage charges. (Ow.) Now that we've moved onward to bigger and better packages, we're shelling out just about $100 per month for a rock-solid server solution that can be upgraded seamlessly in the future. But since upgrades can only go so far without being prohibitively expensive, I thought it was time to take a look at some of our coding approaches.

Enter memcache, the distributed database caching solution originally designed by LiveJournal to help them deal with massive databases and large volumes of users. DavidMJ has written some shiny ACM modules to help phpBB3 make use of some caching systems, and a memcache module was among them.

That didn't work so well. It gave about a 50% boost to phpBB3's performance (which was great!), but we were still choking the server, and ended up upgrading to a bigger and more robust package with MediaTemple. So I started looking into more options, and DavidMJ suggested xcache. So I go grab xcache and compile it, then enabled it in php. Bingo! There's a 500% boost in our page compile times, and across most of our pages we're now well under 0.1 second compile times. (With the exception of viewtopic.php, which frequently approaches 2 seconds due to bad coding on my part... this will be fixed soon.)

So now that I've got the thirst for speed, let's take a look at how we're performing. To do this, use the apache benchmarking tool:ab -n 100000 http://www.mydomain.com/my_page
This will test the URL you specify 100,000 times, and give you some feedback about how the page performs. You'll end up with something looking like this:

Server Software: Apache/2.2.3
Server Hostname: www.mydomain.com
Server Port: 80
Document Path: /my_page
Document Length: 0 bytes
Concurrency Level: 1
Time taken for tests: 15.30100 seconds
Complete requests: 1
Failed requests: 0
Write errors: 0
Non-2xx responses: 1
Total transferred: 715 bytes
HTML transferred: 0 bytes
Requests per second: 0.07 [#/sec] (mean)
Time per request: 15030.100 [ms] (mean)
Time per request: 15030.100 [ms] (mean, across all concurrent requests)
Transfer rate: 0.00 [Kbytes/sec] received
Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 0.0 0 0
Processing: 15030 15030 0.0 15030 15030
Waiting: 30 30 0.0 30 30
Total: 15030 15030 0.0 15030 15030


Some tweaks to the default xcache config that I recommend:

Set the number of caches to one per processor on your server!
; set to cpu count (cat /proc/cpuinfo |grep -c processor)
xcache.count = 4

This post will be updated as I explore phpBB3 and more server side options. (I wrote part of this post, then stopped writing... and figure I'd publish it a couple days later anyway!)