Использование My SQL в разработке Java-приложений Александр Ноздрин

Использование My. SQL в разработке Java-приложений Александр Ноздрин Software Developer Server Runtime team 07. 02. 2008 My. SQL AB Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database 1

Содержание • Обзор Connector/J – Общие слова, версии – Общие проблемы – Примеры использования – Отладка & Instrumentation – Приемы эффективного использования • Производительность Java-решений – Производительность и масштабируемость – Архитектурные проблемы – Масштабируемость сервера БД • Как оптимизировать производительность – Методология Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database 2

Обратная связь! Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database

Connector/J: Agenda • Что такое Connector/J, какие есть версии • Общие проблемы и методы их решения • Примеры использования – Выполнение запросов – Получение значений AUTO_INCREMENT столбца – Работа с хранимыми процедурами, prepared statements • Отладка • Instrumentation – Slow queries – Отслеживание производительности – Usage Advisor • Приемы эффективной работы Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database 4

Connector/J: обзор • Обеспечивает подключение для клиентских приложений • JDBC Type 4 driver (pure-Java, no My. SQL client libraries) Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database 5

Connector/J: версии • Connector/J 5. 1 – Type IV; совместимость с My. SQL 4. 1, 5. 0, 5. 1, 6. 0 • Connector/J 5. 0 – Connector/J 3. 1 + распределенные транзакции • Connector/J 3. 1 – работает с My. SQL 4. 1 и 5. 0 • Connector/J 3. 0 – базовая версия; My. SQL 3. x, 4. 1 Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database 6

Общие проблемы • “Server configuration denies access to data source” – No UNIX Domain Sockets in Java – Connector/J использует TCP/IP – My. SQL сервер должен быть запущен с поддержкой TCP/IP – GRANTs • Сервер закрывает соединение через 8 часов бездействия – “ручное” переподключение – свойство “auto. Reconnect” • не восстанавливает состояние подключения • не рекомендуется, deprecated Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database 7

Общие проблемы (2) • “Result set is not updatable” exception – В My. SQL нет понятия row id – Можно обновлять только те result sets, которые были получены путем запроса таблиц с первичным ключом – No joins • FLOAT in Primary Key – Не получается обновить строку – Connector/J добавляет WHERE-клаузу – Проблемы с округлением – Лучше использовать DOUBLE, DECIMAL Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database 8

Примеры использования • Получение соединения Connection c = Driver. Manager. get. Connection( “jdbc: mysql: //localhost/test? user=xxx&password=xxx”); • Выполнение запросов Statement s = c. create. Statement(); Result. Set rs = s. execute. Query(“SELECT. . . ”); int num_rows = s. execute. Update(“UPDATE | DELETE | INSERT. . . ”); bool select_query = s. execute(“SELECT | UPDATE | DELETE | INSERT. . . ”); Result. Set rs = s. get. Result. Set(); int num_rows = s. get. Update. Count(); Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database 9

Примеры использования Получение значения AUTO_INCREMENT столбца CREATE TABLE t 1(c 1 INT AUTO_INCREMENT, c 2 VARCHAR(255)); • Statement. get. Generated. Keys() – JDBC API 3. 0, portable Statement s = c. create. Statement( java. sql. Result. Set. TYPE_FORWARD_ONLY, java. sql. Result. Set. CONCUR_UPDATABLE); s. execute. Update( “INSERT INTO t 1(c 2) VALUES(“hello, world”), Statement. RETURN_GENERATED_KEYS); Result. Set rs = s. get. Generated. Keys(); rs. next(); // Error if false int auto. Gen. Id = rs. get. Int(1); rs. close(); • SELECT LAST_INSERT_ID() – My. SQL specific Statement s = c. create. Statement(); s. execute. Update(. . . ); Result. Set rs = s. execute. Query(“SELECT LAST_INSERT_ID()”); Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database 10

Примеры использования Получение значения AUTO_INCREMENT столбца • Updatable result set – insert. Row() Statement s = c. create. Statement( java. sql. Result. Set. TYPE_FORWARD_ONLY, java. sql. Result. Set. CONCUR_UPDATABLE); Result. Set rs = s. execute. Query(“SELECT c 1, c 2 FROM t 1”); rs. move. To. Insert. Row(); rs. update. Int(“c 2”, 1234); rs. insert. Row(); rs. last(); int auto. Gen. Id = rs. get. Int(“c 1”); rs. close(); Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database 11

Примеры использования хранимые процедуры • Использование хранимых процедур CREATE PROCEDURE p 1(v 1 INT, INOUT v 2 INT, OUT v 3 INT) BEGIN. . . END • Подготовка callable statement Callable. Statement cs = c. prepare. Call(“{call p 1(? , ? )}”); • Регистрация INOUT / OUT параметров cs. register. Out. Parameter(2, Types. INTEGER); // by Index cs. register. Out. Parameter(“v 3”, Types. INTEGER); // by Name • Задание значений IN / INOUT параметров cs. set. Int(1, 1234); // by Index cs. set. Int(“v 2”, 4321); // by Name Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database 12

Примеры использования хранимые процедуры • Вызов процедуры boolean has_results = cs. execute(); • Получение значений INOUT / OUT параметров int v 2 = cs. get. Int(2); // by Index int v 3 = cs. get. Int(“v 3”); // by Name • Получение result set while (has_results) { Result. Set rs = cs. get. Result. Set(); . . . has_results = cs. get. More. Results(); } Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database 13

Отладка • logger Имя класса, который реализует com. mysql. jdbc. log. Log. По умолчанию: дамп в STDERR. • dump. Metadata. On. Column. Not. Found [false] Позволяет вывести в лог информацию о столбцах когда Result. Set. find. Column() возвращает ошибку. • dump. Queries. On. Exception [false] Позволяет получить запрос, который вызывал исключение, в сообщении SQLException. Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database 14

Instrumentation • Выводит в лог медленные запросы • Выдает аггрегированные показатели производительности • Выдает советы по лучшему использованию (usage advisor) Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database 15

Instrumentation медленные запросы • slow. Query. Threshold. Millis(Nanos) [20000] • auto. Slow. Log [true] Драйвер поддерживает статистику и дампит запросы превышающие 99% • explain. Slow. Queries [false] • log. Slow. Queries [false] Wed Apr 14 05: 38: 02 PDT 2004 WARN: at testsuite. simple. Connection. Test. test. Savepoint (Connection. Test. java: 376) Slow query (exceeded 50 ms): DROP TABLE IF EXISTS test. Savepoints Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database 16

Instrumentation Performance Monitor • gather. Perf. Metrics [false] • report. Metrics. Interval. Millis [30000] • profile. SQL [false] Wed Apr 14 05: 26: 02 PDT 2004 INFO: at testsuite. Base. Test. Case. tear. Down(Base. Test. Case. java: 186) ** Performance Metrics Report ** Longest reported query: 200 ms Shortest reported query: 1 ms Average query execution time: 38. 33333336 ms Number of queries executed: 12 Number of queries prepared: 0 Number of prepared statement executions: 0 Histogram: Queries taking between 1 and 13: 6 Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database 17

Instrumentation Usage Advisor • Печатает в лог “советы по использованию” – забытые объекты – слишком много строк в result set – неиспользуемые столбцы в result set – запрос значений столбца с использованием неправильного типа данных – неполный обход возвращенного result set • Properties – use. Usage. Advisor [false] – result. Set. Size. Threshold [100] Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database 18

Instrumentation Usage Advisor Wed Apr 14 05: 22: 28 PDT 2004 WARN: Profiler Event: [WARN] at testsuite. simple. Metadata. Test. test. Foreign. Keys (Metadata. Test. java: 88) duration: 0 ms, connection-id: 0, statement-id: 112, resultset-id: 138, message: Result. Set implicitly closed by driver. Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database 19

Приемы эффективной работы • Использовать последний драйвер – Собственно улучшается производительность драйвера – Драйвер лучше использует возможности сервера • Использовать Result. Set. get. XXX(index) вместо get. XXX(name) • Не запрашивать Updatable (Scrollable) Result. Set когда это не нужно • Освобождать ресурсы ASAP • Использовать Statement. get. Generated. Keys() для получения значений AUTO_INCREMENT-столбцов Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database 20

Тюнинг Connector/J • Callable statements cache – cache. Callable. Stmts [false] – callable. Stmt. Cache. Size [100] • Prepared statements – cache. Prep. Stmts [false] – prep. Stmt. Cache. Size [25] – prep. Stmt. Cache. Sql. Limit [256] – Server-side prepared statements • Meta-data cache – cache. Result. Set. Metadata [false] – metadata. Cache. Size [50] • Кроме того: – load balancer – query timeouts – memory Copyright 2007 My. SQL AB / speed trade-ofs The World’s Most Popular Open Source Database 21

Connector/J • Рекомендуется версия Connector/J 5. 1 • Pure-Java driver • Имеется ряд расширений для отладки и оптимизации • Может выдавать "советы" по использованию • Большие возможности настройки • Зависит от версии сервера Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database 22

Производительность Java-приложений • Производительность и масштабируемость • Проблемы архитектуры • Выбор способа доступа к данным – Низко-уровневой: JDBC-драйвер – ORM (Hibernate) – JDBC Frameworks (Spring) • Масштабируемость сервера БД – Connection Pool – Replication – Partitioning Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database 23

Performance vs Scalability • Производительность – как быстро происходит одна "бизнес"-транзакция • Масштабируемость – как хорошо система справляется с ростом нагрузки в терминах продолжительности работы без сбоев с необходимой производительностью • Вообще говоря можно иметь высокую производительность без возможности масштабирования • Обычно не возможно иметь возможность масштабирования без производительности Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database 24

Увеличение производительности • Профилирование приложения – Memory allocation issues: fragmentation, gc, . . . – Планирование с учетом I/O – Планирование с учетом мультизадачности – Подбор алгоритмов • SQL Запросы / Индексы – EXPLAIN – Изменение запросов – Изменение схемы БД – Добавление индексов Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database 25

Поддержка масштабируемости • В б. Ольшей степени это черта архитектуры • Нужно продумывать сначала • Тяжело меняется в середине цикла Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database 26

Некоторые архитектурные проблемы • Черезмерная нормализация • Real-time целостность, когда это не требуется • Пессимистичные блокировки SELECT. . . FOR UPDATE вместо UPDATE. . . WHERE id = 12 AND version = 34 • Бизнес-логика и/или данные в коде представления вместо MVC – Меньше возможности добавления кеширования – Более вероятно, что управление соединениями не централизовано Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database 27

Способ доступа к данным • Простой JDBC • ORM – Hibernate • JDBC Framework

Масштабирование сервера БД • Типичный пример веб-приложения • Один сервер БД • Один или несколько WEB/Application серверов • Варианты решения проблемы: – Replication – Partitioning Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database 32

Репликация • Replication – добавление slaves только для чтения – Slave должен быть таким же производительным как и Master – Обычно Slave должен быть гораздо более производительным, чем Master • Master создает binlog из нескольких потоков • Slave читает в одном – "Приукрашивает" реальные возможности системы • Сбой нескольких Slaves может вызвать каскадную деградацию системы Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database 33

Partitioning • Partitioning – разделение нагрузки на БД на ряд серверов • Вводится некоторое "правило", определяющее разделение данных (=> куда посылать запросы) • Может дать "линейное" увеличение производительности • Затрудняет некоторые вещи: – Cross-partitioned data – Миграция данных • Нужно планировать сразу (сделать потом существенно сложнее) Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database 34

Виды разбиения данных • Разбиение данных по диапазону • Использование хеш-функции • Использование словаря

Range Partitioning Application Server Pool Copyright 2007 My. SQL AB The World’s Most Popular

Range Partitioning – Pros / Cons • Не требует поддержания общего каталога • Тяжело изменить правила разбиения (диапазоны) после внедрения – Надо учесть, что не всегда возможно контролировать распределение (пример: имена пользователей) • Имеет наибольший смысл, когда есть всего два диапазона (старые / новые данные) • В общем случае не рекомендуется Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database 37

Hash Partitioning Application Server Pool Hash(partition-key) MOD “N” – in this case 4 Copyright

Hash Partitioning – Pros / Cons • Лучше Range Partitioning • Простой в реализации • Распределение тем не менее может оказаться несбалансированным • Очень тяжело переносить данные Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database 39

Global Dictionary Partitioning Bubba=1, Johnny=2… Memcached Application Server Pool Dictionary 1 Copyright 2007 My.

Global Dict Partitioning – Pros / Cons • Наиболее гибкий – Перенос данных – без проблем – Добавление нового железа – без проблем • Сложнее во внедрении – Необходим кеш для лучшей производительности – Кеш должен быть построен как write-through – Возможно имеет смысл заполнять кеш на этапе взлета приложения – Кеш должен быть HA Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database 41

Производительность Java-приложений Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database

Как оптимизировать производительность? • Методология • Какой масштаб бедствия? Copyright 2007 My. SQL AB

Методология оптимизации • Тестировать рано, тестировать часто – Во время разработки обычно приложение тестируется под малой нагрузкой. • Выделить отдельный testing environment • Использовать production-data • Ничего не делать руками – всегда создавать скрипты • Тест должен быть повторяемым • Менять только одну вещь за раз • Протолировать все изменения и результаты • За статистику надо платить Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database 44

Масштаб бедствия? Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database

Low Volume / Complexity Tuning Only • Добавление, оптимизация индексов • Тюнинг параметров My. SQL сервера • Оптимизация запросов • Увеличение размера "кучи" для JVM Copyright 2007 My. SQL AB The World’s Most Popular Open Source Database 46