Re: [PHP] mysql cache query as xml
On Jul 10, 2009, at 5:25 PM, "workerho...@studysite.eu" > wrote: first thanks to all who have read ;-) your solution looks like the method how i done it actually, i have tested the last hours the solution with sql lite on application server the Solution: 3 Mysql Server ( 1 more to handle the big load ) (1 Master, 2 Slaves) mysql replication 10 Applikation Server ( get today two more from my hoster :-) ) on Application Serer running php and sql lite by the first load from mysql server the big query get synchronised with the lokal sql lite and write into the database entrys are about 6 hour valid, after then the server get the new list. performance looks nice: total load time: between 0.03-0.09 Seconds but i found another problem by the time i worked on the server application server can create images and thumbs of them in various sizes (gd lib etc.) then this server open a ftp connection ( ftp_connect() ) to a global data & storage server the data server has just running ftp so i must created the thumbs on application server and move all files to the data server: question: *can php handle some compression with ftp* ? so that i can move some more data? chirs You could zip the files together, but on the whole images don't compress much. Setting the FTP process to run on a cron would be the best way to avoid doing a lot of transfers at one time. Bastien -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql cache query as xml
first thanks to all who have read ;-)
your solution looks like the method how i done it actually,
i have tested the last hours the solution with sql lite on application
server
the Solution:
3 Mysql Server ( 1 more to handle the big load ) (1 Master, 2 Slaves)
mysql replication
10 Applikation Server ( get today two more from my hoster :-) )
on Application Serer running php and sql lite
by the first load from mysql server the big query get synchronised with
the lokal sql lite and write into the database
entrys are about 6 hour valid, after then the server get the new list.
performance looks nice:
total load time: between 0.03-0.09 Seconds
but i found another problem by the time i worked on the server
application server can create images and thumbs of them in various sizes
(gd lib etc.)
then this server open a ftp connection ( ftp_connect() ) to a global
data & storage server
the data server has just running ftp so i must created the thumbs on
application server and move all files to the data server:
question:
*can php handle some compression with ftp* ? so that i can move some
more data?
chirs
Michael A. Peters schrieb:
workerho...@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...
Are you saving to file or caching as a query result?
Also note that you can cache an array of rows (at least with APC but I
suspect memcache as well) - say my_fetch(key) is you function to fetch
from cache and my_store(key,data,life) is your function to store.
$result = my_fetch('big_query');
if (! $result) {
$sql = 'your query';
$rs = mysql_query($sql);
while ($row = mysql_fetch_object($rs)) {
$result[] = $row;
}
my_store('big_query',$result,21600);
}
No xml involved and you can loop through the results.
If you'd rather do it as xml, you can cache the xml as a string and
then fetch it, importing it into a DOM or whatever to extract your
results.
$xml = my_fetch('queryResultAsXML');
if (! $xml) {
generate xml and cache it
}
$dom = new DOMDocument('1.0','utf-8');
$dom->loadXML($xml);
Not sure what you are doing, apoligize if these suggestions are
useless or already considered.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql cache query as xml
workerho...@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...
Are you saving to file or caching as a query result?
Also note that you can cache an array of rows (at least with APC but I
suspect memcache as well) - say my_fetch(key) is you function to fetch
from cache and my_store(key,data,life) is your function to store.
$result = my_fetch('big_query');
if (! $result) {
$sql = 'your query';
$rs = mysql_query($sql);
while ($row = mysql_fetch_object($rs)) {
$result[] = $row;
}
my_store('big_query',$result,21600);
}
No xml involved and you can loop through the results.
If you'd rather do it as xml, you can cache the xml as a string and then
fetch it, importing it into a DOM or whatever to extract your results.
$xml = my_fetch('queryResultAsXML');
if (! $xml) {
generate xml and cache it
}
$dom = new DOMDocument('1.0','utf-8');
$dom->loadXML($xml);
Not sure what you are doing, apoligize if these suggestions are useless
or already considered.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql cache query as xml
On Fri, 10 Jul 2009 13:29:31 -0400 Bastien Koert wrote: > On Fri, Jul 10, 2009 at 1:23 PM, > workerho...@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! > > > > > > > > Daniel Brown schrieb: > >> > >> On Fri, Jul 10, 2009 at 13:07, > >> workerho...@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 > > > > > > is all the data from the query the same for each user? I.e. that they > get the same 4K rows of data for that query? How is that query done? > are there date parameters or other fields that would allow table > partitioning on the data? Could you use a temp table, to store that > data or a more fixed table that stores just that query's dataset? > > Also how large is the main table? > > I don't know much about mysql, as I've been using it only for some basic things, but, couldn't you just set a bigger query_cache? The first user would have that 0.3" query, but the other 99 would get the results in... 0.001"? 0.002"? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql cache query as xml
yes i think i should do this Daniel Brown schrieb: On Fri, Jul 10, 2009 at 13:23, workerho...@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
On Fri, Jul 10, 2009 at 1:23 PM, workerho...@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! > > > > Daniel Brown schrieb: >> >> On Fri, Jul 10, 2009 at 13:07, >> workerho...@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 > > is all the data from the query the same for each user? I.e. that they get the same 4K rows of data for that query? How is that query done? are there date parameters or other fields that would allow table partitioning on the data? Could you use a temp table, to store that data or a more fixed table that stores just that query's dataset? Also how large is the main table? -- Bastien Cat, the other other white meat -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql cache query as xml
On Fri, Jul 10, 2009 at 13:23, workerho...@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.br...@parasane.net || danbr...@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: [PHP] mysql cache query as xml
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, workerho...@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: [PHP] mysql cache query as xml
On Fri, Jul 10, 2009 at 13:07, workerho...@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.br...@parasane.net || danbr...@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: [PHP] mysql cache query as xml
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.br...@parasane.net || danbr...@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: [PHP] mysql cache query as xml
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... Andrew Ballard schrieb: On Fri, Jul 10, 2009 at 12:36 PM, Daniel Brown wrote: 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-subscr...@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. 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. However, I'm not sure I'd spend time trying to devise a "fast" XML cache for a query that only took 0.3 seconds to execute. By itself, that isn't bad performance unless this is a query that is called frequently by several concurrent users. Personally, I'd look into ways to improve the execution of the query itself in MySQL (making sure the query is sargable and improving indexes, etc.) until I thought I had exhausted everything there. Just my 2 cents. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql cache query as xml
On Fri, Jul 10, 2009 at 12:36 PM, Daniel Brown wrote: > 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-subscr...@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. 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. However, I'm not sure I'd spend time trying to devise a "fast" XML cache for a query that only took 0.3 seconds to execute. By itself, that isn't bad performance unless this is a query that is called frequently by several concurrent users. Personally, I'd look into ways to improve the execution of the query itself in MySQL (making sure the query is sargable and improving indexes, etc.) until I thought I had exhausted everything there. Just my 2 cents. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
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-subscr...@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, workerho...@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 just > 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.br...@parasane.net || danbr...@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
