search

tetsuzawa / isucon9-qualify-suburi-20230114

0 stars 0 forks source link

最初にチェックするリスト #1

Open tetsuzawa opened 1 year ago

tetsuzawa commented 1 year ago
soudai commented 1 year ago 
$  cat /etc/os-release
NAME="Ubuntu"
VERSION="18.04.5 LTS (Bionic Beaver)"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 18.04.5 LTS"
VERSION_ID="18.04"
HOME_URL="https://www.ubuntu.com/"
SUPPORT_URL="https://help.ubuntu.com/"
BUG_REPORT_URL="https://bugs.launchpad.net/ubuntu/"
PRIVACY_POLICY_URL="https://www.ubuntu.com/legal/terms-and-policies/privacy-policy"
VERSION_CODENAME=bionic
UBUNTU_CODENAME=bionic
soudai commented 1 year ago

3台構成、すべてOSは同じ

soudai commented 1 year ago

isu_a

Ubuntu 18.04.5 LTS
Intel(R) Xeon(R) Platinum 8375C CPU (x86_64/3.50GHz/(BOGO)5799MIPS/48K/1280K/55296K/2CPU/2Threads) aes,avx,avx2,avx512_bitalg,avx512_vbmi2,avx512_vnni,avx512_vpopcntdq,avx512bw,avx512cd,avx512dq,avx512f,avx512ifma,avx512vbmi,avx512vl,fma,mmx,sha_ni,sse,sse2,sse4_1,sse4_2,ssse3,vaes
MEMORY 4.00GB (SWAP: 0.00B)
DISK /dev/nvme0n1p1 ext4 (mount=/) 8.24GB/38.71GB(22%) INODE 257.64K/4.88M(6%)
NETWORK ens5 (MTU=9001) PUBLIC=18.176.115.32 PRIVATE=192.168.0.11

isu_b

Ubuntu 18.04.5 LTS
Intel(R) Xeon(R) Platinum 8375C CPU (x86_64/3.50GHz/(BOGO)5799MIPS/48K/1280K/55296K/2CPU/2Threads) aes,avx,avx2,avx512_bitalg,avx512_vbmi2,avx512_vnni,avx512_vpopcntdq,avx512bw,avx512cd,avx512dq,avx512f,avx512ifma,avx512vbmi,avx512vl,fma,mmx,sha_ni,sse,sse2,sse4_1,sse4_2,ssse3,vaes
MEMORY 4.00GB (SWAP: 0.00B)
DISK /dev/nvme0n1p1 ext4 (mount=/) 8.24GB/38.71GB(22%) INODE 257.64K/4.88M(6%)
NETWORK ens5 (MTU=9001) PUBLIC=3.114.249.23 PRIVATE=192.168.0.12

isu_c

Ubuntu 18.04.5 LTS
Intel(R) Xeon(R) Platinum 8375C CPU (x86_64/3.50GHz/(BOGO)5799MIPS/48K/1280K/55296K/2CPU/2Threads) aes,avx,avx2,avx512_bitalg,avx512_vbmi2,avx512_vnni,avx512_vpopcntdq,avx512bw,avx512cd,avx512dq,avx512f,avx512ifma,avx512vbmi,avx512vl,fma,mmx,sha_ni,sse,sse2,sse4_1,sse4_2,ssse3,vaes
MEMORY 4.00GB (SWAP: 0.00B)
DISK /dev/nvme0n1p1 ext4 (mount=/) 8.24GB/38.71GB(22%) INODE 257.64K/4.88M(6%)
NETWORK ens5 (MTU=9001) PUBLIC=52.199.247.203 PRIVATE=192.168.0.13
soudai commented 1 year ago

3台とも同じ構成 コア2個、メモリ4GなのでSSDが40GBで31GB空いてるから余裕はある

mackee commented 1 year ago
main.go の SQL一覧 ```sql SELECT * FROM `users` WHERE `id` = ?: L386 SELECT * FROM `users` WHERE `id` = ?: L400 SELECT * FROM `categories` WHERE `id` = ?: L411 SELECT * FROM `configs` WHERE `name` = ?: L424 INSERT INTO `configs` (`name`, `val`) VALUES (?, ?) ON DUPLICATE KEY UPDATE `val` = VALUES(`val`): L474 INSERT INTO `configs` (`name`, `val`) VALUES (?, ?) ON DUPLICATE KEY UPDATE `val` = VALUES(`val`): L484 SELECT * FROM `items` WHERE `status` IN (?,?) AND (`created_at` < ? OR (`created_at` <= ? AND `id` < ?)) ORDER BY `created_at` DESC, `id` DESC LIMIT ?: L532 SELECT * FROM `items` WHERE `status` IN (?,?) ORDER BY `created_at` DESC, `id` DESC LIMIT ?: L548 SELECT id FROM `categories` WHERE parent_id=?: L616 SELECT * FROM `items` WHERE `status` IN (?,?) AND category_id IN (?) AND (`created_at` < ? OR (`created_at` <= ? AND `id` < ?)) ORDER BY `created_at` DESC, `id` DESC LIMIT ?: L649 SELECT * FROM `items` WHERE `status` IN (?,?) AND category_id IN (?) ORDER BY created_at DESC, id DESC LIMIT ?: L666 SELECT * FROM `items` WHERE `seller_id` = ? AND `status` IN (?,?,?) AND (`created_at` < ? OR (`created_at` <= ? AND `id` < ?)) ORDER BY `created_at` DESC, `id` DESC LIMIT ?: L771 SELECT * FROM `items` WHERE `seller_id` = ? AND `status` IN (?,?,?) ORDER BY `created_at` DESC, `id` DESC LIMIT ?: L789 SELECT * FROM `items` WHERE (`seller_id` = ? OR `buyer_id` = ?) AND `status` IN (?,?,?,?,?) AND (`created_at` < ? OR (`created_at` <= ? AND `id` < ?)) ORDER BY `created_at` DESC, `id` DESC LIMIT ?: L875 SELECT * FROM `items` WHERE (`seller_id` = ? OR `buyer_id` = ?) AND `status` IN (?,?,?,?,?) ORDER BY `created_at` DESC, `id` DESC LIMIT ?: L897 SELECT * FROM `transaction_evidences` WHERE `item_id` = ?: L961 SELECT * FROM `shippings` WHERE `transaction_evidence_id` = ?: L972 SELECT * FROM `items` WHERE `id` = ?: L1034 SELECT * FROM `transaction_evidences` WHERE `item_id` = ?: L1086 SELECT * FROM `shippings` WHERE `transaction_evidence_id` = ?: L1096 SELECT * FROM `items` WHERE `id` = ?: L1147 SELECT * FROM `items` WHERE `id` = ? FOR UPDATE: L1165 UPDATE `items` SET `price` = ?, `updated_at` = ? WHERE `id` = ?: L1180 SELECT * FROM `items` WHERE `id` = ?: L1193 SELECT * FROM `transaction_evidences` WHERE `id` = ?: L1227 SELECT * FROM `shippings` WHERE `transaction_evidence_id` = ?: L1244 SELECT * FROM `items` WHERE `id` = ? FOR UPDATE: L1292 SELECT * FROM `users` WHERE `id` = ? FOR UPDATE: L1319 INSERT INTO `transaction_evidences` (`seller_id`, `buyer_id`, `status`, `item_id`, `item_name`, `item_price`, `item_description`,`item_category_id`,`item_root_category_id`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?): L1342 UPDATE `items` SET `buyer_id` = ?, `status` = ?, `updated_at` = ? WHERE `id` = ?: L1370 INSERT INTO `shippings` (`transaction_evidence_id`, `status`, `item_name`, `item_id`, `reserve_id`, `reserve_time`, `to_address`, `to_name`, `from_address`, `from_name`, `img_binary`) VALUES (?,?,?,?,?,?,?,?,?,?,?): L1430 SELECT * FROM `transaction_evidences` WHERE `item_id` = ?: L1482 SELECT * FROM `items` WHERE `id` = ? FOR UPDATE: L1502 SELECT * FROM `transaction_evidences` WHERE `id` = ? FOR UPDATE: L1521 SELECT * FROM `shippings` WHERE `transaction_evidence_id` = ? FOR UPDATE: L1541 UPDATE `shippings` SET `status` = ?, `img_binary` = ?, `updated_at` = ? WHERE `transaction_evidence_id` = ?: L1565 SELECT * FROM `transaction_evidences` WHERE `item_id` = ?: L1613 SELECT * FROM `items` WHERE `id` = ? FOR UPDATE: L1633 SELECT * FROM `transaction_evidences` WHERE `id` = ? FOR UPDATE: L1652 SELECT * FROM `shippings` WHERE `transaction_evidence_id` = ? FOR UPDATE: L1672 UPDATE `shippings` SET `status` = ?, `updated_at` = ? WHERE `transaction_evidence_id` = ?: L1702 UPDATE `transaction_evidences` SET `status` = ?, `updated_at` = ? WHERE `id` = ?: L1715 SELECT * FROM `transaction_evidences` WHERE `item_id` = ?: L1759 SELECT * FROM `items` WHERE `id` = ? FOR UPDATE: L1778 SELECT * FROM `transaction_evidences` WHERE `item_id` = ? FOR UPDATE: L1797 SELECT * FROM `shippings` WHERE `transaction_evidence_id` = ? FOR UPDATE: L1817 UPDATE `shippings` SET `status` = ?, `updated_at` = ? WHERE `transaction_evidence_id` = ?: L1842 UPDATE `transaction_evidences` SET `status` = ?, `updated_at` = ? WHERE `id` = ?: L1855 UPDATE `items` SET `status` = ?, `updated_at` = ? WHERE `id` = ?: L1868 SELECT * FROM `users` WHERE `id` = ? FOR UPDATE: L1973 INSERT INTO `items` (`seller_id`, `status`, `name`, `price`, `description`,`image_name`,`category_id`) VALUES (?, ?, ?, ?, ?, ?, ?): L1986 UPDATE `users` SET `num_sell_items`=?, `last_bump`=? WHERE `id`=?: L2011 SELECT * FROM `items` WHERE `id` = ? FOR UPDATE: L2061 SELECT * FROM `users` WHERE `id` = ? FOR UPDATE: L2081 UPDATE `items` SET `created_at`=?, `updated_at`=? WHERE id=?: L2102 UPDATE `users` SET `last_bump`=? WHERE id=?: L2113 SELECT * FROM `items` WHERE `id` = ?: L2123 SELECT * FROM `categories`: L2157 SELECT * FROM `users` WHERE `account_name` = ?: L2187 INSERT INTO `users` (`account_name`, `hashed_password`, `address`) VALUES (?, ?, ?): L2252 SELECT * FROM `transaction_evidences` WHERE `id` > 15007: L2294 ```

これの抽出ワンライナー

cat main.go | perl -nal -E 'say "$1: L$." if /"((?:SELECT|DELETE|UPDATE|INSERT)[^"]*)"/'
soudai commented 1 year ago

MySQL 5.7.40じゃん

soudai commented 1 year ago

bench実行前

SELECT count(*) FROM categories;
43

SELECT count(*) FROM configs;
0

SELECT count(*) FROM items;
50000

SELECT count(*) FROM shippings;
15007

SELECT count(*) FROM transaction_evidences;
15007

SELECT count(*) FROM users;
4000
soudai commented 1 year ago 
 systemctl list-units --type=service
UNIT                                           LOAD   ACTIVE SUB     DESCRIPTION
accounts-daemon.service                        loaded active running Accounts Service
acpid.service                                  loaded active running ACPI event daemon
apparmor.service                               loaded active exited  AppArmor initialization
apport.service                                 loaded active exited  LSB: automatic crash report generation
atd.service                                    loaded active running Deferred execution scheduler
blk-availability.service                       loaded active exited  Availability of block devices
cloud-config.service                           loaded active exited  Apply the settings specified in cloud-config
cloud-final.service                            loaded active exited  Execute cloud user/final scripts
cloud-init-local.service                       loaded active exited  Initial cloud-init job (pre-networking)
cloud-init.service                             loaded active exited  Initial cloud-init job (metadata service crawler)
console-setup.service                          loaded active exited  Set console font and keymap
cron.service                                   loaded active running Regular background program processing daemon
dbus.service                                   loaded active running D-Bus System Message Bus
ebtables.service                               loaded active exited  ebtables ruleset management
getty@tty1.service                             loaded active running Getty on tty1
grub-common.service                            loaded active exited  LSB: Record successful boot for GRUB
irqbalance.service                             loaded active running irqbalance daemon
isucari.golang.service                         loaded active running isucon9 qualifier main application in golang
keyboard-setup.service                         loaded active exited  Set the console keyboard layout
kmod-static-nodes.service                      loaded active exited  Create list of required static device nodes for the current kernel
lvm2-lvmetad.service                           loaded active running LVM2 metadata daemon
lvm2-monitor.service                           loaded active exited  Monitoring of LVM2 mirrors, snapshots etc. using dmeventd or progress polling
lxcfs.service                                  loaded active running FUSE filesystem for LXC
lxd-containers.service                         loaded active exited  LXD - container startup/shutdown
mysql.service                                  loaded active running MySQL Community Server
networkd-dispatcher.service                    loaded active running Dispatcher daemon for systemd-networkd
nginx.service                                  loaded active running A high performance web server and a reverse proxy server
polkit.service                                 loaded active running Authorization Manager
rsyslog.service                                loaded active running System Logging Service
serial-getty@ttyS0.service                     loaded active running Serial Getty on ttyS0
setvtrgb.service                               loaded active exited  Set console scheme
snap.amazon-ssm-agent.amazon-ssm-agent.service loaded active running Service for snap application amazon-ssm-agent.amazon-ssm-agent
snapd.apparmor.service                         loaded active exited  Load AppArmor profiles managed internally by snapd
snapd.seeded.service                           loaded active exited  Wait until snapd is fully seeded
snapd.service                                  loaded active running Snap Daemon
ssh.service                                    loaded active running OpenBSD Secure Shell server
systemd-journal-flush.service                  loaded active exited  Flush Journal to Persistent Storage
systemd-journald.service                       loaded active running Journal Service
systemd-logind.service                         loaded active running Login Service
systemd-modules-load.service                   loaded active exited  Load Kernel Modules
systemd-networkd-wait-online.service           loaded active exited  Wait for Network to be Configured
systemd-networkd.service                       loaded active running Network Service
systemd-random-seed.service                    loaded active exited  Load/Save Random Seed
systemd-remount-fs.service                     loaded active exited  Remount Root and Kernel File Systems
systemd-resolved.service                       loaded active running Network Name Resolution
systemd-sysctl.service                         loaded active exited  Apply Kernel Variables
systemd-timesyncd.service                      loaded active running Network Time Synchronization
systemd-tmpfiles-setup-dev.service             loaded active exited  Create Static Device Nodes in /dev
systemd-tmpfiles-setup.service                 loaded active exited  Create Volatile Files and Directories
systemd-udev-trigger.service                   loaded active exited  udev Coldplug all Devices
systemd-udevd.service                          loaded active running udev Kernel Device Manager
systemd-update-utmp.service                    loaded active exited  Update UTMP about System Boot/Shutdown
systemd-user-sessions.service                  loaded active exited  Permit User Sessions
ufw.service                                    loaded active exited  Uncomplicated firewall
unattended-upgrades.service                    loaded active running Unattended Upgrades Shutdown
user@1001.service                              loaded active running User Manager for UID 1001

LOAD   = Reflects whether the unit definition was properly loaded.
ACTIVE = The high-level unit activation state, i.e. generalization of SUB.
SUB    = The low-level unit activation state, values depend on unit type.

56 loaded units listed. Pass --all to see loaded but inactive units, too.
To show all installed unit files use 'systemctl list-unit-files'.
soudai commented 1 year ago

isu_a

root@ip-192-168-0-11:~# sudo ufw status
Status: inactive
root@ip-192-168-0-11:~# sudo ufw disable
Firewall stopped and disabled on system startup

isu_b

isucon@ip-192-168-0-12:~$ sudo ufw disable
Firewall stopped and disabled on system startup

isu_c

isucon@ip-192-168-0-13:~$ sudo ufw disable
Firewall stopped and disabled on system startup
tetsuzawa commented 1 year ago

url一覧

./main.go:325:17: pat.Post("/initialize")
./main.go:326:17: pat.Get("/new_items.json")
./main.go:327:17: pat.Get("/new_items/:root_category_id.json")
./main.go:328:17: pat.Get("/users/transactions.json")
./main.go:329:17: pat.Get("/users/:user_id.json")
./main.go:330:17: pat.Get("/items/:item_id.json")
./main.go:331:17: pat.Post("/items/edit")
./main.go:332:17: pat.Post("/buy")
./main.go:333:17: pat.Post("/sell")
./main.go:334:17: pat.Post("/ship")
./main.go:335:17: pat.Post("/ship_done")
./main.go:336:17: pat.Post("/complete")
./main.go:337:17: pat.Get("/transactions/:transaction_evidence_id.png")
./main.go:338:17: pat.Post("/bump")
./main.go:339:17: pat.Get("/settings")
./main.go:340:17: pat.Post("/login")
./main.go:341:17: pat.Post("/register")
./main.go:342:17: pat.Get("/reports.json")
./main.go:344:17: pat.Get("/")
./main.go:345:17: pat.Get("/login")
./main.go:346:17: pat.Get("/register")
./main.go:347:17: pat.Get("/timeline")
./main.go:348:17: pat.Get("/categories/:category_id/items")
./main.go:349:17: pat.Get("/sell")
./main.go:350:17: pat.Get("/items/:item_id")
./main.go:351:17: pat.Get("/items/:item_id/edit")
./main.go:352:17: pat.Get("/items/:item_id/buy")
./main.go:353:17: pat.Get("/buy/complete")
./main.go:354:17: pat.Get("/transactions/:transaction_id")
./main.go:355:17: pat.Get("/users/:user_id")
./main.go:356:17: pat.Get("/users/setting")
./main.go:358:13: pat.Get("/*")