WinGate HTTP cache

[josh84]

Hi,

I've installed the MySQL on windows server 2008 Standard Edition and created a database by the name of WinGate. I've connected the database to ODBC and tested the connection. Everything turns out to be working so far. I login to WinGate and try to change the WinGate HTTP cache and the new database does not show up in the list of databases. Have i done something wroing ? how can i resolve this ?

[adrien]

Hi

the DSN needs to be a system DSN, else it's not visible to the LocalSystem account that WinGate runs in.

Also, on a 64 bit OS, need to use the 32 bit ODBC manager. This is in windows\Syswow64\ocbcad32.exe, NOT windows\system32\odbcad32.exe (which is the 64 bit version - go figure!).

Regards

Adrien

[josh84]

Hi,

Thanks for you reply Adrien.

i ran the 32 bit ocbcad32.exe on the 64bit machine and the MySQL server is not shown in the list. What next ?

[adrien]

did you install the 32 bit MySQL drivers?

[josh84]

Hi,

Didn't know that i had to do that on a x64 OS. Well that is done and now the problem is that the web pages are not been cached. The Cache is already created and it stays empty. Tried redoing the whole process but nothing happening.

How can i resolve this ?

Regards,
Anish

[adrien]

yeah, actually x64 OSes are actually 2 OSes in one. You have the main 64 bit one, and a 32 bit subsystem. The 32 bit subsystem uses its own drivers for a lot of things, so you need to install both versions.

Do you get any errors logged?
Is the HTTP Cache enabled on the WWW proxy (it is by default)?

Regards

Adrien

[josh84]

Hi,
Thanks adrien for the reply. I've got it resolved after some trial and error. How many concurrent connections can it serve ?

[adrien]

we normally use about 10 for MySQL. I'm sure it can handle a lot more, but may depend on how the MySQL server is configured - e.g. there are different modes when you install depending on use case (web server vs something else).

We do recommend if you can to run the DB server on another computer than WinGate though if possible - then WinGate isn't competing with the DB for CPU/Memory/Disk access etc. It can have quite a big impact on performance.

Adrien

[josh84]

ok runnning the database on another computer is a good idea but wouldn't that time to retrive the content from that other pc ?

If i had to configure it on another computer how do i connect to the WinGate HTTP Cache ?

Regards,
Anish

[adrien]

the network latency is small compared to the work the DB has to do to process the queries, so you get a significant improvement.

To connect to a remote server, you just change the DSN you created to point through to the server on the other computer.

Remember with MySQL DSNs to set some options - particularly "Enable automatic reconnect" on the Flags1 tab (once you hit details).

[josh84]

Ok that was new to me.

Will try this once i get a pc for. Will keep the reconnect part in mind when i configure the same.

What specification would you recommend for the HTTP Cache pc ?

[adrien]

how many users do you have? Normally I recommend getting as fast a computer as you can, but if you've only a small network then there's no need.

[josh84]

Total users are just 25.

[josh84]

I had changed the localhost of the DSN to the ip address of the server but the database does not show up in the drop down list. I've enabled auto connect also. Is there anything that i've to do on the server so that clients can connect ?

Regards,
Anish

[adrien]

Hi

MySQL should have some good documentation about how to connect from remote. THe username / password can get interesting, as by default it's locked to a client I P address, e.g. root@127.0.0.1

I think you can change it to user@% to work at any host.

You need to create the actual database in the remote DB as well - WinGate will create the tables.

[josh84]

am new to configuring databases. So bear with me please.

I've been trying to connect the HTTP cache database from the wingate server to a local pc on the LAN.

heres what i've done,

Created a database on the local pc (10.10.10.2) with mysql. test connection from local pc using ODBC. Test was sucessful. Tried connecting from the wingate server (10.10.10.1) using 32 bit ODBC connector in windwos 2008. I've entered 10.10.10.2 in the Tcp/Ip field. entered the username 'root' and password 'R00t' for the database on 10.10.10.2 and when i clicked the database field, it was blank.

How can i resolve the same ?

regards,
Anish

[adrien]

Hi

did you create a new database inside MySQL on that LAN machine? E.g. does it show up when you create a DSN on that computer (10.10.10.2)?

What software are you using to manage MySQL?

Adrien

[josh84]

i didn't understant what you've said " did you create a database inside MYSQL" but i did create a database http_cache with MYSQL bench Work and it does show up in the ODB connector in 10.10.10.2 ( That is where is the database is also created). Now how do i connect the WinGate server to the database (http_cache) on 10.10.10.2

About What software to manage the mysql, Do i need one ? i didn't know. i thought it was just connecting databases with the odbc connector. What software should i use to manage mysql ?


Regards,
Anish

[adrien]

Hi

did you look for any support for MySQL or MyODBC? These are not our products, so we're probably not the best place to get support for them. Try google for MyODBC remote connection to MySQL, or here's a link you'll find if you do that

http://www.phphelp.com/article/article.php?art=4

Adrien

[josh84]

Thanks for the reply.

That link did not do much of a help. I've managed to configure the server after a little bit of research.

all is well now.

Regards,
Anish

[adrien]

Hi

sorry about that link... from the title it looked like a good one, but when I read it it was very old.

Did you find another good link for setting this up? If so, could you please post it here?

Thanks

Adrien

[josh84]

Thats ok i didn't mean no offense.

Well, i didn't find any post regarding what we require in our scenario. After i gave up on searching and started testing things by myself i found it out.

What i can't still understand is, what is the point in using a remote database when the cached files are always stored on the local system ( it has to be on the system where the wingate is installed or else wingate can't detect the same on a network drive)?

i'd be happy to post the steps out here once i get it sorted out.



Regards,
Anish

[adrien]

Hi

sorry, just saw this needed a reply still.

The reason it's better to run the DB on another computer, even though the files are stored on the WinGate computer is due to the amount of CPU it takes to process queries. the database can grow quite large with a large cache, and have many records. Many fields are indexed as well, so the DB will use more and more memory as the database grows. Searching indexes for records many times per second will also take some CPU.

If the Database and WinGate are on the same computer, then they compete for CPU. Accessing the file on disk isn't really a problem and isn't CPU intensive, it's the lookups and updates / adds to the database that take the CPU. So by moving that CPU load off the WinGate server, you get overall better performance, even though the query needs to be sent across the network, and the response sent back. We've seen this every time.

With an MDB index, the whole DB runs as a DLL in the process, and manipulates the MDB file directly, so it's pretty quick, but cannot handle more than 1 concurrent connection to the database before performance takes a huge hit. When we were testing, we found if we allowed 2 connections to the MDB, while it still worked, the performance (query/s) dropped by about 90%. So we limit connections for MDB to 1.

Regards

Adrien