PaaPsik

5y

PHP dev help/advice needed!
We have problems with mysql. Still stuck with mariaDB, I'm using indexes (correct ones) and we have problems with scaling. we have a few tables with over 100mil rows, 1 of them is being read every morning with a subselect that counts unique rows, and fails every time because of timeout/lock, the temp table size was increased and helped for a little while but as time goes on the table grows and the problem reappears. I'm reading from a slave server that was purposely created for read only, yet we still have problems. We're using managed dedicated servers for out hosting and they aren't willing to optimise the database configs for our needs. What are the easiest options for scaling at this point? Going fully dedicated server and perconaDB? NOsql? Sharding the server? Anyone got any good blogposts or something to read about this? your own experience?

question

nosql

sharding

php

mysql

perconadb

mysql optimisation

mariadb

Ranter

Comments

1

zotigapo

770

5y

Migrate your relational db to nosql.
And use cloud services for nosql,

Mongodb
Dynamodb
......

Obvisiously, hosting providers db is on the shared resource (same server).

So if one change effects other customer.
0

zotigapo

770

5y

If you want to work on shardings best work on
Elasticsearch
Cassandra
0

helloworld

9500

5y

https://aws.amazon.com/rds/mysql/
0

PaaPsik

1048

5y

@helloworld were afraid of amazon because if any fuckups happen and there are too many writes/reads where there shouldn't be - it'll cost an arm and a leg and a kidney. Or am I wrong here?
0

arraysstartat1

1083

5y

Depends on what you have in that table, what makes row unique and how it is changed you could partition and count rows only on latest partition.

I would also consider increasing timeout.
0

heyheni

21666

5y

did you consider switching to graphQL which doesn't query the whole database every time.
https://www.graphql.com/

Other than that look what AWS, Google or Microsoft has to offer.
https://aws.amazon.com/rds/
https://cloud.google.com/sql/
https://azure.microsoft.com/service...
1

arraysstartat1

1083

5y

@heyheni Pretty sure they are doing that query every morning, because it can have different result and they don't want to get yesterdays results.
0

PaaPsik

1048

5y

@zotigapo the hosting isn't shared, its dedicated but managed.
1

Pfannkuchensack

21

5y

Which databases engine is in use? Innodb or myisam?
1

PaaPsik

1048

5y

@Pfannkuchensack I replaced most tables to use inno as it doesn't have table locking
1

Pfannkuchensack

21

5y

Add a Table with the result of the subselect count. So you dont have to count on the big query.

But cant say if this is the right anwser, need some info about the SQL query

Related Rants

devRant © 2021 Hexical Labs LLC
Privacy Policy | Terms of Service