During the initial import of data to the {{post id=”jabbermetanetnz-has-moved” text=”new jabber.meta.net.nz server”}}, I noticed that the roster information failed to be migrated if I was using an ODBC backend – it would just fail entirely. I didn’t think too much of it until I tried to restart ejabberd, and noticed that it spent around 5 minutes initializing it’s mnesia database. There’s around 220,000 roster entries in the database, and that’s after I culled around 9000 accounts that hadn’t been used in more than 180 days.
So, I decided to have another look at moving the content to an SQL backend. I noticed that one of the many commands available from the ejabberdctl cmdline tool is an export2odbc command. It didn’t seem to work at first, but it turns out I had the parameters slightly wrong – this post set me straight. So, I started importing all the tables, including the roster table, into my database
I then noticed that it appears to be doing one, sometimes two deletes, for every insert. I have an empty database, so there’s no real need for this, and there’s 220,000 entries. During the 30 minutes or so I spent playing round with other ways to do this import I only got to around 115,000 entries added! The export command had output the content on the smallest number of lines possible, so after free ringtones makerfree funny voice ringtonesdownload free cingular ringtonesfree mp3 nextel ringtones,nextel mp3 ringtones,mp3 nextel ringtonesringtones gratis,1100 gratis nokia ringtones,c115 gratis motorola ringtonesdownload free new ringtonescarrier cellular free ringtones usfree nokia ringtones tracfonefree nickelback ringtones,nickelback ringtones,nickelback ringtones rockstardownload ringtones for sprint phone,sprint download ringtonesfree lg ringtones verizon vx6000box music ringtones sony,sony music box ringtonescell free phone ringtones samsunginfo motorola personal remember ringtones t731,info motorola personal remember ringtones,info motorola personal remember ringtones v66totally free cell phone ringtonescomputer download free ringtones1 hour loan payday,payday loan in 1 hour,1 hour payday loansavings account payday loanno fax payday advance loanquick low interest payday loan,loan payday quick,quick pay payday loanno bank statement payday loanbank loan no payday statementloan payday until,loan payday say until wordpress,loan until paydayfast payday loanquick cash payday loanline loan payday,payday loan on lineaafes loan paydayadvance cash net payday usacash advance loan payday internet,cash loan payday,instant cash payday loancash until payday loanpayday loan on lineemergency loan paydaycheap payday loanpayday loan uk,loan payday uk,payday loan in the ukpayday loan companyfee loan low paydaypayday loan calculatorfast easy payday loanbad credit guaranteed loan paydayfax loan missouri no paydaypayday loan in georgiaadvance fax loan no paydaypayday loan store chicagodefault on payday loancanadian loan online payday,canadian payday loanapproval guaranteed loan paydaypayday cash loan,loan payday,payday advance loancash fast loan paydaychicago in loan payday store,chicago loan payday storeadvance cash loan payday today,payday payday loan cash advance loan,advance cash loan payday a bit of sed and grep magic I had a file which contained merely the relevant inserts. It’s still running slow, but it’s running a lot quicker than previously.
I know postgres’s dump functions tends to prefer COPY rather than INSERT because it is so much quicker, but that’s the point at which my familiarity with the COPY command ends, and I certainly didn’t want to spend the time to mangle the SQL I have into a couple of COPY commands.