Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

What am I going to do when the database becomes too big?


livefooduk

Recommended Posts

Posted

My new OSC store has been live for just over one week and the compressed size of a data base backup has grown from 77,161 bytes (finished shop no customers) to 295,162 (around 600 customers and 600 orders)

What am I going to do when the database becomes too big? We have a customer base of around 4,000 customers of which 60% place their orders via the internet.

It used to take 1-2 seconds to backup now it takes 20-30 seconds at this rate it will be totally unmanageable in 2 or 3 years time.

I think we need a way to automatically remove all orders over say 6 months old or we will have 31,000 orders on there after 1 year.

 

(BTW don't think "wow this guy must be making loads" they are all VERY LOW value orders :cry: )

Best wishes

Steve

Posted

You do a SQL querry to copy the old transactions to a backup database. Then remove the older transactions.

 

aj

"Of the things we think, say or do:

Is it the TRUTH?

Is it FAIR to all concerned?

Will it build GOODWILL and BETTER FRIENDSHIPS?

Will it be BENEFICIAL to all concerned?"

Posted

Think of your 'Order History' when you are deciding on what you believe to be old orders.

 

You may have regular visitors to your site, that only purchase something once in a blue moon.

 

What I'd do, is create an SQL query based on the users last visit date... then, if the date exceeds your set time frame, move the data to a backup database (which you will only need to backup once since no new information will be added) and somehow have it linked so their order history checks the current database, then the backup database.

 

My $0.02 worth...

 

Tony

"The price of success is perseverance. The price of failure comes much cheaper."

Posted

in two 3 years time the datebase will be differant as well as things will possible change...although im thinking with the large database test that i have done....it takes about 2-3 mins to do....which from the numbers you have shown i had about 6 times more data information. So im sure if you want to keep a finacial backing and well secure site as well as old data records....im sure the wait is well worth it.....

 

heck just take a bathroom break...

Posted

Be sure to optimize all of your tables, that could help considerably.

 

Also, if you selected to store the sessions in the database clear them out or create a cron job that automatically deletes old sessions.

 

The time that you mentioned is that what it takes to do the mysqldump or to transfer the file? Even with that large of a database, the dump should not take that long. :?:

"Great spirits have always found violent opposition from mediocre minds. The latter cannot understand it when a man does not thoughtlessly submit to hereditary prejudices but honestly and courageously uses his intelligence." - A. Einstein

Posted
You do a SQL querry to copy the old transactions to a backup database. Then remove the older transactions.

 

aj

That is what I need but I am quite new to all this and don't have a clue how I would write an sql query to do that. :cry:

Could someone recomend a good (reasonably priced) programer that could write that for me?

Be sure to optimize all of your tables, that could help considerably.

 

I have heard a little about this and about "indexing the tables" I haven't got a clue what that is all about :cry:

 

Also, if you selected to store the sessions in the database clear them out or create a cron job that automatically deletes old sessions.

Again my knowledge is sadly lacking I know what a cron job is but not how to set it up, I have my own dedicated server but I do not have root access (I do have shell access) can I still set up a cron job?

The time that you mentioned is that what it takes to do the mysqldump or to transfer the file? Even with that large of a database, the dump should not take that long.

That is the time it takes from me clicking the "backup database" button in OSC admin (backup gzipped and stored in admin directory not downloaded) to when it says "your back up was succesful"

When I did a restore (again from the admin directory not uploaded) to the test copy of my shop the restore took nearly a minute, I am supprised the browser did not "time out" before it finished.

Best wishes

Steve

Posted

A couple of things that I have told my users (based on level of user/ programmer experience)

 

All mysql data is stored textualy. This means that your backup is a text file that can be read by a text editor.

 

All of the data in the backup is broken down by the individual table that it comes from. Each table in the database has an unique ID system that automatically increments by 1 for each new record added ( even if data is deleted).

 

What this allows you do do is to create DELTA's or incremental backups.

 

There are some tables in the database that you can purge on a fairly regular basis- Order history, sessions are just two.

 

Before you purge do a master backup, then delete out old or unneccessary data using a utility such as PHPMYADMIN. As long as you have the original data each subsequent backup will contain only those NEW data items that have been added since that last major backup.

 

Should you ever want to reconnect all of your delta backups together , the task is fairly easy using MySQL.

 

However if your database is hosed - well there's really no point in closing the gate if the pigs are jumping over the fence.

 

SO...

 

IF I may make a reccomendation: LEARN MYSQL! :!:

 

As a store installer/operator I would think that it would be a good business decision. You can help yourself by creating a secondary store on another domain and practice, practice, practice. Copy over the existing data set and use it to learn on, make mistakes, etc.

 

Simple MySQL commands can be very powerfull and will take you a long way.

 

heres a link to the MySQL doc:

http://www.mysql.com/documentation/mysql/index.html

 

check out the sections on SELECT, INSERT, DELETE, DROP, CREATE, INDEX

Loren

 

 

The great thing about beating your head against the wall is that it feels SO GOOD when you stop!

Posted

I found the Core MySQL book helpful

 

isbn 0-13-066190-2

 

Reviewed by MySQL lead developer Michael Widenius.

 

aj

"Of the things we think, say or do:

Is it the TRUTH?

Is it FAIR to all concerned?

Will it build GOODWILL and BETTER FRIENDSHIPS?

Will it be BENEFICIAL to all concerned?"

Posted

Jaguar, thank you that seems to be the perfect solution I was looking for, I had visions of having to store hundreds of different backups with the deleted data on them, at least I now know I don't have to do that :(

However if your database is hosed - well there's really no point in closing the gate if the pigs are jumping over the fence.

I assume that you meant to type "hosted"? well my whole server is hosted but it is not shared and the database is on the same server (I think it must be any way I use "localhost" to connect to it) so would there be any reason why I could not do as you suggested?

I can and have set up a copy of my store in another directory with it's own database so I guess I will have to get reading and start practicing :(

 

Thank you all for the your time, help and advise.

Best wishes

Steve

Posted

No, I am pretty sure he meant hosed....

 

The process of backing up your data for sales is not that hard. You should be backing it up anyway once a month, quarter, year, etc.

 

If you do, then reconnecting all the data, should the need arise, is quite simple. You could also reconnect the data offline if you need it elsewhere.

 

aj.

"Of the things we think, say or do:

Is it the TRUTH?

Is it FAIR to all concerned?

Will it build GOODWILL and BETTER FRIENDSHIPS?

Will it be BENEFICIAL to all concerned?"

Posted
No, I am pretty sure he meant hosed....

Well now I am really confused :roll: What the heck is "hosed"

Any way I have ordered that book from amazon so I expect all these strange terms may have some meaning to me once I start ploughing my way through that.

Best wishes

Steve

Posted

hosed /adj./ Same as down. Used primarily by Unix hackers. Humorous: also implies a condition thought to be relatively easy to reverse. Probably derived from the Canadian slang `hoser' popularized by the Bob and Doug Mackenzie skits on SCTV, but this usage predated SCTV by years in hackerdom (it was certainly already live at CMU in the 1970s). See hose. It is also widely used of people in the mainstream sense of `in an extremely unfortunate situation'.

 

Once upon a time, a Cray that had been experiencing periodic difficulties crashed, and it was announced to have been hosed. It was discovered that the crash was due to the disconnection of some coolant hoses. The problem was corrected, and users were then assured that everything was OK because the system had been rehosed. See also dehose.

 

http://jargon.net/jargonfile/h/hosed.html

 

aj

"Of the things we think, say or do:

Is it the TRUTH?

Is it FAIR to all concerned?

Will it build GOODWILL and BETTER FRIENDSHIPS?

Will it be BENEFICIAL to all concerned?"

Posted

Like 26.2 said :lol:

 

Hosed as in : Why did I ever get into computers in the first place. :wink:

 

Yes , I did mean Hosed!.

 

 

Incremental backups will same you alot of time and effort. This is standard practice in traditionaly large database installations.

 

In addition you can also run DUAL databases. However , lets just concentrate on getting your single database healthy. Then we can talk about a shared database environment.

 

One of the earlier posters made a comment about Indexes. I agree, that a bad Index or a Index that has been improperly desinged can be a real bottleneck in the dataflow.

 

If you have created secondary tables , this could be a issue. If you have not and are using a plain vanilla version of the database as it comes from OSC than incremental backups (DELTA's) are a viable solution.

 

 

Thanks for the post on SCTV - 26.2, It brought back memories of "Bob & Doug", "The One Door Mall, and "Count Floyd" as well as "Edith Prickly"

 

:lol:

Loren

 

 

The great thing about beating your head against the wall is that it feels SO GOOD when you stop!

Posted

oh how the memories start to rush back.

 

Makes me almost teary eyed for my Ohio Scientific with 16Kb of RAM, 5 1/4 floppy drives and Green Monochrome screens. Say what is a mouse for anyway? We didn't need it for cpm. Just look at what those Xerox clowns started! Why I bet you could trace the decline of SNL and the cancelation of SCTV to gooey operating systems. :lol:

 

aj

"Of the things we think, say or do:

Is it the TRUTH?

Is it FAIR to all concerned?

Will it build GOODWILL and BETTER FRIENDSHIPS?

Will it be BENEFICIAL to all concerned?"

Posted
If you have created secondary tables , this could be a issue. If you have not and are using a plain vanilla version of the database as it comes from OSC than incremental backups (DELTA's) are a viable solution.

 

I have added quite a few extra tables for some of the contributions I have added, does this mean that incremental backups are now not a viable solution? :cry:

Best wishes

Steve

Posted

No problem doing the incremental backups on tables that are added to OSC in MySQL.

 

My statement regarding the additional databases was in reference to Indexes that you or contributors may have created on new tables. Indexes are a factor in proper data flow.

 

Incremental backups can easily be done using the same process whether the database is virgin OSC or a compliation of OSC and new tables.

 

All of the tables in MYSQL whether created by the OSC team or yourself will react the same way when doing incremental backups. Ideally you or somebody else has created a unique ID in the tables allowing your to easily reconect the data at some distant pint in the future, if no no big deal justa little more SQL code is needed.

 

 

Creating a secondary OSC site for your learning is a good way to go. I do it all the time. In fact I currently have three different versions running fora client now, 1=Production, 2= Dev 3= Dev + Contributions

 

I sync all of the code and the databases up when I am ready to release the live version.

Loren

 

 

The great thing about beating your head against the wall is that it feels SO GOOD when you stop!

Posted

Steve, as soon as you get the book or right now online at mysql you need to start reading and learning. It will all make more sense once you have learned the basics of sql.

 

You can do this!

 

aj

"Of the things we think, say or do:

Is it the TRUTH?

Is it FAIR to all concerned?

Will it build GOODWILL and BETTER FRIENDSHIPS?

Will it be BENEFICIAL to all concerned?"

Posted

Get the book , dont wait for the movie. :D

 

Another good book on MYSQL database is from NewRiders Publishing (actually they have two) One is tittled "MYSQL" and the other is called "PHP Functions"

 

They are fairly distinctive in the bookshelf as the yhave a completly red spine with bold white lettering.

Loren

 

 

The great thing about beating your head against the wall is that it feels SO GOOD when you stop!

Posted

Thanks guys, I have suprised myself with my understanding of php (not that I understand much but I have got to grips with some of it), so I WILL get stuck in and learn this mysql stuff too, but it's slow going for a guy who left school barely able to read or write.

Thanks again for all your help :D

Best wishes

Steve

Posted

The MySQL manual has all of the information that you need to get started while you are waiting for the books to arrive and is free.

 

Browse through that and by the time the books come you should have a good head start that will be further reinforced by reading the books.

"Great spirits have always found violent opposition from mediocre minds. The latter cannot understand it when a man does not thoughtlessly submit to hereditary prejudices but honestly and courageously uses his intelligence." - A. Einstein

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...