Avatar of Kinderly Wade
Kinderly WadeFlag for United States of America asked on

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

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!
DatabasesMicrosoft SQL ServerMySQL ServerSQL

Avatar of undefined
Last Comment
Kinderly Wade

8/22/2022 - Mon
Darren

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
ASKER CERTIFIED SOLUTION
David Favor

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Kinderly Wade

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 Favor

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
Kinderly Wade

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 Favor

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.
Darren

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Kinderly Wade

Thanks David!