msound.tech - Improving MySQL Insert Performance
Improving MySQL Insert Performance









Search Preview

Improving MySQL Insert Performance

msound.tech
Warning: Do NOT do this on a production server!
.tech > msound.tech

SEO audit: Content analysis

Language Error! No language localisation is found.
Title Improving MySQL Insert Performance
Text / HTML ratio 47 %
Frame Excellent! The website does not use iFrame solutions.
Flash Excellent! The website does not have any flash contents.
Keywords cloud ext4 performance partition access var email default data time reliability speed settings parameter etcfstab timestamp devsda7 mntnewvar written case noatime
Keywords consistency
Keyword Content Title Description Headings
ext4 11
performance 7
partition 6
access 5
var 5
email 4
Headings
H1 H2 H3 H4 H5 H6
1 3 0 0 0 0
Images We found 0 images on this web page.

SEO Keywords (Single)

Keyword Occurrence Density
ext4 11 0.55 %
performance 7 0.35 %
partition 6 0.30 %
access 5 0.25 %
var 5 0.25 %
email 4 0.20 %
default 4 0.20 %
data 4 0.20 %
time 4 0.20 %
reliability 3 0.15 %
speed 3 0.15 %
settings 3 0.15 %
parameter 3 0.15 %
etcfstab 3 0.15 %
timestamp 3 0.15 %
devsda7 3 0.15 %
mntnewvar 3 0.15 %
written 3 0.15 %
case 3 0.15 %
noatime 3 0.15 %

SEO Keywords (Two Word)

Keyword Occurrence Density
new partition 4 0.20 %
a new 3 0.15 %
parameter is 3 0.15 %
to the 3 0.15 %
access time 3 0.15 %
to import 3 0.15 %
is written 3 0.15 %
written to 3 0.15 %
in my 2 0.10 %
is my 2 0.10 %
to improve 2 0.10 %
to do 2 0.10 %
I chose 2 0.10 %
I got 2 0.10 %
with the 2 0.10 %
free space 2 0.10 %
my etcfstab 2 0.10 %
laptop and 2 0.10 %
This is 2 0.10 %
the last 2 0.10 %

SEO Keywords (Three Word)

Keyword Occurrence Density Possible Spam
is written to 3 0.15 % No
written to the 3 0.15 % No
a new partition 3 0.15 % No
last access timestamp 2 0.10 % No
to move var 2 0.10 % No
devsda7 var ext4 2 0.10 % No
to import my 2 0.10 % No
20 minutes to 2 0.10 % No
to a new 2 0.10 % No
var to a 2 0.10 % No
move var to 2 0.10 % No
I wanted to 2 0.10 % No
with ext4 settings 2 0.10 % No
experimenting with ext4 2 0.10 % No
There are three 2 0.10 % No
default option is 2 0.10 % No
The default option 2 0.10 % No
case of crash 1 0.05 % No
safest thing to 1 0.05 % No
of crash will 1 0.05 % No

SEO Keywords (Four Word)

Keyword Occurrence Density Possible Spam
is written to the 3 0.15 % No
to a new partition 2 0.10 % No
The default option is 2 0.10 % No
experimenting with ext4 settings 2 0.10 % No
var to a new 2 0.10 % No
move var to a 2 0.10 % No
to move var to 2 0.10 % No
safest thing to do 1 0.05 % No
journal This is the 1 0.05 % No
This is the safest 1 0.05 % No
is the safest thing 1 0.05 % No
the safest thing to 1 0.05 % No
msoundtech About Improving MySQL 1 0.05 % No
thing to do and 1 0.05 % No
to the journal This 1 0.05 % No
to do and in 1 0.05 % No
do and in case 1 0.05 % No
and in case of 1 0.05 % No
in case of crash 1 0.05 % No
case of crash will 1 0.05 % No

Msound.tech Spined HTML

Improving MySQL Insert Performance msound.techWell-nighImproving MySQL Insert Performance May 12, 2013 Warning: Do NOT do this on a production server! So, my palmtop has been psoriasis when I try to import a database (about 6MB .sql file). It takes well-nigh 20 minutes to run the script. After doing some reading, I found a way to modernize the speed. The whole thing has to do with the fact that my / partition is running ext4 file system. And ext4 has some default settings that are good for reliability, but not so good for speed.SurpassingI started experimenting with ext4 settings, I wanted to move /var to a new partition so my experiments do not stupefy the unshortened OS. Steps to move /var to a new partition in Ubuntu 12.04 Luckily for me, I had some unpartitioned self-ruling space lying virtually in my nonflexible disk. I created a new partition using that self-ruling space, and formatted it as ext4. In your case, you might have to resize an existing partition. Do this carefully, or you might lose your data. In my specimen the new partition was in sda7. So, first I marching into single user mode. # mkdir /mnt/newvar # mount /dev/sda7 /mnt/newvar # mv /var/* /mnt/newvar/ # vi /etc/fstab Add a line at the end /dev/sda7 /var ext4 defaults 0 2 I rebooted the machine, and everything came up fine. Still MySQL was taking 20 minutes to import my database. So, next I got virtually to experimenting with ext4 settings. Steps to modernize ext4 speed (and reduce reliability) There are three main parameters at play here: The first parameter is wangle time. There are three possible modes: atime, relatime and noatime. When you segregate atime, everytime a file is accessed, the last accessed timestamp is written to the inode. This means, for every read, there will be a write, and therefore might be reason for slow performance. The noatime option will rationalization the filesystem not to update the last wangle timestamp. Be warned that this might unravel some email applications that rely to last wangle timestamp to see if an email was read or unread. The default option is tabbed “relatime” and is similar to noatime, but updates the wangle time only when the previous wangle time was older than the current modify or transpiration time. This will modernize performance as well as not unravel any email applications. But I wanted to pericope max performance, and since it is my minutiae laptop, and not an email server, I went superiority and chose “noatime”. The second parameter is data. The valid options are journal, ordered and writeback. The default option is ordered, and all data is written to the main filesystem surpassing the metadata is written to the journal. This is the safest thing to do, and in specimen of crash will recover fine. However, to push the performance, I chose “writeback”. In this mode, the data ordering is not preserved. This is somewhat risky if there is a crash or unreticent poweroff. But this is my palmtop and I am feeling lucky. So! The third parameter is barrier. By default ext4 enables write barrier. This forces proper on-disk ordering of periodical commits. Again, it’s a question of reliability vs performance. So, I chose to disable barrier. I edited my /etc/fstab and it now looks like this: /dev/sda7 /var ext4 noatime,data=writeback,barrier=0 0 2 Also I replaced “/dev/sda7” in my /etc/fstab with the UUID of the device, that I got from running the writ blkid. Doing these steps gave me a huge performance improvment. I am worldly-wise to import my MySQL database in seconds! It’s been a few weeks now and so far I have not had any problems. Be sure to read the man page of mount, the documentation of ext4 and Nick’s blog well-nigh improving ext4 filesystem performance. msound.tech msound.tech mani.sound@gmail.com msound msound Mani Soundararajan is a web developer who specializes in PHP, Drupal and DevOps. He loves Docker and his latest interests include go lang. When he is not hacking, he likes to go on cross-country motorbike rides.