[aprssig] High speed datbase inserts (CGI)

[Steve Dimse]

On 3/1/05 at 4:35 PM Andrew Rich <vk4tec at tech-software.net> sent:

>I have been experimenting with writting to files every 10 minutes and then
>doing inserts into mysql
>from these files. With the idea of giving the database time to breath for
>searches.
>
Unnecessary. If you have an application where the selects take a long time, and
there is not a need to accesss new data immediately, then you can use INSERT
DELAYED to intelligently batch the inserts. This is far more efficient that what
you are proposing.

http://dev.mysql.com/doc/mysql/en/insert-delayed.html

>I have some questions regarding perl. In the duration I am closing off one
>file and opening up another,
>would the telnet data be "pooled" by Net/Telnet.pm.
>
Not sure what you mean by pooled. If you are asking if the data is buffered, yes
it is, you would not lose data while the program was doing a close/open.

>Would I be better off inserting straight into the database ? and not worry
>about files ?
>
Yes.

>How does findu.com do it ?
>
directly, and without DELAYED, I want new data to become immediately available.
On average over the last month, findu's MySQL averaged 106 transactions per
second, 30% inserts and 60% selects, with a 99.63% key efficiency. You can get
these stats with a program called mytop, like the unix top, but for MySQL. 

You must tune MySQL to the insert/select mix of your application, out of the box
it is set up to be easy on system resources, if you are running it on a box
largely dedicated to the database, you need to tell MySQL it is OK to grab more
resources. There are books written about MySQL optimization, a good place to
start is the MySQL chapter:

http://dev.mysql.com/doc/mysql/en/mysql-optimization.html
 
Steve K4HG