Random bits of info

I've got some queries that run for a long time. Sometimes longer than others. I wanted to see how long active queries have been running. Here's a view to consistently do that: CREATE VIEW public.view_activequeries AS ( SELECT age(query_start, backend_start) AS queryage,* FROM pg_stat_activity WHERE current_query NOT LIKE '%IDLE%' ORDER BY queryage DESC); Then you just run: select * from public.view_activequeries; You get a list of all active queries in descending order of how long they have been running, i.e. longest running queries listed first.

--->  All credit goes here  <--- In case that ever disappears: place in .bash_profile ####SSHagent settings#### SSH_ENV="$HOME/.ssh/environment" function start_agent {      echo "Initializing new SSH agent..."      /usr/bin/ssh-agent | sed 's/^echo/#echo/' > "${SSH_ENV}"      echo succeeded      chmod 600 "${SSH_ENV}"      . "${SSH_ENV}" > /dev/null      /usr/bin/ssh-add; } # Source SSH settings, if applicable if [ -f "${SSH_ENV}" ]; then      . "${SSH_ENV}" > /dev/null      ps -ef | grep ${SSH_AGENT_PID} | grep ssh-agent$ > /dev/null || {          start_agent;      } else      start_agent; fi

I needed a quick way to send an email when a script was done. Did some digging around and converted another CGI script to a plain perl script and finally got the syntax correct.  #!/usr/bin/env perl my $sendmail = "/usr/sbin/sendmail -t"; my $reply_to = "Reply-to: replyto\@addresshere\n"; my $subject = "Subject: Some amazing subject\n"; my $send_to = "To: destination\@addresshere\n"; open (SENDMAIL, "|$sendmail") or die "Cannot open sendmail: $!\n"; print SENDMAIL $reply_to; print SENDMAIL $subject; print SENDMAIL $send_to; print SENDMAIL "Content-type: text/plain\n"; print SENDMAIL "Content here. You could also put the content in a variable above."; close(SENDMAIL);

Via : http://communities.vmware.com/thread/195941 sudo /Library/Application\ Support/VMware\ Fusion/boot.sh --restart

The documentation is a little off. To alter a resource queue in Greenplum run the following: ALTER RESOURCE QUEUE queue_name COST THRESHOLD your_new_threshold; The documentation states that you use WITH: ALTER RESOURCE QUEUE myqueue WITH (MAX_COST=3e+10); That, however, never worked for me. If you run \h ALTER RESOURCE QUEUE from a psql prompt, it gives you the proper syntax.

We run daily dumps on our PostgreSQL databases. The command we use to dump the databases is: pg_dump --blobs --compress=9 --format=c --verbose DBNAME --file=DUMPFILE.c I recently had to restore one of the dumps to a different server and kept receiving the error: ERROR: invalid byte sequence for encoding "UTF8": 0x93 I discovered that my original system had the database encoded as SQL_ASCII and the new system was encoding all new databases as UTF8. So I ran a create database with the proper encoding: CREATE DATABASE newdbname WITH ENCODING 'SQL_ASCII'; Then restored the file using pg_restore: pg_restore --dbname=newdbname DUMPFILE.c

Recently started loading data into a brand new Greenplum DCA. Data load speeds should be blazing fast right? Well, ours were very slow. It  was taking 50 seconds to load a 2.6GB csv file, pitifully slow. I finally figured out what error I had made. We were moving data from one GP system to another. That process involved dumping the schema out of the production system, dumping the data out of the production system, restoring the schema in the new contingency system, then restoring the data into the new contingency system. The problem was that when we restored the schema into the new system, we restored everything, including indexes. So as I was attempting to load the new data, the system was indexing it at the same time. After dropping indexes, that 2.6GB csv file loaded in about 8 seconds. That's more like it.

Had a user submit a query recently: select count(*) from (select * from viewA UNION select * from viewB) viewA pulls from two tables with a combined 1.5 billion rows and around 60 columns. viewB pulls from a table with about 300 million rows. I rewrote the query as follows: select (select count(*) from viewA) + (select count(*) from viewB) AS count Same result but the first query has an explain plan with a cost 26X the second. Second query runs in about 3 minutes.

I did not come up with this. A quick google search turns up multiple ideas. I combined a few and trimmed things down to how I wanted the title to look. Here's my final result: case $TERM in   (xterm*)   export PROMPT_COMMAND='echo -ne "\033]0;${USER}@$(hostname -s)\007"'   ;; esac I added that code to ~/.bash_profile. Now my Terminal title changes when I open a Terminal or ssh into a machine then exit back to my Mac.

I have an earlier post where I list how to update table A from table B. In that example the WHERE clause sets tableA.column=tableB.column. That works perfectly as long as your tables are DISTRIBUTED BY(column). If one, or both, are distributed randomly, you are out of luck. At least this applies to 3.3.x. I haven't tried this on a GP 4.x setup. I had tableA distributed randomly and when I tried to redistribute by column I would get gang errors. Here's the fix I implemented. Run a pg_dump --schema-only on tableA. Run ALTER TABLE and rename tableA to tableA_orig. Edit the schema dump of tableA and change distributed by from randomly to (column). Run the schema dump to recreate tableA distributed by column. Populate the new tableA with, INSERT INTO tableA SELECT * FROM tableA_orig. There's a few other things to be aware of. You'll need to redo the indexes on tableA or edit the schema and change the name of any indexes because the original indexes will now be associated w...

Starting in the 4.x release, you get size info from the gp_toolkit schema. To get the size of all databases and their size in bytes: select sodddatname, sodddatasize from gp_toolkit.gp_size_database; To see the database size in GB, TB, and/or MB. TB: select sodddatname, (sodddatsize/1073741824)/1024 AS sizeinTB from gp_toolkit.gp_size_of_database; GB: select sodddatname, (sodddatsize/1073741824) AS sizeinGB from gp_toolkit.gp_size_of_database; MB: select sodddatname, (sodddatsize/1048576) AS sizeinMB from gp_toolkit.gp_size_of_database; For schema sizes , connect to your database and run: TB: select sosdnsp, (sosdschematablesize/1073741824)/1024 AS schemasizeinTB from gp_toolkit.gp_size_of_schema_disk; GB: select sosdnsp, (sosdschematablesize/1073741824) AS schemasizeinGB from gp_toolkit.gp_size_of_schema_disk; MB: select sosdnsp, (sosdschematablesize/1048576) AS schemasizeinMB from gp_toolkit.gp_size_of_schema_disk; If you want a specific schema only, add ...

Ran into a problem with being unable to log into an OS X machine joined to an AD domain. I had previously logged in successfully with this account. Logged in as a local administrator on the machine and noticed the account was no longer listed in System Preferences/Accounts but other domain accounts were, odd.  Ran dscl . -list users and the account was listed. There was also a directory structure under /Users/userid. Ran dscl . -delete /Users/userid and was then able to successfully log in and the home directory was untouched so no data was lost.

I noticed recently that some queries on a table were running very slow. Simple counts were taking longer on what appeared to be smaller tables. I say appeared because the tables had 1/5 the number of rows as other tables but queries were slower. The raw data files contained about 7 GB of data. To see what the Greenplum system had for table size I ran this query: select pg_size_pretty(pg_relation_size('schema.table_name')); The answer was 190GB! Clearly there were problems.  This table does get reloaded every month with new data but I truncate the table before reloading it so you aren't supposed to run into issues. Anyway there turned out to be a couple of solutions. One was to run a vacuum full on the table. After running that the table size was reported as 5.635 MB. I did try a vacuum on the table but it had no impact on size. Another solution is to redistribute the data randomly then redistribute by the table key. ALTER TABLE schema.table_name SET DISTRIBUTED RANDOM...

Here Nuts and bolts: 1. Once you've pulled Lion down from the Mac App Store, right-click on the installer and select the option "Show Package Contents." This is your Mac's way of tearing the wrapping off a virtual install disk to access all the shiny bits (aka "files") inside. 2. Open the "Contents" folder, then look for a "SharedSupport" folder and open that. Inside, you'll find something called "InstallESD.dmg." This is the money file we're looking for (or the "master control program" if you're a Tron wonk). 3. Copy that file ("InstallESD.dmg") to a folder outside of the installer (your desktop works). 4. Open the "Utilities" folder on your Mac and launch "Disk Utility." 5. Select "Burn" from the "Images" menu option, or just click the yellow and black icon on the menu bar (which, disturbingly, looks just like the official symbol for nucle...

I am having a problem with my Mac where the right side of the menu bar will freeze when I run VMware. The problem is that my clock freezes and I lose track of time. While this isn't a solution for the overall problem, I found a fix that allows me to reset the menu bar and get my clock back on track. Simply run the following command in a Terminal: killall SystemUIServer

Every so often I update a Drupal site and need to update a bunch of modules/themes at the same time. Fortunately, the naming convention of modules/themes is consistent so I can update many with one little script. First, scp all the module/theme gz files to the location you want to store them. Next, cd into that directory. Last, run this script: for thm in $(ls *.gz) do         thmnew=$(ls ${thm}|cut -d'-' -f1)         tar -cf ${thmnew}_old.tar ${thmnew}         mv ${thmnew}_old.tar /usr/local/src/drupal/tmp/themesupgraded/         mv ${thmnew} /usr/local/src/drupal/tmp/themesupgraded/         tar -zxvf ${thm}         mv ${thm} /usr/local/src/drupal/tmp/themesupgraded/ done

Hallelujah! These drive me nutty some times. Highlights: Open Terminal  defaults write com.apple.dock no-bouncing -bool TRUE  killall Dock To reenable: Open Terminal  defaults write com.apple.dock no-bouncing -bool FALSE  killall Dock Source

I wanted to take a look at the total amount of data that would be transferred for an rsync operation. Running  /usr/bin/rsync --dry-run -avhz --delete -e ssh /sourcepath destserver:/destpath would give the the Total Size of the /sourcepath but not the amount transferred. Adding --stats will give you the amount of data that needs to be transferred to get the /sourcepath and /destpath in sync /usr/bin/rsync --dry-run -ahz --stats --delete -e ssh /sourcepath destserver:/destpath

Ever try to run ssh-add and get this message? Could not open a connection to your authentication agent Run this command exec ssh-agent /bin/bash or just add that to your .bash_profile so it runs on login.

There's a known bug in CentOS 5.x that causes install to fail if you select the Extras repositories during the setup process. So DO NOT chose the Extras repository when you install.