MySQL vs JSON file data storing
posted in /home/PHP on 2012-02-12 |
I'm not exactly a performance perfectionist, but currently I'm working on a tool which will be used in a large system. It will deliver texts to a multi-language environment and there will be several sites/apps which will performe big amount of request to it. It is the right moment for me to choose how to speed up my code and the work of the tool overall.
The texts are stored in MySQL database. The first option is to fetch the data directly from there and serve it whenever it is needed. Two problems with that - there will be a lot of records in the database and there will be a lot of requests every second. Naturally the tool offers export of all the information in one single JSON file. So I decided to get the data from there, instead of making mysql queries all the time. I was wondering which of these two approaches is faster and will fit in my needs.
I created a dummy database and fill it with more then 20000 records. The following script did the job:
Simple method which exports the data to a file in JSON format.The file became really big - around 40MB. I'm sure that I'll never reach 40MB with the tool. However, it is good for the testing purposes.
Here are the two methods which test the fetching from MySQL database and from the JSON file.printRecord is a function which just outputs the data.
The results of the tests are:
MySQL reading - 2.97s
JSON file reading - 3.16s
However, the above results are actually based on only one request from one user. I decided to use the Apache benchmark tool to simulate multiple requests. And it looks like MySQL approach still wins. The json.php takes less time but some of the requests fail due to Out of memory problem of PHP.
What you think? What are your tips and tricks for such kind of situations?
The texts are stored in MySQL database. The first option is to fetch the data directly from there and serve it whenever it is needed. Two problems with that - there will be a lot of records in the database and there will be a lot of requests every second. Naturally the tool offers export of all the information in one single JSON file. So I decided to get the data from there, instead of making mysql queries all the time. I was wondering which of these two approaches is faster and will fit in my needs.
I created a dummy database and fill it with more then 20000 records. The following script did the job:
Simple method which exports the data to a file in JSON format.The file became really big - around 40MB. I'm sure that I'll never reach 40MB with the tool. However, it is good for the testing purposes.
Here are the two methods which test the fetching from MySQL database and from the JSON file.printRecord is a function which just outputs the data.
The results of the tests are:
MySQL reading - 2.97s
JSON file reading - 3.16s
However, the above results are actually based on only one request from one user. I decided to use the Apache benchmark tool to simulate multiple requests. And it looks like MySQL approach still wins. The json.php takes less time but some of the requests fail due to Out of memory problem of PHP.
What you think? What are your tips and tricks for such kind of situations?
Facebook
Twitter
Digg
Delicious
Google Bookmarks
LinkedIn