Feb 14
Вот тут Admin Zone Forums - MySQL Server Tweaking Basic обсуждают настройку MySQL, сохраню ка, я это себе, там есть что почитать.
А вот тут приводят волешбный конфиг, его тоже надо изучить
Смотреть конфиг
Feb 14
А здесь продают не плохую вещь для постоянного мониторинга MySQL instance
MONyog - Single Server - $218 CAD
Попробовал - хорошая штука. Свою такую за $218 не написать конечно.
Ещё пробовал перловые innotop-1.6.0 и mytop-1.6
Как говорится - “… жалкое подобие левой руки”, зато бесплатно.
Feb 14
Вот здесь есть некая волшебная штука - SQL Relay
Обещают возможность соединения с множеством MySQL серверов
The good news is that SQL Relay’s performance here is comparable to PHP’s persistent connections. The reason is because SQL Relay has already established X number of Oracle (or MySQL) connections and strives to maintain that number continually. Then, your PHP app connects and disconnects from the SQL Relay server, which is lightweight.
Getting everything to run smoothly does take a fair bit of tuning and careful management of your SQL Relay connections. If you have leaky code that doesn’t disconnect properly, you can still flood your connection pool, no pun intended. Wish I had benchmarks but, the main thing is that SQL Relay offers similar performance and won’t flood your database with connections, both of which are good things.
Выглядит заменчиво, надо посмотреть - вероятно это дешевая альтернатива domain data separation при больших нагрузках
Feb 02
На тему покупки Sun-ом MySQL AB, встретил интересный комментарий:
- Теперь вместо LAMP будет LAPP, только и всего 
Dec 15
MySQL 5.0
mysql-connector-java-5.0.4-bin.jar
166 Mb dump file with 1 704 067 rows
|
Method
|
Time
|
|
Strait insert, one row at a time
|
6:15 min
|
|
Same insert but with prepared statement
|
6:42 min
|
|
Batched insert (10 at a time)
statement.addBatch()
…
statement.executeBatch()
|
6:20 min
|
|
Select into … values (…),(…)….
|
1:56 min
|
|
Bulk insert (100 at a time)
|
1:13 min
|
|
Bulk insert (1000 at a time)
|
1:05 min
|
Dec 11
+ 400% performance increase in comparison with packed MyIsam (according to Livejournal people)
+ 20% less storage space in comparison with packed MyIsam
+ with really big tables > 1.5Gb table scan on ARCHIVE beats MyIsam
- no indexes whatsoever, not even primary keys
- MERGE can not be used
Dec 11
+ Any engine type
+ each host can have average sized disks
+ access over the network
- more memory it total as there are many DB instances
Dec 11
Application: one host with a huge disk space.
+ less memory as one DB instance serves all
- huge disk is a must, although we can map drives and use symlinks
- local access to db data files only (no network)
* Works only with regular/packed MyIsam DB i.e. each time there is a new chunk of data we have to stop DB and to the maintenance chores - dump/import/pack
* It is possible to merge readonly and regular tables
* Select with a key type search returns single record even if there were a few others with the same key. Select with a “like” type search (table scan) returns all of them.
Nov 24
В общей сложности, убил 8 часов на борьбу с юникодом, в Java, Ant, MySQL JDBC, MySQL4, MySQL5. Поборол в конце концов. Но что забавно - всё началось с юзера под названием “Pruszcz Gdański”.
Шлю тебе, неизвестный Принц из Гданська, горячий привет! Pruszcz Gdański
Mar 24
How to make MySQL blazingly fast
1) Increase innodb_buffer_pool_size to 80% of memory of dedicated host. At least give it 265M. Here under is a snippet from mysql.ini
set-variable=innodb_buffer_pool_size=256M
set-variable=innodb_additional_mem_pool_size=20M
Without any further optimization it gives you 50% performance gain (against old setting of 32Mb)
2) Increase innodb_log_file_size to 25% of innodb_buffer_pool_size
set-variable=innodb_log_file_size=64M
set-variable=innodb_log_buffer_size=8M
Packaged with optimization above it can shave you up to additional 10%
3) Make sure that for read-only transactions your switched ON auto commit setting. And then switched it back for read-write tasks. Set read-only settings properly to JDBC statement as well.
Here you will see drastic improvement if you treated read-only transaction wrongly i.e. committed and rolled back them yourself from DAO
4) Make sure that you execute operation with DB concurrently. I was able to get 30% improvement when running 8 threads. There is a catch here though – by default internally MySQL holds 8 threads to serve DB requests. Make number of threads here and there consistent.
5) Change isolation level to READ-UNCOMMITTED if it possible (default for InnoDB is REPEATABLE READ)
set-variable=transaction-isolation=READ-UNCOMMITTED
My tests show that gain here is almost negligible around 4%
