GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

MySql Performance when no. of columns increase...

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySql Performance when no. of columns increase...

    We are using mySQL for a data entry application. The application has 4 stages of data entry, whereby each one updates different table of the same database.

    The number of columns available in each of the 4 tables is around 300. Insert statements are fired in during each stage of processing.

    Per day (10 hour working day) around 3000 inserts are fired on each table. This happens in a sequence (stage 1 -> stage 2 -> stage 3 -> stage 4).

    The point to note is that over 10 days the number of records in each of these 4 tables will grow to anywhere between 30,000 to 50,000 records.

    The number of connections to this database is roughly around 120-150. Concurrency might be in the range of 30-40.

    Would like to know if there are any conditions under which the MYSQL server may fail? As the amount of data stored increases, will fresh data inserts start taking time? Will there be timeouts?

    Any help in this regard would be of great help.

    I am attaching the my.ini file that we are using.

  • #2
    How large is your database in megabytes?

    Because if you say that you have 300 columns in 4 different tables and you have about 40,000 rows:
    Then the storage without any additional indexes will be:

    [average_column_size] * 300 * 40,000 * 4

    So if they where int all of them it would mean 4 bytes per column
    = 192MB.

    So how large is your ibdata1 file?

    And then looking at your my.ini file I would say that a lot of the values are _very_small for a DB that size.
    Which means that you would get very poor performance compared to what you could get.

    My guess is that you have a lot more RAM available on that server than the about 30MB that you have configured MySQL to use right now.

    Read up on the innodb settings, the comments in the file is very explanatory and it is very easy to understand.

    Comment


    • #3
      I guess you would need to provide more details to get meaningfull answers. What do you mean by MySQL failing ?
      what timeouts ?

      Comment

      Working...
      X