MySQL insert/update/delete very slow

General programming chat and advice for beginners

16 posts
by Narf03 » Sat Jul 21, 2012 5:03 pm
Just try out MySQL, noticed extremely slow when doing changes into database, inserting 1 row with just an integer into a table take about 1 sec, sometime more. Updating a row with primary key is about half second, but update or delete a row without any index or primary key will timeout(about 600 rows in that table).

Select is ok even select entire table of 600+rows take 0.1 second. Select *, rand() on 600 rows take 0.3 second which is very good also, so I think only when changes that need to reflect onto SD will be very slow and not related to CPU speed. I need to know are they anyway to speed it up? Currently I think it's unusable.

Thanks.
Posts: 230
Joined: Mon Jun 11, 2012 3:44 pm
by Syrioth » Sat Jul 28, 2012 8:44 pm
don't know if this is useful to you but i've seen lots of people using SQLite instead of MySQL and from what i've seen it seems to run at an ok speed(just from what i've read on the forums)
Posts: 7
Joined: Fri Jul 27, 2012 9:49 am
Location: Scotland
by Narf03 » Sun Jul 29, 2012 5:54 am
I got multiple program sharing a database so I unable to use SQLite.
Posts: 230
Joined: Mon Jun 11, 2012 3:44 pm
by cil8 » Mon Aug 13, 2012 9:36 pm
I haven't a solution for your issue, but I'm running postgresql on my raspberry with usb harddisk and I'm able to insert 1000 rows, with one index on the table, in 6 seconds. So this isn't a raspberry limit but e problem is mysql and/or some incompatibility it has with rpi-optimized kernel.

Maybe changing table type, from myisam to innodb or vice-versa, could be helpful.
Posts: 97
Joined: Tue Feb 14, 2012 10:47 am
by rmwebs » Wed Aug 22, 2012 9:50 am
I've had some experience using MySQL on memory and CPU restrictive systems, namely VPS servers (I ran a VPS service provider). We had a lot of clients having problems running MySQL on low-end 256mb VPS systems, the solution was to optimise the hell out of the MySQL config.

There are a number of guides out there, this one seems pretty good: http://www.tutdepot.com/optimize-apache ... 256mb-vps/

In a nutshell, you want to disable innodb as that slows mysql down a lot, then its just a case of tweaking the settings.

Hope this helps. You'll notice the above one also applies to Apache, which can also be pretty well optimised, but it still wont be as fast as lighttpd.
Posts: 197
Joined: Wed Jan 25, 2012 11:30 pm
by el10t » Wed Aug 22, 2012 2:10 pm
I'm no expert but used to use SQL at work yonks ago. We had times when it would take an age to update records on a db because it was rebuilding an index after every insert on a big set of records. Could this be your issue? Try deleting the indexes before your insert and see what happens.
Posts: 24
Joined: Wed Aug 22, 2012 2:04 pm
by alysdal » Sun Jan 20, 2013 6:01 pm
I used innoDB and was my to programs, client and server was unable to access it at the same time..
When i changed the db engine to MyISAM it worked much better!
Posts: 2
Joined: Wed Sep 19, 2012 9:40 am
by andrum99 » Sun Jan 20, 2013 8:33 pm
alysdal wrote:I used innoDB and was my to programs, client and server was unable to access it at the same time..
When i changed the db engine to MyISAM it worked much better!


I second that. I installed MySQL on my Pi to store temperature readings and make a point of disabling InnoDB. To do this, add the following lines to the [mysqld] section of /etc/mysql/my.cnf:

Code: Select all
ignore_builtin_innodb
default_storage_engine=MyISAM

You then need to delete the current tables that you have created using InnoDB and recreate them using the new default storage engine, MyISAM. I use PHPMyAdmin for my database administration - very useful tool. You can get it quote easily as follows:

Code: Select all
sudo apt-get update
sudo apt-get install phpmyadmin

You can then go to http://localhost/phpmyadmin, log in with the MySQL root username and password, and work on your database.

Cheers

Andrew.
My blog, with lots of Raspberry Pi goodness: andrum99.blogspot.co.uk
Posts: 227
Joined: Fri Jul 20, 2012 2:41 pm
by technion » Fri Jan 25, 2013 4:41 am
alysdal wrote:I used innoDB and was my to programs, client and server was unable to access it at the same time..
When i changed the db engine to MyISAM it worked much better!


There's a reason MyISAM is not the default.

I would be very cautious using this for anything containing important data.
Posts: 231
Joined: Sun Dec 02, 2012 9:49 am
by martijnm » Sat Apr 13, 2013 6:32 pm
Disable innodb:
Code: Select all
[mysqld]
skip-innodb
#ignore_builtin_innodb
default_storage_engine=MyISAM
Posts: 1
Joined: Sat Apr 13, 2013 6:29 pm
by cyrano » Sun Apr 14, 2013 12:12 pm
It's not that MySQL is slow, it's just the myriad of ways you can misuse it. There is someone on this forum running a pile of db's with fairly complicated updating and reading besides a number of other apps on an RPi, off the SD card. Of course, he's had a lifetime's experience in optimizing SQL.

Why is Wordpress such a slow beast? Because the way it uses the database is very, very inefficient. So, Apache, WP, MySQL on a Raspberry will be hardly usable, out off the box. With proper optimizing, it runs smoothly.

Going to Postgres or any other db app, will just land you with different problems. Look at the way your SQL functions. Learn how they are treated internally by the dbms. There's no dbms on earth that is optimized for bad SQL language.
User avatar
Posts: 503
Joined: Wed Dec 05, 2012 11:48 pm
Location: Belgium
by cleverca22 » Sun Apr 14, 2013 12:15 pm
by default, innodb will try to flush everything to the 'disk' (sdcard) after every single query

if you start a transaction (execute 'begin', and 'commit' when done), it will bulk all the queries up, and massively increase your thruput under innodb

myisam doesnt support transactions, so turning innodb will likely make some things worse

there are also some innodb options to fully disable that sync, but then you start to have potential data loss upon power failure
Last edited by cleverca22 on Sun Apr 14, 2013 12:43 pm, edited 1 time in total.
Posts: 168
Joined: Sat Aug 18, 2012 2:33 pm
by cleverca22 » Sun Apr 14, 2013 12:43 pm
doing some simple tests

insert query: 'insert into test (test) values (1);'

doing the insert triggers a single write to 8 sectors on the SD card instantly (probly before the query returns)
exactly 1 second later, another 7 writes show up, to 160, 32(5 times), and 8 sectors
average insert time ranges from 0.04 to as high as 0.56 under some rare cases

properly bulking many inserts into a single query like this
Code: Select all
insert into test (test) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45),(46),(47),(48),(49),(50),(51),(52),(53),(54),(55),(56),(57),(58),(59),(60),(61),(62),(63),(64),(65),(66),(67),(68),(69),(70),(71),(72),(73),(74),(75),(76),(77),(78),(79),(80),(81),(82),(83),(84),(85),(86),(87),(88),(89),(90),(91),(92),(93),(94),(95),(96),(97),(98),(99),(100);


results in it taking 0.07 seconds, with 24, 160, 32(times 5), and 8 writes, very similar pattern to before

if i do the same massive query within a begin; and commit; block, it often does no writes at all (cant io bind!), and seems to do the writes at a random point in the future, until i do commit, forcing it to save it all

Code: Select all
dumb insert: 22.90137539164 rows/sec
dumb + bulk: 691.51769928655 rows/sec
dumb insert with transaction: 856.58937328201 rows/sec
bulk with transaction: 4568.5963030383 rows/sec


http://pastebin.com/PHUbftsP

put together a simple test script, showing how fast each method is, this is purely on the defaults in rasbian, i'm sure it can go much faster if its configured right

by making a minor change to the mysql config file ive massively increased performance
Code: Select all
[mysqld]
innodb_flush_log_at_trx_commit=2

Code: Select all
dumb insert: 683.37220991508 rows/sec
dumb + bulk: 2083.979788948 rows/sec
dumb insert with transaction: 806.27604071803 rows/sec
bulk with transaction: 6089.1483098104 rows/sec

http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit
Posts: 168
Joined: Sat Aug 18, 2012 2:33 pm
by Narf03 » Sun Apr 14, 2013 3:42 pm
Tnx everybody for your replies. Cleverca22 I will try your suggestion, thanks again.
Posts: 230
Joined: Mon Jun 11, 2012 3:44 pm
by Blindfreddy » Tue Mar 18, 2014 8:30 pm
I had the same issue with extremely slow writes with MySQL on the raspberry pi, using owncloud. I am certain that the root cause for this is with the SD-card, which is not meant for random writes, especially small ones. SD-card are good in cameras, where few large files are written and then never changed. But in the pi and in combination with write-intensive applications - that is, many small writes - SD-cards perform very poorly. Run an iotop and you will note mmcblk or similar causing very high wait on IO, which means the system is waiting for the SD-card to complete the write operation.

In my case, I was using owncloud and syncing thousands of files. Each file upload causes several write operations, namely the file itself plus the metadata in the database, plus redo and archive logs. This results in several second delays for each file being uploaded. Tweaking MySQL won't help much, the issue is with the underlying SD-Card being used as a harddisk. My solution was to move the MySQL database to a real harddisk and symlink to it. Performance has improved vastly, now it takes around 300ms for each file - not stellar but better by at least a factor of 10x.

So, if you have this issue, I recommend either getting a real HDD or an SD-card that is very good at 4K writes.
Posts: 4
Joined: Sat Apr 20, 2013 8:50 am
by DougieLawson » Tue Mar 18, 2014 11:50 pm
Make sure your database is using INNODB or upgrade to MariaDB and use the newer Aria engine.
Hacker on ZX80, Microtan65, Raspberry Pis and Arduinos
Unemployed mainframe database troubleshooter
RPi owner since 2012.
Twitter: @DougieLawson

Gaffer tape is "The Force", it has a dark side and a light side and it holds the Universe together.
User avatar
Posts: 7271
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK