This is something you shouldn’t have to learn the hard way: How to create a ratings system without using aggregate functions. Specifically, what I’m talking about by an aggregate, is the AVG() SQL function. The problem with using AVG(), is it reads every row in the table, which works well when you’re not dealing with very many rows, but as your data grows, the query speed progressively slows down. This is because aggregate functions essentially perform a sequential scan on the table. The solution for this, is to store the average for each individual item in a table, and use simple math calculations for updates.

Such a table may look something like this:

CREATE TABLE item_ratings (
    item_id INTEGER NOT NULL PRIMARY KEY,
    rating DOUBLE PRECISION,
    votes INTEGER NOT NULL DEFAULT 0
);

CREATE INDEX item_ratings_rating_key ON item_ratings (rating);
CREATE INDEX item_ratings_votes_key ON item_ratings (votes);

There’s a number of options for adding the initial entry, that updates will eventually be done on, such as doing it in code or via a trigger/rule. For the sake of simplicity, lets say there’s already a blank entry for item #1.

INSERT INTO item_ratings (item_id, rating, votes) VALUES (1, 0, 0);

Now, instead of adding a new entry for each vote cast, you simply update the previous entry. Lets say someone casts a vote, giving it a 4 star rating. That would be handled as such:

UPDATE item_ratings SET
    rating = ((rating * votes) + 4) / (votes + 1),
    votes = (votes + 1)
    WHERE item_id = 1;

SELECT * FROM item_ratings WHERE item_id = 1;
 item_id | rating | votes
---------+--------+-------
      1          4        1

That’s pretty much it. Now all ratings are stored per item, instead of generated by an aggregate. This can save major headaches when dealing with large datasets, since AVG() in PostgreSQL tends to be slow when working with such datasets.

I’m experiencing a number of different issues. For one, the connection speed is wildly inconsistent. Sometimes I get 5mbps, other times I get under 1mbps. The connection is always dropping, and reconnecting. Sometimes it spends 30+ minutes disconnecting and reconnecting, before it finally gets a stable connection. Large downloads are constantly being interrupted because of the connection dropping.

This is crazy. I’m giving them another angry phone call on Monday.

There’s many different methods to use caching, with memcached. I call this one the Aggressive approach.

The basic logic is this: if you don’t even touch the database, you will be able to serve far more requests, than if you did. The biggest downside is, you can’t really do things too dynamically, and really, this only works well for flat pages (ie., it’s not for anything that displays content, based on the user).

In my case, I added the following code (shown as pseudo code) to the main/controller/dispatch script:

$memcache = new Memcache;
$memcache->connect(...);
$data = $memcache->get($_SERVER['REQUEST_URI'])

if ($data) {
    echo $data;
    die();
 }


$db = new PDO(...);
ob_start();
ob_implicit_flush(false);
...
... do whatever ...
...
$data = ob_get_contents();
$memcache->set($_SERVER['REQUEST_URI'], $data, 0, 3600);
ob_end_flush(); 

One of the biggest downsides to this approach, is figuring out how to delete cached objects when something’s updated. If you have a single table that holds the pages that you’re caching, however, you can just add a hook that deletes the cached object, by URI, when anything is written that would affect it.

This really doesn’t work well for anything super dynamic, but it’s great for increasing performance in areas that would otherwise waste resources.

Qwest finally got my Internet working, *phew*, after an agonizing 13 days. Now a new problem has reared it’s ugly head: My internet connection is ridiculously slow. I mean seriously slow. My gateway shows the downlink speed at 1100kbps. At my old apartment, it was usually around 6900. What’s worse: my actual connection speed is slower than what the gateway displays.

SpeedTest.net gives me an average of ~800kbps, just barely faster than my uplink speed! The meter jumped wildly between 1200kbps and 200kbps, during the test. This is absolutely ridiculous. I haven’t called yet, but I’m hoping it will end up being a simple fix. This whole experience has left me dismayed and flabbergasted. This is the first time I’ve ever considered Comcast as a serious alternative.

So, when it came time to move, I called Qwest to have them transfer my services. They were supposed to be setup when I arrived, and guess what… They weren’t. Not only that, but I had to wait on the phone for 2 hours, just to schedule them to come by, at their convenience, to fix the problem, while listening to “Your call is important to us”… Yeah right, more like your money is important to us.

Not only that, but it took them a week just to show up. Not only that, when they finally got around to hooking everything up, they routed me to the wrong ISP. And so here I am, waiting 10 days, and counting, for them to fix the problem. The only reason they can get away with this, is because they are a monopoly.

I’ve been really discouraged by the overall lack of decent solutions for minimizing SSH brute force attacks. There’s the typical and obvious one’s, that just about every article states, like setting AllowUsers/DenyUsers, and setting PermitRootLogin = no. The problem with those, is while they may strengthen security, they don’t stop bots from trying to brute force your server, which does nothing from a performance standpoint. Since having multiple bots trying to brute force your server can cause server load to go through the roof, this can be very important.

Disabling passwords, and only accepting key based logins can make you more secure, however, it still doesn’t deal with performance issues. And also, if you have a drive failure, and loose your SSH key, how do you login? If nobody else can make you a new key pair, and login to copy it over, then you’re locked out of your own server. Also, how do you login from someone else’s computer?

You can move SSH to a different port, but then you have to update the config for all your users. This can be a big pain in the ass to handle, but it can solve both the security and performance issues with brute force attacks. Overall it’s pretty weak, though, since some bots have started probing other ports, like 2222, to see if SSH is running on it. It surely wouldn’t work for anything big.

Then there’s log-based learning, like fail2ban. I have a problem with this too, as log injection attacks can be used to trick the learning daemon into causing a denial of service attack. Overall, this is a pretty weak solution too. There’s already been proof-of-concept log injection attacks for just about every log based learning daemon, and there’s sure to by more in the future.

And on to iptables based solutions. By filtering at firewall level, you can see clients that are trying to connect to SSH. By far, the biggest weakness of this setup is it can’t determine if a login was successful or not, it can only see SYN packets. If you have it setup to block clients that try to connect 3 or more times in a minute, then three successful logins would cause a legitimet client to be blocked. While it may sound absurd that a client would login that frequently, it isn’t when you consider that tab-completion is available in SSH and SCP, and for each tab-completion event, SSH logs in then back out; the limit can easily be exhausted, and cause legit clients to be blocked.

There really needs to be a better solution. It seems like the only real way to solve this, is to build some kind of configurable adaptive firewall into SSH, itself, to detect and deny brute force attacks.

When working with large columns, you can run into some indexing issues. For instance, there’s a limit on how many characters a btree index entry can use, which poses problems for indexing text fields, since inserts and updates will fail if they use more characters than the index can use. Also, you can run into performance and disk consumption issues with large columns. The solution for this is to index columns using the hashtext() function. so, ie:

CREATE INDEX table1_column1_hash ON table1 hashtext(column1);

Then in your queries, do:

SELECT * FROM table1 WHERE hashtext(column1) = hashtext('foo');

There’s some issues with this solution, mainly being that you can only have exact matches for the column, so you can’t do range scans or LIKE queries, however, if you need searching features you should probably be using full text searching, which is available in PostgreSQL 8.2+.

Since everyone else seems to have a list of how to speed up PHP Apps, I thought it was about time I made one. Here’s some basic rules for speeding up PHP Applications:

• Avoid including large files/classes. The more PHP has to process, the slower it is. A lot of frameworks have needlessly large classes. It’s recommended that you split things up into smaller “modules” and include them as needed. Also, only include what you need. PHP has to process everything, even the stuff you don’t use. With some sites, I’ve seen the number requests per second increase, 4 times, simply by moving away from needlessly large frameworks and unneeded includes.
• Use PHP’s built-in functions whenever you can. PHP has a lot of functions for working with strings, arrays, etc. A lot of times, they can save you headaches, and improve performance over user created functions.
• Avoid Regular Expressions. Instead, use functions like str_replace, in place of preg_replace, or strstr, in place of preg_match, when possible.
• Be careful of the way you reference objects. PHP is known to have trouble with garbage collection under certain situations, such as when you’re dealing with classes that are circular-referenced. These types of situations usually go unnoticed, until PHP start exceeding it’s memory limit.
• Avoid functions like file_get_contents(), and file_put_contents(), when working with large files… Unless you want to run out of memory.
• Use memcached, aggressively if possible. Hmm, lets see, 100 requests/second vs 1000 requests/second… On the same server. It’s a no brainer.
• Don’t ever use persistent connections. They may seem like a good idea, and they may appear to make your app faster in benchmarks, but it will severely cripple your ability to handle high traffic… Let me explain, persistent connections and connection pooling aren’t the same. Persistent connections are per user, and users will often end up with 2 or 3 persistent connections open for them. Nobody else can use these connections; this leads to your server running out of database connections with a relatively small number of users (think 40-60). If you really need this functionality, use an actual connection pooler.
• Avoid making your database grumpy; most bottlenecks are related to the database. This typically means avoiding sequential scans, aggregate functions, etc. With MySQL, avoid complex JOINs, as well as date range scans, and “WHERE column IN (SELECT …)” queries. With PostgreSQL, avoid “SELECT COUNT(*)”, and keep the total number of queries to a minimum.
• Get more servers. Single servers can only handle so many requests per second. When you get to a point where your server is struggling under the load, and there’s no clear bottlenecks, the only thing you can do is scale to your needs.