On Wed, Aug 06, 2008 at 03:16:28AM -0700, Cronje Fourie wrote:
Hi Stefan...
All your assumptions are correct. - Both systems use a single SATA disk, SWAP and data partition on same disks - Compiled the MonetDB, from Jun-SuperTar, with options: --enable-sql --enable-optimise --nightly-stable - gcc etc are all latest on both machines, gcc 4.2.3 - compiled with 64bit OID's on 64bit machine. - I'm loading plain text data - Including the create table statement at the bottom, it's a whopper :) a couple of date fields are loaded as VARCHAR because the source data format is not compatible with MonetDB.
On the load side the VM usage went up to 11GB...on the 64bit system. I can't remember the exact value on the 32bit system but was around 3GB for VM.
A part of the difference could be explained from the types. Varchar and char use atleast 8 bytes on the 64bit system (only 4 on 32 bit). Also the var heaps will take more space on the 64 bit system, because of the double elimination hash tables. Niels
One major difference between the two systems is that the 32bit system is a SERVER install, vs full KDE on the 64bit.
I'm now trying to run the same load on a 32bit system, which also has KDE installed and it is also much slower than on the server. Incidentally VM usage on this machine is 1GB
I'm downloading the ubuntu server image at the moment and will install and test on that shortly, then get back to you with the results.
CREATE TABLE PrimalPool ( id10 char(10) NOT NULL, idno varchar(13) NOT NULL, cell_phone varchar(20) NULL, combined_expenses_monthly double NULL, combined_income_monthly double NULL, customer_age_id integer NULL, deceased tinyint NULL, demographic char(10) NULL, do_not_mail tinyint NULL, dob varchar(50) NULL, email varchar(150) NULL, emperica_score integer NULL, empscore_date varchar(50) NULL, ethnicity_id char(1) NULL, forename_1 varchar(20) NULL, forename_2 varchar(20) NULL, forename_3 varchar(20) NULL, gender_id char(1) NULL, home_language char(15) NULL, home_phone varchar(20) NULL, initials varchar(3) NULL, language char(1) NULL, marital_status_id char(1) NULL, market_sector_id tinyint NULL, prospect_age tinyint NULL, surname varchar(50) NULL, title_id varchar(20) NULL, work_phone varchar(20) NULL, bond_rent_payments integer NULL, bureau_installment_monthly double NULL, expense_bond_monthly double NULL, expense_maintenance_monthly double NULL, expense_municipal_services_monthly double NULL, income_tax_monthly double NULL, maint_payments integer NULL, municipal_payments integer NULL, net_income_monthly double NULL, pension_contrib_monthly double NULL, salary_gross_monthly double NULL, salary_other_monthly double NULL, statutory_expenses_monthly double NULL, uif_contrib_monthly double NULL, sbcscore integer NULL, sbcscore_date varchar(50) NULL, source_id integer NULL, account_charge_off tinyint NULL, avg_account_age integer NULL, avg_amount_offered integer NULL, avg_bscore integer NULL, avg_bscore_range integer NULL, avg_credit_limit integer NULL, avg_hilife_balance integer NULL, avg_income integer NULL, has_bad_exclusion_score tinyint NULL, has_negative_block_code tinyint NULL, has_payment_projection_score tinyint NULL, has_staff_loan tinyint NULL, invalid_logo_1stloan tinyint NULL, mail_date varchar(50) NULL, mailing_indicator tinyint NULL, mailing_key tinyint NULL, max_account_age integer NULL, max_agreement_date varchar(50) NULL, max_amount_offered integer NULL, max_bscore integer NULL, max_bscore_date varchar(50) NULL, max_bscore_range integer NULL, max_credit_limit integer NULL, max_hilife_balance integer NULL, max_income integer NULL, max_open_date varchar(50) NULL, min_account_age integer NULL, min_agreement_date varchar(50) NULL, min_amount_offered integer NULL, min_bscore integer NULL, min_bscore_date varchar(50) NULL, min_bscore_range integer NULL, min_credit_limit integer NULL, min_hilife_balance integer NULL, min_income integer NULL, min_open_date varchar(50) NULL, no_accounts tinyint NULL, no_active_accounts tinyint NULL, no_inactive_accounts tinyint NULL, rcs_offer_campaign_no varchar(20) NULL, rcs_offer_expiry_date varchar(50) NULL, rcs_offer_interest_rate tinyint NULL, sum_contract_delinquency integer NULL, sum_payment_recency tinyint NULL, sum_return_mail integer NULL, sum_warning_code integer NULL, account_active char(1) NULL, account_age integer NULL, account_id bigint NULL, account_status char(3) NULL, agreement_date varchar(50) NULL, amount_offered double NULL, application_id varchar(20) NULL, block_code_1 varchar(20) NULL, block_code_2 varchar(20) NULL, bscore integer NULL, bscore_date varchar(50) NULL, bscore_range varchar(20) NULL, campaign_no varchar(20) NULL, contract_date varchar(50) NULL, contract_delinquency tinyint NULL, credit_limit double NULL, crlife_admin double NULL, current_balance double NULL, customer_id char(30) NULL, entity_id integer NULL, expiry_date varchar(50) NULL, group_id integer NULL, hilife_balance double NULL, income double NULL, installment_value double NULL, interest_rate double NULL, logo_id varchar(3) NULL, open_date varchar(50) NULL, palink_type varchar(20) NULL, payment_recency integer NULL, payout_amount double NULL, payout_date varchar(50) NULL, physical_address_id char(32) NULL, principal_value double NULL, prodcatid integer NULL, res_address_id char(32) NULL, return_mail integer NULL, risk_rating varchar(20) NULL, tag_date varchar(50) NULL, term_period tinyint NULL, warning_code tinyint NULL, warning_code1 integer NULL, warning_code2 integer NULL, warning_code3 integer NULL, write_off_balance double NULL, accno varchar(20) NULL, account_no varchar(20) NULL, address_class varchar(1) NULL, address_code varchar(3) NULL, address_id_ia varchar(30) NULL, address_line1 varchar(65) NULL, address_line2 varchar(65) NULL, address_line3 varchar(65) NULL, address_line4 varchar(65) NULL, address_line5 varchar(65) NULL, address_source_date varchar(50) NULL, address_type varchar(3) NULL, address_type_id char(1) NULL, amc varchar(15) NULL, amc_id varchar(10) NULL, building_type varchar(3) NULL, change_status varchar(3) NULL, clean_address_line1 varchar(65) NULL, clean_address_line2 varchar(65) NULL, clean_address_line3 varchar(65) NULL, clean_address_line4 varchar(65) NULL, clean_address_line5 varchar(65) NULL, country varchar(50) NULL, country_code varchar(6) NULL, delivery_code varchar(1) NULL, dpid varchar(20) NULL, lines_in tinyint NULL, lmc varchar(20) NULL, lmc_id varchar(15) NULL, location_type varchar(20) NULL, mpcode varchar(5) NULL, mpname varchar(36) NULL, postcode varchar(10) NULL, region varchar(40) NULL, sec_location_type varchar(20) NULL, spcode varchar(8) NULL, spname varchar(25) NULL, structure_status varchar(3) NULL, suburb varchar(65) NULL, suburb_ethnic_code varchar(2) NULL, suburb_income tinyint NULL, suburb_income_band varchar(2) NULL, suburb_lat double NULL, suburb_long double NULL, tag varchar(40) NULL, town varchar(65) NULL, umc varchar(15) NULL, validity_status varchar(3) NULL, prospect_right bigint null DEFAULT 0, has_email tinyint NULL DEFAULT 0, has_address tinyint NULL DEFAULT 0, has_phone tinyint NULL DEFAULT 0, has_home_phone tinyint NULL DEFAULT 0, has_cell_phone tinyint NULL DEFAULT 0, has_work_phone tinyint NULL DEFAULT 0, chargeoff_date varchar(50) NULL, mloans_index char(30) NULL, filler_space char(1) NULL DEFAULT ' ', filler_empty varchar(1) NULL DEFAULT '', curr_date varchar(50) NULL DEFAULT '', prospect_owner bigint NULL DEFAULT 0, reference_no varchar(20) NULL, c char(30) NULL DEFAULT '0' ) GO
Thanks for the help Cronje
Stefan Manegold wrote:
On Tue, Aug 05, 2008 at 05:25:09PM +0200, Cronje Fourie wrote:
Hi guys....
I've got to machines running Monet. 1. Quad core Intel with 4GB RAM, Ubuntu 8.04 32BIT (server ed) 2. AMD64 Quad Core, 4GB RAM, KUbuntu 8.04 64BIT (kde 3.5.9)
On system 1 I can do a "copy into" of 6million records in no time at all :)
On system 2 the same function, with the exact same data takes forever.
Any thoughts on the reason? Is this a memory related issue?
I assume that you configure MonetDB on both machines with the same (if any) optimization and/or debugging options and compiled it with the same (versions of) tools (in particular compiler).
Morover, I assume that you built a 64-bit version with 64-bit OIDs on the 64-bit machine (should be the default on a 64-bit machines).
Finally, I assume that both machines have comparable I/O systems (disks).
Did/can you check how big your mserver5 process is growing on both machines during the copy into, and/or whether either machines (obviously the second one) is swapping heavily during the copy into?
How wide are your records, i.e., what does your 'create table' statement look like?
Do you load from a plain (uncompressed) file or from a compressed (how) file?
Stefan
Cronje
-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |
------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- View this message in context: http://www.nabble.com/COPY-INTO-on-Ubuntu-64bit-is-slow-tp18833879p18848282.... Sent from the monetdb-users mailing list archive at Nabble.com.
------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Niels Nes, Centre for Mathematics and Computer Science (CWI) Kruislaan 413, 1098 SJ Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl