Hi Paolo,
On Thu, 13 May 2010, Paolo Lucente wrote:
I think it would make no semantic difference, but would increase MySQL
performance with these table types, if the primary key listed
stamp_inserted first instead of last.
The change you propose, as you say, would not be impacting - but would
you have any testing handy which confirms the hint?
I did some testing with interesting results :)
With MyISAM tables, placing stamp_inserted first instead of last results
in a 10x *increase* in the index size! I filed a MySQL bug report about
this:
http://bugs.mysql.com/bug.php?id=53631
However, I do get 25% better INSERT performance for my trouble:
ch...@gcc(tmp)$ time mysql -u root pmacct_temp < pmacct-short.sql
real 0m26.496s
user 0m2.352s
sys 0m0.917s
ch...@gcc(tmp)$ time mysql -u root pmacct_temp < pmacct-short.sql
real 0m26.463s
user 0m2.283s
sys 0m0.978s
ch...@gcc(tmp)$ vi pmacct-short.sql
(move stamp_inserted from last to first in the primary key of acct_v7)
ch...@gcc(tmp)$ time mysql -u root pmacct_temp < pmacct-short.sql
real 0m19.833s
user 0m2.312s
sys 0m0.861s
ch...@gcc(tmp)$ time mysql -u root pmacct_temp < pmacct-short.sql
real 0m20.022s
user 0m2.374s
sys 0m0.942s
With InnoDB, I don't know how to measure the size of the index, and I
don't see the performance increase either.
I don't know whether to recommend this change or not, based on my testing.
10x increase in index size is a heavy penalty for 25% faster INSERTs.
Another option to get a monotonically increasing key is to set a
synthetic index, ie. a spare field defined as AUTO_INCREMENT
By adding an AUTO_INCREMENT ID column and removing the old PRIMARY KEY, I
got another 15% performance increase:
ch...@gcc(tmp)$ time mysql -u root pmacct_temp < pmacct-short.sql
real 0m17.003s
user 0m2.243s
sys 0m0.878s
ch...@gcc(tmp)$ time mysql -u root pmacct_temp < pmacct-short.sql
real 0m16.924s
user 0m2.265s
sys 0m0.875s
But then of course one loses protection against duplicate rows. If I add
an AUTO_INCREMENT column but change the old PRIMARY KEY to a UNIQUE INDEX
instead of removing it, then I see a slight drop in performance instead of
an increase. With stamp_inserted last in the UNIQUE INDEX:
ch...@gcc(tmp)$ time mysql -u root pmacct_temp < pmacct-short.sql
real 0m27.758s
user 0m2.516s
sys 0m0.931s
ch...@gcc(tmp)$ time mysql -u root pmacct_temp < pmacct-short.sql
real 0m27.882s
user 0m2.368s
sys 0m0.951s
(i.e. 27.7 seconds against 26.5). And with stamp_inserted first:
ch...@gcc(tmp)$ time mysql -u root pmacct_temp < pmacct-short.sql
real 0m21.513s
user 0m2.478s
sys 0m0.946s
ch...@gcc(tmp)$ time mysql -u root pmacct_temp < pmacct-short.sql
real 0m21.242s
user 0m2.379s
sys 0m0.893s
(i.e. 21.2 seconds against 19.8).
Cheers, Chris.
--
Aptivate | http://www.aptivate.org | Phone: +44 1223 760887
The Humanitarian Centre, Fenner's, Gresham Road, Cambridge CB1 2ES
Aptivate is a not-for-profit company registered in England and Wales
with company number 04980791.
_______________________________________________
pmacct-discussion mailing list
http://www.pmacct.net/#mailinglists
