Tag Info

New answers tagged

0

I have followed your fantastic instructions and dozen’s more on the internet step by step but inspite of changing the log file size to 256M, every time in the end I restart the server it still defaults to 5MB log file. Could there be another location other than /etc/my.cnf where I also need to change it. Cheers,


0

alter table add column, algorithm=inplace, lock=none will alter a MySQL 5.6 table without copying the table and without locking impact. Just tested this yesterday, mass inserted 70K rows into a 280K row 7 partition table, 10K rows into each partition, with 5 seconds sleep in between to allow other throughput. Started the mass inserts, then in separate ...


1

I am not sure when but sooner or later we're going to see deprecation of MyISAM. InnoDB is performing well and it does have fulltext as well (though performance is a bit of something being worked over there.) InnoDB is even a default storage engine in latest MySQL versions. If you really really need performance from FULLTEXT go with MyISAM (Note ...


1

Have you tested or would it be possible to drop indexes on the destination DB table(s) where you are inserting into, insert those into smaller batched chunks (optimal as indicated above), and then rebuild the indexes on the destination table(s) once all inserts are complete? May be something easy enough to test to confirm.


3

If you want a proper database then of the two you should go for innodb - it supports ACID transactions, referential integrity, and smaller granularity locking. myISAM is faster in a number of read-only use cases but: The lack of support for transaction safety can lead to corruption in multi-user situations where several updates happen at the same time ...


3

There are a few things I can clarify for you here: Yes, it is a good practice to delay secondary index creation until after you import the data (starting from MySQL 5.5 - not before). Mysqlpump does this by default. When you delay secondary index creation, internally MySQL will read, sort and then create the index (reducing fragmentation). For MySQL 5.7 ...


1

My reason why to not to use innodb_file_per_table is performance. I did some tests for our database with 450 tables on mysql 5.5.45 Linux CentOS release 6.7 For unit tests which inserts fixtures into the database before each test (not using all tables everytime) and also tests itself does work with database a lot (inserts, update, deletes, selects) the ...


1

The problem is actually very simple. Here is what happened When you installed MySQL, the 5 InnoDB systems tables exist in two places inside /var/lib/mysql/mysql as 5 .frm and 5 .ibd files inside the data dictionary within ibdata1 (InnoDB System Tablespace) At some point in your installation, you must have deleted ibdata1. This left the 10 InnoDB system ...


0

You'll need to put a trigger on the siteArea table so that whenever it's updated or inserted, it'll call a stored procedure with an argument representing the new site area and that procedure will calculate the needed values and insert them in a table. Your question is too ambiguous for me to describe further.


0

This happened to me recently and this thread has been invaluable for helping me understand what's happening. I'm running a LAMP stack on Ubuntu 14.04 with 1GB Ram. My server kept crashing due to web traffic spikes. For me, fiddling with the mysql config file only extended the amount of time I'd experience another random crash. To test and fix this, I ...


0

For the sake of it I'll describe an approach not mentioned above. It is typically used for temporal data. Not sure it will fit your needs, but here it goes. The idea is to have a copy of your original with two additional attributes, begin_time and end_time: create table ... ( ... , begin_time timestamp default now() not null -- MySQL timestamp deviates from ...


1

Not sure if the issue is resolved. But as suggested here're some ways you can do this : 1. ALTER TABLE Approach : ALTER TABLE ENGINE=INNODB; Considerations for this approach : As you mentioned you have a big table this command will take time to execute. CREATE TABLE and INSERT INTO Approach : You can create a empty table with INNODB storage engine and ...


1

I have an MyISAM2InnoDB blog that covers your question thoroughly. It discusses index differences, especially PRIMARY KEY and FULLTEXT, space (2x-3x is typical), LOCK TABLES, decreasing key_buffer_size and increasing innodb_buffer_pool_size (very important), etc.


0

Deltas The data you are "snapshotting" -- how often does it change? I suggest looking into storing only the "deltas". When some piece of the snapshot does not change at all, the delta is empty, and you can store nothing. For reconstructing a snapshot at some point in the past, the processing is costly -- you need to walk through the versions, applying ...


0

Create a new table, that has all the columns of the existing table, but also a time column, which is set to the value of the time the snapshot was initiated. You're adding a new fact to store about an existing set of facts (each table should store one kind of fact, ideally). The value of the time column identifies each snapshot. If you need to query across ...


1

Solved: ERROR 1030 (HY000) at line 810: Got error -1 from storage engine The above error means you have innodb_force_recovery set in your my.cnf I'm not sure why I couldn't find this easily in mysql documentation, but it's simple enough.


3

InnoDB tables tend to occupy more space than MyISAM tables do. While converting, new physical files are being created, so you have to have free space on the disk (Hard to estimate, but to be safe I would say 120G) If you use Percona tools to make the change online, all modifications on the original table will be stored in a temporary table using triggers. ...


1

By default, XtraDB is used as the InnoDB replacement. If you login to MariaDB and run SHOW ENGINES;, look for the InnoDB. According to the Documentation for SHOW ENGINES;: Note that both MySQL's InnoDB and Percona's XtraDB replacement are labeled as InnoDB. However, if XtraDB is in use, it will be specified in the COMMENT field. See XtraDB and InnoDB. ...


0

Do not use any suggestions from mysqltuner unless you understand what they mean - mostly they are unfortunately nonsense. There is a page in manual covering internal usage of tmp tables. I will try to give a short overview: automatically created on-disk tables use MyISAM engine, you can not change that to InnoDB afaik, those in memory use MEMORY engine ...


0

Because your mysql is still creating disk tables: [!!] Temporary tables created on disk: 81% (2K on disk / 2K total) Looking at the source of tuner-script $mycalc{'pct_temp_disk'} = int(($mystat{'Created_tmp_disk_tables'} / $mystat{'Created_tmp_tables'}) * 100); *snip* if ($mycalc{'pct_temp_disk'} > 25 && $mycalc{'max_tmp_table_size'} < ...


0

So yes as you have mentioned about SQL injection the other advantage is what you guessed. Quoting from documentation: Using prepared statements with placeholders for parameter values has the following benefits: Less overhead for parsing the statement each time it is executed. Typically, database applications process large volumes of ...


1

You are near or at a limit of row size. There is a limit of about 8000 bytes per record. TEXT fields, by default, store only 767 bytes toward that 8000, and store the rest elsewhere. You have enough TEXT fields to threaten the 8000. Plan A: Have one JSON TEXT field that is a structure containing the 9 structures you now have. This will easily avoid the ...


1

Consider batching your commits. A batch size of 1024 is a good starting size. Change batch sizes until you reach your optimum throughput.


0

All three files are necessary and sufficient for restoring all the InnoDB tables. You cannot load a single InnoDB table with a technique like that. (MyISAM is a different story.) 5.6.8 has "transportable tablespaces" which lets you move a single non-partitioned table from one place to another, but it takes special instructions, not just file copies. ...


0

In fact, two files will suffice: MyTable.frm MyTable.ibd The table schema you'll get from the frm file and the data - from ibd file. or MyTable.ibd ibdata1. ibdata1 stores InnoDB dictionary which describes the schema, too. Sometimes, a trained eye can guess the schema from ibd file, but it must be relatively simple.


0

Just delete ib_logfileX files and restart mysql service. You changed the innodb-log-file-size var to size more bigger than already set it in your mysql daemon.


1

When this DML be written to iblog files ? -- "as needed" during the hour When the DML be written to binary log files ? -- Never. Information for the binlog is gathered, but not written until COMMIT. Will the related data pages on disc be modified during 10:00 AM to 11:00 AM -- There may be modifications to the pages. They will be invisible to the user. ...


0

It used to be that a high transaction rate in InnoDB would stall periodically. The TPS would drop to zero for a few seconds, and there was nothing that anyone could do about it. Percona came along, figured out what was causing it and put out a version of MySQL that fixed the problem. (It had something to do with the dirty pages in the buffer_pool getting ...


0

RAID-5 controller with a battery-backed write cache is excellent. Writes appear (to MySQL) to be instantaneous. When comparing RAID-5 to -10, you need to decide whether you are comparing N drives to N drives or comparing M GB to M GB. N drives: RAID-10 scatters its reads across N drives; So does RAID-5. Writes hit 2 drives in either case, but the ...



Top 50 recent answers are included