domas mituzas Skip to content domas mituzas Menu me all talks tech query cache tuner blank lietuviškai catching top waits Modern systems are complicated beasts with lots of interdependent activities between threads, programs and kernels. Figuring out some problems is nearly impossible without building some time machine and crystal ball mix that tells exactly what happened. Did your cgroups CPU limit inject a sleep in the middle of mmap_sem acquisition by ps? Is everyone waiting for a mutex that is held by someone who is waiting for a DNS response? Did you forget to lock in your libnss.* libraries into memory and hence ended up stalling in unexpected place under memory pressure? Continue reading catching top waits Tweet Like Loading... Author Domas MituzasPosted on 2020/07/062020/07/06Categories mysqlTags bpf my talk on facebook infrastructure Continue reading my talk on facebook infrastructure Tweet Like Loading... Author Domas MituzasPosted on 2017/01/192017/03/14Categories facebook MySQL does not need SQL The DBMS part of MySQL is fine storage engines (especially with new kids on the block), replication, etc, but it really sucks at executing SQL. I wont even get into complex SQL that has complex data dependencies, will start with very basic tasks. SELECT * FROM table WHERE indexed = "A" LIMIT 1 If multiple indexes can satisfy the query, MySQL will hit each of them at least twice looking up first A and last A records. It will do that while planning the SQL execution. Once it comes up with a plan, it will go and hit the index it picked again, this time once. So, if you have two candidate indexes, you will have 5 index accesses at 4 positions. How would a direct cursor access work? Single index hit. Want to simulate that in SQL? You can add a FORCE INDEX hint, then only one index will be considered, but still at two positions and you will have three index accesses. I wrote about this before. This query: SELECT * FROM table WHERE indexed IN (101, 201, 301) LIMIT 1 Would do six index dives during preparation as well (two for each IN position), plus another one during execution seven dives when one would suffice. What we learn from this is that whenever theres an opportunity for lazy evaluation, MySQL will gladly miss it and do most work possible to run your query. If you could express it yourself, youd be lazy and youd be efficient. It gets even worse if we start expecting something more advanced than a basic cursor positioning. Whenever youre doing a range scan on (a, b) indexed table: SELECT * FROM table WHERE a BETWEEN 1 and 100 AND b=1234567 LIMIT 1 Theres an easy optimization given low-cardinality a you jump to each a position and then you can do a dive for the b value. In a way you can emulate this behavior with: SELECT * FROM table JOIN ( SELECT DISTINCT a FROM table ) x USING (a) WHERE b=1234567 LIMIT 1 As I mentioned, MySQL will skip any opportunity to be lazy and in this case it will fully materialize all distinct a values. If it were able to lazy evaluate, theres a chance we can terminate the scan early. Were adding ability to skip-scan records in our builds (you can follow the work at https://reviews.facebook.net/D59877). It is quite easy to describe whatever is needed in basic loop though storage engines already provide with necessary hooks for these types of access methods. Another basic single-table SELECT that fails with SQL is a standard feed query: SELECT * FROM table WHERE category IN (5,6) ORDER BY time DESC LIMIT 5; MySQL will have to pick between two alternatives, one would be just scanning time index and searching for specified categories among found rows, another is read all entries for each category, sort them all and return top 5. Efficient way to execute such query would involve having per-category cursors and merging their reads, something like: ( SELECT * FROM table WHERE category = 5 ORDER BY time DESC LIMIT 5 ) UNION ALL ( SELECT * FROM table WHERE category = 6 ORDER BY time DESC LIMIT 5 ) ORDER BY time DESC LIMIT 5 MySQL is unable to merge these two cursors without writing all the data into temporary file and sorting it although we already are reading much smaller datasets than with alternative naive (and readable) query. Besides, each subselect will open a table handler with all the associated buffers and if you want to merge hundred cursors youre looking at hundred open tables. You can do this operation efficiently in bash (with sort -m), it isnt that complicated algorithm in any scripting language, but having such access method in MySQLs SQL doesnt seem likely. Even where MySQL is already doing efficient loose scan (like in indexed-based GROUP BY), there were basic bugs open for years where efficiency would go down the drain simply because a LIMIT is added. Therere quite a few other limitations in access methods that are quite annoying to work around. For example a query like this one: SELECT * FROM table ORDER BY priority DESC, time ASC Would be unable to use a regular index on (priority, time) it can only walk everything in one direction and cannot have mixed order scans that are trivial to implement in basic procedural algorithm (move cursor to lowest time of lower priority, read all records for that priority in ascending order before repositioning the cursor to even lower priority). Of course, one can change the direction of an index, or even have multiple indexes on same columns just to get an ordered read efficient. But none of that is needed if theres a proper access method that can be used by SQL execution. Directional index hints may be needed just to specify common order (e.g. prefix compression in RocksDB makes one direction cheaper than the other, although both still much cheaper than full blown filesort). Even basic single order traversal breaks if youre joining two tables: SELECT * FROM t1 JOIN t2 USING (b) ORDER BY t1.a, t1.b, t2.c In some cases I (*shudder*) had to use FORCE INDEX without an ORDER BY to pick a direction I needed. One could expect basic functionality like ordered JOIN traversal to be part of SQL package. Therere various other issues related to JOINs again, in the best tradition of MySQL, lazy evaluation of joins is not possible it will gladly do more work than youd ever need, reading from tables you did not ask for. These things slowly slowly change with each version, and to get basic things right we have to yell a lot. Hurray, 5.7 may finally have fixes for issues we were working around back when 5.x series did not exist) but that is just way too slow for an SQL layer to evolve. MySQL employs many bright engineers many of whom are trying to make better cost decisions on same existing limited set of access methods they use, by spending more cycles and adding more and more overhead. In order to execute various queries one has to either resort to network roundtrips after each row or over-fetch data all the time. You may suggest stored procedures, but their data access is limited to running SQL queries and returning them as multiple result sets (or buffer everything in temporary table). Amount of deferred work and lazy evaluation you can do in a stored procedure is quite limited and overhead of running many SQL statements within a procedure is high. Ability to access data via lower level interfaces by high performance server-side language (Lua? JS?) would allow to circumvent many many many limitations and inefficiencies of existing SQL optimizer and execution layer while still allowing occasional SQL access, high performance storage engines, replication and all the tooling that has been built to support MySQL at scale. What do we do today? We run lots of that conditional logic in our application and absorb lots of the cost with a cache layer in the middle. Theres a chance that we would be able to spend less CPU, less I/O, less memory and less network resources if we could ask smarter queries expressed as procedures instead of wrangling all the relational algebra on top of dumb executor. In some cases we have different database systems. P.S. Many of the hypothetical scenarios map to workloads where amounts of data and query volume warrants all the optimizations I discussed here. P.P.S. Other databases may have other set of issues. Tweet Like Loading... Author Domas MituzasPosted on 2016/08/03Categories facebook, mysqlTags sql4 Comments on MySQL does not need SQL linux memory management for servers Weve been learning for many years how to run Linux for databases, but over time we realized that many of our lessons learned apply to many other server workloads. Generally, server process will have to interact with network clients, access memory, do some storage operations and do some processing work all under supervision of the kernel. Unfortunately, from what I learned, therere various problems in pretty much every area of server operation. By keeping the operational knowledge in narrow camps we did not help others. Finding out about these problems requires quite intimate understanding of how things work and slightly more than beginner kernel knowledge. Many different choices could be made by doing empiric tests, sometimes with outcomes that guide or misguide direction for many years. In our work we try to understand the reasons behind differences that we observe in random poking at a problem. In order to qualify and quantify operational properties from our server systems we have to understand what we should expect from them. If we build a user-facing service where we expect sub-millisecond response times of individual parts of the system, great performance from all of the components is needed. If we want to build high-efficiency archive and optimize data access patterns, any non-optimized behavior will really stand out. High throughput system should not operate at low throughput, etc. In this post Ill quickly glance over some areas in memory management that we found problematic in our operations. Whenever you want to duplicate a string or send a packet over the network, that has to go via allocators (e.g. some flavor of malloc in userland or SLUB in kernel). Over many years state of the art in user-land has evolved to support all sorts of properties better memory efficiency, concurrency, performance, etc and some of added features were there to avoid dealing with the kernel too much. Modern allocators like jemalloc have per-thread caches, as well as multiple memory arenas that can be managed concurrently. In some cases the easiest way to make kernel memory management easier is to avoid it as much as possible (jemalloc can be much greedy and not give memory back to the kernel via lg_dirty_mult setting). Just hinting the kernel that you dont care about page contents gets them immediately taken away from you. Once you want to take it back, even if nobody else used the page, kernel will have to clean it for you, shuffle it around multiple lists, etc. Although that is considerable overhead, it far from worst what can happen. Your page can be given to someone else for example, file system cache, some other process or kernels own needs like network stack. When you want your page back, you cant take it from all these allocations that easily, and your page has to come from free memory pool. Linux free memory pool is something that probably works better on desktops and batch processing and not low latency services. It is governed by vm.min_free_kbytes setting, which has very scarce documentation and even more scarce resource allocation on 1GB machine you can find yourself with 5% of your memory kept free, but then therere caps on it at 64MB when autosizing it on large machines. Although it may seem that all this free memory is a waste, one has to look at how kernel reclaims memory. This limit sets up how much to clean up, but not at when to trigger background reclamation that is done at only 25% of free memory limit so memory pool that can be used for instant memory allocation is at measly 16MB just two userland …