Posted: June 10th, 2010
By: Tommy Unger
I was surprised that I didn’t easily find a query which gives me all of the table sizes for all of my Postgres database table. I did find this nice bit:
SELECT pg_size_pretty(pg_total_relation_size(table_name))
But I wanted to take it a step further and get the list of the largest tables in my database. This query worked like a charm:
SELECT table_name, pg_size_pretty(pg_total_relation_size(table_name))
FROM information_schema.tables
WHERE table_type='BASE TABLE'
AND table_schema = 'public'
ORDER BY pg_total_relation_size(table_name) DESC
Finally, I wanted to know if any of these tables had a column name containing the text “org id”.
SELECT t.table_name, pg_size_pretty(pg_total_relation_size(t.table_name))
, case when cn.table_name is not null then ‘Yes’ ELSE ” end as “has_col”
FROM information_schema.tables t
LEFT JOIN
(select distinct c.table_name
from information_schema.columns c
where c.column_name LIKE ‘%org%id%’) cn on cn.table_name = t.table_name
WHERE t.table_type=’BASE TABLE’ AND table_schema = ‘public’
ORDER BY pg_total_relation_size(t.table_name) DESC
;
Posted: December 1st, 2009
By: Tommy Unger
MySQL 5 conveniently comes with a windows installer which works flawlessly to get the novice user up and running on the ubiquitous open source database. However, another way to put mysql onto a windows box is without the installer by just downloading the zipped binaries.
Once you’ve unzipped the binaries, you have a few steps to take:
Step 1. Run mysqld –install – This will setup the mysql daemon as a windows service. It will look in your windows base directory %WINDIR% for a my.ini file. (On windows the my.conf file is called my.ini, and I have no idea why this is)
Step 2. Open regedit. And change this key
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\MySQL\ImagePath to this:
“<path-to-mysql>\bin\mysqld” –defaults-file=”<path-to-mysql>\my.ini” MySQL
or this, if you just want to use one of the default medium sized instance ini file:
“<path-to-mysql>\bin\mysqld” –defaults-file=”<path-to-mysql>\my-medium.ini” MySQL
Posted: July 16th, 2009
By: Tommy Unger
This post Survey: People really do click on spam ads really got me thinking about a few things. First, my history.
Back in some year, let’s say 1998 or maybe 1997 I got my first portal email address (had an aol address and a college address before that). It was tommy_unger@yahoo.com . Yes, I’m putting the email right out there for all of the bots to parse, and I expect the spam onslaught continue. The address is absolutely worthless because it is 99% spam! Much of it in foreign languages (arabic or persian or something from that part of the world seems popular these days. Then of course there’s the cryptic meaningless text, or just “Hi, come to my website so we can have sexy time”. My 8 month old son could probably detect spam better than Yahoo’s algorithms.
Then, finally, came gmail. I signed up with a better address. Same name as above without the underscore, and low-and-behold, I see about one spam message a month. I remember seeign a video a few years back about gmail’s spam detection and it seems right on the money. What I also like is that gmail also rarely(maybe never) has the false positive case where they dump an important message into the spam bucket.
So, back to the article I began with… It mentions:
few users ever click the “report as spam” button on their email client; most just delete spam messages as they arrive (and fail to help train their spam filters in the process)
I’ve got a hunch that gmail has higher rates of this “report as spam” click than the other email clients. I also have a feeling that yahoo mail’s “report as spam” button is just a black hole that ever gets used to clean up anyone’s spam.
Combine gmail’s possible higher “report as spam” click rate with a probable much better algorithm and/or system and you get a much nicer web mail experience in my opinion. So, thanks yahoo tech blog (you still do content well, of course), for reminding me why I love gmail.
Posted: March 24th, 2009
By: Tommy Unger
I don’t care if this is a nerdy post or if it’s duplicated all over the internet. I just want to put it out there for reference. Here’s the linux/unix/cygwin code for recursively removing subversion directories.
rm -rf `find . -name .svn`
I found the example here, but you can never have too much duplicate content floating around the internet.
