Re: mysql cache query as xml
Re: mysql cache query as xml
am 10.07.2009 18:36:34 von Daniel Brown
Chris;
From my understanding of your question, your message (included
below in its entirety) is better sent to the MySQL General list, which
I've CC'd on this reply. If you haven't yet, please subscribe there
at mysql-subscribe@lists.mysql.com to follow the thread for responses.
If I'm misunderstanding and you're asking a PHP-related question,
please rephrase your question.
[Full original message follows.]
On Fri, Jul 10, 2009 at 12:22,
workerholic@studysite.eu wrote:
> hi guys, i need some help by optimize the performance.
> my problem is that i need a lot of rows the whole site (don't ask i need =
the
> rows really :-) )
> this is about ~4000 rows it will be loaded from mysql database in 0.3
> seconds
> my idea was to cache this rows in a xml file like for example:
>
>
> =A0 some hash id
> =A0 category title
>
> ......
>
> also load query from mysql first, save to xml using 6 hours, erase the
> cached file, load query against....
> but to load the same num rows from xml during more then 3 seconds in
> comparison mysql need just 0.3 seconds.
>
> how can i optimize the reading from xml faster?
>
> server design:
> 2 mysql server (Master & Slave with Replication =A0)
> 8 Applikation Server with connect to the 2 mysql server
>
> this i the reason why i want to cache this query anyway! other querys jus=
t
> need about 0.0004 seconds, but this is the slowest query!
> i hope someone can help me or had a better ideas to solve this problem!
>
> thanks chris
>
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
--=20
daniel.brown@parasane.net || danbrown@php.net
http://www.parasane.net/ || http://www.pilotpig.net/
Check out our great hosting and dedicated server deals at
http://twitter.com/pilotpig
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: mysql cache query as xml
am 10.07.2009 19:09:41 von Daniel Brown
On Fri, Jul 10, 2009 at 12:59, Andrew Ballard wrote:
>
> I understood the question to be how to improve performance by caching
> MySQL results into an XML document (which, given that it was posted
> here) within a PHP script. Perhaps this is not the correct
> interpretation, but if so it would be relevant.
You're probably correct. Seems I'm just experiencing a Friday
Fog, so to speak.
(And no, there are no chemical connotations implied there, just
that the weekend and all the work it entails is looming much closer.
;-P)
--
daniel.brown@parasane.net || danbrown@php.net
http://www.parasane.net/ || http://www.pilotpig.net/
Check out our great hosting and dedicated server deals at
http://twitter.com/pilotpig
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: mysql cache query as xml
am 10.07.2009 19:17:51 von Daniel Brown
On Fri, Jul 10, 2009 at 13:07,
workerholic@studysite.eu wrote:
> hi andrew i think you understand my problem a little,
> but if 100 user load this query at the same time, the two mysql server had a
> lot to do!
> so i think to cache this query as xml to the application server local make
> thinks faster,
> but, i would like to have the same performance to read this xml document as
> read the query from mysql server...
> i dont know why php is so slow to read the xml file...
It will be slower to read a file than data from an SQL database by
sheer design --- regardless of whether it's XML, CSV, plain text, etc.
And MySQL is faster still because it's run as a server with it's own
processing engine, completely independent of the PHP engine and
spawned process. Other factors involved are disk seek time, memory
capabilities, et cetera, but the SQL-vs-file point is the biggest.
For PHP to locate something within the file, it must load the
entire file into memory or read it byte-by-byte, line-by-line, from an
exact offset (given explicitly). SQL databases such as MySQL work
similarly, but don't catalog all data in quite the same linear
fashion. Further, MySQL is capable of indexing, allowing it to return
the data far faster.
There's a time and a place for each, but it sounds as though what
you're attempting to do would not be best-served by caching it in an
XML sheet.
Also, something to keep in mind (with no offense intended by any
means): if you have two database servers (using replication) for
load-balancing and they - combined - cannot handle 100 simultaneous
connections and queries, you may want to re-evaluate your
infrastructure and architecture.
--
daniel.brown@parasane.net || danbrown@php.net
http://www.parasane.net/ || http://www.pilotpig.net/
Check out our great hosting and dedicated server deals at
http://twitter.com/pilotpig
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: mysql cache query as xml
am 10.07.2009 19:23:43 von workerholic
hmm, the infrastructure ist good, this is just this query....
so to solve my problem i could run mysql on the application server and
store just this table
and read the query from them, it could solve my problem litte, i hope so!
Daniel Brown schrieb:
> On Fri, Jul 10, 2009 at 13:07,
> workerholic@studysite.eu wrote:
>
>> hi andrew i think you understand my problem a little,
>> but if 100 user load this query at the same time, the two mysql server had a
>> lot to do!
>> so i think to cache this query as xml to the application server local make
>> thinks faster,
>> but, i would like to have the same performance to read this xml document as
>> read the query from mysql server...
>> i dont know why php is so slow to read the xml file...
>>
>
> It will be slower to read a file than data from an SQL database by
> sheer design --- regardless of whether it's XML, CSV, plain text, etc.
> And MySQL is faster still because it's run as a server with it's own
> processing engine, completely independent of the PHP engine and
> spawned process. Other factors involved are disk seek time, memory
> capabilities, et cetera, but the SQL-vs-file point is the biggest.
>
> For PHP to locate something within the file, it must load the
> entire file into memory or read it byte-by-byte, line-by-line, from an
> exact offset (given explicitly). SQL databases such as MySQL work
> similarly, but don't catalog all data in quite the same linear
> fashion. Further, MySQL is capable of indexing, allowing it to return
> the data far faster.
>
> There's a time and a place for each, but it sounds as though what
> you're attempting to do would not be best-served by caching it in an
> XML sheet.
>
> Also, something to keep in mind (with no offense intended by any
> means): if you have two database servers (using replication) for
> load-balancing and they - combined - cannot handle 100 simultaneous
> connections and queries, you may want to re-evaluate your
> infrastructure and architecture.
>
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: mysql cache query as xml
am 10.07.2009 19:27:26 von parasane
On Fri, Jul 10, 2009 at 13:23,
workerholic@studysite.eu wrote:
> hmm, the infrastructure ist good, this is just this query....
> so to solve my problem i could run mysql on the application server and store
> just this table
> and read the query from them, it could solve my problem litte, i hope so!
You may also want to look into SQLite --- it's perfectly designed
for this kind of situation.
--
daniel.brown@parasane.net || danbrown@php.net
http://www.parasane.net/ || http://www.pilotpig.net/
Check out our great hosting and dedicated server deals at
http://twitter.com/pilotpig
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: mysql cache query as xml
am 10.07.2009 19:43:56 von workerholic
yes i think i should do this....
Daniel Brown schrieb:
> On Fri, Jul 10, 2009 at 13:23,
> workerholic@studysite.eu wrote:
>
>> hmm, the infrastructure ist good, this is just this query....
>> so to solve my problem i could run mysql on the application server and store
>> just this table
>> and read the query from them, it could solve my problem litte, i hope so!
>>
>
> You may also want to look into SQLite --- it's perfectly designed
> for this kind of situation.
>
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mysql cache query as xml
am 10.07.2009 19:58:21 von Gavin Towey
This is more of an application architecture question than a mysql question.=
Though I'll offer my two cents:
What are you going to do with the XML file? Write it to disk, so you can l=
ater read it, and parse it just to get your result set back? That sounds a=
lot slower than fetching it from mysql again. You can test it though -- w=
rite a script, time a bunch of iterations and prove to yourself if it's a g=
ood idea or not.
Generally the way to optimize reading xml faster is to not do it.
IMO if you want caching then use something that was meant for it like memca=
ched.
Regards,
Gavin Towey
-----Original Message-----
From: parasane@gmail.com [mailto:parasane@gmail.com] On Behalf Of Daniel Br=
own
Sent: Friday, July 10, 2009 9:37 AM
To: workerholic@studysite.eu
Cc: PHP; MYSQL General List
Subject: Re: [PHP] mysql cache query as xml
Chris;
From my understanding of your question, your message (included
below in its entirety) is better sent to the MySQL General list, which
I've CC'd on this reply. If you haven't yet, please subscribe there
at mysql-subscribe@lists.mysql.com to follow the thread for responses.
If I'm misunderstanding and you're asking a PHP-related question,
please rephrase your question.
[Full original message follows.]
On Fri, Jul 10, 2009 at 12:22,
workerholic@studysite.eu wrote:
> hi guys, i need some help by optimize the performance.
> my problem is that i need a lot of rows the whole site (don't ask i need =
the
> rows really :-) )
> this is about ~4000 rows it will be loaded from mysql database in 0.3
> seconds
> my idea was to cache this rows in a xml file like for example:
>
>
> some hash id
> category title
>
> ......
>
> also load query from mysql first, save to xml using 6 hours, erase the
> cached file, load query against....
> but to load the same num rows from xml during more then 3 seconds in
> comparison mysql need just 0.3 seconds.
>
> how can i optimize the reading from xml faster?
>
> server design:
> 2 mysql server (Master & Slave with Replication )
> 8 Applikation Server with connect to the 2 mysql server
>
> this i the reason why i want to cache this query anyway! other querys jus=
t
> need about 0.0004 seconds, but this is the slowest query!
> i hope someone can help me or had a better ideas to solve this problem!
>
> thanks chris
>
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
--
daniel.brown@parasane.net || danbrown@php.net
http://www.parasane.net/ || http://www.pilotpig.net/
Check out our great hosting and dedicated server deals at
http://twitter.com/pilotpig
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.com
The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e contact the sender by reply email and destroy all copies of the original =
message.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg
RE: [PHP] mysql cache query as xml
am 10.07.2009 19:58:21 von Gavin Towey
This is more of an application architecture question than a mysql question.=
Though I'll offer my two cents:
What are you going to do with the XML file? Write it to disk, so you can l=
ater read it, and parse it just to get your result set back? That sounds a=
lot slower than fetching it from mysql again. You can test it though -- w=
rite a script, time a bunch of iterations and prove to yourself if it's a g=
ood idea or not.
Generally the way to optimize reading xml faster is to not do it.
IMO if you want caching then use something that was meant for it like memca=
ched.
Regards,
Gavin Towey
-----Original Message-----
From: parasane@gmail.com [mailto:parasane@gmail.com] On Behalf Of Daniel Br=
own
Sent: Friday, July 10, 2009 9:37 AM
To: workerholic@studysite.eu
Cc: PHP; MYSQL General List
Subject: Re: [PHP] mysql cache query as xml
Chris;
From my understanding of your question, your message (included
below in its entirety) is better sent to the MySQL General list, which
I've CC'd on this reply. If you haven't yet, please subscribe there
at mysql-subscribe@lists.mysql.com to follow the thread for responses.
If I'm misunderstanding and you're asking a PHP-related question,
please rephrase your question.
[Full original message follows.]
On Fri, Jul 10, 2009 at 12:22,
workerholic@studysite.eu wrote:
> hi guys, i need some help by optimize the performance.
> my problem is that i need a lot of rows the whole site (don't ask i need =
the
> rows really :-) )
> this is about ~4000 rows it will be loaded from mysql database in 0.3
> seconds
> my idea was to cache this rows in a xml file like for example:
>
>
> some hash id
> category title
>
> ......
>
> also load query from mysql first, save to xml using 6 hours, erase the
> cached file, load query against....
> but to load the same num rows from xml during more then 3 seconds in
> comparison mysql need just 0.3 seconds.
>
> how can i optimize the reading from xml faster?
>
> server design:
> 2 mysql server (Master & Slave with Replication )
> 8 Applikation Server with connect to the 2 mysql server
>
> this i the reason why i want to cache this query anyway! other querys jus=
t
> need about 0.0004 seconds, but this is the slowest query!
> i hope someone can help me or had a better ideas to solve this problem!
>
> thanks chris
>
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
--
daniel.brown@parasane.net || danbrown@php.net
http://www.parasane.net/ || http://www.pilotpig.net/
Check out our great hosting and dedicated server deals at
http://twitter.com/pilotpig
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.com
The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e contact the sender by reply email and destroy all copies of the original =
message.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg