New answers tagged

0

This worked for me: Clear the mysql data directory at /var/lib/mysql assuming that you have backups or you don't really need that data anymore. Then sudo service mysqld start


0

SELECT sprId, COALECSE(SUM(calc*(1-load)), 0), COALECSE(SUM(calc*load), 0) FROM source_table GROUP BY sprId


0

If some of the values are 21 characters, dropping to 17 will lose data. You probably don't want to do that. Be conservative -- what will future data be like? Maybe 22 characters? Drop the length to, say, 30. No space will be saved by changing the max length -- in the "data" or in the "index". Well, not because of the change. However, most ALTERs ...


0

Have you thought about either using the first 8 characters or up until the first dash as a basis for partitioning? You should start by looking at how many unique values you would get when you partition by either the first 8 characters or the first dash. I don't want to assume that the first dash falls on the 9 character. Then you can try to partition with ...


0

... Oracle will be forced to read the full table in one query if the column isn't somehow optimized. The word you're searching for is "indexed". ... this column is a primary key. If this column is the Primary Key of the table, then it is indexed. As such, queries for a individual values will be fast: select ... from table1 where id = ? Other, ...


1

If your instances are accessible over the net than you can simply dump the prod instance on the dev instance via the host having required utilities and access to both instances: mysqldump --host 10.20.30.40 \ -u myaccount1 \ -pmypassword1 \ --all-databases \ --events \ --routines \ | mysql --host 20.30.40....


1

Suggestions to consider for your my.cnf [mysqld] section innodb_lru_scan_depth=100 # from 1024 to conserve 90% of CPU cycles used for function innodb_buffer_pool_instances=8 # from 16 to reduce overhead until more RAM is avail innodb_flush_neighbors=2 # from 0 to clear innodb_buffer_pool_pages_dirty quicker thread_cache_size=100 # from 13 to reduce ...


2

Shane Bester made a suggestion as a comment on my bug report which explains that this is a known bug and will be resolved in next release. More importantly, there is a workaround which I've tried with great success. Thanks Shane wherever you are! internal_tmp_mem_storage_engine=MEMORY Shane said this: Thanks for the test data. Found a workaround on 8....


0

Possibly what happened was that a complex SELECT was run. It was so complex that it built one or more giant temporary tables that overran the disk tmpdir filesystem.


0

There additional complexities in row format. What has been discussed is InnoDB; MyISAM works differently. InnoDB has 4 ROW_FORMATs. You should probably look at those if you would like some flexibility. The general rule for InnoDB is to focus on where and how rows are stored. In this, the limit is about 8KB; more specifically a little less than half the ...


0

It is also stored the length of the data, that is why it is 9 - 12 , if you want it exactly. https://github.com/jeremycole/mysql/blob/master/storage/innobase/include/btr0cur.h#L762 /** The reference in a field for which data is stored on a different page. The reference is at the end of the 'locally' stored part of the field. 'Locally' means storage in the ...


1

ibdata1 never shrinks. .ibd files never shrink. When a table is in ibdata1 and is ALTERed, space in that file is temporarily consumed, thereby possible stretching the file. With .ibd, an ALTER will probably create a new copy, then toss the old one, thereby temporarily using diskspace, then releasing it. There are too many variables to make any predictions. ...


0

information_schema.PARTITIONS.PARTITION_DESCRIPTION Consider naming the partitions in an obvious way, such as p20200419 to contain data for the 19th (and defined LESS THAN ('2020-04-20')) More tips: http://mysql.rjweb.org/doc.php/partitionmaint and its companion: http://mysql.rjweb.org/demo_part_maint.pl.txt Example Given: SHOW CREATE TABLE pmdemo....


0

Do a logical dump with mysqldump instead of physical backup with xtrabackup or similar tool.


0

Rate Per Second = RPS Suggestions to consider for your AWS RDS Parameters group key_cache_age_threshold=7200 # from 300 (seconds) to delay age out only to Read again key_buffer_size=64M # from 16M to reduce key_reads RPS of ~ 50 key_cache_division_limit=50 # from 100 to have a Hot/Warm split in RAM key_cache_block_size=16384 # from 1024 to reduce ...


Top 50 recent answers are included