Re: [Perfparse-users] Problems with slow insert into mysql DB

[Flo G. <fl...@bi...>]

Hi,

generally the time needed for a insert in a table with N rows is=20
proportional to log N if the table has indexes.

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

In many rdbms one additionally can see a performance drop if cache memory=
=20
is used up. In this case the memory used to cache table index data may be=
=20
to small. Since perfparse uses innodb tables, you should increase the size=
=20
of:

-------- http://dev.mysql.com/doc/mysql/en/innodb-start.html ----------
innodb_buffer_pool_size

The size of the memory buffer InnoDB uses to cache data and indexes of its=
=20
tables. The larger you set this value, the less disk I/O is needed to=20
access data in tables. On a dedicated database server, you may set this to=
=20
up to 80% of the machine physical memory size. However, do not set it too=
=20
large because competition for the physical memory might cause paging in=20
the operating system.

------------------------------------------------------------------------

You should also consider to increase innodb_log_file_size and probably=20
innodb_log_files_in_group to avoid frequent checkpoints. On a big machines=
=20
play with innodb_thread_concurrency.


Hope this helps.

Flo





On Thu, 14 Jul 2005, Sand Philipp wrote:

> Hi,
>
> don't know if some can help me out with that in this list...but here's th=
e question:
>
> my perfparse mysql DB has grown in size over the last months...the poor l=
ittle mysql DB is about 41GB of size ;)
> For inserting the Data in the Database I use the Method, where Nagios put=
s the Data into a serviceperf.log, perfparse gets to this data every 10 min=
utes and Nagios is reloaded then.
> For an insert of about 200KB it takes about 1.30minutes and it seems as i=
t keeps getting slower and slower...
>
> Are there some limits for mysql databases regarding the lines of one tabl=
e or something?? Any "tuning" tips?
>
> Currently I'm using mysql 4.0.21 on fedora core 2 and a Dual 1,4GHz CPU S=
erver with 2GB RAM and 2 Ultra 320 SCSI Harddrives...
>
>
> BTW,
> About half a year ago, there was a plan to redesign the perfparse databas=
e... any news to this? ;)
>
> Thanks in advance!
> Philipp
>
> _____________________________
>
> Philipp Sand
> OC-CC-TEC-SYS
>
> SYCOR GmbH
> Heinrich-von-Stephan-Stra=DFe 1-5
> D - 37073 G=F6ttingen
>
> Telefon=09+49 (0) 551 - 490 - 0
> Telefax=09+49 (0) 551 - 490 - 232468
>
> phi...@sy...
> www.sycor.de
> ------------------------------------------------
> =A0
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by the 'Do More With Dual!' webinar happen=
ing
> July 14 at 8am PDT/11am EDT. We invite you to explore the latest in dual
> core and dual graphics technology at this free one hour event hosted by H=
P,
> AMD, and NVIDIA.  To register visit http://www.hp.com/go/dualwebinar
> _______________________________________________
> Perfparse-users mailing list
> Per...@li...
> https://lists.sourceforge.net/lists/listinfo/perfparse-users
>