MySQL question

MySQL question



David Hubbard wrote:
> Locking will only be an issue when using dbf files on
> shared storage, but Empresa includes a shared-storage-safe
> locking mechanism so there should not be problems in that
> setup. MySQL-based load balancing greatly removes a lot
> of the overhead compared to mivadata on shared storage
> load balancing because you no longer have multiple servers
> competing for access to the same files, you just have
> multiple servers issuing queries to one mysql server
> and it worries about the locking and unlocking of tables
> in mysql.

Let's say the store is going to use MySQL and load balancing with the
shared data on the one server and shared scripts on another. All is
fine. Now you decide to run a module that uses the dbf for its internal
config files. That module expects to write the data to the mivadata
directory. But to be specific it expects to write it to
mivadata/Merchant5/sXX/module subdirectory. In this MySQL store, the
mivadata/Merchant5/sXX/ directory does not exist or does it? If I
understand this correctly, there is a Merchant5/sXX but it is not under
mivadata if it is MySQL. If this is correct, then every module is going
to have to check for the mivadata/Merchant2/sXX directory before
creating its module data subdirectory. Heaven help us if the fexists
returns a false negative and the module starts creating directories that
could have been created at setup.mvc time.

--
Bill Weiland A2Z Emporium Plus <A HREF ="http://www.emporiumplus.com/store.mvc ">http://www.emporiumplus.com/store.mvc </A>
Modules for eCommerce. Mail Mgr, Coupon, PayPal, Froogle/Yahoo feeds
Rate This, Gift/Wish List, Wait List Mgr, EZ Batch, Shipping & more
Online Documentation <A HREF ="http://www.emporiumplus.com/docs">http://www.emporiumplus.com/docs</A>
Question <A HREF ="http://www.emporiumplus.com/mivamodule_wcw.mvc?Screen=SPTS ">http://www.emporiumplus.com/mivamodu...vc?Screen=SPTS </A>
|

MySQL question



Heads up Bill. All of the big hosts plan to use remote storage. Heck =
alot of them do that already. So if you are saying your not writing your =
modules to take full advantage of MySQL I suggest you reconsider.=20

If I am misreading what you are saying then disregard.


Wil Hatfield
HyperConX Customer Care

HyperConX International - <A HREF ="http://www.hyperconx.com">http://www.hyperconx.com</A>
1.800.894.3613 - Toll Free in the US and Canada

Check out the all new Miva Pages:
<A HREF ="http://www.hyperconx.com/miva/">http://www.hyperconx.com/miva/</A>

Premium e-commerce hosting, 24/7 technical support, toll free=20
support lines for your convenience, great low cost packages to
choose from, Authorize.Net Direct retailer, need high-speed=20
connectivity well we have that too. Everything a business=20
needs to succeed. Host with the Pros and sell like one too!




-----Original Message-----
From: [email protected]
[mailto:[email protected]]On Behalf Of William
Weiland
Sent: Thursday, May 05, 2005 6:02 AM
To: [email protected]
Subject: RE: [m5u] MySQL question


In most scenarios, the MM5 would not need the load balancing across =
multiple servers, so the issue would not come up. But when a store =
decides it wants that load balancing, there will be database issues =
along with the issue of needing mirror scripts copies of the modules. =
Updating module versions (3rd party and miva core) will be a headache. =
It is one thing for the host to unzip multiple copies of core mvc files, =
but what about the new update wizard. It is going to flow the new =
module versions to one location. So you will have one server updated =
with new scripts and others not. It definitely is going to require some =
sort of technique to put all scripts on the single server. If that is =
done, then it might not be an issue with the data either. This is going =
to be a trial and error experimentation. I would hope that hosts share =
their knowledge rather than "trade secret" these issues. Film at 11.


--=20
Bill Weiland - Emporium Plus <A HREF ="http://www.emporiumplus.com/store.mvc=20">http://www.emporiumplus.com/store.mvc=20</A>
Modules for eCommerce. Mail Mgr, Coupon, Rate This, Wait List Mgr,=20
PayPal, Gift/Wish List, Froogle data feed, Shipping & dozens more
Online Documentation <A HREF ="http://www.emporiumplus.com/tk3/v3/doc.htm=20">http://www.emporiumplus.com/tk3/v3/doc.htm=20</A>
Question <A HREF ="http://www.emporiumplus.com/mivamodule_wcw.mvc?Screen=3DSPTS =20">http://www.emporiumplus.com/mivamodu...?Screen=3DSPTS =20</A>
|
=20
---- David Hubbard <[email protected]> wrote:=20
> From: William Weiland
> >=20
> > The modules within merchant were written that way because=20
> > they have had a year or two to write them. You know how
> > long we have had since Miva settled on how and where the
> > data would be located and files named. So the suggested
> > first step was to use the old access method for your

> > module's own configuration and concentrate on the mivasql methods =
for
> > accessing store data (products/cats/orders/etc). The next step, =
which
> > will take considerable time if the module has a lot of configuration
> > databases, will be to convert the module's internal data to mivasql
> > calls. =20
>=20
> Ah, I didn't realize that was even an option. That
> definitely would raise some issues with load balancing;
> those considering it would need to ensure that the
> modules they are using have been written using the
> SQL method so no surprises result.
>=20
> David
>=20
>=20

MySQL question



From: [email protected]=20
>=20
> Heads up Bill. All of the big hosts plan to use remote=20
> storage. Heck alot of them do that already. So if you are=20
> saying your not writing your modules to take full advantage=20
> of MySQL I suggest you reconsider.=20

In his case it would not matter because very few, if
any, hosts would be using a combination of a mysql
back-end and multiple web servers on the front end
that are not sharing their storage. If he writes to
mivadata, it's going to be the same mivadata no matter
which web server handles the request if there are
multiple web servers involved.

David

> If I am misreading what you are saying then disregard.
>=20
>=20
> Wil Hatfield
> HyperConX Customer Care
>=20
> HyperConX International - <A HREF ="http://www.hyperconx.com">http://www.hyperconx.com</A>
> 1.800.894.3613 - Toll Free in the US and Canada
>=20
> Check out the all new Miva Pages:
> <A HREF ="http://www.hyperconx.com/miva/">http://www.hyperconx.com/miva/</A>
>=20
> Premium e-commerce hosting, 24/7 technical support, toll free=20
> support lines for your convenience, great low cost packages to
> choose from, Authorize.Net Direct retailer, need high-speed=20
> connectivity well we have that too. Everything a business=20
> needs to succeed. Host with the Pros and sell like one too!
>=20
>=20
>=20
>=20
> -----Original Message-----
> From: [email protected]
> [mailto:[email protected]]On Behalf Of William
> Weiland
> Sent: Thursday, May 05, 2005 6:02 AM
> To: [email protected]
> Subject: RE: [m5u] MySQL question
>=20
>=20
> In most scenarios, the MM5 would not need the load balancing=20
> across multiple servers, so the issue would not come up. But=20
> when a store decides it wants that load balancing, there will=20
> be database issues along with the issue of needing mirror=20
> scripts copies of the modules. Updating module versions (3rd=20
> party and miva core) will be a headache. It is one thing for=20
> the host to unzip multiple copies of core mvc files, but what=20
> about the new update wizard. It is going to flow the new=20
> module versions to one location. So you will have one server=20
> updated with new scripts and others not. It definitely is=20
> going to require some sort of technique to put all scripts on=20
> the single server. If that is done, then it might not be an=20
> issue with the data either. This is going to be a trial and=20
> error experimentation. I would hope that hosts share their=20
> knowledge rather than "trade secret" these issues. Film at 11.
>=20
>=20
> --=20
> Bill Weiland - Emporium Plus <A HREF ="http://www.emporiumplus.com/store.mvc=20">http://www.emporiumplus.com/store.mvc=20</A>
> Modules for eCommerce. Mail Mgr, Coupon, Rate This, Wait List Mgr,=20
> PayPal, Gift/Wish List, Froogle data feed, Shipping & dozens more
> Online Documentation <A HREF ="http://www.emporiumplus.com/tk3/v3/doc.htm=20">http://www.emporiumplus.com/tk3/v3/doc.htm=20</A>
> Question <A HREF ="http://www.emporiumplus.com/mivamodule_wcw.mvc?Screen=3DSPTS =">http://www.emporiumplus.com/mivamodu...?Screen=3DSPTS =</A>

> |
> =20
> ---- David Hubbard <[email protected]> wrote:=20
> > From: William Weiland
> > >=20
> > > The modules within merchant were written that way because=20
> > > they have had a year or two to write them. You know how
> > > long we have had since Miva settled on how and where the
> > > data would be located and files named. So the suggested
> > > first step was to use the old access method for your
>=20
> > > module's own configuration and concentrate on the mivasql=20
> methods for
> > > accessing store data (products/cats/orders/etc). The=20
> next step, which
> > > will take considerable time if the module has a lot of=20
> configuration
> > > databases, will be to convert the module's internal data=20
> to mivasql
> > > calls. =20
> >=20
> > Ah, I didn't realize that was even an option. That
> > definitely would raise some issues with load balancing;
> > those considering it would need to ensure that the
> > modules they are using have been written using the
> > SQL method so no surprises result.
> >=20
> > David
> >=20
> >=20

MySQL question



From: Bill Guindon [mailto:[email protected]]=20
>=20
> 'run' was a poor choice of words on my part. I should have used
> 'install' in it's place.
>=20
> I see from your other post that installing multiple copies of Merchant
> is one approach, but the maintenance issues that Bill mentioned
> (upgrades, images, etc.) would seem to make that a poor choice.

In most cases it would be a headache as a replication
mechanism would have to be developed, but there are
inherent advantages for some. If your needs are
absolute availability, then having a separate copy
of the scripts on each web server means any web server
can die with no outage and there is also no central
file server or appliance that could die and take all
the web servers with it. Then on the back-end mysql
can be set up for replication between two servers so
you're 100% redundant in every way. That's going to
be a pretty rare setup for a Miva Merchant store though. :-)

David

MySQL question



From: William Weiland
>=20
> Let's say the store is going to use MySQL and load balancing
> with the shared data on the one server and shared scripts on
> another. All is fine. Now you decide to run a module that
> uses the dbf for its internal config files. That module
> expects to write the data to the mivadata directory. But
> to be specific it expects to write it to
> mivadata/Merchant5/sXX/module subdirectory. In this MySQL
> store, the mivadata/Merchant5/sXX/ directory does not exist
> or does it? If I understand this correctly, there is a
> Merchant5/sXX but it is not under mivadata if it is MySQL.
> If this is correct, then every module is going
> to have to check for the mivadata/Merchant2/sXX directory before
> creating its module data subdirectory. Heaven help us if the fexists
> returns a false negative and the module starts creating=20
> directories that could have been created at setup.mvc time. =20

My assumption is that if you are using SQL in your
module, a test for whether something exists will
return the same result whether the underlying database
is mivasql or mysql since the commands are the same.
Then if the mivadata resides on shared storage, the
xbase-specific commands would also act as normal.

David

MySQL question



Wil Hatfield wrote:
> Heads up Bill. All of the big hosts plan to use remote storage. Heck alot of them do that already. So if you are saying your not writing your modules to take full advantage of MySQL I suggest you reconsider.
> Wil Hatfield
> HyperConX Customer Care

The next step in the modules is to change the module's configuration
type tables into SQL compatibility. They run fine already as they are
in mivasql and most mysql. But in some rare occassions they may not.
It is prudent to develop for the 99%. The 1% will get taken care of
over the next couple months.

--
Bill Weiland A2Z Emporium Plus <A HREF ="http://www.emporiumplus.com/store.mvc ">http://www.emporiumplus.com/store.mvc </A>
Modules for eCommerce. Mail Mgr, Coupon, PayPal, Froogle/Yahoo feeds
Rate This, Gift/Wish List, Wait List Mgr, EZ Batch, Shipping & more
Online Documentation <A HREF ="http://www.emporiumplus.com/docs">http://www.emporiumplus.com/docs</A>
Question <A HREF ="http://www.emporiumplus.com/mivamodule_wcw.mvc?Screen=SPTS ">http://www.emporiumplus.com/mivamodu...vc?Screen=SPTS </A>
|

MySQL question



--------------010205090405040601060207
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit



David Hubbard wrote:

>From: Bill Guindon [mailto:[email protected]]
>
>
>>'run' was a poor choice of words on my part. I should have used
>>'install' in it's place.
>>
>>I see from your other post that installing multiple copies of Merchant
>>is one approach, but the maintenance issues that Bill mentioned
>>(upgrades, images, etc.) would seem to make that a poor choice.
>>
>>
>
>In most cases it would be a headache as a replication
>mechanism would have to be developed, but there are
>inherent advantages for some. If your needs are
>absolute availability, then having a separate copy
>of the scripts on each web server means any web server
>can die with no outage and there is also no central
>file server or appliance that could die and take all
>the web servers with it. Then on the back-end mysql
>can be set up for replication between two servers so
>you're 100% redundant in every way. That's going to
>be a pretty rare setup for a Miva Merchant store though. :-)
>
>David
>
>
Yep, this is all just an excersize in single point of failure management.

Even the replicated mysql server is not EXACTLY hands free. Primary goes
down and it needs intervention to send the queries to the secondary,
although I suppose if you had a lot of money, you could fix that too...

Same with the extra hardware load balancer. We have a spare, but it
would require some wires to be swapped...

It just so happens that I have a spare Netapp for the backup of my
"centeral storage", as well as redundant load balancing switches...

It's a constant formula of how complicated and expensive you want to get
for the benefit you receive...

Sometimes, simpler is better, and these days, single servers don't crash
like they used to, but if you have the resources, you can build a system
that has quite a bit of failover.

Tim.


--------------010205090405040601060207--

MySQL question



Hola Tim! Don't think we've chatted much since the conference last year.

Just as an aside, one semi-easy solution for $400-$700 (better using
FreeBSD) would be:

<A HREF ="http://www.etinc.com/index.php?page=bwmgr.htm">http://www.etinc.com/index.php?page=bwmgr.htm</A>

Too many features for a normal human to fully grasp, but an incredibly
powerful package when looked at with mission-specific blinders. Its
prioritization options can directly address some of what you are
describing, and largely do it in a "set and forget" mode. The company has
a rep for being incredibly rude at times, but sometimes you just have to
accomodate a pissed programmer's brillance (large smile). Their hardware
prices suck rocks -- but their application does not.

Jonathan
Driftwood Network Services



At 01:46 PM 5/5/2005, Tim Traver wrote:
>Yep, this is all just an excersize in single point of failure management.
>
>Even the replicated mysql server is not EXACTLY hands free. Primary goes
>down and it needs intervention to send the queries to the secondary,
>although I suppose if you had a lot of money, you could fix that too...
>
>Same with the extra hardware load balancer. We have a spare, but it would
>require some wires to be swapped...
>
>It just so happens that I have a spare Netapp for the backup of my
>"centeral storage", as well as redundant load balancing switches...
>
>It's a constant formula of how complicated and expensive you want to get
>for the benefit you receive...
>
>Sometimes, simpler is better, and these days, single servers don't crash
>like they used to, but if you have the resources, you can build a system
>that has quite a bit of failover.
>
>Tim.
>

MySQL question



Hey Jonathan !

That's an interesting piece of software. I am partial to hardware based
solutions though, like ArrowPoint (now Cisco), or Foundry ServerIrons.
The boot times are much faster, and the kernels are much smaller and
less complicated.

Problem with MySQL is that even if you've put two MySQL servers behind a
VIP, and had the slave as the failover, that would only work for reads,
and not writes until you made the slave a master server.

Like I had mentioned, its all about what steps you want to take at 3
a.m. when something goes down (knock on driftwood), and you have to make
a quick change to get it back going again on the redundant hardware...

The F5 guys were a software load balancer in the beginning (I think they
have both software and hardware stuff now), but I remember people in our
access center using F5's and having horrible troubles with them...

Of course, we've come a long way with this computer stuff, huh ?

t


Jonathan - Driftwood wrote:

> Hola Tim! Don't think we've chatted much since the conference last year.
>
> Just as an aside, one semi-easy solution for $400-$700 (better using
> FreeBSD) would be:
>
> <A HREF ="http://www.etinc.com/index.php?page=bwmgr.htm">http://www.etinc.com/index.php?page=bwmgr.htm</A>
>
> Too many features for a normal human to fully grasp, but an incredibly
> powerful package when looked at with mission-specific blinders. Its
> prioritization options can directly address some of what you are
> describing, and largely do it in a "set and forget" mode. The company
> has a rep for being incredibly rude at times, but sometimes you just
> have to accomodate a pissed programmer's brillance (large smile).
> Their hardware prices suck rocks -- but their application does not.
>
> Jonathan
> Driftwood Network Services
>
>
>
> At 01:46 PM 5/5/2005, Tim Traver wrote:
>
>> Yep, this is all just an excersize in single point of failure
>> management.
>>
>> Even the replicated mysql server is not EXACTLY hands free. Primary
>> goes down and it needs intervention to send the queries to the
>> secondary, although I suppose if you had a lot of money, you could
>> fix that too...
>>
>> Same with the extra hardware load balancer. We have a spare, but it
>> would require some wires to be swapped...
>>
>> It just so happens that I have a spare Netapp for the backup of my
>> "centeral storage", as well as redundant load balancing switches...
>>
>> It's a constant formula of how complicated and expensive you want to
>> get for the benefit you receive...
>>
>> Sometimes, simpler is better, and these days, single servers don't
>> crash like they used to, but if you have the resources, you can build
>> a system that has quite a bit of failover.
>>
>> Tim.
>>
>
>
>

MySQL question



I haven't completely read throught this thread. so forgive me if it's
been addressed.
But that seems kinda really weird to me... I've done some programming in
perl, php, vbasic etc.
Once you get the database connector done.. it's so easy to hook it all up.
IF the developers are worried about multi load balanced front ends and
want a lisc for each one then it makes sense.
I (yes I"m a merchant so I"m biased) would remind them that most of
their customer storefronts don't do this and will never do this.
For the few that do... talk about great exposure and advertising to be
able to say "so and so uses my modules and their store is kicking butt
and making them money"
that is worth something.

If it's the "I want to sell a lisc for each front end site" then the
config file on each box makes a lot of sense.
But does it even have to be in a database file format? Why not just
stick it in a teeny one line text file.
Call it a key file or something like that. If the key is on the local
machine and the code in the key matches the domain
then let the sucker run. You could even have your module cache the key
in ram for an increase in speed. But
truthfully doing reads on a little 4K file ain't that big a deal.

Or am I missing something?

Kelly

William Weiland wrote:

>David Hubbard wrote:
>
>
>>That's correct. You can load balance a Merchant store that
>>uses dbf files by sharing the mivadata directory amongst
>>multiple servers in some manner, but my testing of the
>>common ways of doing that showed that the performance
>>penalty that results was too great to make it practical.
>>The mysql-based Merchant on the other hand solves this
>>problem as sending the queries to a local instance is just
>>a few milliseconds faster than sending them over the network
>>to a central mysql server.
>>
>>But my impression of Merchant 5 was that all modules are
>>written using SQL statements and then if one chooses to
>>use the mivasql back-end, Miva's translation layer kicks
>>in and redirects those operations into dbf files. So you
>>can effectively choose mivasql or mysql on the backend
>>with no change at the application layer?
>>
>>David
>>
>>
>
>The modules within merchant were written that way because they have had
>a year or two to write them. You know how long we have had since Miva
>settled on how and where the data would be located and files named. So
>the suggested first step was to use the old access method for your
>module's own configuration and concentrate on the mivasql methods for
>accessing store data (products/cats/orders/etc). The next step, which
>will take considerable time if the module has a lot of configuration
>databases, will be to convert the module's internal data to mivasql
>calls.
>
>
>

MySQL question



I've done load balances serving before as an employee of a company.

ON apache.. I'd have a cron job check for differences in file structure
of certain directories and just
copy the new stuff over periodically. Not a big deal and pretty seamless

On MS it's not quite that easy, but the same thing can be done and
trigged by the scheduler.

Also if it were me and I hadn't set that auto copy up yet... I"d know
enought that I'd need to go install the dang thing on all my servers
manually to get the files onto them.
I doubt anyone is going to be doing loadbalancing and not realize that
it's a little more complicated than just one server.

Kelly


>Another issue. When installing a 3rd party module, the admin upload is
>putting it on one server. Who is putting the mirror on all the other
>servers when load balancing?
>
>
>

MySQL question



I think even having the graphics on a totally different machine would
help too.
You could have merchant load balanced, but keep the database and the
graphics somewhere else on diff machines.
It would be invisible to the customers...
but then you can have the servers optimized for the type of content they
are serving up.
The best config for the sql box isn't going to be the same as one
serving http requests or just graphics.

you could have one sql server
1 sql server (or 2 one that is updated periodically to use for failover
if the primary fails)
10 merchant servers
2 graphics servers

I can only dream my stores should get so big to warrant that..

Kelly

David Hubbard wrote:

>From: [email protected]
>
>
>>David Hubbard wrote:
>>
>>
>>>But my impression of Merchant 5 was that all modules
>>>
>>>
>>Another issue. When installing a 3rd party module, the admin
>>upload is putting it on one server. Who is putting the mirror
>>on all the other servers when load balancing?
>>
>>
>
>The server admin managing a load balanced cluster has
>to manage that in some way. It would be feasible to
>put the script files on shared storage so all the web
>servers share the same copy of the scripts since the
>I/O demands on that side are minimal; that would avoid
>the need to manually replicate the files since the
>replication would have to include graphics as well as
>even Merchant itself due to the streaming updates.
>
>David
>
>

MySQL question



And regarding load balancing David Hubbard said:

> Technically you are running Empresa and Merchant on each
> server, they just might be sharing at least the same data,
> or at most the same files as well.

And this begs a licensing question.

In such an environment should one need a separate MIVA Merchant
license number for each load balanced box? Or at least have to
buy a special load balancing license?

Obviously there is no place to store such multiple license
numbers and I am fairly certain MIVA's license manager isn't
smart enough to detect the need, and the license agreement only
restricts use based on "Internet Domain" and "Storefront", and
the definition of each does not seem to prevent load balancing.

Empresa used to be licensed on a per CPU basis so that if you had
a dual CPU server you had to pay for two Empresa licenses, so I
assume there is a reasonable chance MIVA might consider requiring
some sort of additional license fees for load balanced MIVA
Merchant. And I think it might be in MIVA's best interest to
consider this.

- Jeff Huber
President, 4TheBest eCommerce Solutions
http://4TheBest.com
[email protected]
Office: 760-742-1469
Cell: 760-445-8454
=20

MySQL question



From: [email protected]=20
>=20
>=20
> And this begs a licensing question.
>=20
> In such an environment should one need a separate MIVA Merchant
> license number for each load balanced box? Or at least have to
> buy a special load balancing license?

I don't think one should have to; Miva shouldn't
penalize people for growing their store large, this
isn't an Oracle product. :-)

> Obviously there is no place to store such multiple license
> numbers and I am fairly certain MIVA's license manager isn't
> smart enough to detect the need, and the license agreement only
> restricts use based on "Internet Domain" and "Storefront", and
> the definition of each does not seem to prevent load balancing.

This is a very good point, their license server would most
likely disable the license in some load balanced configurations.
The reason is that it is tied to the primary IP address of
the server that does the check-in; so you'd have multiple
servers checking in based on which one you hit when logging
into the admin and Miva's license server would disable it
because the license would keep shifting between IP's. The
exception would be load balancers that rewrite the address of
the return traffic to always be the virtual IP. You could get
around this by going directly to a back-end server for
admin duties or only putting SSL on one of the load balanced
servers but Miva may end up having to at least make the
license server tolerant of load balancing in some way should
people start doing that more.

> Empresa used to be licensed on a per CPU basis so that if you had
> a dual CPU server you had to pay for two Empresa licenses, so I
> assume there is a reasonable chance MIVA might consider requiring
> some sort of additional license fees for load balanced MIVA
> Merchant. And I think it might be in MIVA's best interest to
> consider this.

The Empresa VM has always been free so that shouldn't be an issue.

David