Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase «««12345

MySQL Primer for the SQL Sever DBA Expand / Collapse
Posted Monday, September 21, 2009 12:06 PM


Group: General Forum Members
Last Login: Wednesday, July 10, 2013 2:38 PM
Points: 14, Visits: 59
I've seen issues building indexes on large tables taking excessive amounts of time on anything over 50 GB keys. As I understand it this is do to the sorting process. In fact I've actually asked mysql at performance class on this issue and the answer is to build the keys and tables offline then replace the underlying files. I'd be curious how large your indexes are, and what your mem config is.
Post #791343
Posted Tuesday, September 22, 2009 4:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 31, 2012 2:29 AM
Points: 2, Visits: 6
One of the MYD files is 100 gb and another is 40 gb. For both these tables, the indexes were defined before any data was put in. The tables were then populated using load data infile and a trigger to create the geometry object. As such, I can't really tell how long the index creation took, as it was all part of the overall load process, which did take 3 days on one of the tables. It is likely that the index creation would have taken a very long time, had I done it after data load, as MyISAM tables are completely rewritten in the process.

Now that these tables and indexes are set up, I can do several thousand inserts a second, of fairly complex polygonal data, which I consider acceptable.

My config file for sort buffer size, etc, is more or less the same as the sample huge.ini that comes with MySQL, as the box in question, is as 64-bit beast that only runs MySQL.
Post #791650
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse