We help IT Professionals succeed at work.

Better and more efficient way to retrieve data when user requests for information

150 Views
Last Modified: 2018-09-09
Dear experts!

I am just wondering if there is a way for me to convert information from relational database such as MSSQL, MySQL etc... into a NO SQL or simply something like memcache in a key -value pair? This is what I want to achieve:

1. instead of having user hitting the SQL DB with requests when they want to get product information etc....
2. store the information into NO SQL or memcache (for a faster and dynamic data retrieval)
3. block the user from hacking SQL DB with sql injection
4. allow a faster update with the data by updating product information in NO SQL or memcache from the SQL DB. ****(I wish to know some good suggestions if I can have a real time update) **** before I have 2 SQL DB and using bash script to do the product information sync like every 30mins or so

Any good idea is appreciated. Thanks!
Comment
Watch Question

DarrenProject Manager / Technical Lead
CERTIFIED EXPERT

Commented:
Hi,

What technologies are you currently using?

Whats the web server, what are the services written in?

I'm also a little confused as to why MySQL or SQL Server are not fast enough for you. If it's simple product information then it should not be a problem to retrieve the data.

If it's cached then it has to be updated in the cache and so if you have product information like quantity and someone purchases something then you want the next person to know that the amount left has reduced. If you use caching then the amount will not update until it is refreshed in the cache.

A little more info would be good.

Thanks,

Darren
Fractional CTO
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Kinderly Wadeprogrammer

Author

Commented:
Hi Experts!

Sorry that I didn't provide enough information.

I was thinking of storing all the product information in the memory (likely a key value pair). I was doing a test as in storing all product information in the memory or using a query to pull all the products information, my result is that memory is faster than pulling the data each time from the database. (of course if just pulling a single or just a few product information will not be an issue). Say if I have over 10K of products and I need to pull like 20 columns per sku then it will make a difference (I may need to pull more 10K of rows later).

If I stored the product information in the memory  (there wasn't much update of the product per day unless if I wanted to process sales orders or similar type of transaction) there will not be much update to the product information even if there was an update it will be the selected few products which I can simply just update those products only.

So this why I was thinking of just store the information into the memory instead of using the query to query for product information each time. I just followed basic rule as in memory will out perform data in database, files, etc.. because I may need to trigger the I/O instead of simply pulling the data out of memory. Of course if I shutdown the server the information would be gone (I assumed this will be the same if I shutdown or restart the sql server the information will not be available as well) and I would need to push the product information from DB into memory just once (when I started the server) and information will stay in the memory (these server do not need to be shutdown often unless need to run some sort of maintenance).

One of the question that I am trying to overcome is this:

Say if I am using MS SQL or MYSQL as my database. I simply pushed the product information into the memory (using memcache as example). If company's employee needs to update a few product (not the full product list). What will be the best for me to update the product information in the memcache from database?

METHOD 1: using sql procedural/function
Can I achieve via the sql procedural/function that allows me to do an update or insert if new product when I set the trigger as in calling the procedural or function call when the product has been updated?

METHOD 2: bash script run by a cron job
Can I simply write a bash script code to update the memcache at a defined time interval (like every 30mins etc....) to update the product in memcache?

Or none of the two from above is good and there is a better method out there that can achieve this? Thanks!

2. I can run a bash script to
David FavorFractional CTO
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Storing in memory is different than having another process manage your data, like memcached, because accessing your data in a separate process  will always be slower, because you must access memcached over a socket connection... so your data access speed via external process will never come close to memory speed... or MySQL buffer speed, which uses page based, shared memory, so runs almost as fast as memory mapped directly into your program.

If speed is a consideration, you'll immediately drop the idea of an external data server.

Also, really think through this.

You're trying to... come up with better caching than MySQL itself, which caches all data in memory buffers anyway.

Better to just run your code + retune with mysqltuner every few days, will no diagnostics emit.

If you try + write your own cache... there are many factors to consider, such as how + when to expire data, so you don't just swap all your processes out of memory... because you're storing massive amounts of memory data.

MySQL is a very smart set of code. Highly unlikely you can come up with a better scheme than the MySQL developers over decades of work.

Not impossible, just unlikely.
Kinderly Wadeprogrammer

Author

Commented:
I see now. Can I use the mysql database and define my table as engine memory which will have a better performance as compared to memache? If so is there a similar mechanism in MS SQL as well with storing the data in engine memory like the mysql? Thanks
David FavorFractional CTO
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
You can + keep in mind, the memory storage engine data is ephemeral... it disappears each time MySQL restarts.

So you'll have to copy all your data in/out, based on your situation.

I should also mention, if you're concerned about performance, you'll never install MySQL. Install MariaDB from the MariaDB repositories.

Also, run Ubuntu Bionic as your OS, because Bionic ships with Kernel-4.15 which provides dramatic performance improvements over any of the pre-4.0 Kernels.

Also... sigh... so many things...

In /etc/fstab add the mount options of noatime,dioread_nolock which will...

1) noatime - Dramatically increase speed writing log files, database + all system file access.

2) dioread_nolock - Fixes a horrible bug that's been around for years for any code using O_DIRECT which is what most MySQL/MariaDB installs use.
DarrenProject Manager / Technical Lead
CERTIFIED EXPERT

Commented:
MySQL buffer speed, which uses page based, shared memory, so runs almost as fast as memory mapped directly into your program

Hi,

I didn't know abut the above which is a really good feature.

I was watching this question to see if there was anything that I could learn (which I did).

Apart from using some other form of storage such as NoSQL or memory  as you have already suggested I have no more info to add to this.

Thanks,

Darren
Kinderly Wadeprogrammer

Author

Commented:
Thanks David!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.