Haven't been playing admin with a Windows machine for a long time, I was surprised to see that it still sported the same obscene-looking, obscure, cryptic registries. The problem started when I wanted to install the AT&T Global Network client last Sunday so I can access office network (it's a MS shop, sad, I know) from home.
The installation was successfully completed without any error message presented to me, but I couldn't figure out my password. I would have left the program as it was, but I felt booting in took longer since I installed it. So, I uninstalled it and didn't find any noticeable difference.
Came Monday at work. I put the laptop on its dock and was going to start my workday when I found out that while it can connect to some hosts, it can't connect to DNS, email and Internet gateway. I didn't think it was the laptop at first as I was using it to browse around web sites last night. But after talking to some network people, it turned out that only my laptop can't reach those servers.
AppSupport was called in to take a look but couldn't resolve what is wrong. So, they gave me this 3com eth 10/100 PCMCIA card with an X-JACK where the network cable supposed to be plugged in to.
I'd have settled with another network card except that the jack is right to the left of my left hand, and as I was working, my left hand kept knocking on it. Each time, the connection was disrupted.
So, I googled around and found these:
http://netsecurity.about.com/gi/dynamic/offsite.htm?zi=1/XJ&sdn=netsecurity&zu=http%3A%2F%2Fwww.windowsnetworking.com%2Fkbase%2FWindowsTips%2FWindowsXP%2FUserTips%2FTroubleShooting%2FRemoveUnusedDriversandDevices.html
and
http://groups.google.com/group/comp.os.ms-windows.misc/browse_frm/thread/ac65a539ed467eba/b9714c5f0a5d7682?lnk=st&q=failed+to+uninstall+the+device&rnum=12&hl=en#b9714c5f0a5d7682
With these tips, I removed all network drivers and reinstall them. Things started working again. Yay, I avoided a Win2k reinstallation.
(originally from http://microjet.ath.cx/WebWiki/2005.11.01_Removing_Stubborn_Windows_Device_Driver.html)
2005-08-27
AMD 64 hyper transport single channel
Results from running memtest86 on three computers:
Notes:
Athlon64 on socket 939 (I don't have one) would have two channels to the RAM. Of course, to take advantage of the second channel, you'd have to have a second DIMM module.
I have no idea how to test the decrease in latency of RAM access AMD touts in Athlon64 with its built-in memory controller.
(originally from http://microjet.ath.cx/WebWiki/Amd64HyperTransportSingleChannel.html)
| Memtest86 Summary | |||
| | AMD Athlon XP 2100 | Intel Pentium M 1.3GHz | AMD Athlon 64 3400 (socket754) |
| Operating Frequency | 1.7GHz | 1.3GHz | 2.4GHz |
| L1 Size(KiB) | 128 | 64 | 128 |
| L2 Size(KiB) | 256 | 1024 | 512 |
| RAM Size(MiB) | 768 | 496 | 1024 |
| L1 Rate(MiB/s) | 10602 | 16034 | 19664 |
| L2 Rate(MiB/s) | 3375 | 7967 | 4886 |
| RAM Rate(MiB/s) | 505 | 939 | 1441 |
| Chipset | VIA KT400(A)/600 | Intel i855GM/GME FSB:99MHz | SIS 760/M760 |
| Settings | DDR266 | RAM: 132MHz (DDR264) CAS: 2-3-2-6 | DDR400 |
- memtest misidentified the operating frequency of the Athlon64. It should be 2.2GHz.
Athlon64 on socket 939 (I don't have one) would have two channels to the RAM. Of course, to take advantage of the second channel, you'd have to have a second DIMM module.
I have no idea how to test the decrease in latency of RAM access AMD touts in Athlon64 with its built-in memory controller.
(originally from http://microjet.ath.cx/WebWiki/Amd64HyperTransportSingleChannel.html)
2005-08-23
Result pagination with postgresql
A common problem with webapps is providing an interface to page through result set like what various search engines do.
I have yet to find a website that discuss this in depth. So, here is a summary of solutions I came up with by looking at various pieces in the Web for doing result pagination with Postgresql. I hope this would give the sorely needed encouragement for people to start sharing their findings.
The problem actually has three components:
And there are two basic approaches:
To illustrate the pros and cons, I am employing two kind of queries: cheap and expensive. I categorise queries according to the effort exacted from pgsql: cheap and expensive. This categorisation is only for simplicity purposes as there are, of course, grey areas, queries that are neither cheap nor expensive; not to mention that cheap and expensive are subjective terms anyway.
For example, for the first page, the query would be executed with OFFSET 0 and LIMIT 10. The second page would be OFFSET 11 LIMIT 10.
This approach is popular and is found in various web applications. It is simple to implement and has an acceptable performance on cheap queries.
Number of matching results could be counted with a
The latency in page display is low in the beginning and degrades linearly as user moves deeper.
The problem with this approach is it does not reuse previous effort. This is especially problematic if the query is expensive.
Another problem is each query would potentially see different snapshot of the data. If user is browsing page n and the underlying data changes, refreshing or revisiting page n would show a different data.
cheap query
This approach could be implemented by using either a temporary table or a without hold cursor. Both implementations require the webapp to maintain and reuse the transaction in which the table or cursor is defined.
A common strategy is to maintain a fixed number of connections to the database and assign one connection to the processing of a query in a round-robin way, i.e. map a specific query criteria to a specific connection.
In each connection, a transaction is held open throughout the duration of the webapp. This transaction would hold various temporary tables or cursors. You would want to keep the transaction open as long as possible.
Warning: keeping a transaction open for a long time would have the
Therefore, your webapp should be able to re-connect and re-setup the temporary tables or cursors setup if the existing connection or transaction is no longer valid.
Being able to re-setup would also allow the DBA to vacuum thoroughly and/or make schema updates by simply killing and temporarily blocking connections from your webapp during low-traffic hours without having to restart your webapp. This is a big deal if the DBA person is not the sysadmin or have permission to restart your webapp.
Before processing each query, it is recommended to generate a
The implementation gives you a free count of matching result when you do the
cheap query
cheap query
The hybrid approach would operate on piecemeal result set until a certain threshold is reached, e.g.: paging past page 7. When that happens, one of the full result set approach is executed, preferably in the background. The webapp could transition to using the full result set when it is ready.
(originally from http://microjet.ath.cx/WebWiki/ResultPaginationWithPostgresql.html)
I have yet to find a website that discuss this in depth. So, here is a summary of solutions I came up with by looking at various pieces in the Web for doing result pagination with Postgresql. I hope this would give the sorely needed encouragement for people to start sharing their findings.
The problem actually has three components:
- displaying result for a certain page,
- not causing undue latency in page display, and
- counting number of results.
And there are two basic approaches:
- operating on piecemeal result set
- operating on full result set
To illustrate the pros and cons, I am employing two kind of queries: cheap and expensive. I categorise queries according to the effort exacted from pgsql: cheap and expensive. This categorisation is only for simplicity purposes as there are, of course, grey areas, queries that are neither cheap nor expensive; not to mention that cheap and expensive are subjective terms anyway.
dms3_test=> create view cheap as select id from document; CREATE VIEW Time: 150.078 ms dms3_test=> create view expensive as SELECT doc.id FROM attribute as attr0, attribute_name as an0, document as doc, state, document_attribute as da0 WHERE state.id = doc.state_id AND state.name = 'new' AND da0.doc_id = doc.id AND attr0.id = da0.attribute_id AND an0.id = attr0.name_id AND an0.name = 'ssis_client' AND attr0.value ILIKE 'client1'; CREATE VIEW Time: 120.979 msSince what is pro and what is con depend very much against the context, I simply list the characteristics of each approach without further labelling.
Operating on Piecemeal Result Set (New Query for Each Page)
In this approach, a new query is run for each page. Each query differs only in OFFSET and LIMIT clauses.For example, for the first page, the query would be executed with OFFSET 0 and LIMIT 10. The second page would be OFFSET 11 LIMIT 10.
This approach is popular and is found in various web applications. It is simple to implement and has an acceptable performance on cheap queries.
Number of matching results could be counted with a
SELECT COUNT(*) in the beginning. This number could be cached as well so as to reduce the load on the server. The latency in page display is low in the beginning and degrades linearly as user moves deeper.
The problem with this approach is it does not reuse previous effort. This is especially problematic if the query is expensive.
Another problem is each query would potentially see different snapshot of the data. If user is browsing page n and the underlying data changes, refreshing or revisiting page n would show a different data.
cheap query
dms3_test=> abort; begin; select count(*) from cheap; select * from cheap order by id offset 0 limit 10; select * from cheap order by id offset 50000 limit 10; ROLLBACK Time: 1.640 ms BEGIN Time: 6.187 ms count --------- 1010431 (1 row) Time: 1094.187 ms id ---- 1 2 3 4 5 6 9 10 11 12 (10 rows) Time: 57.371 ms id ------- 50003 50004 50005 50006 50007 50008 50009 50010 50011 50012 (10 rows) Time: 134.610 msexpensive query
dms3_test=> abort; begin; select count(*) from expensive; select * from expensive order by id offset 0 limit 10; select * from expensive order by id offset 50000 limit 10; ROLLBACK Time: 2.698 ms BEGIN Time: 4.584 ms count ------- 68276 (1 row) Time: 18034.510 ms id ----- 6 50 55 65 89 109 110 133 144 155 (10 rows) Time: 76.929 ms id -------- 749659 749661 749667 749685 749692 749720 749732 749740 749741 749778 (10 rows) Time: 14424.053 msCharacteristics:
- simple implementation
- no setup cost
- suitable for cheap queries
- suitable if user is expected to browse through only few pages
- not isolated from underlying changes
- latency degrades linearly
Operating on Full Result Set
This approach takes off from the previous one by reusing previous effort. The database takes a hit only on new query criteria, instead of every time the user changes pages.This approach could be implemented by using either a temporary table or a without hold cursor. Both implementations require the webapp to maintain and reuse the transaction in which the table or cursor is defined.
A common strategy is to maintain a fixed number of connections to the database and assign one connection to the processing of a query in a round-robin way, i.e. map a specific query criteria to a specific connection.
In each connection, a transaction is held open throughout the duration of the webapp. This transaction would hold various temporary tables or cursors. You would want to keep the transaction open as long as possible.
Warning: keeping a transaction open for a long time would have the
following negative side-effects:
- prevents vacuum from removing all dead tuples.
- blocks changes to schema
- may block other transactions if data is modified within the transaction
Therefore, your webapp should be able to re-connect and re-setup the temporary tables or cursors setup if the existing connection or transaction is no longer valid.
Being able to re-setup would also allow the DBA to vacuum thoroughly and/or make schema updates by simply killing and temporarily blocking connections from your webapp during low-traffic hours without having to restart your webapp. This is a big deal if the DBA person is not the sysadmin or have permission to restart your webapp.
Before processing each query, it is recommended to generate a
SAVEPOINT so that any error in processing a query would not destroy the transaction. Using Temporary Tables
The result set could be piped into a temporary table via theCREATE TEMPORARY TABLE foo AS command. It is important to remember to use a temporary table since it is not journalled into the WAL (write-ahead logging) which would have negative impact on performance. The implementation gives you a free count of matching result when you do the
CREATE TEMPORARY TABLE AS. I am not sure why psql does not show the count, but it is accessible from within a stored procedure or your DB driver. cheap query
dms3_test=> abort; begin; create temporary table foo as select * from cheap order by id; select * from foo order by id offset 0 limit 10; select * from foo order by id offset 50000 limit 10; ROLLBACK Time: 60.744 ms BEGIN Time: 0.686 ms SELECT Time: 15125.956 ms id ---- 1 2 3 4 5 6 9 10 11 12 (10 rows) Time: 4397.762 ms id ------- 50003 50004 50005 50006 50007 50008 50009 50010 50011 50012 (10 rows) Time: 4413.789 msexpensive query
dms3_test=> abort; begin; create temporary table foo as select * from expensive order by id; select * from foo order by id offset 0 limit 10; select * from foo order by id offset 50000 limit 10; ROLLBACK Time: 52.777 ms BEGIN Time: 3.683 ms SELECT Time: 18666.615 ms id ----- 6 50 55 65 89 109 110 133 144 155 (10 rows) Time: 314.754 ms id -------- 749659 749661 749667 749685 749692 749720 749732 749740 749741 749778 (10 rows) Time: 342.207 msCharacteristics:
- complex implementation
- high setup cost
- free result count as a side-effect
- suitable for expensive queries
- suitable if user is expected to comprehensively browse the result set
- isolated from underlying changes
- latency still degrades linearly but more gently
Using Without Hold Cursors
Without hold cursors are destroyed at the end of transaction, similar to temporary tables. On the other hand, with hold cursors outlive the creating transaction, although they are still bounded within a session. I recommend using without hold cursors to simplify garbage management.cheap query
dms3_test=> abort; begin; declare cheap_cursor scroll cursor for select * from cheap order by id; move all from cheap_cursor; move first from cheap_cursor; fetch 10 from cheap_cursor; move absolute 50000 from cheap_cursor; fetch 10 from cheap_cursor; ROLLBACK Time: 4.054 ms BEGIN Time: 0.970 ms DECLARE CURSOR Time: 1.022 ms MOVE 1010431 Time: 12434.136 ms MOVE 1 Time: 4.409 ms id ---- 2 3 4 5 6 9 10 11 12 13 (10 rows) Time: 4.418 ms MOVE 1 Time: 30.055 ms id ------- 50003 50004 50005 50006 50007 50008 50009 50010 50011 50012 (10 rows) Time: 3.875 msexpensive query
dms3_test=> abort; begin; declare expensive_cursor scroll cursor for select * from expensive order by id; move all from expensive_cursor; move first from expensive_cursor; fetch 10 from expensive_cursor; move absolute 50000 from expensive_cursor; fetch 10 from expensive_cursor; ROLLBACK Time: 2.044 ms BEGIN Time: 0.739 ms DECLARE CURSOR Time: 51.912 ms MOVE 68276 Time: 19036.148 ms MOVE 1 Time: 1.055 ms id ----- 50 55 65 89 109 110 133 144 155 186 (10 rows) Time: 0.911 ms MOVE 1 Time: 30.226 ms id -------- 749659 749661 749667 749685 749692 749720 749732 749740 749741 749778 (10 rows) Time: 1.736 msCharacteristics:
- complex implementation
- high setup cost
- suitable for expensive queries
- suitable if user is expected to comprehensively browse the result set
- isolated from underlying changes
- barely noticeable latency
Hybrid Approach
One could do a hybrid approach. The implementation would be even more complex, but in some cases, it could combine the no setup cost benefit of the piecemeal approach and the low latency of the full result approach.The hybrid approach would operate on piecemeal result set until a certain threshold is reached, e.g.: paging past page 7. When that happens, one of the full result set approach is executed, preferably in the background. The webapp could transition to using the full result set when it is ready.
Summary
| Summary of Implementations | |||||
| Query Type | Implementation | Setup(ms) | Counting(ms) | First Page(ms) | 5000th Page(ms) |
| Cheap | New query per page | N/A | 1094.187 | 57.371 | 134.610 |
| Temporary Table | 15125.956 | N/A | 4397.762 | 4413.789 | |
| Cursor | 1.022 | 12434.136 | 8.827 | 33.930 | |
| Expensive | New query per page | N/A | 18034.510 | 76.929 | 14424.053 |
| Temporary Table | 18666.615 | N/A | 314.754 | 342.207 | |
| Cursor | 51.921 | 19036.148 | 1.966 | 31.962 | |
pvmove problem
I am using LVM2 with linux 2.6.11. One of the drive in the volume is going bad. I could hear screeching noise while the platters were spinning.
That is bad. Not a problem, though, as I can just vacate the data from that drive using pvmove.
The bad drive is /dev/bad. /dev/avail is a volume with some free space.
Let's move it to the only drive with available space, /dev/avail. /dev/bad has 12800 allocated PE while /dev/avail has 20658 free PE. I was not expecting any problem fitting the data in /dev/bad into /dev/avail.
Urk? It needs to be contiguous? What to do now?
Searching through the lvm mailing list shows that pvmove is dumb. It only sees the first free PE (physical extent). OK, let's work around this.
pv0 is the physical volume corresponding to /dev/bad. From this, we see that there are three segments residing in pv0. The first starts at PE 30720.
Let's try to fill that 1777 free PE on the dest drive, /dev/avail. That means, we'll be moving PE 30720 to (30720+1777-1=32496) from pv0.
Finally, after repeating the above process for the remaining segments, /dev/bad, aka pv0, is free of data and is safe to take down.
(originally from http://microjet.ath.cx/WebWiki/pvmove%20problem.html)
That is bad. Not a problem, though, as I can just vacate the data from that drive using pvmove.
The bad drive is /dev/bad. /dev/avail is a volume with some free space.
# pvdisplay /dev/bad /dev/avail --- Physical volume --- PV Name /dev/bad
VG Name vg0 PV Size 148.09 GB / not usable 0 Allocatable yes PE Size (KByte) 4096 Total PE 37911 Free PE 25111 Allocated PE 12800 PV UUID e5EvaO-0oo5-Zenl-KzkY-wFf7-EkQX-di1mXt --- Physical volume --- PV Name /dev/avail
VG Name vg0 PV Size 186.30 GB / not usable 0 Allocatable yes PE Size (KByte) 4096 Total PE 47694 Free PE 20658 Allocated PE 27036 PV UUID HLtCMK-751U-IFAW-5Aj3-FQ3w-5tqO-8svvwt
Let's move it to the only drive with available space, /dev/avail. /dev/bad has 12800 allocated PE while /dev/avail has 20658 free PE. I was not expecting any problem fitting the data in /dev/bad into /dev/avail.
# pvmove -i 5 -v /dev/bad
Finding volume group "vg0"
Archiving volume group "vg0" metadata.
Creating logical volume pvmove0
Moving 0 extents of logical volume vg0/lv0Insufficient contiguous allocatable extents (1777) for logical
volume pvmove0: 12800 required
Unable to allocate temporary LV for pvmove.
Urk? It needs to be contiguous? What to do now?
Searching through the lvm mailing list shows that pvmove is dumb. It only sees the first free PE (physical extent). OK, let's work around this.
# vgcfgbackup
# grep pv0 /etc/lvm/backup/vg0
pv0 {
"pv0", 30720
"pv0", 0
"pv0", 17920
pv0 is the physical volume corresponding to /dev/bad. From this, we see that there are three segments residing in pv0. The first starts at PE 30720.
Let's try to fill that 1777 free PE on the dest drive, /dev/avail. That means, we'll be moving PE 30720 to (30720+1777-1=32496) from pv0.
# pvmove -i 5 -v /dev/bad:30720-32496
Finding volume group "vg0"
Archiving volume group "vg0" metadata.
Creating logical volume pvmove0
Moving 0 extents of logical volume vg0/lv0Moving 1777 extents of logical volume vg0/lv1
Moving 0 extents of logical volume vg0/lv2Moving 0 extents of logical volume vg0/lv3
Found volume group "vg0"
Updating volume group metadata
Creating volume group backup "/etc/lvm/backup/vg0"
Found volume group "vg0"
Found volume group "vg0"
Loading vg0-pvmove0
Found volume group "vg0"
Loading vg0-lv1Checking progress every 5 seconds
/dev/hdf2: Moved: 7.7%
/dev/hdf2: Moved: 14.4%
/dev/hdf2: Moved: 22.6%
/dev/hdf2: Moved: 29.7%
/dev/hdf2: Moved: 37.4%
/dev/hdf2: Moved: 44.6%
/dev/hdf2: Moved: 52.3%
/dev/hdf2: Moved: 60.0%
/dev/hdf2: Moved: 67.7%
/dev/hdf2: Moved: 75.4%
/dev/hdf2: Moved: 83.1%
/dev/hdf2: Moved: 90.3%
/dev/hdf2: Moved: 97.4%
/dev/hdf2: Moved: 100.0%
Found volume group "vg0"
Found volume group "vg0"
Found volume group "vg0"
Loading vg0-pvmove0
Found volume group "vg0"
Loading vg0-lv1 Found volume group "vg0"
Found volume group "vg0"
Removing temporary pvmove LV
Writing out final volume group after pvmove
Creating volume group backup "/etc/lvm/backup/vg0"
Finally, after repeating the above process for the remaining segments, /dev/bad, aka pv0, is free of data and is safe to take down.
# vgreduce vg0 /dev/bad
Toss it in the garbage bin.
(originally from http://microjet.ath.cx/WebWiki/pvmove%20problem.html)
2005-08-14
How Does Emacs Know Which Functions Are Interactive
When you do M-x foo in emacs, you are asking emacs to execute an interactive function named "foo". The function must be declared in a special way as follow:
The form on the second line, "(interactive)", tells emacs that this function is an interactive function.
But how does the emacs knows you put that form in that function? It could try to execute the function, but it may produce an unwanted side-effect (like executing "rm -rf /" if the condition is met).
Could you do a partial execution, e.g.: when a function is defined, execute only the first form and don't execute the rest? But that does not explain the following capability:
Well, that blows the hypotheses that when a function is defined, emacs executes only the first form. There could be no execution whatsoever since doing so may cause severe side-effect. What if the execute form in call-foo/3 is executed? Bad, bad, bad.
So, where is the magic?
The magic lies in macro
defun is actually a special form call. A special form is a form that may be implemented and/or executed differently. If a form is implemented in non-elisp language, it is a special form. If a form is not a function call form, it is a special form (ordinarily, '(something)' in elisp would execute the function 'something').
A special form basically can do anything to its body (macro), including scanning for the interactive form when it is called, just like what the special form defun does.
(originally from http://microjet.ath.cx/WebWiki/HowDoesEmacsKnowWhichFunctionsAreInteractive.html)
(defun foo/1 () (interactive) (if (eq some-condition t) (execute "rm -rf /")))
The form on the second line, "(interactive)", tells emacs that this function is an interactive function.
But how does the emacs knows you put that form in that function? It could try to execute the function, but it may produce an unwanted side-effect (like executing "rm -rf /" if the condition is met).
Could you do a partial execution, e.g.: when a function is defined, execute only the first form and don't execute the rest? But that does not explain the following capability:
(defun foo/2 (do-the-rm-rf) (interactive (list (read-string "Run rm -rf /?" "N"))) (if (string= do-the-rm-rf "Y") (execute "rm -rf /"))) (defun call-foo/2 () (funcall 'foo/2 "N")) (defun call-foo/3 () (interactive (list (execute "rm -rf /"))))When you do M-x foo/2, emacs would first run the read-string function, prompt the user, get the answer from user, and then run the function foo/2. But if you call foo/2 non-interactively by calling it from another function like in call-foo/2, emacs will not prompt the user at all.
Well, that blows the hypotheses that when a function is defined, emacs executes only the first form. There could be no execution whatsoever since doing so may cause severe side-effect. What if the execute form in call-foo/3 is executed? Bad, bad, bad.
So, where is the magic?
The magic lies in macro
defun is actually a special form call. A special form is a form that may be implemented and/or executed differently. If a form is implemented in non-elisp language, it is a special form. If a form is not a function call form, it is a special form (ordinarily, '(something)' in elisp would execute the function 'something').
A special form basically can do anything to its body (macro), including scanning for the interactive form when it is called, just like what the special form defun does.
(originally from http://microjet.ath.cx/WebWiki/HowDoesEmacsKnowWhichFunctionsAreInteractive.html)
2005-07-21
Ruby on Debian
In Debian Woody and Sarge (previous stable and current stable trees as of today), the ruby packages are split up into many packages. This has caused problems to Debian users not having enough dpkg-foo skill, which are many.
The situation is corrected in Debian unstable, but it would be a while before the correction trickles down to testing, and even longer to stable.
In the meantime, you can do this instead:
which would install all ruby packages produced from the official ruby1.8 source tarball.
The situation is corrected in Debian unstable, but it would be a while before the correction trickles down to testing, and even longer to stable.
In the meantime, you can do this instead:
apt-get install grep-dctrl # gives you grep-availablea apt-get install `grep-available -ns Package -F Source -X ruby-defaults` pt-get install `grep-available -ns Package -F Source -X ruby1.8` apt-get install libopenssl-ruby
which would install all ruby packages produced from the official ruby1.8 source tarball.
~ $ grep-available -ns Package -F Source -X ruby-defaults libgdbm-ruby libruby libtcltk-ruby libiconv-ruby rdoc libcurses-ruby libsyslog-ruby libsdbm-ruby libreadline-ruby ri libdbm-ruby libxmlrpc-ruby irb ruby libyaml-ruby libpty-ruby libtk-ruby libtest-unit-ruby libdl-ruby ruby-elisp
~$ grep-available -ns Package -F Source -X ruby1.8 ruby1.8-elisp libopenssl-ruby1.8 ri1.8 ruby1.8-examples libdbm-ruby1.8 libreadline-ruby1.8 libruby1.8 libgdbm-ruby1.8 libruby1.8-dbg irb1.8 libtcltk-ruby1.8 rdoc1.8 ruby1.8-devTotal packages: 23 packages.
~ $ grep-available -ns Package -F Source -X ruby1.8|wc -l 13 ~ $ grep-available -ns Package -F Source -X ruby-defaults|wc -l 20
(originally from http://microjet.ath.cx/WebWiki/RubyOnDebian.html)
2005-07-05
Escaping URI for CGI
Quick, how do you encode a space in a URI? As '+' or as '%20'?
The answer is the standard 'it depends'.
URI was originally specified by RFC 1738. At that time, they were still calling it URL. The specification was revised and renamed to URI in RFC 2396. Since the URI was formalised in RFC 1738 before the importance of supporting internationalisation was recognised, RFC 2396 clarifies that unless communicated otherwise, one could assume the URI to be in US-ASCII character set.
The RFC acknowledges a URI may be composed of many components. It uses
as an example of a partial URI that has four components. The "/", ";", "?" symbols are components separators and are defined by each component's schema. The above separator symbols are just for example purposes.
Most of section 2 of the RFC talks about encoding character data into URI. It is long and hard to read.
Wouldn't it be simpler if it is presented as bullet points? Anyway, the gist of section 2 is as follow:
This allowance is used by the W3C's application/x-www-form-urlencoded specification to specify a different way of encoding values. Specifically, W3C's specification specifies that the space character must be encoded as '+', and the original members of the reserved class must be escaped (effectively removes the fluidity of the reserved class definition).
So, to answer the question: how do you encode a space in URI, the answer would be: in a generic context, as "%20", and in dealing with web forms (CGI as well), "+".
Long winded answer.
(originally from http://microjet.ath.cx/WebWiki/EscapingURIForCgi.html)
The answer is the standard 'it depends'.
URI was originally specified by RFC 1738. At that time, they were still calling it URL. The specification was revised and renamed to URI in RFC 2396. Since the URI was formalised in RFC 1738 before the importance of supporting internationalisation was recognised, RFC 2396 clarifies that unless communicated otherwise, one could assume the URI to be in US-ASCII character set.
The RFC acknowledges a URI may be composed of many components. It uses
<first>/<second>;<third>?<fourth>
as an example of a partial URI that has four components. The "/", ";", "?" symbols are components separators and are defined by each component's schema. The above separator symbols are just for example purposes.
Most of section 2 of the RFC talks about encoding character data into URI. It is long and hard to read.
Wouldn't it be simpler if it is presented as bullet points? Anyway, the gist of section 2 is as follow:
alpha = lowalpha | upalpha
lowalpha = "a" | "b" | "c" | "d" | "e" | "f" | "g" | "h" | "i" |
"j" | "k" | "l" | "m" | "n" | "o" | "p" | "q" | "r" |
"s" | "t" | "u" | "v" | "w" | "x" | "y" | "z"
upalpha = "A" | "B" | "C" | "D" | "E" | "F" | "G" | "H" | "I" |
"J" | "K" | "L" | "M" | "N" | "O" | "P" | "Q" | "R" |
"S" | "T" | "U" | "V" | "W" | "X" | "Y" | "Z"
digit = "0" | "1" | "2" | "3" | "4" | "5" | "6" | "7" |
"8" | "9"
alphanum = alpha | digit
mark = "-" | "_" | "." | "!" | "~" | "*" | "'" | "(" | ")"
reserved = ";" | "/" | "?" | ":" | "@" | "&" | "=" | "+" |
"$" | ","n
unreserved = alphanum | mark
uric = reserved | unreserved | escaped
delims = "<" | ">" | "#" | "%" | <">
- The escape syntax is "%" hex hex, e.g.: "%20" for space for the US-ASCII character set.
- The characters in delims class MUST be escaped.
- The membership of the reserved character class is fluid and depends on the context. "/" could be reserved in one context, and not reserved in another. If you want to use a reserved character in your data, you'd have to escape it.
- An implication of the above point is that the membership of the unreserved character class is also fluid. Reserved's losses are unreserved's gains. To continue the example with "/", the "/" character would be added into the unreserved class if "/" is not reserved in a particular context.
- All unreserved characters must be escaped
- All unreserved characters may be used as-is.
- Unreserved characters may also be escaped. For example, "~" (a mark) may be escaped as "%7e".
This allowance is used by the W3C's application/x-www-form-urlencoded specification to specify a different way of encoding values. Specifically, W3C's specification specifies that the space character must be encoded as '+', and the original members of the reserved class must be escaped (effectively removes the fluidity of the reserved class definition).
So, to answer the question: how do you encode a space in URI, the answer would be: in a generic context, as "%20", and in dealing with web forms (CGI as well), "+".
Long winded answer.
(originally from http://microjet.ath.cx/WebWiki/EscapingURIForCgi.html)
2005-03-31
Stupid BIOS
Several days ago I tried installing Linux on my laptop, Acer TM-290. I have usually been using Debian on my other laptops, but this time, I was wondering about Ubuntu.
So I downloaded and installed Ubuntu (Hoare) on the laptop. As usual, I always put the swap partition on the innermost track. That means the first partition.
On the first boot, the BIOS complained:
Having never used Ubuntu before, I was suspicious of its installation process. In any case, Hoare was not a 'stable' release, just a preview release at this time. So, I proceeded installing Debian on it.
Same error.
Then I installed Win2000 on it and there was no error.
I was so confused. I tried debugging the bootcode loader in the MBR and found nothing wrong.
Then today I noticed that Win2000 installation always set the bootable flag of the first partition. I was wondering if that would have any effect at all.
It did. That was the trigger. The BIOS wanted the first partition to have the bootable flag set regardless of whether it is actually a bootable partition. I always put the swap partition as the first partition. So, I now have a bootable swap partition. Whatever that means.
(originally from http://microjet.ath.cx/WebWiki/WhenBIOSBecomesStupid.html)
So I downloaded and installed Ubuntu (Hoare) on the laptop. As usual, I always put the swap partition on the innermost track. That means the first partition.
On the first boot, the BIOS complained:
Hard disk boot sector invalid Press 'H' to retry Hard Disk, any other key for floppy
Having never used Ubuntu before, I was suspicious of its installation process. In any case, Hoare was not a 'stable' release, just a preview release at this time. So, I proceeded installing Debian on it.
Same error.
Then I installed Win2000 on it and there was no error.
I was so confused. I tried debugging the bootcode loader in the MBR and found nothing wrong.
Then today I noticed that Win2000 installation always set the bootable flag of the first partition. I was wondering if that would have any effect at all.
It did. That was the trigger. The BIOS wanted the first partition to have the bootable flag set regardless of whether it is actually a bootable partition. I always put the swap partition as the first partition. So, I now have a bootable swap partition. Whatever that means.
(originally from http://microjet.ath.cx/WebWiki/WhenBIOSBecomesStupid.html)
2005-02-10
The Thirty Million Question
Recently I went for an interview where I was asked this question: How do you load thirty millions entries from a file into a database within 4 hours.
I was puzzled on its significance. I have been using postgresql and loading 30 million entries is nothing special at all
Turned out they were using MS SQL. I was not sure what the deal was with batch loading of 30 million entries in MS SQL as I have minimum experience with that product. They cited a problem they encountered: apparently, they could not load 30 million entries within 4 hours without resorting to a technique which they had expected me to answer. Also, they said there was a problem with transaction log being overflowed. Beat me up, but I don't expect MS SQL to be having such problems, especially not on a high-end database machine that they had (it had 24 CPU, and I assume an equally impressive disk system and memory)
I was not able to come up with an answer that they were looking for, that is, to break up the data into sections and bulk-load each section from separate session. Each session was to be initiated from a different computer, so as to reduce the load. (Note, though, that the question was highly MSSQL-specific. As I demonstrate below, postgresql has not any problem with loading 30 million entries).
Of course, the point of their question was not that loading 30 millions entries was hard, but rather, how do you load a large data within as short a time as possible.
So I was left thinking how loading from different sessions could hasten the loading process. Sure, I've read debates on postgresql mailing list on how loading from multiple sessions could reduce the time, but I have never paid any attention to it since I was not interested in it at that time.
I did not know much about the subject and had no opinion on that. So I accepted their answer for the time being. But, ever the curious type, I decided to conduct my own experiment.
I set on trying to answer three questions:
This was, by no means, a dedicated database nor powerful machine. The experiment was going on while I continued doing what I usually do: web browsing, software development, chatting, etc. I do not expect to get consistent results between runs due to other ongoing activities nor do consistent results matter much since what I was looking for was an upper bound.
The experiment to answer question #3 was performed on a two 3 GHz Pentium 4 machine with 512 MB RAM and a RAID-5 array with 10K RPM SCSI disks. This was a dedicated database machine and I expected to get a more consistent results.
I expected that if multiple sessions turn out to hasten the process, then the gain would depend on how many indexes in the table. Data loading itself is an IO-bound activity, so, even with multiple sessions, the total write throughput would still be constrained by the maximum write rate of the disk system. On the other hand, indexing would involve a significant amount of computation. So, that would be limited by the amount of computing power available. You could probably run more than n sessions on a n-CPU computer since the CPUs would be idle some of the time while waiting for disk I/O. Yet, at some point, the overhead of context switches would be too big, so certainly there is a limit on the number of sessions. What is the limit? I don't know but I suspect that would depend very much on your system.
Then I generated 4 data files:
Then I created a database and a table in postgresql v. 7.4.6:
All the column names are fictitious.
I did not declare a primary key yet because I wanted to time data loading without any indexing as having a primary key implies having a unique index.
The total for loading and indexing the first thirty millions was: 27m20s + 22m54s = 50m14s.
Good news! That was nowhere near 4 hours! Could this be a fluke?
Let's load the next three thirty-million datasets:
Don't heed the 0%CPU figure, as that's the CPU consumption of the client (psql), and not the server.
But, as you can see, the figures: 38m20s, 44m01s, and 45m03s are not that different from each other, and more importantly, all are under 4 hours. Notice that loading the first dataset took significantly longer than the rest. I do not know exactly why that was the case, but my hypothesis is the disk cache effectiveness is low if the loading and indexing are done separately. Perhaps the hypothesis is wrong, but what is important is I could now answer the first two questions:
Now, let's time loading the first thirty million without indexing it:
The time, 12m26s, about half of 27m20s as in the laptop case, was as expected since loading should be an IO-bound process. A 10K RPM disk should be able to write data about twice as quickly as a 4200 RPM disk.
Indexing it:
Total time for the first batch was: 12m26s + 8m55s = 21m21s.
As expected, indexing benefited from a more powerful CPU. Then, could concurrent sessions reduce the total time simply because that uses the other CPU? Before I went on answering that question, I timed the combined loading and indexing of the data.
15m00s, a bit faster than doing the loading and indexing separately.
Now let's time two-concurrent loading and indexing:
I think they need to look closer at their DBA...
I was puzzled on its significance. I have been using postgresql and loading 30 million entries is nothing special at all
Turned out they were using MS SQL. I was not sure what the deal was with batch loading of 30 million entries in MS SQL as I have minimum experience with that product. They cited a problem they encountered: apparently, they could not load 30 million entries within 4 hours without resorting to a technique which they had expected me to answer. Also, they said there was a problem with transaction log being overflowed. Beat me up, but I don't expect MS SQL to be having such problems, especially not on a high-end database machine that they had (it had 24 CPU, and I assume an equally impressive disk system and memory)
I was not able to come up with an answer that they were looking for, that is, to break up the data into sections and bulk-load each section from separate session. Each session was to be initiated from a different computer, so as to reduce the load. (Note, though, that the question was highly MSSQL-specific. As I demonstrate below, postgresql has not any problem with loading 30 million entries).
Of course, the point of their question was not that loading 30 millions entries was hard, but rather, how do you load a large data within as short a time as possible.
So I was left thinking how loading from different sessions could hasten the loading process. Sure, I've read debates on postgresql mailing list on how loading from multiple sessions could reduce the time, but I have never paid any attention to it since I was not interested in it at that time.
I did not know much about the subject and had no opinion on that. So I accepted their answer for the time being. But, ever the curious type, I decided to conduct my own experiment.
I set on trying to answer three questions:
- Is loading 30 millions data in 4 hours hard to accomplish with postgresql?
- Does postgresql have any problem with loading 30 millions data in a single transaction?
- Does loading data from multiple sessions hasten the process?
This was, by no means, a dedicated database nor powerful machine. The experiment was going on while I continued doing what I usually do: web browsing, software development, chatting, etc. I do not expect to get consistent results between runs due to other ongoing activities nor do consistent results matter much since what I was looking for was an upper bound.
The experiment to answer question #3 was performed on a two 3 GHz Pentium 4 machine with 512 MB RAM and a RAID-5 array with 10K RPM SCSI disks. This was a dedicated database machine and I expected to get a more consistent results.
I expected that if multiple sessions turn out to hasten the process, then the gain would depend on how many indexes in the table. Data loading itself is an IO-bound activity, so, even with multiple sessions, the total write throughput would still be constrained by the maximum write rate of the disk system. On the other hand, indexing would involve a significant amount of computation. So, that would be limited by the amount of computing power available. You could probably run more than n sessions on a n-CPU computer since the CPUs would be idle some of the time while waiting for disk I/O. Yet, at some point, the overhead of context switches would be too big, so certainly there is a limit on the number of sessions. What is the limit? I don't know but I suspect that would depend very much on your system.
Questions #1 and #2
First, I write a simple script to generate the data:#!/usr/bin/env ruby1.8
#/tmp/gen_data.rb
ARGV[0].to_i.upto(ARGV[1].to_i){|x|
puts "#{x}\t#{10000+x}\t#{rand 500}\tdescription-#{x}"
} Sure, the data file they are importing would not be this simple. But that should not matter much since the most important thing, i.e.: reserving a row for the new data, is done equal amount of times. Populating the row mostly depends on your write throughput of your I/O data.
Then I generated 4 data files:
dede:~$ /usr/bin/time /tmp/gen_data.rb 1 30000000 > /tmp/data1.txt 333.07user 13.80system 6:29.38elapsed 89%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (5major+501minor)pagefaults 0swaps dede:~$ /usr/bin/time /tmp/gen_data.rb 30000001 60000000 > /tmp/data2.txt 331.99user 13.95system 6:25.52elapsed 89%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (11major+477minor)pagefaults 0swaps dede:~$ /usr/bin/time /tmp/gen_data.rb 60000001 90000000 > /tmp/data3.txt 322.55user 13.38system 6:13.09elapsed 90%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (0major+488minor)pagefaults 0swaps dede:~$ /usr/bin/time /tmp/gen_data.rb 90000001 120000000 > /tmp/data4.txt 324.78user 13.67system 7:19.02elapsed 77%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (0major+488minor)pagefaults 0swaps dede:~$ ls -lah /tmp/data*.txt -rw-r--r-- 1 ysantoso ysantoso 1.2G Feb 9 21:20 /tmp/data1.txt -rw-r--r-- 1 ysantoso ysantoso 1.2G Feb 9 21:45 /tmp/data2.txt -rw-r--r-- 1 ysantoso ysantoso 1.2G Feb 9 22:05 /tmp/data3.txt -rw-r--r-- 1 ysantoso ysantoso 1.3G Feb 10 22:30 /tmp/data4.txt dede:~$ head -1 /tmp/data1.txt 0 10000 389 description-0 dede:~$ tail -1 /tmp/data1.txt 30000000 30010000 278 description-30000000
Then I created a database and a table in postgresql v. 7.4.6:
thirtymillion=> create table transactions (tid int4, mssinceunixepoch int4, amount int8, description text);
CREATE TABLE
thirtymillion=> \d transactions
Table "public.transactions"
Column | Type | Modifiers
------------------+---------+-----------
tid | integer |
mssinceunixepoch | integer |
amount | bigint |
description | text |
All the column names are fictitious.
I did not declare a primary key yet because I wanted to time data loading without any indexing as having a primary key implies having a unique index.
thirtymillion=# copy transactions from '/tmp/data.txt'; COPY Time: 1639971.939 ms (27m20s)So, 27m20s to load the first 30 millions. That's not bad at all. But how much time does indexing takes?
thirtymillion=> alter table transactions add primary key (tid); NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "transactions_pkey" for table "transactions" ALTER TABLE Time: 1374100.087 ms (22m54s)
The total for loading and indexing the first thirty millions was: 27m20s + 22m54s = 50m14s.
Good news! That was nowhere near 4 hours! Could this be a fluke?
Let's load the next three thirty-million datasets:
dede:~$ echo "copy transactions from '/tmp/data2.txt' "| /usr/bin/time sudo -u postgres psql thirtymillion COPY 0.01user 0.00system 38:19.59elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (0major+2286minor)pagefaults 0swaps dede:~$ echo "copy transactions from '/tmp/data3.txt' "| /usr/bin/time sudo -u postgres psql thirtymillion COPY 0.02user 0.01system 44:00.94elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (12major+2274minor)pagefaults 0swaps dede:~$ echo "copy transactions from '/tmp/data4.txt' "| /usr/bin/time sudo -u postgres psql thirtymillion COPY 0.01user 0.01system 45:03.43elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (22major+2264minor)pagefaults 0swaps
Don't heed the 0%CPU figure, as that's the CPU consumption of the client (psql), and not the server.
But, as you can see, the figures: 38m20s, 44m01s, and 45m03s are not that different from each other, and more importantly, all are under 4 hours. Notice that loading the first dataset took significantly longer than the rest. I do not know exactly why that was the case, but my hypothesis is the disk cache effectiveness is low if the loading and indexing are done separately. Perhaps the hypothesis is wrong, but what is important is I could now answer the first two questions:
- Is loading 30 millions data in 4 hours hard to accomplish with postgresql? No
- Does postgresql have any problem with loading 30 millions data in a single transaction? No
Question #3
I setup several data files, each contained 30 million entries for four sub-experiments:- Time separate loading and indexing as in the previous experiment
- Time combined loading and indexing.
- Time two concurrent loading and indexing sessions. The machine had two CPUs, so each CPU handled at most a session.
- Time six concurrent loading and indexing sessions. The idea was to simulate high CPU and IO consumptions condition.
$ /usr/bin/time ./gen_data.rb 1 30000000 > data1.txt 180.55user 4.74system 3:05.40elapsed 99%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (17major+531minor)pagefaults 0swaps
Now, let's time loading the first thirty million without indexing it:
$ echo "copy transactions from '/c1/tmp/30mil/data1.txt'"| sudo -u postgres /usr/bin/time psql thirtymillion COPY 0.00user 0.01system 12:26.37elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (0major+2015minor)pagefaults 0swaps
The time, 12m26s, about half of 27m20s as in the laptop case, was as expected since loading should be an IO-bound process. A 10K RPM disk should be able to write data about twice as quickly as a 4200 RPM disk.
Indexing it:
$ echo "alter table transactions add primary key (tid)" | /usr/bin/time psql thirtymillion NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "transactions_pkey" for table "transactions" ALTER TABLE 0.00user 0.01system 8:55.03elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (0major+2024minor)pagefaults 0swaps
Total time for the first batch was: 12m26s + 8m55s = 21m21s.
As expected, indexing benefited from a more powerful CPU. Then, could concurrent sessions reduce the total time simply because that uses the other CPU? Before I went on answering that question, I timed the combined loading and indexing of the data.
$ echo "copy transactions from '/c1/tmp/30mil/data2.txt'"| sudo -u postgres /usr/bin/time psql thirtymillion COPY 0.00user 0.01system 15:00.04elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (0major+2025minor)pagefaults 0swaps
15m00s, a bit faster than doing the loading and indexing separately.
Now let's time two-concurrent loading and indexing:
$ echo "copy transactions from '/c1/tmp/30mil/data3.txt'"| sudo -u postgres /usr/bin/time psql thirtymillion & echo "copy transactions from '/c1/tmp/30mil/data4.txt'"| sudo -u postgres /usr/bin/time psql thirtymillion & COPY 0.01user 0.01system 30:41.26elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (1major+2016minor)pagefaults 0swaps COPY 0.01user 0.01system 31:05.41elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (1major+2017minor)pagefaults 0swaps
It took about 30min to load 2*30=60 millions rows. It's twice as long as loading 30 million rows. The loading time seems to grow linearly.
If the time taken is linear at 30million/15mins, then 6*30mil=180 million rows should take about 6*15min=1h30min. Let's verify:
$ echo "copy transactions from '/c1/tmp/30mil/data5.txt'"| sudo -u postgres /usr/bin/time psql thirtymillion & echo "copy transactions from '/c1/tmp/30mil/data6.txt'"| sudo -u postgres /usr/bin/time psql thirtymillion & echo "copy transactions from '/c1/tmp/30mil/data7.txt'"| sudo -u postgres /usr/bin/time psql thirtymillion & echo "copy transactions from '/c1/tmp/30mil/data8.txt'"| sudo -u postgres /usr/bin/time psql thirtymillion & echo "copy transactions from '/c1/tmp/30mil/data9.txt'"| sudo -u postgres /usr/bin/time psql thirtymillion & echo "copy transactions from '/c1/tmp/30mil/data10.txt'"| sudo -u postgres /usr/bin/time psql thirtymillion & COPY 0.01user 0.01system 1:36:44elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (0major+2022minor)pagefaults 0swaps COPY 0.01user 0.01system 1:38:23elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (0major+2023minor)pagefaults 0swaps COPY 0.01user 0.01system 1:39:05elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (0major+2011minor)pagefaults 0swaps COPY 0.01user 0.02system 1:39:09elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (0major+2016minor)pagefaults 0swaps COPY 0.01user 0.01system 1:39:18elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (0major+2011minor)pagefaults 0swaps COPY 0.01user 0.02system 1:39:20elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (0major+2020minor)pagefaults 0swaps
Indeed! 180 million rows took about 1h30min.
So, 12*30million= 360 millions should take about 12*15min = 3 hours. Let's verify:Twelve! concurrent bulk-loading with only 2 CPUs to perform those:
$ echo "copy transactions from '/c1/tmp/30mil/data11.txt'"| sudo -u postgres /usr/bin/time psql thirtymillion & echo "copy transactions from '/c1/tmp/30mil/data12.txt'"| sudo -u postgres /usr/bin/time psql thirtymillion & echo "copy transactions from '/c1/tmp/30mil/data13.txt'"| sudo -u postgres /usr/bin/time psql thirtymillion & echo "copy transactions from '/c1/tmp/30mil/data14.txt'"| sudo -u postgres /usr/bin/time psql thirtymillion & echo "copy transactions from '/c1/tmp/30mil/data15.txt'"| sudo -u postgres /usr/bin/time psql thirtymillion & echo "copy transactions from '/c1/tmp/30mil/data16.txt'"| sudo -u postgres /usr/bin/time psql thirtymillion & echo "copy transactions from '/c1/tmp/30mil/data17.txt'"| sudo -u postgres /usr/bin/time psql thirtymillion & echo "copy transactions from '/c1/tmp/30mil/data18.txt'"| sudo -u postgres /usr/bin/time psql thirtymillion & echo "copy transactions from '/c1/tmp/30mil/data19.txt'"| sudo -u postgres /usr/bin/time psql thirtymillion & echo "copy transactions from '/c1/tmp/30mil/data20.txt'"| sudo -u postgres /usr/bin/time psql thirtymillion & echo "copy transactions from '/c1/tmp/30mil/data21.txt'"| sudo -u postgres /usr/bin/time psql thirtymillion & echo "copy transactions from '/c1/tmp/30mil/data22.txt'"| sudo -u postgres /usr/bin/time psql thirtymillion & COPY 0.01user 0.02system 3:29:18elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (0major+2018minor)pagefaults 0swaps COPY 0.01user 0.02system 3:32:57elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (0major+2016minor)pagefaults 0swaps COPY 0.01user 0.01system 3:33:27elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (0major+2017minor)pagefaults 0swaps COPY 0.01user 0.02system 3:33:41elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (0major+2017minor)pagefaults 0swaps COPY 0.01user 0.02system 3:33:52elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (0major+2021minor)pagefaults 0swaps COPY 0.01user 0.02system 3:34:12elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (0major+2016minor)pagefaults 0swaps COPY 0.01user 0.01system 3:34:37elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (0major+2019minor)pagefaults 0swaps COPY 0.01user 0.02system 3:34:39elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (0major+2019minor)pagefaults 0swaps COPY 0.01user 0.02system 3:35:08elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (0major+2019minor)pagefaults 0swaps COPY 0.01user 0.01system 3:35:16elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (0major+2014minor)pagefaults 0swaps COPY 0.01user 0.01system 3:35:19elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (0major+2012minor)pagefaults 0swaps COPY 0.01user 0.01system 3:35:23elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k 0inputs+0outputs (0major+2011minor)pagefaults 0swaps
It's close. Predicted 3 hours, actual 3h35min.
I didn't really pay attention to the CPU consumption during the run, so I couldn't tell if the slowdown was due to the process being CPU-bound or IO-bound.
But I now can answer question #3:
Does loading data from multiple sessions hasten the process? No.
If anything, loading data from too many sessions actually slows down the process.
By the end of the experimentation, I've loaded more than 500 million rows into pgsql at a remarkably steady rate of 30 million rows /15 mins. I am not sure why they needed 4 hours to do the same. Even accounting for various referential integrity checkings that may be present, 4 hours is still 16x 15 mins.
I think they need to look closer at their DBA...
2004-11-05
What Extensibility Suppose To Be
http://ww.telent.net/diary/%5B%20updated%20for%20elisp%20syntax%20error%2C%2013%3A01%3A52%20GMT%20%5D
(originally from http://microjet.ath.cx/WebWiki/WhatExtensibilitySupposeToBe.html
(originally from http://microjet.ath.cx/WebWiki/WhatExtensibilitySupposeToBe.html
Subscribe to:
Posts (Atom)