Fast Insert

How to tune up mysql server to make INSERT work very fast without any extra access to hard drive ?(make server very slow)

 

insert queries are the fastest queries in sql, so you can use such a cache system to decrease usage of select queries.

 

please share purpose or the practical example you have come across, this will help us give you best feasible solution.

because i can simply say, keep archive table and your inserts will be fast. keep myisam table with only one auto_increment column and your inserts will be fast..

 

Disable any and all logging within the database, use a lazy writing filesystem.
Or run your client and server within the same OS and use an load local infile to directly load csv files which is orders of magnitude faster then regular inserts.

Or utilize an embedded DB within your application to eliminated protocol overhead and transmissions overhead, these become an api call, you still need to make sure it does no logging and uses lazy writing.

 

How to turn off all logging and how to use that lazy writing filesystem ?

 

and if you could please explain about that embedded DB and how it works ?

thank you very much for your replies

 

I am having some very heavy crawlers which collect information and insert it into mysql, it could be 100 parallel working scripts which doing inserts at the same time, what I already did is used group insert instead of single inserts, still very slow

 

If it is 100 parallel script then forget about the embedded DB.
You might be helped better to see how http://lucene.apache.org/nutch/ does this

 

What is nutch what does it do ?

 

So many professionals... I need more advices Please

 

Nutch is a project that makes an open source webcrawler, this software is used at several sites.

What you can learn from the is how to make your system scale.
This is the same project that runs the hadoop project that aims to replicate the Google Filesystem and HBase which aims to replicate Google BigTables Database.
Since Yahoo uses these Hadoop and HBase programs on a 10.000 node system I am inclined to believe it works.

Don't think databases, think architecture for your solution, and if you have 100 parallel crawlers and you want to proces that data then you can really learn from people who implemented Nutch / Hadoop systems.
Research the Internet for you guidance.

 

Thanks ,

What do you know about /etc/fstab
I read somewhere that with heavy use database it makes I/O going crazy because it has to write to file(update last access time) every time you access database even with SELECT, some people suggesting to set MYSQL database files to NOATIME which make it update only in case of writing to DB.

What is your opinion about that ?