I'm not familiar with Percona, what problem does it solve?
Their Xtradb storage engine has very tangible performance benefits over InnoDB, especially on SSDs.
Loading half a billion rows into MySQL
51–60 of 101 posts
Re: Loading half a billion rows into MySQL
#52Why would anyone partition tables -- ever?
Lots of reasons :)
Re: Loading half a billion rows into MySQL
#53This discussion reminds me of non-technical users. They will tolerate incredible sluggishness. It is amazing what Oracle and the free alternatives get away with. People, including engineers/developers, not only tolerate sluggishness but when something actually works on the first try they think it's amazing.
I've witnessed this tolerance for mediocrity for years and it still continues to blow my mind.
Re: Loading half a billion rows into MySQL
#54Does it blow anyone else's mind that database servers are so powerful nowadays that you can just load half a billion rows into a SINGLE TABLE and pretty much expect things to work? Obviously there are caveats but all in all a nice chunk of RAM backed by a large SSD goes a long way.
My telemetry table is >2B rows and growing. 5B is starting to worry me; .5B, not so much. This is even backed by ( not much ) spinning rust.
Re: Loading half a billion rows into MySQL
#55I'm not familiar with Percona, what problem does it solve?
Beyond the other listed benefits: Percona also provides support contracts quite inexpensively, which can be worth their weight in diamonds. I bought a support contract after our Percona instance started to slow down, and the level of quality of the analysis and the results blew my mind. And I've been doing SQL for 25 years.
Re: Loading half a billion rows into MySQL
#56Does it blow anyone else's mind that database servers are so powerful nowadays that you can just load half a billion rows into a SINGLE TABLE and pretty much expect things to work? Obviously there are caveats but all in all a nice chunk of RAM backed by a large SSD goes a long way.
I'm more impressed with how cheap it is.
An SSD is on par with a 15K disk, but delivers 10X the perf, throw 10 SSDs in an array and you've got what used to cost $100,000 for about $5K
Re: Loading half a billion rows into MySQL
#57Assuming your event data is immutable (i.e. no UPDATEs, just INSERTs), you'd probably have fewer headaches long-term if you just dumped the database to flatfiles, stored in HDFS and queried using Hive (which has MySQLish query syntax anyway). This architecture will take you to billions of rows quite happily. This is the architecture we use for eventstream analysis at SnowPlow ( https://github.com/snowplow/snowplow ).
It sounds like a problem better solved by triggers, or a few materialized views.
Re: Loading half a billion rows into MySQL
#58It's more work for the computer to process data stored as "rows" than data stored as columns. Think of how a disk is organized. If you abandon MySQL and store data as columns you can load a trillion rows. But forget I mentioned this.
We have been doing some tests with http://www.monetdb.org/Home. We saw inserts per second in the range of 50k/sec on a laptop.
Re: Loading half a billion rows into MySQL
#59I want to say this gentleman is doing it wrong. If his current set-up is what I perceive from the description he is not going to be gaining any speed or scalability. by moving to one giant table. In fact he might be losing some. It sounds like he set up 12 monthly tables and is inserting data into each one by month then he is querying across all 12 tables and joining the results. You can do that query in one fell swoop using UNION, If you have a significant number of entries the overhead of opening the 12 tables is negligible. If your looking up on a primary key then you will be more then fast enough.
Switch your tables into MyISAM and make a 13th table. insert the current month into the 13 table and query across all 13 tables when you need to get data out. At the end of the month move all records in the 13th table into its proper month table. ( make the 13th table a memory table sync it to disk every few minutes or put it on a really fast SSD )
Re: Loading half a billion rows into MySQL
#60Earlier quoted context omitted.
To save everyone the math, that's ~20k inserts a second.
Oracle can do a hell of a lot more than that if you preload your tables as transportable table spaces.
MySQL can do a whole lot more then that too. I routinly get 15k to 20k writes per second while the server is under normal load. You can spike that a great deal higher depending on what else your doing and what kind of table your writing.