New on LowEndTalk? Please Register and read our Community Rules.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
Need help with MySQL to RAM situation
Hello,
So, we have so you start 32Gb RAM server, and we run e-commerce shop on it with loads of querys to MySQL. I'm thinking why don't we put all MySQL data to RAM for better speed, that part ok, but I'm a php developer, need advice from sysadmin about this possible or not thing and tutorial for this magic.
Comments
How large is your SQL database?
Not sure about MySQL to RAM, but I can recommend upgrading to MariaDB - it's better in performance compared to MySQL especially when you are running a lot of queries in a short period of time.
265,7 MiB x 3 DB's
MySQL -> MariaDB it's option, but we have loads of free RAM ~30 to use, ant we pay for that, but still MySQL makes 120-150% load in CPU, the RAM is 2-10% used.
@kenkelis as said by @Verelox, you should migrate to MariaDB 10.0.
It would be a great idea to host your database on SSD, and maybe let the database cache do its work? You may have to tweak some parameters in MariaDB, your requests or your tables.
So you suggest Query Cache set to memory, not to file, yes?
EDIT: Turned on query_cache in testing server, now watching how load is handling.
@kenkelis: I'm not an expert regarding tuning performance of MySQL/MariaDB :-)
AFAIK, query_cache is always in memory, no?
Very important, don't set your query cache to a very high value, it will kill your performance: https://www.percona.com/blog/2007/03/23/beware-large-query_cache-sizes/ and http://haydenjames.io/mysql-query-cache-size-performance/
There are two scripts I like to use to know if a server is well optimized or if there's a huge problem with the conf:
https://github.com/major/MySQLTuner-perl
https://launchpad.net/mysql-tuning-primer
Thanks, my good sir. I thing we are going to remake fully server with ubuntu 14.04, Ajenti (V) CP, Will drop MySQL, and add MariaDB, config MemCache and watch what happens. Right now we get around 6-7k unique visitors every day, but filters killing server with queries.
If anyone has good ideas for setup, I'm all ears.
EDIT: PHP code is custom.
I have some suggestions, but you don't really need to reinstall the server to upgrade to MariaDB; you can upgrade right from MySQL as they are compatible, as far as I know; if you are reinstalling for other reasons however, I would personally suggest not going with control panels as I always find it easier to setup the Apache server manually, that should save you resources as well. I would also suggest trying CentOS instead of Ubuntu as I find it more stable, but that's only my personal experience; Ubuntu might be better in your case.
Finally, since the PHP code is custom, I would recommend running "show processlist" when you're inside MySQL to see and track the queries which take time to load so that you can optimize the code when possible.
P.S You can find how to setup MariaDB's repositories for your distribution here
having mysql reside in ram is very DANGEROUS. as if the server hang or go down for any reason all your data is lost
that being said you can do this
creating ramdisk in memory and having all your mysql data hosted there (change the path of mysql data)
That will give you very fast speed but zero reliability
you could make the situation better by creating hourly backup script to backup your mysql to a disk in case something goes wrong and in this case you only loose 1 hour of data in case of hang or power loss.
or even better setup mysql replication job to another mysql instance on disk by that way all writes are synced while reads are from memory
but in all cases you need alot of testing to make sure the solution you are making is working in disaster cases (automate the restoration on startup etc)
Thanks
Do you fine with paid support?
If so, let me know. I am managing high-loaded MySQL databases near every day (50-900 millions of rows per table). When MySQL database fits in memory, you'll have zero problems at any load.
Basic overview: Free. Advanced MySQL tuning (including application part): approximately 50$. Advanced software tuning (includes MySQL tuning) (nginx -> php-fpm or hhvm -> MariaDB; if you are using another stack of technologies - i'll safely migrate you to proper ones) - approximately 150$. Long-term support/monitoring: from as low as 15$ per month.
Migrate to MariaDB 10, enable TokuDB plugin, change tables to Toku
http://www.tokutek.com/tokudb-for-mysql/
OP mention ecommerce site, If he using opencart 1 series stop product count and use a pach to stop counting it in categories... Open Cart devs never study how to use mysql in opencart 2 just stop product count
What the OP should do is to optimise caching in my.cnf.
Don't jump with 'Change to MariaDB' and similar, that's not the ultimate solution for all problems in the world.
I give the advice based on assumption my.cnf is already optimized when OP mentioned that he want to move everything in RAM
Oracle way to fix mysql problems (just remove the feature) is not the correct one
MariaDB has many advantages as support for TokuDB (which Persona bought recently) and pool of threads (which can be found in MySQL enterprise)
never assume something is optimized, especially when the OP says he is not a sysadmin, but a programmer.
I am willing to bet there is plenty of space to host this on an 4 GB ram VPS, with 4 cores or so, E5 with hyperthreading on host. Unless those 6-7 k users all come within one hour and then it might get complicated, but still doable. With SSD will even run like a champ.
Writing on o ramdisk, while possible, as detailed before, is extremely dangerous, but you can mitigate that with frequent dumps to the disk, a few hundred MB databases can be dumped every 10 minutes or so, without issues, even on non-ssd storage, since they are sequential (I presume not a file-by-file copy, of course, since that will give you inconsistent data, in the lucky case it will not be completely broken).
Would do mysql caching and web server/PHP caching
Run this:
What is the output?
Post your current global variables first - "SHOW GLOBAL VARIABLES;"
We have a Customer on 128 GB Dedi for Magento who explicitly wants MySQL only, MySQL is caching 99% queries and uses as much memory as it wants, meaning it is possible to tweak it properly.
Script given by @MarkTurner is excellent, definitely recommend this one, however still requires a bit of knowledge, it's not a replacement for DBA :-).
@all I'm home from work, tomorrow will make test.
The reason I'm asking about upgrade server is simple, freelance sysadmin support server very little, so I'm as head of developer have to find a solution, so new sysadmin will make it, and support it.
As I already told, we use custom code on cakephp framework. So some codes are old, loads of queries from select (95% of queries is SELECT), but there is a one problem, CPU load with MySQL is to big, mysql takes up all CPU, and server just lags. We are starting test in VMbox, will clone/migrate MySQL database to MariaDB (already have tutorial), some stress test.
About tread title, we gave so much RAM, so >1 Gb DB's looked really cool idea
Via
What about Redis, there's some discussion about it being better then Memcache.
@MarkTurner - gonna run this in test server (1 of 3 testing server we have in VM's at IT dep.)
@Profforg - your pricing is good for me, but I work at huge company with lots of manager, so I'm 3rd by rank, and buying service is not up to me, but will have in mind, the I release my own project.
@kenkelis First of all, you need to tune MySQL. If you are not already set tmpdir=/dev/shm this alone will fix half your CPU issues, the problem is it is stuck waiting on IO because you are trying to perform joins and selects on the hard drive instead of in RAM (using this tmpdir setting changes that so it uses the ramdisk for joins and selects). This will likely fix most of your issue. After that, get something like mysqltuner and make adjustments to your caching ( http://mysqltuner.pl ).
We run lot of very large sites at the company I work at and we use clusters with replication for a lot of it, but really what is needed is to be doing JOINS and SELECT actions in memory, not the whole database being in memory. Once setup this way you will notice a drastic reduction in IO wait and CPU load on the server. If you tune MySQL correctly you shouldn't need to mess with putting all your data into ram, which in and of its self is a bit more than risky.
Edit: looks like I just saved you $10,000 so if your feeling generous let me know.
Edit2: You can also consider adding caching to your situation using memcached for example or maybe Zend Opcache to leverage for caching your MySQL objects to reduce overall load on MySQL in general. There are a lot of things you can do here outside of putting your precious data all in ram.
my 2 cents.
Cheers!
Run mysqltuner first because you'll lose the cumulative data when you restart MySQL to change the tmpdir
Best thing to do is Remote SQL in a ssd server.
You can index the entire database, effectively (for reads) putting the entire db in ram and eliminating all row by row scanning. The con to this is slow system restart (the entire db has to be reindexed every restart). Your writes will still be bottlenecked by disk IOPS (most db's spend >90% of the time reading as opposed to writing).
Btw, feature-wise there is no reason to use memcached today (except you are already skilled/invested in it).
http://stackoverflow.com/questions/10558465/memcached-vs-redis
Memcached/Redis is only needed if you do too many joins/filtering. Until you see this bottleneck, cache+index is sufficient.
@TheLinuxBug - so, created virtual (RAM) drive, and targeted mysql tmpdir to it, this I think will work for some time, till we remake server.
Now we are creating Ubuntu 14.04 LEMP stack with Ajenti (V) control panel, with Nginx and MariaDB as main components.
Need to read more about virtual drive, and is it possible to make cache more powerful.
@all Thank for help, for now we will remake old server stack, and see what happens.
That step is not needed, you can direct the tmp data there
also enable query cache (set memory for it) if server my.cnf is not optimized it is enabled with zero memory
@Maounique and @coolice - done it, now monitoring everything till monday. And again, thank you for your help