Tag: 000 records

Is it good database design to store large hashes in your tables?

Post author By Jessica Alba
Post date April 30, 2020
No Comments on Is it good database design to store large hashes in your tables?

I have to produce a lot of graphs using a lot of historical data (1000+ data points for each graph), and depending on some pre-defined options, I need to display that specific data in a graph. Let’s say I have 4 possible combinations of options X and Y, (X,Y),(X,X),(Y,X),(Y,Y) If each of these combinations takes […]

Tags 000 records, 000), and depending on some pre-defined options, but it will also mean that my database will grow immensly and will host data that is not neccessarily needed and will only be needed if a use, however after numerous days trying to optimise them and alter my code design to save time, however this still means a 20s waiting period for that first user. I have also considered that my queries may be innefficient and slow, I have to produce a lot of graphs using a lot of historical data (1000+ data points for each graph), I need to display that specific data in a graph. Let's say I have 4 possible combinations of options X and Y, I think 20s is the fastest I can achieve with the hardware available. Any thoughts on this?, is it acceptable to store summary data in the form of a hash in the database schema? Now, let's say there are 1, this will mean computing these summary hash's which will save 20s per request, X, y, Y) If each of these combinations takes 20 seconds for the queries to execute

Database Development

LOAD DATA INFILE Performance Degrading

Post author By Jessica Alba
Post date April 4, 2020
No Comments on LOAD DATA INFILE Performance Degrading

I have very little database experience, but I’ve inherited a database for a production system that has major problems that I am attempting to fix. The system receives a continuous stream of data from internet of things devices that my company uses to monitor manufacturing equipment. Each record is 180 bytes, and I receive about […]

Tags ~2300 records/sec). I receive the data in a csv file with 20, 000 records, 000 records per second and my system cannot keep up with the incoming data. My team has been dealing with this by moving the database off of, 000 records per second. As the database grows this insert rate decreases. After about 2 months the insert rate drops below 2, a few times a month I am asked to retrieve all the data for a given sensor for the last 6 months. When the database is empty, a query is done to look for records from the same device ID over the last day. The records returned from the query are post-processed to calc, allow me to age off data gracefully. I assumed partitioning would also solve the LOAD DATA INFILE slowdown, and I receive about 200 million records per day (~36 GB/day, and recreating an empty database. This is extremely problematic when I'm asked to do a 6 month report on a device. In my dev environment I h, at the very least, but after running with my new design for 3 weeks, but I've inherited a database for a production system that has major problems that I am attempting to fix. The system receives a continuous, I have very little database experience, LOAD DATA INFILE can load about 40, so I can attempt to keep 6 months of data in the database (I read it's best to have less than 50 partitions). I decided to use partitioning t, so LOAD DATA INFILE is used to directly import the data. The record's primary key is explicitly set in the csv, so there is no auto increment locking occurring. When each record is loaded, the less likely it is to be queried. I'd say data more than 3 days old is rarely queried. However, where each record is a device ID and the sensor's data. The csv format maps directly to our database schema for the device sensor readings

Database Development

LOAD DATA INFILE Performance Degrading

Post author By Jessica Alba
Post date April 3, 2020
No Comments on LOAD DATA INFILE Performance Degrading

Tags ~2300 records/sec). I receive the data in a csv file with 20, 000 inserts per second Specifically, 000 records, 000 records in a temporary table and performed my queries to queue them up for post processing then copied the table to the long term sensor, 000 records per second and my system cannot keep up with the incoming data. My team has been dealing with this by moving the database off of, 000 records per second. As the database grows this insert rate decreases. After about 2 months the insert rate drops below 2, a few times a month I am asked to retrieve all the data for a given sensor for the last 6 months. When the database is empty, a query is done to look for records from the same device ID over the last day. The records returned from the query are post-processed to calc, allow me to age off data gracefully. I assumed partitioning would also solve the LOAD DATA INFILE slowdown, and I receive about 200 million records per day (~36 GB/day, and recreating an empty database. This is extremely problematic when I'm asked to do a 6 month report on a device. In my dev environment I h, at the very least, but after running with my new design for 3 weeks, but I don't understand how to implement this in practice or why it works. If I held the 20, but I've inherited a database for a production system that has major problems that I am attempting to fix. The system receives a continuous, but none of them said how to do this. How is this handled in practice? What else can I do to maintain insert performance as the database gro, I can already see the performance degrading. Some details about my database configuration: MySQL 5.5 with 256 GB of RAM on the server inno, I have very little database experience, LOAD DATA INFILE can load about 40, my questions are: Can I use a temporary table, or if I held a week of data in an "active partition" and then moved the partition to the long term storage table at the end of the week, or short term table, shouldn't this data be in the innodb buffer pool? Does an entire table's index need to fit in the innodb buffer pool? Or can the index for a, so I can attempt to keep 6 months of data in the database (I read it's best to have less than 50 partitions). I decided to use partitioning t, so LOAD DATA INFILE is used to directly import the data. The record's primary key is explicitly set in the csv, so there is no auto increment locking occurring. When each record is loaded, the less likely it is to be queried. I'd say data more than 3 days old is rarely queried. However, to improve performance? I've seen this suggested on many blogs, where each record is a device ID and the sensor's data. The csv format maps directly to our database schema for the device sensor readings, wouldn't the copy perform the same operations as LOAD DATA INFILE? The same indexes would have to be computed and the long term table would s

Development User Help

The disadvantages of having too many files in single directory

Post author By How To
Post date March 7, 2020
No Comments on The disadvantages of having too many files in single directory

I have two options: Either: To have a database with 100,000 records, Or – To have these records as separate (and very small) text files in the single directory. What are the disadvantages of the second approach, except the copying will take a long time? I ask about the disadvantages from the performance point of […]

Tags 000 records, except the copying will take a long time? I ask about the disadvantages from the performance point of view., I have two options: Either: To have a database with 100, Or - To have these records as separate (and very small) text files in the single directory. What are the disadvantages of the second approa

Development

Why is MEMBER OF() faster than JSON_CONTAINS()?

Post author By Full Stack
Post date December 31, 2019
No Comments on Why is MEMBER OF() faster than JSON_CONTAINS()?

I’m playing with MySQL 8’s new JSON capabilities, in particular multi-value indexes. I noticed that there are 2 ways to check if a JSON array contains a given value: the MEMBER OF() operator, and the JSON_CONTAINS() function. They both return the same set of results for every query I make, but surprisingly, MEMBER OF seems […]

Tags '51'); +----------+ | count(*) | +----------+ | 7287 | +----------+ 1 row in set (0.36 sec) If first thought that this was because the s, @value); +----------+ | count(*) | +----------+ | 7287 | +----------+ 1 row in set (0.38 sec) Unless I'm mistaken, 0 rows affected (0.00 sec) mysql> SELECT count(*) FROM test WHERE JSON_CONTAINS(catIds, 000 inserted records later: mysql> SELECT count(*) FROM test WHERE 51 member of (catIds); +----------+ | count(*) | +----------+ | 7287, 000 records, 000 values total in the catIds fields: CREATE TABLE test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, 456]'); ... ... some 200, and the JSON_CONTAINS() function. They both return the same set of results for every query I make, but surprisingly, catIds JSON NOT NULL, I'm playing with MySQL 8's new JSON capabilities, in particular multi-value indexes. I noticed that there are 2 ways to check if a JSON array contains a given value: the MEMBER OF() operator, KEY test_categories ((CAST(catIds AS UNSIGNED ARRAY))) ); INSERT INTO test (catIds) VALUES('[123, MEMBER OF seems to be 3x faster than JSON_CONTAINS. Example on a table with 200, MEMBER OF() and JSON_CONTAINS() are equivalent feature-wise. In this case, PRIMARY KEY (`id`), so I tried assigning it to a variable first; but this does not make it any faster: mysql> SET @value = CAST(51 as JSON); Query OK, why is one of them faster than the other?, with around 700

Development

Twilio: Is there a way to export all opt outs?

Post author By Full Stack
Post date October 27, 2019
No Comments on Twilio: Is there a way to export all opt outs?

I want to export everyone who has reply stop (opt out) for my messages. I see that my options are API. However, through the API I can see that I get everything in pages of 50 messages, so I have to go and do a lot of code to go page by page, message by […]

Tags 000 records, and when I dont filter I get error: Your export request has more than 10, however when each time i try to filter on status received I get the following error, I want to export everyone who has reply stop (opt out) for my messages. I see that my options are API. However, message by message just to check if the content is stop. Seems a bit unnecessary for a 1 time job. From the console in my account, please filter your results and try again. Surely there must be an easy way that I can get all the list of numbers that had opt out?, so I have to go and do a lot of code to go page by page, through the API I can see that I get everything in pages of 50 messages

Development Heroku Ruby

Making ruby garbage collection kick in and run on heroku

Post author By Full Stack
Post date October 25, 2019
No Comments on Making ruby garbage collection kick in and run on heroku

I have a web service in a Ruby on Rails app hosted on heroku. When this service runs, a lot of memory gets used. I call the service in batches, so after each batch, all that memory is no longer needed. However, it goes above the heroku quota, and starts firing R14 errors. Monitoring the […]

Tags 000 record set three times. Then because of the 10 minute resolution, 000 record set twenty times. Here are my relevant heroku config settings: RUBY_GC_HEAP_FREE_SLOTS: 600000 RUBY_GC_HEAP_GROWTH_FACTOR:, 000 records, 10 at a time per API call. I made a code update at 2 PM UTC which restarted the dynos. Then ran through the whole 12, a lot of memory gets used. I call the service in batches, all that memory is no longer needed. However, and starts firing R14 errors. Monitoring the memory, but nothing helps. heroku config:add RUBY_GC_MALLOC_LIMIT_MAX=320.megabyte EDIT: People have asked to see the code. Although I don't think, errors: errors.empty? ? nil : errors) end Some information which may be helpful, even though the host is above the memory quota. How can I configure the ruby garbage collection to indicate that at the quota the garbage co, here it is. class Admin::Api::V1::CvesController < ApiController def upload result = Cve.transaction_upload_content(params['upload_str, I am uploading 12, I have a web service in a Ruby on Rails app hosted on heroku. When this service runs, indicating that the garbage collection is not running, it goes above the heroku quota, restarted the dynos just before 3PM, so after each batch, status: result[:errors] ? :unprocessable_entity : :created end end def self.transaction_upload_content(upload_file) byte_content = u, the memory never goes down and GC.stat[:minor_gc_count] does not change, then ran through the whole 12, waited 10-20 minutes, waited 20 minutes