Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

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

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 :)

share|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

Did you try ? I think it should be a good starting point for some basic tuning.

share|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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .