upload on 2014.12 [ten important tips of MySQL database design for better performance] Download this PDF

Installing Apache2 With PHP5 And MySQL Support On Fedora 21 (LAMP)

LAMP is short for Linux, Apache, MySQL, PHP. This tutorial shows how you can install an Apache2 webserver on a Fedora 21 server with PHP5 support (mod_php) and MySQL support.

Installing Apache2 With PHP5 And MySQL Support On Fedora 21 (LAMP) LAMP is short for Linux, Apache, MySQL, PHP. This tutorial shows how you can install an Apache2 webserver on a Fedora 21 server with PHP5 support (mod_php) and MySQL support.

Installing Apache2 With PHP5 And MySQL Support On Fedora 21 (LAMP) LAMP is short for Linux, Apache, MySQL, PHP. This tutorial shows how you can install an Apache2 webserver on a Fedora 21 server with PHP5 support (mod_php) and MySQL support.

There are few easy ticks to see what is stuck inside MySQL instance. All these techniques will not give you whole picture, but might help to find queries that block performance. Let’s start from what exactly doing your MySQL server right now.

Which queries are running now?

This will give you an idea what’s running right now so you can find long running queries which slowing down MySQL and/or causing replication lag:

mysql -e "SHOW PROCESSLIST" | grep -v -i "sleep"

It is more convenient than just run “SHOW PROCESSLIST” as it’s hiding all connected threads in “Sleep” state so you’ll get a clean output. Also you can get same output but updating each second:

watch -n1 'mysql -e "SHOW FULL PROCESSLIST" | grep -v -i "Sleep"'

What to look for? This is complex output but you can start with Time and State columns. When you see a query running for more …

It's easy to load multiple rows of data from a file into a MySQL table with a single LOAD DATA INFILE command, but if the file is over a certain size you may want to load it in multiple chunks. When I have a file with millions of lines I typically use pt-fifo-split to separate the file into multiple chunks in order to limit the size of each transaction. Unlike most tools in Percona Toolkit, pt-fifo-split doesn't actually interact with your database, but this is one MySQL use case where it's very helpful.

Here's a quick example in bash that illustrates how I can take a large tab-delimited file, break it into multiple 100,000 line chunks, and load the chunks into a table:

``` FLAT_FILE="/tmp/big_file.txt" …

This Log Buffer Edition goes right through the fields of salient database blog posts and comes out with something worth reading.

Oracle:

Extract SQL full text from SQL Monitor html.

Disruption: Are Hot Brands Breaking the Rules?

Understanding Flash: Unpredictable Write Performance.

The caveats of running .sql scripts with GUI tools.

…

Dear Perl and MySQL community,

I'm pleased to announce the release of DBD::mysql 4.029

From the changelog:

• Added fix to tests to create test database if not exists (contstant failure on Travis) (CaptTofu)
• Support the fraction of (Oracle) MySQL Fabric that is supported by the most recent Connector/C (Steffen Mueller smuellercpanorg
• Statistics Info Milan Šorm is4ucz> for work on statistics_info
• Fix for RT 97625, use after free(), Reini Urban rurbancpanorg and Giovanni Bechis bigiosnbit>

Thanks to all who contributed!

Patrick "CaptTofu" Galbraith

Please feel free to visit:

CPAN DBD-mysql-4.029

And as always:

Github repo

When I talk about troubleshooting I like to repeat: "Don't grant database access to everybody!" This can sound a bit weird having one can give very limited read-only access.

But only if ignore the fact what even minimal privileges in MySQL allows to change session variables, including those which control server resources. My favorite example is "Kill MySQL server with join_buffer_size". But before version 5.7 I could only recommend this, but not demonstrate. Now, with help of memory summary tables in Performance Schema, I can show how unprivileged user can let your server to use great amount of swap.

At first lets create a user account with minimal privileges and login.

$../client/mysql -ulj -h127.0.0.1 -P13001
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.6-m16-debug-log Source distribution

…

Here is a list of my most useful tools that I use when doing performance audits.
Please note, I am writing this mainly for myself, because I sometimes end up trying to find them in my other blog post about mastering indexing and this may save me time as well as a few changes that have happened over the years.
Regular Slow Log Report pt-query-digest slow_query.log  >slow.txt
All Queries (that use indexes) for a certain table pt-query-digest slow_query.log  --filter '($event->{fingerprint} =~ m/^(!?select|update|delete)/) &&  ($event->{arg} =~ m/mytable /) ' --limit=100% >mytable.txt
Longest Running Select Queries - most painful queries with response time % right next to them. pt-query-digest slow_query.log  --filter '($event->{fingerprint} =~ …