up vote 3 down vote favorite
share [g+] share [fb]

I am performing massive insertion operation towards a MySQL database (100+ million records). Afterwards, I select records out of the two tables using for the most part their primary key. I seek to optimise the system with regards to performance and the current configuration parameters I have in mind are:

  • binary logging turned off for increased performance,
  • MyISAM storage engine for increased performance (however I will also try the "heavier" (transactional) InnoDB since it utilises private data buffer and finer locking),
  • connection via socket since I am connecting to a server in the same computer.

Any suggestions?

link|improve this question
What kind of data? Can you give a description of its structure? – trurl Jul 6 '11 at 16:28
feedback

3 Answers

up vote 0 down vote

I advise against MyISAM tables, even though in some cases they can be faster, because they are less functional, and data corruption is much more of a possibility.

Connecting via a socket instead of TCP is a good idea, but it won't make any noticeable difference for the types of queries you're talking about.

Depending on the usage patterns for your data, you might consider partitioning your data into multiple tables. Without knowing more about your data, I can't offer specific recommendations, but you can read up on Partitioning in MySQL to get an idea. And of course if you choose to go that route, and have more specific questions, you know where to ask :)

link|improve this answer
@trurl and @Flimsy, thanks. My data represent a graph actually, so there is a table Node(id INT(10) NOT NULL, title VARCHAR(300) NOT NULL, PRIMARY KEY(id)) and a table Edge(from_node_id INT(10) NOT NULL, to_node_id INT(10) NOT NULL, PRIMARY KEY(from_node_id, to_node_id)). After I insert the data from a text file, I execute the equivalent of a shortest path algorithm between any two nodes. – mfg Jul 7 '11 at 7:30
feedback
up vote 0 down vote

Did you try http://www.mysqltuner.com/ ? I think it should be a good starting point for some basic tuning.

link|improve this answer
Care to elaborate on its features? – Ivo Flipse♦ Jul 7 '11 at 21:11
It gives suggestions on some settings you may change on the MySQL server base on the statistics. It won't change any setting of your MySQL server. – Raymond Tau Jul 8 '11 at 9:30
feedback
up vote 0 down vote

If the mass insert operation involves the LOAD DATA INFILE, INSERT...SELECT, or the extended INSERT ... VALUES command, please remember to set this:

[mysqld]
bulk_insert_buffer_size=1G

The default for bulk_insert_buffer_size is 8M. That is way too small for large bulk inserts.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.