 |
|
|
|
PostgreSQL vs MySQL
PostgreSQL vs MySQL Современный блогохостинг – какой он? Какую СУБД выбрать, как спроектировать схему данных и где узкие места при использовании той или иной СУБД? Протестируем производительность СУБД MySQL и PostgreSQL на примере блогохостинга. О сравнении производительности MySQL и PostgreSQL написана не одна статья, и всезнающий Google выдает больше миллиона документов, посвященных этой теме. Однако, все тесты, которые доступны в Интернете, – это работа СУБД на синтетических запросах, имеющих малое отношение к конкретным проектам. Мы же тестируем пригодность СУБД для реализации конкретного проекта, а именно – блогохостинга с большим количеством активных пользователей, которые не только ведут свои дневники, пишут и читают комментарии, но и читают записи своих друзей.
Схема БД Сервер и настройки системы Сервер – HP ProLiant DL380 2xXeon3.2, RAM 5 Гб, SCSI HDD u320. Операционная система – Linux Debian, kernel 2.6.18.
Установленное ПО:
К сожалению, настройки как MySQL, так и PostgreSQL достаточно сложны, и предоставить их в этой статье вместе с достойным описанием не позволяет объем. Поэтому ограничусь лишь утверждением, что оба сервера были настроены на максимальную производительность, а приведенные здесь результаты тестов – лучшие из полученных. MySQL: # InnoDB sync_binlog = 0 innodb_buffer_pool_size = 2048M innodb_additional_mem_pool_size = 64M innodb_log_files_in_group = 2 innodb_log_file_size = 512M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 0 # MyISAM key_buffer = 1024M table_cache = 1024 sort_buffer_size = 16M read_buffer_size = 4M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 4M query_cache_size = 16M PostgreSQL: shared_buffers = 500000 temp_buffers = 4000 max_prepared_transactions = 0 work_mem = 128000 maintenance_work_mem = 256000 max_stack_depth = 8192 max_fsm_pages = 200000 max_fsm_relations = 10000 max_files_per_process = 1000 stats_start_collector = on stats_command_string = off stats_row_level = on autovacuum = on Схема БД представлена на рисунке. Для PostgreSQL: CREATE TABLE users ( user_id bigint NOT NULL, user_name character varying(100) NOT NULL, CONSTRAINT users_pk PRIMARY KEY (user_id) ) WITHOUT OIDS; CREATE UNIQUE INDEX users_user_name_u ON users USING btree (user_name); CREATE TABLE friends ( user_id bigint NOT NULL, friend_id bigint NOT NULL, CONSTRAINT friends_pk PRIMARY KEY (user_id, friend_id), CONSTRAINT user_id_fk1 FOREIGN KEY (user_id) REFERENCES users (user_id), CONSTRAINT user_id_fk2 FOREIGN KEY (friend_id) REFERENCES users (user_id) ) WITHOUT OIDS; CREATE TABLE posts ( user_id bigint NOT NULL, post_id bigint NOT NULL, post_date bigint NOT NULL, post_title character varying(256) NOT NULL, post_body text, CONSTRAINT posts_pk PRIMARY KEY (user_id, posts_id), CONSTRAINT user_id_fk3 FOREIGN KEY (user_id) REFERENCES users (user_id) ) WITHOUT OIDS; CREATE TABLE comments ( user_id bigint NOT NULL, posts_id bigint NOT NULL, comments_id bigint NOT NULL, from_user_id bigint NOT NULL, comment_date bigint NOT NULL, comment_title character varying(256) NOT NULL, comment_body text, CONSTRAINT comments_pk PRIMARY KEY (user_id, posts_id, comments_id), CONSTRAINT user_posts_id_fk1 FOREIGN KEY (user_id, posts_id) REFERENCES posts (user_id, posts_id), CONSTRAINT from_user_id_fk2 FOREIGN KEY (user_id) REFERENCES users (user_id) ) WITHOUT OIDS; Для MySQL: DROP TABLE IF EXISTS users; CREATE TABLE users ( user_id int(10) NOT NULL auto_increment, user_name varchar(100) NOT NULL, PRIMARY KEY (user_id) ) ENGINE=InnoDB; DROP TABLE IF EXISTS friends; CREATE TABLE friends ( user_id int(10) NOT NULL, friend_id int(10) NOT NULL, PRIMARY KEY (user_id, friend_id) ) ENGINE=InnoDB; DROP TABLE IF EXISTS posts; CREATE TABLE posts ( user_id int(10) NOT NULL, post_id int(10) NOT NULL, post_date int(10) NOT NULL, post_title varchar(100) NOT NULL, post_body text NOT NULL, PRIMARY KEY (user_id, post_id) ) ENGINE=InnoDB; DROP TABLE IF EXISTS comments; CREATE TABLE post_cmt ( user_id int(10) NOT NULL, post_id int(10) NOT NULL, comment_id int(10) NOT NULL, from_user_id int(10) NOT NULL, comment_date int(10) NOT NULL, comment_title varchar(250) NOT NULL, comment_body text NOT NULL, PRIMARY KEY (user_id, post_id, comment_id) ) ENGINE=InnoDB; Для тестов с ENGINE=MyISAM отличий в структуре БД нет. Все дампы схем доступны по адресу: http://www.reki.ru/products/blogoservice. Методика тестирования Распредение количества запросов на чтение и запись было выбрано в отношении 80% и 20% соответственно. После запуска на тестирование производилась выдержка в течение нескольких десятков секунд для стабилизации результатов. На каждую СУБД производилось пять тестов, результат усреднялся. Тестировались следующие запросы (в процентах от общего числа):
Эти четыре типа запросов дают практически полную эмуляцию работы блогосервиса. Для PostgreSQL запросы выглядят так: Чтение френдленты: SELECT user_id, MAX(posts_id) FROM posts WHERE user_id IN (SELECT friend_id FROM friends WHERE user_id = UserId) GROUP BY user_id; Чтение постов и комментариев: SELECT * FROM posts WHERE (posts_id = PostId); SELECT * FROM comments WHERE ((user_id = UserId) AND (posts_id = PostId)) ORDER BY comment_date; Запись поста: SELECT NewPost(UserId, Date, Title, Body); Запись комментария: SELECT NewComment(UserId, Date, Title, Body); Создание пользователя: SELECT NewUser(UserName) Редактирование свойств пользователя: SELECT ModifyUser(UserId, UserName); Для MySQL: Запрос чтение ленты. Общий вид запроса следующий: SELECT user_id, MAX(posts_id) FROM posts WHERE user_id IN (SELECT friend_id FROM friends WHERE user_id = UserId) GROUP BY user_id; Для максимально оптимальной выборки необходимо на внутренний запрос подключить индекс по первичному ключу таблицы friends, а для внешнего запроса – первичный ключ по таблице posts. Однако, поскольку MySQL всех версий не умеет адекватно оптимизировать внешний запрос вне зависимости от созданных индексов и их явного указния через FORCE(key list), разбиваем сложный запрос на два простых: в первом запросе получаем список пользователей, а во втором – выбираем посты. UsersSet = SELECT friend_id FROM friends WHERE user_id = UserId и SELECT user_id, MAX(post_id) FROM posts WHERE user_id in (UsersSet) GROUP BY user_id Я не утверждаю, что MySQL 5 не умеет делать подзапросы. Утверждается, что скорость работы двух простых запросов выше скорости работы одного сложного ввиду специфических особенностей работы оптимизатора запросов. Чтение постов и комментариев: SELECT * FROM posts WHERE (posts_id = PostId); SELECT * FROM comments WHERE ((user_id = UserId) AND (posts_id = PostId)) ORDER BY comment_date; Запись поста: PostID = SELECT MAX(post_id) FROM posts; INSERT IGNORE INTO posts (user_id, post_id, post_date, post_title, post_body) VALUES (UserID, PostID, Date, Title, Body); Запись комментария: SELECT COUNT(*) FROM users WHERE (user_id = iUserId); SELECT COUNT(*) FROM users WHERE (user_id = iPosterId); SELECT COUNT(*) FROM posts WHERE (post_id = iPostId); CommentId = SELECT MAX(comment_id) FROM commnets; INSERT IGNORE INTO \ comments (user_id, posts_id, comment_id, from_user_id, comment_date, comment_title, comment_body) \ VALUES (UserId, PostId, CommentId, PosterId, Date, Title, Body);</pre> Создание пользователя: SELECT COUNT(1) FROM users WHERE user_name = UserName; INSERT INTO users(user_name) VALUES (UserName); GET_LAST_INSERT_ID Редактирование свойств пользователя: SELECT COUNT(1) FROM users WHERE user_id = UserId; UPDATE users SET user_name = UserName WHERE user_id = UserId; Как мы можем видеть, алгоритмы работы с БД оптимизированны как под MySQL, так и под PostgreSQL. Важно отметить, что не существует одинаковых архитектурных решений для этих двух СУБД. В каждом случае требуется индивидуальный подход как в проектировании структуры базы данных, так и в реализации механизма вставок и выборок данных. Именно поэтому в высоконагруженных проектах недопустимо использование «универсальных» фреймворков для БД, поскольку ни один из них не учитывает архитектуру проекта, в котором используется, а большинство их них не учитывает даже порядка использования индексов в СУБД. Поговорим про оптимизацию кода. Разумеется, количество «служебных» запросов (проверок наличия или отсутствия пользователя, комментариев и т. п.) можно сократить, введя, например, users_id как хэшированное имя пользователя или разместив в памяти хэштаблицу ID и имен пользователей. Однако здесь мы тестируем производительность именно СУБД, а не всего программного комплекса в целом. Результаты Я сознательно не измерял время подключения к БД, а использовал пул заранее созданных подключений. На наш взгляд, отсутствие пула и постоянные переподключения к базе у высоконагруженного проекта – уже показатель его неправильной архитектуры. Результаты тестирования СУБД MySQL и PostgreSQL в зависимости от количества подключений
Выводы Вся описанная в литературе хваленая производительность MySQL достигается только на примитивных запросах на чтение при отсутствии записи в таблицу. Как только требуется пересечь в выборке несколько таблиц, под нагрузкой на запись, MySQL проигрывает PostgreSQL в разы. Поэтому ниша MySQL – проекты, не требующие какой-либо ссылочной целостности и бизнес-логики внури базы данных и структурой, в которой возможны только простые выборки без сложных пересечений. Даже в проекте типа блогосервиса, MySQL в разы уступает в производительности PostgreSQL, а при достаточно большом количестве одновременных выборок (больше 20) происходит отказ от обслуживания. Разумеется, не существует «серебряной пули», которая могла бы убить любого зверя. В любом и каждом проекте следует тестировать применимость той или иной технологии и ни в коем случае нельзя при выборе СУБД полагаться на предыдущий «опыт» других разработчиков, ведь вполне может оказаться что их выбор – не более чем дань традиционному заблуждению. Удачи! |
Андрей Шетухин
| Добавить комментарий |
|
Комментарии могут оставлять только зарегистрированные пользователи |
|
