vb.org Archive - compress mysql responses / cache them

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)

- - compress mysql responses / cache them (https://vborg.vbsupport.ru/showthread.php?t=131373)

compress mysql responses / cache them

Hi everyone,

I'm running vB and it's database on different servers. A forum hit consumes around 350 kB of traffic (from the database server to the webserver) - that makes more than 100 GB per month.
I already tried the MYSQL_CLIENT_COMPRESS flag but it seems not to be working:

Code:

@@ -272,3 +272,3 @@ class vB_Database

                {

-                       $link = $this->functions[$usepconnect ? 'pconnect' : 'connect']("$servername:$port", $username, $password);

+                       $link = $this->functions[$usepconnect ? 'pconnect' : 'connect']("$servername:$port", $username, $password, MYSQL_CLIENT_COMPRESS);

                }

I still see the database content rushing in clear text.
However, a self-written php file that queries the database using MYSQL_CLIENT_COMPRESS works fine, the result gets compressed.

I saw there is many unvarying data being transferred on each forum hit - like the forum templates, phrases, permissions etc. In fact that's the majority of what's being transferred.
Is there a way of caching these tables locally (in php, on the webserver's side)? If not, how to compress the data?

Regards,
cycomate

Are your mysql and web servers not directly bridged? And is your provider charging your for the bandwidth that they use to comminucate?

Weird, I personally wouldnt even start to think about sending any communications like this over any sort of public network.

The servers are on different networks.
It's the only possibility for me to run vB on two webservers with one database (load balancing). I was using ssh to tunnel the sql queries but encryption and compression was too heavy for the machines at high traffic and whenever the ssh tunnel was interrupted the forum I had to check and restart them, which made the forum unusable for some time. A direct mysql connection is far better but insecure?.

So, to get back on topic, is there a way of caching tables or compressing the mysql connection?

?) just to make it clear: I am currently using an ssh tunnel for the communication between the web servers and the database. The traffic for a forum hit is around 350 kB w/o encryption plus ~50 to 75 kB encryption overhead (I disabled the encryption for measuring purpose). I was asking for MYSQL_CLIENT_COMPRESS because I believe letting mysql handle the compression could be better than leaving it to ssh. And there is much unvarying data (forum templates, phrases, ..) that's being transferred over and over each time someone hits the forum. Caching this content on webserver-side would be great.

It seems to me the better option would be for you to either get new servers that are closer, or new servers that are cross connected. For your own sake.

Getting new servers is not an option to me.

Well it seems there's no solution of caching static content on the webserver-side. I'm still curious why MYSQL_CLIENT_COMPRESS won't work.

X vBulletin 3.8.12 by vBS Debug Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (1)bbcode_code_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (5)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages:

Phrase Groups Available:

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01489 seconds Memory Usage 1,715KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (1)bbcode_code_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (5)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: