Creating tables is perhaps the most popular task in database development. In this article, we will review the ways of creating MySQL tables. There are two main ways of creating tables in MySQL databases: Executing a query that includes the CREATE TABLE statement Using the corresponding functionality of MySQL-related tools and IDEs The first approach […]
The post How to Create a Table in MySQL appeared first on Devart Blog.
Introduction This is the fifth in a series of blogs that detail how to setup and use a LAMP Stack that makes use of MySQL HeatWave in the Oracle Cloud. Part 1 will set the scene by detailing what a LAMP stack is and what deployment options are available in the Oracle Cloud. It will […]
Recently, somebody asked me how he can find the long running transactions in MySQL.
I already have one MySQL Shell plugin that allows you to find the
current transactions sorted by time. The plugin allows you to
also get the details about the desired transaction. See
check.getRunningStatements().
Let’s see how we can easily find those long transaction that can be a nightmare for the DBAs (see MySQL History List Length post).
SELECT thr.processlist_id AS mysql_thread_id,
concat(PROCESSLIST_USER,'@',PROCESSLIST_HOST) User,
Command,
FORMAT_PICO_TIME(trx.timer_wait) AS trx_duration,
current_statement as `latest_statement`
FROM …[Read more]
We often see an int column of a table that needs to be changed to unsigned-int and then unsigned-bigint due to the value being out of range. Sometimes, there may even be blockers that prevent us from directly altering the table or applying pt-online-schema-change on the primary, which requires the rotation solution: apply the change on the replica first, switch over the writes to the replica, and then apply the change on the previous primary. In this case, MySQL will have to replicate unsigned-int to unsigned-bigint for a while.
One might think it is obvious and straightforward that MySQL should be able to replicate unsigned-int to unsigned-bigint because unsigned-bigint has a larger size(8 bytes) which covers unsigned-int(4 bytes). It is partly true, but there are some tricks in practice. This blog will show you those tricks through the scenarios.
Let’s understand the scenarios and issues that one may face when replicating from …
[Read more]Welcome to this week’s OpenLampTech newsletter, the newsletter for MySQL and PHP developers. There is always something to learn and share from all the curated sources out there and OpenLampTech has the MySQL, PHP, and LAMP Stack content covered. Thank you for reading!
The Newsletter for PHP and MySQL Developers
Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.
In OpenLampTech issue #42 we have articles covering:
This tutorial help to create advanced search functionality with PHP and Mysql. We’ll create a PHP form that takes input and search into the MySQL table. Advanced search provides more options to the end user to filter the search result. Steps to implement advanced search PHP mysqli Create a MySQL database and populate it with […]
The post Advanced search with PHP and MySQL appeared first on Phpflow.com.
OH:
“And now let’s quickly push 2 billion rows into this database VM.”
That is best done in YOLO mode. This is a mode of operation for a database that minimizes disk writes in favor of batched bulk writes.
It is not ACID, so if anything goes wrong during the load, the instance is lost. That is why it is called YOLO mode.
You are supposed to do this on a spare replica and not the production primary. If you are not having at least one more replica than needed in your MySQL deployment, I consider your setup defective.
set global innodb_flush_log_at_trx_commit = 2;
gives up ACID commits: On COMMIT, …
[Read more]Knowing your database can scale provides great peace of mind. We built PlanetScale on top of Vitess so that we could harness its ability to massively scale. One of the core strengths in our ability to scale is horizontal sharding. To demonstrate the power of horizontal sharding, we decided to run some benchmarking. We set up a PlanetScale database and started running some benchmarks with a common tpc-c sysbench workload. We weren’t aiming for a rigorous academic benchmark here, but we wanted to use a well-known and realistic workload. We will have more benchmark posts coming and have partnered with an academic institution who will be releasing their work soon. For this post, there are two goals. The first is to demonstrate PlanetScale’s ability to handle large query volumes. For this, we set a goal of a million queries per second. In Vitess terms, this is not a large cluster. There are many Vitess clusters running at much higher query volumes, …
[Read more]Discover how PlanetScale handles one million queries per second (QPS) with horizontal sharding in MySQL
You may have read MyRocks Use Case: Big Dataset and been intrigued enough to want to evaluate RocksDB with InnoDB. It has several advantages including requiring less storage space.
So how do you start?
On a fresh install of Percona Server for MySQL, install RocksDB by entering sudo apt install percona-server-rocksdb:
percona@DellXPS:~$ sudo apt install percona-server-rocksdb [sudo] password for percona: Reading package lists... Done Building dependency tree Reading state information... Done The following NEW packages will be installed: percona-server-rocksdb 0 upgraded, 1 newly installed, 0 to remove and 32 not upgraded. Need to get 65.3 MB of archives. After this operation, 292 MB of additional disk space will be used. Get:1 …[Read more]
