MySQL
I've been working with MySQL since 2005 and before that the Pick/D3 database since 1998 and I have brief experience with postgress, both installing and supporting the products.
I've used both MySQL and Pick/D3 in a High Availability solution (master-slave replication) for trading platforms.
I've also used MySQL slaves as a redundancy/backup solution (across continents) to guarantee data in the event of total failure at a one location (a backOffice trading application for a Financial Services provider and at a PoP for an ISP) the geographically remote would have an up to the second DR copy. This included constant monitoring of the solution to confirm it was working.
One of the lessons learned early on (from a Support Manager) was not to trust only the DBMS tools to know if a replicated solution was in-sync, but rather to establish one or more business relevant metrics and compare master and slave record sets on those.
- I have been involved in high performance requirements and I have ongoing experience in diagnosing and taking action where slow queries need attention.
- I've used the MySQL UDF library along with triggers and stored procedures to creatively solve a business problem that could not be resolve otherwise.
- I have used Views to colate and segment data to allow stricly permissioned SQL level access for end-users to run queries without risk.
- I have (controversially depending on your PoV) placed business logic in stored procedures to make business data more available to folk with SQL skills (so that these requests could be serviced without programmer time)
To get an idea of scale, here are the outputs of the status command a main and slave server:
Main
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 16014786 Server version: 5.0.77-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> status -------------- mysql Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (x86_64) using readline 5.1 Connection id: 16014786 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.0.77-log Source distribution Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: latin1 Conn. characterset: latin1 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 99 days 11 hours 25 sec Threads: 36 Questions: 19158580012 Slow queries: 9530 Opens: 27952745 Flush tables: 4 Open tables: 64 Queries per second avg: 2229.498 -------------- mysql>
Slave
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 557832 Server version: 5.0.77 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> status -------------- mysql Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (x86_64) using readline 5.1 Connection id: 557832 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.0.77 Source distribution Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: latin1 Conn. characterset: latin1 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 22 days 16 hours 7 min 48 sec Threads: 43 Questions: 2610068396 Slow queries: 748 Opens: 461544 Flush tables: 1 Open tables: 64 Queries per second avg: 1332.437 -------------- mysql>
Happy to work as an Employee, Consultant, Contractor / Self Employed or via Limited Company