LinuxQuestions.org
Help answer threads with 0 replies.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Distributions > Slackware
User Name
Password
Slackware This Forum is for the discussion of Slackware Linux.

Notices


Reply
  Search this Thread
Old 09-06-2018, 01:09 PM   #1
hedron
Member
 
Registered: Jul 2009
Location: NYC
Distribution: Slackware64-multilib 14.2, SARPI
Posts: 287

Rep: Reputation: 27
replace into is extreme slow, does it occur in slack-current?


My issue is identical to: https://stackoverflow.com/questions/...extremely-slow

The solution is either to use myisam or upgrade to 5.7. (insert ...duplicate command isn't right for my situation.) I don't know what's different between innodb and myisam, but I'm told to use innodb because something technical. But if myisam is faster, shouldn't I use that one? Why ride a snail when you have a horse?

Anyway, I'm wondering if slackware-current's mariadb has this issue? I can't tell by the file name, since it doesn't use Oracle's versioning.
 
Old 09-06-2018, 02:43 PM   #2
abga
Member
 
Registered: Jul 2017
Location: EU
Distribution: Slackware
Posts: 721

Rep: Reputation: 404Reputation: 404Reputation: 404Reputation: 404Reputation: 404
I don't know what MySQL/MariaDB is good for other than wasting precious time on it, pretending to use a relational DB, as I myself did a long time ago, by the time PostgreSQL was a little difficult to build, to tune and a lot of people called it "way too complex", BullS..t.
MySQL became popular as a small DB for storing webpages data and simple tables, part of the LAMP bundle.
As far as I remember, MyISAM was the default engine MySQL started with, not ACID compliant (thus cannot be called a database, but a trivial form of organizing data), didn't support transactions and very fast because of these "handicaps". Then, recently, the slower innoDB was introduced as an alternative engine, ACID compliant and supporting transactions, helping MySQL to become and be called a DB.
You can start with this article (and the links contained) if you'd like to learn more about these two engines:
https://stackoverflow.com/questions/...sam-and-innodb

MariaDB has some more improvements over these two engines, but I cannot tell you if these improvements are faster or not, as I'm not even installing it in Slackware:
https://mariadb.com/kb/en/library/ch...torage-engine/

Depending on your use case of DB, or if you'd like to get serious about databases, learn (standard) SQL and use a true&professional open source DB, then I suggest moving to PostgreSQL. You'll thank me later.
https://slackbuilds.org/repository/1...em/postgresql/

Last edited by abga; 09-06-2018 at 02:58 PM. Reason: typo
 
4 members found this post helpful.
Old 09-07-2018, 11:48 PM   #3
Richard Cranium
Senior Member
 
Registered: Apr 2009
Location: Carrollton, Texas
Distribution: Slackware64 14.2
Posts: 3,071

Rep: Reputation: 1453Reputation: 1453Reputation: 1453Reputation: 1453Reputation: 1453Reputation: 1453Reputation: 1453Reputation: 1453Reputation: 1453Reputation: 1453
Quote:
Originally Posted by hedron View Post
My issue is identical to: https://stackoverflow.com/questions/...extremely-slow

The solution is either to use myisam or upgrade to 5.7. (insert ...duplicate command isn't right for my situation.) I don't know what's different between innodb and myisam, but I'm told to use innodb because something technical. But if myisam is faster, shouldn't I use that one? Why ride a snail when you have a horse?

Anyway, I'm wondering if slackware-current's mariadb has this issue? I can't tell by the file name, since it doesn't use Oracle's versioning.
https://mariadb.com/kb/en/library/ma...compatibility/

According to that link, no.

EDIT: I haven't used PostgreSQL for ~3 years for anything special, but you will have to run a job to purge deleted records from your tables every so often. Even so, I'll agree with @abga's assessment of the two databases; PostgreSQL was the DB we migrated to at work after deciding the Oracle was just too expensive for our use case. It was not possible to migrate to MySQL since it did not support database triggers at that time (assuming that it does so now).

Last edited by Richard Cranium; 09-07-2018 at 11:54 PM.
 
Old 09-08-2018, 02:58 AM   #4
Darth Vader
Senior Member
 
Registered: May 2008
Location: Romania
Distribution: DARKSTAR Linux 2008.1
Posts: 2,727

Rep: Reputation: 1231Reputation: 1231Reputation: 1231Reputation: 1231Reputation: 1231Reputation: 1231Reputation: 1231Reputation: 1231Reputation: 1231
Quote:
Originally Posted by hedron View Post
My issue is identical to: https://stackoverflow.com/questions/...extremely-slow

The solution is either to use myisam or upgrade to 5.7. (insert ...duplicate command isn't right for my situation.) I don't know what's different between innodb and myisam, but I'm told to use innodb because something technical. But if myisam is faster, shouldn't I use that one? Why ride a snail when you have a horse?

Anyway, I'm wondering if slackware-current's mariadb has this issue? I can't tell by the file name, since it doesn't use Oracle's versioning.
The REPLACE INTO is (in)famous slow since Ice Age in MySQL.

AND generally speaking I seen its usage being considered a quite bad habit which slowdowns considerably the sites.

The real solution is to use general compatible queries like "INSERT INTO <blah blah> ON DUPLICATE KEY UPDATE <blah blag>" and call a day.

Last edited by Darth Vader; 09-08-2018 at 03:02 AM.
 
5 members found this post helpful.
Old 09-08-2018, 07:04 AM   #5
abga
Member
 
Registered: Jul 2017
Location: EU
Distribution: Slackware
Posts: 721

Rep: Reputation: 404Reputation: 404Reputation: 404Reputation: 404Reputation: 404
Quote:
Originally Posted by Richard Cranium View Post
https://mariadb.com/kb/en/library/ma...compatibility/

According to that link, no.

EDIT: I haven't used PostgreSQL for ~3 years for anything special, but you will have to run a job to purge deleted records from your tables every so often. Even so, I'll agree with @abga's assessment of the two databases; PostgreSQL was the DB we migrated to at work after deciding the Oracle was just too expensive for our use case. It was not possible to migrate to MySQL since it did not support database triggers at that time (assuming that it does so now).
With PostgreSQL 8.1 (2005) the AUTOVACUUM daemon was introduced, it's automatically started and you can tune its vacuuming frequency/schedule.
In addition to the lack of some functionalities, MySQL/MariaDB doesn't adhere to standard SQL and that's a big PITA if you need to export its code / import other standard SQL code in it.
 
1 members found this post helpful.
Old 09-08-2018, 08:25 PM   #6
Richard Cranium
Senior Member
 
Registered: Apr 2009
Location: Carrollton, Texas
Distribution: Slackware64 14.2
Posts: 3,071

Rep: Reputation: 1453Reputation: 1453Reputation: 1453Reputation: 1453Reputation: 1453Reputation: 1453Reputation: 1453Reputation: 1453Reputation: 1453Reputation: 1453
Quote:
Originally Posted by abga View Post
With PostgreSQL 8.1 (2005) the AUTOVACUUM daemon was introduced, it's automatically started and you can tune its vacuuming frequency/schedule.
Ah, that's good to know!

Quote:
In addition to the lack of some functionalities, MySQL/MariaDB doesn't adhere to standard SQL and that's a big PITA if you need to export its code / import other standard SQL code in it.
Well, there's MySQL unique stuff that people can (and do) use, which leads to the PITA you mention. (Or "vendor lock-in" as others call it.)
 
Old 09-10-2018, 05:27 PM   #7
abga
Member
 
Registered: Jul 2017
Location: EU
Distribution: Slackware
Posts: 721

Rep: Reputation: 404Reputation: 404Reputation: 404Reputation: 404Reputation: 404
@hedron
Following Darth Vader's suggestion, you should first consider substituting your REPLACE INTO statement with INSERT ON DUPLICATE KEY UPDATE
https://mariadb.com/kb/en/library/replace/
https://mariadb.com/kb/en/library/in...te-key-update/

Here are some references about this recommendation:
http://code.openark.org/blog/mysql/r...to-think-twice
https://stackoverflow.com/questions/...extremely-slow
And a practical example:
https://thomashunter.name/blog/mysql...te-key-update/

As for choosing the right DB engine, or storage engine, with MySQL/MariaDB, based on my previous post, I can advise you to:
- use MyISAM for when you just want to load some data and run some ad-hoc analysis on it, as fast as MySQL/MariaDB can do it.
- use InnoDB for when you need an actual database and care about your data, be ACID compliant, like to support transactions and some other extensions. This is definitely slower.
https://en.wikipedia.org/wiki/ACID
& again, you might want to consider PostgreSQL

@Richard Cranium
https://mariadb.com/kb/en/library/replace/
"REPLACE is a MariaDB/MySQL extension to the SQL standard." ... useless as it turns out, or as you say, just for the purpose of "vendor lock-in"

Last edited by abga; 09-10-2018 at 05:58 PM. Reason: eating letters
 
2 members found this post helpful.
Old 09-15-2018, 12:39 AM   #8
hedron
Member
 
Registered: Jul 2009
Location: NYC
Distribution: Slackware64-multilib 14.2, SARPI
Posts: 287

Original Poster
Rep: Reputation: 27
Quote:
Originally Posted by abga View Post
just for the purpose of "vendor lock-in"
I did not consider that. "REPLACE INTO" is very convenient, I didn't realize it was "unique" to MySQL/MariaDB. I will consider postegreSQL and try out "INSERT...DUPLICATE" even if it feels really awkward.

I'm really an SQL newb, and was actually considering SQLite, since what I'm doing isn't really the most demanding of tasks, and almost could be done with a spreadsheet, but the files would just be too big.
 
Old 09-15-2018, 01:07 AM   #9
Darth Vader
Senior Member
 
Registered: May 2008
Location: Romania
Distribution: DARKSTAR Linux 2008.1
Posts: 2,727

Rep: Reputation: 1231Reputation: 1231Reputation: 1231Reputation: 1231Reputation: 1231Reputation: 1231Reputation: 1231Reputation: 1231Reputation: 1231
Quote:
Originally Posted by hedron View Post
I did not consider that. "REPLACE INTO" is very convenient, I didn't realize it was "unique" to MySQL/MariaDB. I will consider postegreSQL and try out "INSERT...DUPLICATE" even if it feels really awkward.

I'm really an SQL newb, and was actually considering SQLite, since what I'm doing isn't really the most demanding of tasks, and almost could be done with a spreadsheet, but the files would just be too big.
Nope, is not something very convenient (appearances excluded), but a crap extremely prone for dubious errors.

Because it removes the old record if exists and insert always a new one. That will mess with the primary keys and will eventually produce many dubious errors.

For example, imagine a site where the user "admin" has the primary key "id" "1", and a field "realname"

Usually it is supposed that the IDs stay constant, and many sites use things like
Code:
if ($user->id === 1) {
    // This is the super-user, permit him a full access
}
BUT, with that goddamned "REPLACE INTO" when you update the "realname" as "John Doe", the record will be deleted and be inserted one with "id" 666 or whatever. And the site go nuts.

It is just like in Linux, as if when you update the root password, then its UID suddenly become 1755. That will be pretty pretty bad. It will not be root anymore, but some user with elevated access.

Long story short, the "REPLACE INTO" is not "a convenience" but right on another name for "big troubles".

Last edited by Darth Vader; 09-15-2018 at 03:24 AM.
 
2 members found this post helpful.
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Apache server extreme slow, while samba server is extreme fast! Geert86 Linux - Server 5 01-18-2010 12:07 AM
Intel extreme graphics 2 going slow systelm Linux - Hardware 2 05-06-2006 04:30 PM
iintel extreme graphics - how to edit xorg.conf slack 10.1 rkrishna Slackware 2 06-21-2005 09:15 AM
Extreme slow boot after updating to Mandrake 10.1 TommyB Mandriva 5 04-07-2005 05:53 PM
extreme lag and slow dowloads Chooco Linux - Networking 6 05-31-2002 09:56 PM

LinuxQuestions.org > Forums > Linux Forums > Linux - Distributions > Slackware

All times are GMT -5. The time now is 08:08 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration