JavaFan> 3300 insertions/second comes to 30 seconds for 100K.
JavaFan> I find 30 fairly large for "several".
Indeed. When I asked the original question, I didn't have the correct number. Now I do, for one instance of the job.
JavaFan> But is this number for 3300 separate queries, or a single
JavaFan> query with 3300 rows?
I am not sure what you mean by "a single query with 3300 rows." I mean
$sth->execute() is called for each row, and a
$dbh->commit() is called once every 100_000 rows. In the end, I get
i rows inserted in
j wallclock seconds (via Benchmark), and
i / j is equal to approx. 3300.
Other answers --
- How many indices are they? What kind of indices? Do they allow for new rows written at the end of the table, or must the table be rewritten?
- No indices except for a primary key. I don't allow or disallow anything. I am simply inserting the rows. I have no idea if the db is writing them at the end of table, but I am assuming it is. If it is rewriting the entire table everytime then it is a very silly db
- Are there any constraints? Foreign keys? Unique indices? Triggers? Auditting?
- none whatsoever for all of the above
- How many rows are there already?
- How many other processes are using the table while you're doing inserts?
- No other processes. Except, now I have started experimenting with doing parallel INSERTs by starting new perl processes inserting different set of rows. See next question on hardware.
- What's the hardware the database is running on?
- It is a dual 3GHz Xeon Xserve with two cores each. Hence, my experimentation with four concurrent perl processes doing row insertions.
- How much memory does said hardware have?
Re. the rest of your questions:
- How "hot" is the table? Don't know what that means.
- Is the table being replicated? No.
- What's the physical layout of where the data is written to? Don't know what that means.
- What's the physical layout of where your logs are written to? Don't know.
--
when small people start casting long shadows, it is time to go to bed
Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
Read Where should I post X? if you're not absolutely sure you're posting in the right place.
Please read these before you post! —
Posts may use any of the Perl Monks Approved HTML tags:
- a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
| |
For: |
|
Use: |
| & | | & |
| < | | < |
| > | | > |
| [ | | [ |
| ] | | ] |
Link using PerlMonks shortcuts! What shortcuts can I use for linking?
See Writeup Formatting Tips and other pages linked from there for more info.