Size of Result Returned From MySQL

classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

Size of Result Returned From MySQL

bilbosax
This post was updated on .
I am writing an AIR app for mobile devices for real estate.  It uses web
services to get data from MySQL using PHP and returns it to my app.  My
MySQL database will be hosted on Amazon Web Services, and my concern is
cost.  I will have thousands of users pinging my database every day, and the
result sets are quite large.  I need to know HOW large to see if I need to
limit the amount of data that I am giving to my users.  If I am transfering
30GB of data an hour, I am not going to be able to afford the bandwidth per
year.  So I need to know approximately how much data is transfered per query
into my app so I can kind of guestimate how much bandwidth I will need to
pay for, but I am not sure how to do this.

It doesn't look like any of the services allow you to get a result size for
the returned data.  I transfer the results to an arraycollection, but there
does not seem to be a size property for an arraycollection either.  My only
other thought is if there might be a network monitor that I could use to see
how much information was transferred when I process my query across the web.

Any suggestions on how I could get a fairly good idea of how much data is
flowing into my app when I execute a database query would be hugely appreciated?

Thanks guys!!



--
Sent from: http://apache-flex-users.2333346.n4.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: Size of Result Returned From MySQL

Javier Guerrero García
Couldn't you just measure the data sent by the php instead?

On Thu, Aug 31, 2017 at 1:19 AM, bilbosax <[hidden email]> wrote:

> I am writing an AIR app for mobile devices for real estate.  It uses web
> services to get data from MySQL using PHP and returns it to my app.  My
> MySQL database will be hosted on Amazon Web Services, and my concern is
> cost.  I will have thousands of users pinging my database every day, and
> the
> result sets are quite large.  I need to know HOW large to see if I need to
> limit the amount of data that I am giving to my users.  If I am transfering
> 30GB of data an hour, I am not going to be able to afford the bandwidth per
> year.  So I need to know approximately how much data is transfered per
> query
> into my app so I can kind of guestimate how much bandwidth I will need to
> pay for, but I am not sure how to do this.
>
> It doesn't look like any of the services allow you to get a result size for
> the returned data.  I transfer the results to an arraycollection, but there
> does not seem to be a size property for an arraycollection either.  My only
> other thought is if there might be a network monitor that I could use to
> see
> how much information was transferred when I process my query across the
> web.
>
> Any suggestions on how I could get a fairly good idea of how much data is
> flowing into my app when I execute a database query?
>
> Thanks guys!!
>
>
>
> --
> Sent from: http://apache-flex-users.2333346.n4.nabble.com/
>
Reply | Threaded
Open this post in threaded view
|

Re: Size of Result Returned From MySQL

bilbosax
That is an interesting question, but honestly, I don't know.  Do you know if
PHP has the ability to quantify the result set in MB??



--
Sent from: http://apache-flex-users.2333346.n4.nabble.com/
Reply | Threaded
Open this post in threaded view
|

RE: Size of Result Returned From MySQL

Jeff Dafoe
In reply to this post by bilbosax

You didn’t mention what format the data coming out of the PHP app is, but you can probably use strlen(serialize(yourstructure)) to get the size from PHP.

From: bilbosax<mailto:[hidden email]>
Sent: Wednesday, August 30, 2017 7:19 PM
To: [hidden email]<mailto:[hidden email]>
Subject: Size of Result Returned From MySQL

I am writing an AIR app for mobile devices for real estate.  It uses web
services to get data from MySQL using PHP and returns it to my app.  My
MySQL database will be hosted on Amazon Web Services, and my concern is
cost.  I will have thousands of users pinging my database every day, and the
result sets are quite large.  I need to know HOW large to see if I need to
limit the amount of data that I am giving to my users.  If I am transfering
30GB of data an hour, I am not going to be able to afford the bandwidth per
year.  So I need to know approximately how much data is transfered per query
into my app so I can kind of guestimate how much bandwidth I will need to
pay for, but I am not sure how to do this.

It doesn't look like any of the services allow you to get a result size for
the returned data.  I transfer the results to an arraycollection, but there
does not seem to be a size property for an arraycollection either.  My only
other thought is if there might be a network monitor that I could use to see
how much information was transferred when I process my query across the web.

Any suggestions on how I could get a fairly good idea of how much data is
flowing into my app when I execute a database query?

Thanks guys!!



--
Sent from: http://apache-flex-users.2333346.n4.nabble.com/

Reply | Threaded
Open this post in threaded view
|

Re: Size of Result Returned From MySQL

Javier Guerrero García
In reply to this post by bilbosax
Assuming your php output is buffered (99.9999999% of apache servers), just
getting the size of the output buffer would do:

http://php.net/manual/en/function.ob-get-length.php

In case response is also compressed (gzipped, deflated, etc...), or just
for simplicity, you can just grab the contents of the apache log file
(access_log) and sum the "length" column

Or just curl the same php request and save it to a file (curl gives you the
transferred size, filesize gives you the uncompressed size)

Or just look at the network tab of the developer tools in firefox/chrome
when doing the same request from your browser (first line is transferred
size, second line is uncompressed size)

Or just right-click and "save as" in your browser when doing the same
request (uncompressed, gzip afterwards for estimated compressed size)

Or just look at the "content-length" header returned by the apache

Etc... etc... etc...

On Thu, Aug 31, 2017 at 1:46 AM, bilbosax <[hidden email]> wrote:

> That is an interesting question, but honestly, I don't know.  Do you know
> if
> PHP has the ability to quantify the result set in MB??
>
>
>
> --
> Sent from: http://apache-flex-users.2333346.n4.nabble.com/
>
Reply | Threaded
Open this post in threaded view
|

Re: Size of Result Returned From MySQL

Olaf Krueger
In reply to this post by bilbosax
It sounds like you're consuming a web API, so I think it doesn't matter if
there's PHP, MySQL or whatever in the back. The data transferred via http at
the end.
As Javier mentioned the 'content-length' of the response header is probably
the most important value [1].

Maybe it's a good idea to search for some AWS docs which describe how they
calculate the costs.

Olaf

[1] https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/Content-Length



--
Sent from: http://apache-flex-users.2333346.n4.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: Size of Result Returned From MySQL

bilbosax
Thanks for all of the advice.  I was just finally able to get Scout connected
to my mobile device and profile my app.  I noticed that under the Memory
Allocations tab, it is reporting that MessageResponder.result is taking up
45kb of memory.  Do you think that this is an accurate way of detecting the
amount of data returned by the MySQL database???

Thanks!



--
Sent from: http://apache-flex-users.2333346.n4.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: Size of Result Returned From MySQL

Javier Guerrero García
Nope, not at all, and definitely not what Amazon AWS will be billing you
for.

Why don't you try any of the gazillion and byte-accurate methods commented?
You just have to click on your browser launch icon :)

(and again, your concern is *not* the data returned by the MySQL database,
but the amount of data returned *by the web service*, which AFAIK will be
what AWS will be charging you for)

P.S. And if you are really concerned about transferred data size, DO NOT
USE XML for god sakes... ;)

On Wed, Sep 6, 2017 at 11:12 PM, bilbosax <[hidden email]> wrote:

> Thanks for all of the advice.  I was just finally able to get Scout
> connected
> to my mobile device and profile my app.  I noticed that under the Memory
> Allocations tab, it is reporting that MessageResponder.result is taking up
> 45kb of memory.  Do you think that this is an accurate way of detecting the
> amount of data returned by the MySQL database???
>
> Thanks!
>
>
>
> --
> Sent from: http://apache-flex-users.2333346.n4.nabble.com/
>
Reply | Threaded
Open this post in threaded view
|

Re: Size of Result Returned From MySQL

bilbosax
Javier, thanks for your reply.  I'm sorry, I guess I couldn't figure out how
to use a lot of the suggestions posted.  My app is an AIR app running on a
mobile device, so there is no browser involved.  If I am missing something,
if you can think of a way to obtain the amount of data returned to my mobile
device, I would really appreciate any thoughts on that.

You bring up an interesting point though that I wanted to ask you about
further.  I am using PHP to connect to my MySQL database, and it uses ZEND
to pass the data back to me using AMF.  When profiling in Scout, i notice
that during the query it sends a MessageResponder.result back that occupies
31MB of space.  When I drill down into the MessageResponder.result, I
eventually dead end at two entries that say XMLList which occupies 14MB and
XML which occupies 4MB.

Does this suggest that PHP is packaging and returning the results as XML???
Is this bad?  Is there a better way to set up the web service??



--
Sent from: http://apache-flex-users.2333346.n4.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: Size of Result Returned From MySQL

Javier Guerrero García
>
> Is there a better way to set up the web service??


Yes: in general, never use 1 ton of frameworks if it can be done in 1 line
of code :)

The answer depends on a hundred different factors (you didn't tell us
anything about the app itself, so it's hard to guess), but if for "better"
you mean "smaller", and you are only planning to retrieve data from the
database (not update), I would just get rid of the ZEND frameworks as a
whole, set up a HTTPservice with resultFormat="text", make a simple PHP
that return a plain CSV with your results (5-6 lines of code), and on
arrival just decode it (split by ",") and assign it to your datasource.

If your data is more sophisticated, you can go up to JSON, but stay away
from using objects as much as possible: use plain arrays, or try to use
really simple object property names ("n" instead of "first_name"), since
they will be used thousand of times in the response (assuming you return
thousands of rows)

In this case, the size of the results (your original question) is exactly
the size of the plain text file/json returned by the php (minus http
compression if applies), no need to SCOUT it :)

https://en.wikipedia.org/wiki/KISS_principle

On Thu, Sep 7, 2017 at 3:28 AM, bilbosax <[hidden email]> wrote:

> Javier, thanks for your reply.  I'm sorry, I guess I couldn't figure out
> how
> to use a lot of the suggestions posted.  My app is an AIR app running on a
> mobile device, so there is no browser involved.  If I am missing something,
> if you can think of a way to obtain the amount of data returned to my
> mobile
> device, I would really appreciate any thoughts on that.
>
> You bring up an interesting point though that I wanted to ask you about
> further.  I am using PHP to connect to my MySQL database, and it uses ZEND
> to pass the data back to me using AMF.  When profiling in Scout, i notice
> that during the query it sends a MessageResponder.result back that occupies
> 31MB of space.  When I drill down into the MessageResponder.result, I
> eventually dead end at two entries that say XMLList which occupies 14MB and
> XML which occupies 4MB.
>
> Does this suggest that PHP is packaging and returning the results as XML???
> Is this bad?  Is there a better way to set up the web service??
>
>
>
> --
> Sent from: http://apache-flex-users.2333346.n4.nabble.com/
>
Reply | Threaded
Open this post in threaded view
|

Re: Size of Result Returned From MySQL

Olaf Krueger
In reply to this post by bilbosax
>Yes: in general, never use 1 ton of frameworks if it can be done in 1 line
of code :)

I agree with Javier. However, you can go one step further.
If you just have to persist and fetch some data you could also get rid of
PHP.
There are a lot of database cloud services out there that provides a web
API. You could just fetch and persist your data by consuming such a web API.
At the end, you just have to implement some HTTP calls in your AIR app and
you have to convert the payload to your typed AS3 objects. No ZendAMF, PHP,
or whatever else needed!

Call it "MicroServices", "API driven development", "API first" or whatever.
It makes sense to think about it these days... the monolithic approach is
probably dead ;-)

BTW: I highly recommend you to check out also one of the NoSQL databases out
there like CouchDB, MongoDB and others.

HTH,
Olaf




--
Sent from: http://apache-flex-users.2333346.n4.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: Size of Result Returned From MySQL

bilbosax
Interesting suggestions guys. I really appreciate them and will look further
into them as I was unaware that there were web-based API's for retrieving
database data.

I feel a little foolish because this is my first big project and I didn't
fully understand the various RPC options. I set up Flex webservices using
PHP, and in that process, Flex requires that you install Zend. This made me
assume that I was using AMF, but I don't think that it does because my
results were returned as XML. I now believe that to take advantage of AMF, I
need to utilize RemoteObject instead of webservices to get info from my
database.

My Two BIGGEST concerns with the database are speed and cost. Speed for user
experience - right now they are watching a spinner for 12 seconds while the
database provides the data and it gets parsed. I am sending large amounts of
data with each query to my app, so this is going to drive my bandwidth costs
close to $15k/month. AMF delivers data in a binary format, so it is
delivered in much smaller packet sizes, and MUCH faster.

If you are on a PC, James Ward created a Flash App to demo the different
speeds and file sizes and they are significant.

So my first thought is to try using RemoteObject to get the results in AMF
binary, both much faster and smaller file sizes than XML. My question to you
now is, can these web-based API's deliver text as quickly and in as small of
file sizes as AMF???



--
Sent from: http://apache-flex-users.2333346.n4.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: Size of Result Returned From MySQL

bilbosax
I believe this is a link to James Ward's speed and file size comparison of
XML and AMF, REST and SOAP services

http://census2.jamesward.com



--
Sent from: http://apache-flex-users.2333346.n4.nabble.com/
Reply | Threaded
Open this post in threaded view
|

Re: Size of Result Returned From MySQL

Olaf Krueger
In reply to this post by bilbosax
>I feel a little foolish...
Sorry for this, there's no need to feel foolish!

> can these web-based API's deliver text as quickly and in as small of file
> sizes as AMF
AMF is probably faster but I think the question is if the better performance
of AMF is significant or relevant.
Do you've already checked your MySQL queries and do you've checked how long
MySQL needs to deliver the requested data? Maybe the database is your bottle
neck.

Assuming that all actors are perfectly optimized regarding performance, the
next question could be if it really makes sense to load this amount of data
to your app with just one request.

Regarding dependencies:
Please remember that ZendAMF is part of ZendFramework1 and it's not
supported anymore.
This is not a problem if it still works with PHP7 and if you are able to
adjust it to make it compatible with future versions if it doesn't work
anymore some day. Just keep it in mind!

If I remember it correctly you're using the FlashBuilder data-centric
development (I never used it).
This could be also a problem if FlashBuilder doesn't work anymore some day
for whatever reason.
FlashBuilder is also not supported anymore.

What I'd like to say is that there's no need to make your app dependent from
those things.
"The whole world" is going with JSON these days... I assume it would work
also for you ;-)

If you'd like to share your DB stuff to discuss some details.... just do it.

HTH,
Olaf







--
Sent from: http://apache-flex-users.2333346.n4.nabble.com/