MySQL Performance: MySQL => MySql Performance when no. of columns increase...

Members

Help

Home

Home » Performance » MySQL » MySql Performance when no. of columns increase... (

) 1 Vote(s)

Show: Today's Messages :: Show Polls :: Message Navigator

MySql Performance when no. of columns increase... [message #1268]

Thu, 17 May 2007 02:07

agopal
Messages: 1
Registered: May 2007

Junior Member

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.

Attachment: my.ini
(Size: 9.03KB, Downloaded 184 time(s))

Report message to a moderator

Re: MySql Performance when no. of columns increase... [message #1272 is a reply to message #1268 ]

Fri, 18 May 2007 16:29

sterin
Messages: 323
Registered: March 2007
Location: Sweden

Senior Member

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.

Report message to a moderator

Re: MySql Performance when no. of columns increase... [message #1626 is a reply to message #1268 ]

Thu, 16 August 2007 11:47

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

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

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Previous Topic:	Slow page load - seeking to hire server assistance asap
Next Topic:	Strange behaviour on RH4.0 with MySQL 5.037

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 20 22:04:40 EST 2008

Total time taken to generate the page: 0.00985 seconds

.:: Contact :: Home :: MySQL Support by Percona.com ::.