MySQL Primer for the SQL Sever DBA

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Ken Kaufman » MySQL Primer for the SQL Sever DBA

42 posts, Page 5 of 5

«««1 2 3 4 5

MySQL Primer for the SQL Sever DBA

Rate Topic

Display Mode

Topic Options

Author

Message

kenk-601293

Posted Monday, September 21, 2009 12:06 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, June 13, 2013 3:05 PM
Points: 14, Visits: 57

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

john.powell-698372

Posted Tuesday, September 22, 2009 4:30 AM

Forum 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 »

42 posts, Page 5 of 5

«««1 2 3 4 5

Permissions