473,287 Members | 1,581 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Going for a LARGE Table: Any Tips?

Hi there

I'm developing a large web application. Part of this web application will
be storing numerical chart data in a MySQL table - these numbers will be
already calculated, and are just being stored for reference.

In this particular table, the stored data will never be deleted or
changed. The only actions performed will be SELECTs and INSERTs. There
will never be any DELETEs or UPDATEs.

The end-result charts themselves have many rows. Let's say about 40-100
rows per chart. I will be storing thousands of charts. I am definitely
looking at the possibility of there being millions (upon millions?) of
rows at some time in the future. I am very aware of relational database
concepts, and one large table truly makes the most sense in this
particular situation. Believe me, there are foreign keys up the wahoo.

A general question to the experts out there (or just people with
experience ;))... what kind of things should I 'look out for', or
prepare for, when designing/using a table that is so large? My previous
MySQL tables in all my years of programming have never really had more
than 40,000 rows. This table will of course be the largest table in a
database that holds many more tables (about 20 or 30).

Here are things I am concerned/wondering about. Any advice on them would
be very much appreciated.

1) Table type: all the tables in the database that depend on data being
inserted/updated to other tables are all InnoDB using transactions. This
table, as mentioned, will strictly be INSERTed to and SELECTed from.
Should I use MyISAM or InnoDB? Just about all columns will be storing
numbers, possibly a varchar column or two, and no blobs/text columns.
However, these columns will need to be 'searched' sometimes.

2) Backing up / Restoring: What should I look out for in terms of
dumping/restoring the table? Anything other than the traditional
mysqldump?

3) If I do go with an InnoDB table... I imagine I will probably have to
change that 'ibdata' setting, or at least get familiar/involved with it,
since this table will be so large. I've seen the term 'multiple
tablespaces' in reference to InnoDB tables. Is this something I should
be investigating for a table of this size? What exactly does it mean?

4) Do I need to think about replication of any sort? Or slave/master
things? Or can I just use the database as is? There would never be more
than 50 or so people using the database at once (if that matters).

The system is a P4 (2.8), running Linux RedHat, MySQL 4.1.7 (though I'm
pretty sure I will have it upgraded to MySQL 5 before putting this into
production or even building it - or do I even need to), 1 GB of RAM....
basically our own box being managed/hosted somewhere across the
continent.

I know this is a lot of advice to ask. I'm self-taught, and have been
re-reading some MySQL books in preperation for this project, which will
be my 'largest' to date, and will be quite complex overall (this is a
small but still critical part of the overall project). The books teach
well, but they don't offer to much advice to specific situations ;)

Thanks.

Nov 11 '05 #1
1 6180
Hi,

very interesting question ;-).

What the really best solution is depends strongly on how frequently you
query the differents parts of the data. Is there something like 'historical'
data (you need to query less often and only with SELECTs) and current data
(which you need all the time)? Then it might be an option to set up an
archive table. Assuming that you're using MySQL 5, you can create views to
query historical and current data together, if necessary (although I would
rather query a single table if possible than the view, even if it might seem
more convinient to always use the view).

For the "current" table I would use InnoDB, because locking is done at the
row level, so you'll probably get a better performance when doing SELECTs
and INSERTs simulaniously. For the historical tables, if you do not
necessarily need foreign key constraints for them (otherwise you would be
bound to InnoDB), I would rather choose MyISAM. With MyISAM, you can also
create compressed read only tables (if you're sure that there will be no
further INSERTs) that consume less disk space and can be queried faster.
With MyISAM, you could e.g. create one archive table for every month and
merge them together - and use this merged table in the view to get a
complete data set. This way, the data is split up into smaller pieces, but
you can still query them all together, if needed, and take advantage from
techniques that are preferable for dynamic data and for static data at the
same time.

This approach would need some maintainance work. You would need to create a
new archive table every new month, move the old data to this new archive
table and re-create the merge table to include the new table. A Stored
Procedure could help you to automate this process. But you would also gain
advantage in doing backups, because you would only have to backup the
archive tables once (they won't change anymore) and you can create smaller
and more frequent backups on the current data.

Replication should be an option, if the performance is still not suffient
(although I think that it will work for 50 users without replication). If
you use replication, you'd do best to use the master server to do the
INSERTs and the slave server to do the SELECTs. In that case, you can set
different indexes for the master table and for the slave table. As indexes
can slow down INSERTs, you could set the indexes only on the slave server's
table (except any unique indexes that you need to ensure data integrity, of
course) to speed up the SELECT statements, but not to slow down the INSERTs.
However, if the server's at a *very* high load (which is very unlikely for
50 users), the slave server could be a bit behind the master server. But in
most cases, the replication slave is almost as up to date as the master
server (maybe 1/100 of a second behind or so ;-)).

Well, so I guess, I gave you something to think about and maybe some other
newsgroup users a basis for further discussion ;-).

Markus
Nov 11 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Robert | last post by:
I am having performance issues on a SQL query in Access. My query is accessing and joining several tables (one very large one). The tables are linked ODBC. The client submits the query to the...
6
by: BT | last post by:
I'm trying to format a three column table and I can't get things to line up the way I need to. The first column is text of variable length that might wrap onto multiple lines. It is within...
1
by: Ian | last post by:
I want to open a form at a particular record, but I think I'm running into problems because the recordsource query is executing asynchronously. In the form's open event I use...
11
by: Brian Henry | last post by:
Well here is the problem, I have a data set with about 9,000 to 20,000 people in it in the data table "people"... I am then reading it into a list view one at a time row by row... adding each...
2
by: Paul Morrison | last post by:
Hi, I am new to Javascript and am having a bit of difficulty. On my site, in order for a member to unsubscribe from an article, they go to the'Unsubscribe' page where they get a table of all of...
10
by: sasquatch | last post by:
X-No-Archive: Are there any good books that provide tips about how to understand and "play with" large C++ programs? It seems like a very important skill, and yet its hardly taught in the...
4
by: Emin | last post by:
Dear Experts, What is the best way to do a large insert WITHOUT having direct access to the machine SQL Server is running on? For example, imagine I want to insert something like 20,000 records....
25
by: tekctrl | last post by:
Anyone: I have a simple MSAccess DB which was created from an old ASCII flatfile. It works fine except for something that just started happening. I'll enter info in a record, save the record,...
6
mideastgirl
by: mideastgirl | last post by:
YIKES! I am having problems going from mideasthonors.org/addmember.php to either the error page because the form has not been filled out accurately, or to the thank you page!!!! Here is my script,...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.