Tech Thoughts, Mostly on LAMP - by Jon Haddad
Altering a big table sucks, and to make it worse you have no idea what’s happening or how long it will take. I’d like a progress bar, or some status output, or something that gives me the feeling like my server didn’t die.
I was poking around the MySQL Worklog again over the weekend, and found a request for materialized views for MySQL. This feature has existed in Oracle for a while, in DB2 as a materialized query table, and appeared in MS SQL Server 2000 and 2005 as indexed views.
What is a materialized view?
A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. (from Oracle).
Essentially a materialized view lets you tell the database to periodically refresh a table with the results of a query. You may join, group, and perform calculations. The goal is to increase query performance in a read-heavy environment.
Additionally, at least in the other DBs listed, you can put indexes on the fields within the view.
Disadvantages:
According to the high level architecture
Support creation of materialized views, with only the bare minimum — no automatic refresh, no query rewrite.
Which would be a shame, because it seems that adding in automatic refresh would be a pretty small part of a very complex feature. I haven’t dealt with any of the MySQL codebase so this is just my speculation.
Of course, when looking at any feature, it’s important to determine how useful it actually would be to implement. What is the target audience, and are they the existing customers? What’s the goal of adding this feature?
For ther reading, Database journal has a very good overview of indexed views in SQL Server.
I’ll keep it short. In the last few days, login on our dev server broke. We hadn’t changed anything related to it, and everything looked good code wise. What we finally figured out was that our session cookie was set to expire 2 days into the future, and our VMWare image had lost 2 days of time.
Fix by doing the following:
ntpdate ntp.nasa.gov > /dev/null
I was cruising the MySQL Forge Worklog when I came across the idea of Time Delayed Replication. I had never considered the benefits of deliberately keeping a slave server behind a master.
Kristian Koehntopp gives a good example:
Kristian Koehntopp writes:
TDS: Time delayed SQL_THREAD (Have a replication slave that is always
lagging 30 minutes behind).Currently, replication is a rolling recovery: To set up replication you
restore from a full dump with a binlog position. You then continously
download binlog and roll forward. In case of a master crash a slave is a
readily recovered instance (as opposed to a backup, which still has to be
restored).This protects against crashes, but not against oopses.
A time delayed slave (TDS) is a nice protection against oopses.
Sugar on top addendum: With the binlog being a table, any table type,
it will be very easy to delete an oopsing statement out of the
unapplied binlog queue. Currently, a simple time delayed slave will
protect you against oopses, but it will be very hard to extract the
relevant binlog portion out of the TDS replication log skipping the
oopsing statement.With a MyISAM binlog table, it is just a matter of DELETE FROM
REPLICATION.BINLOG WHERE STATEMENT_ID = 1717;
Definately a cool idea. Instead of having to restore from a backup, you just take down your master, pull out the busted query, let the server catch up, and bring the slave up as the new master. Doing a restore from our current database backups takes about 3-4 hours, and we’re not even huge. It seems like your downtime here would be limited to however long it takes your slave to catch up. Additionally, once you take down your master and remove your bogus query (truncate table perhaps?) you can allow the slave to replicate everything and catch up (no longer limited to 30 min behind, ideally). Depending on your traffic, this could limit your downtime in the case of a catastrophic loss to 20 minutes or so instead of hours, and your data loss would be far less.
I was curious how many lines of code were in PHPBB 3. I only wanted to know about the .php files.
find . -name ‘*.php’ -exec wc -l {} \; | awk ‘{ SUM += $1 } END {print SUM}’
The downside to this is that it includes whitespace and braces as lines, as well as comments. Oh well. It’s a good approximation.
Edit: There are 172,189 lines of code in phpbb3.
I posted a complaint a while ago about BBEdit complaining about a few things. I moved onto TextMate for a few weeks, but it lacked some of the powerful BBEdit features I’ve gotten used to.
Then I discovered ctags. I set up a cron on my PHP dev site to rescan my dev folder once an hour. BBEdit automatically discovered my ctags file and gave me the definitions right a right click.
Ctags is available through yum (yum install ctags).
ctags –languages=php –recurse=yes
I’m not sure how I didn’t see this earlier, but it looks like MySQL 5.1 is coming with a load balancer for replicated servers. I’m absolutely pumped about this - we’ve got a few sites running with multiple db slaves and it’s so annoying having to check if they’re behind the master.
It looks like the load balancer will automatically pull slaves out if they fall behind, and route connections to the ones that are the most up to date. It is based on MySQL Proxy, which is currently in Alpha.
I got this today. Solved by restarting the Memcached server. Move along.
Edit: this is actually a reoccurring bug we’re seeing with the memcache 2.2.3 stable build on 2 different boxes
2nd Edit: Actually it was a bug in my code. I wasn’t setting the server and ip correctly, there was a typo in my configuration
If you do any work with single sign on, you’ll be familiar with the concept of exchanging tokens and validating against the authentication server using that token. One of the issues I’ve just run into which resulted in a huge headache is with urlencoding the result of a curl_exec that had a line ending. It’ easy to miss when it’s a longer string and you aren’t paying very close attention. This is a very simple example, and it still takes a second to realize there’s an extra character at the end.
php> echo urlencode(”test@str!ngw!th0u7\n”);
test%40str%21ngw%21th0u7%0A
php> echo urlencode(”test@str!ngw!th0u7″);
test%40str%21ngw%21th0u7
Basically, I’d recommend calling a trim() on any results you get back, unless you love newlines at the end for some reason.
This is exactly what I look for when I’m trying to find a tutorial. Thank you, Andrew M. Ross.
My favorite awk tutorial ever.